hello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
What happens if you remove ";"?
|||hi,
thx for responding..i figured out the problem and i solved it..
bye
Sweety
I have 2 servers. DWTEST and DWSQL1. I developed my packages connecting to DWTEST in the design time. I have 32 packages, one of them is the master one and the others are child packages.
package PM calls P1, P2, P3,.......P31
i have 2 data source in my project. RDS and S0, both setup to connect DWTEST in the design. i defined rds_connection_string and s0_connection_string variables in my master package and created an XML config file and put those variables into that config file.
C:\PROJECTS\DEVELOPMENT\SSIS Configurations\SERVER.dtsConfig
<DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
built the project into directory C:\PROJECTS\DEVELOPMENT\S1_REUSABLE_DATA_STORE\S1_REUSABLE_DATA_STORE\bin\Deployment
when i deploy my packages into DWTEST and run them as a SQL Server Agent job they work because data sources are signing to DWTEST already.
when i am deploying them(using the same manifest file in the deployment directory i wrote above) into the server DWSQL1, i select config file source as:
C:\PROJECTS\TESTING\SSIS Configurations\SERVER.dtsConfig
and update data source strings in it to DWSQL1:
- <DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC\tsql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
When i run SQL Server Agent Job i defined in DWSQL1(forcing it to use DWSQL1 data source in step definition), my master package PM connects to DWSQL1, and my child packages don't use my config definitions and connect to DWTEST which is defined in the design time.
How can i force all my packages to connect DWSQL1 server using the same built files in deployment directory WITHOUT editing my packages(recreating config files or changing the definition of Data Sources and then built the project again) in Visual Studio?
Thanks to you for reading my long question...
I got a solution for my problem from a friend.
Now, i am using XML configuration file in my Master package, and Parent Package Variables in child packages. Child packages gets their data source connection strings from these variables.
Then i am keeping two different copies of XML config file in different servers(DWTEST and DWSQL1) physically.
In design environment my data source conenctions setup to connect to DWTEST. I build my project, then using the Deployment Manifest i deploy my packages into server DWTEST.
In SQL Server Agent Job Definition, in Step definition, at Configurations Tab page i add a config file and navigate to the config file stored in DWTEST(which is using DWTEST in values) and it works in DWTEST. all my child packages also connects to DWTEST.
Then i deploy my packages into DWSQL1 server using the same Deployment Manifest without editing anything in visual studio an building the project again.
in DWSQL1, in SQL Server Agent Job Definition, in Step definition, at Data Sources Tab page i check the checkboxes and edit the strings to connect to DWSQL1, and at Configurations Tab Page, i add a config file and navigate to the config files stored in server DWSQL1(which is using DWSQL1 in values). Then my packages are all working well and connecting to DWSQL1.
So it seems my problem has been solved.
|||I was just thinking, why not make all the packages p1,p2...p30 point to the same configuration file as that's used by PM?
When you deploy using deploymentmanifest, you'll only change the config file once, and since all the packages are looking at the same package, they'll pick up the new settings.
That way, in case you make changes to the config file on DWTEST during development, you don't need to worry about forgetting to update the xml file in dwsql.
Just thinking out loud. :)
sqlHi,
I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.
Does anybody have any suggestions as to why this is happening and what the way to overcome this.
Thanks in advance
MNJ
Hi,
I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.
Does anybody have any suggestions as to why this is happening and what the way to overcome this.
Thanks in advance
MNJ
Hi,
I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.
Does anybody have any suggestions as to why this is happening and what the way to overcome this.
Thanks in advance
MNJ
If I save a report using a cube in an excel spreadsheet, the next time when I open it, and try to drag a dimension, I cannot see any data. I need to refresh the document every time
I process the cube.
Is there way we can automatically refresh these reports ? Has anyone had this problem
before?
I'll appreciate your help.
thanks,
Rok
In 2007, you can configure your connection to refresh data everytime the file is opened or on a fixed interval. In 2003, this appears as a Pivot Table option.
B.
Hello, I am trying to deploy a SSIS package (using deployment utility create at build time) to a different server and ran into problem wonder if someone and direct me to the right path.
development environment: WIN XP, SQL 2005. package is built using server buisiness intelligence. Package content: connect to a "target"SQL2005server using OLE DB using SQL Login authentication to process data. The SQL 2005 DB is on a Win 2003 server (R2) as OS platform.
The package was built, install ( SQL not file system)and ran fine on the development environement; when attempt to deploy the package to the SQL 2005 server where data reside and should be processed (also install to SQL), I receive error " 0x80004005 ...description TCP Provider: No connection could be made because the target machine activily refuse it". which is an odd error since the package is on the server that it try to connect to the data base on it.
If anyone can give me some hint at what cause this problem, appreciate the help.
Not sure, but have you checked that the SQL Server Integration Services service is running on the target machine?|||Yes, that the first thing I check, via surface area configuration and administration tools -> Services both confirmed that the Ingergrated services is started and running.I am getting the following error message every time I try to create a stored procedure - any stored procedure:
Msg 6354, Level 16, State 10, Procedure AuditOperations, Line 14
Target string size is too small to represent the XML instance
A search of books online and MSDN didn't return anything.
Thanks.
Please post some code.
Adamus
|||OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.
CREATE PROCEDURE ListEmployeesByDepartment
@.DepartmentName NVARCHAR(50)
AS
SELECT c.Lastname, c.FirstName
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory h
ON e.EmployeeID = h.EmployeeID
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
WHERE d.name = @.DepartmentName AND h.EndDate IS NULL
ORDER BY 1
|||TennesseeSQL wrote:
OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.
Do you have any triggers on the table?
If so, disable them temporarily.
Adamus
|||The message indicates the object in which the error happened. So check "AuditOperations" SP or trigger code.|||Geez! How stupid?! I'm working through examples preparing for the 70-441 test. That was it. Thanks so much!Hi,
I've been trying to write a stored proc to no avail. I'm trying to set a local variable and every time I try to execute the stored proc I get the following error:
Running [dbo].[StoredProcedure2].
Conversion failed when converting the nvarchar value 'TEST2' to data type int.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[StoredProcedure2].
Here's the code (which I skimmed down to try and pinpoint where the issue was).
Initially, I was trying to assign a numeric string value stored as nvarchar(10) and anytime the value would be over '2147999999' (in that area) it would bomb... Now even this simple code won't work and I can't understand why. If anyone would be kind enough to enlighten me ;-)
ALTER PROCEDURE dbo.StoredProcedure2
AS
DECLARE @.ME NVARCHAR(10);
SET @.ME = 'TEST2';
RETURN @.ME
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.
RETURN [ integer_expression ]
Madhu
|||The value following the RETURN keyword MUST be an integer value.
Strings are not allowable RETURN values.
Instead use:
Code Snippet
ALTER PROCEDURE dbo.StoredProcedure2
AS
DECLARE @.ME NVARCHAR(10);
SET @.ME = 'TEST2';
SELECT @.Me
GO |||It always better to use OUTPUT parameter, or Select statement to get the single row & column data from the SP. Return only supports integer.
NOTE:
PRINT – you can’t get the result on the UI. It will get suppressed.
Hi,
I have two smalldatetime fields starttime and endtime
I want to display them like
select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest
the data in the field is
starttime endtime
1/3/2006 9:00:00 1/3/2006 6:00:00
the result I am getting is
09:00:00 to 18:00:00
Where as I want it something like this
9.00 AM to 6.00 PM
Is it possible? what query should be used for this?
Hi,
by taking a look to BOL ...
PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(nvarchar(30),GETDATE(),109)
HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM
....
DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME
SET @.ST = '1/3/2006 9:00:00'
SET @.ET ='1/3/2006 18:00:00'
SELECT (CONVERT(CHAR(11),@.ST,111) +
SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +
CONVERT(CHAR(11),@.ET,111) +
SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))
-
2006/03/01 9:00AM To 2006/03/01 6:00PM
|||
What if i have an input like:
time
0910
1000
0530
0620
How do i convert this input into smalldatetime?
|||Here is one option (uses the default date of [01/01/1900]):
Code Snippet
DECLARE @.MyTable table
( MyTime varchar(4) )
SET NOCOUNT ON
INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )
SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable
Hi,
I have two smalldatetime fields starttime and endtime
I want to display them like
select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest
the data in the field is
starttime endtime
1/3/2006 9:00:00 1/3/2006 6:00:00
the result I am getting is
09:00:00 to 18:00:00
Where as I want it something like this
9.00 AM to 6.00 PM
Is it possible? what query should be used for this?
Hi,
by taking a look to BOL ...
PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(nvarchar(30),GETDATE(),109)
HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM
....
DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME
SET @.ST = '1/3/2006 9:00:00'
SET @.ET ='1/3/2006 18:00:00'
SELECT (CONVERT(CHAR(11),@.ST,111) +
SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +
CONVERT(CHAR(11),@.ET,111) +
SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))
-
2006/03/01 9:00AM To 2006/03/01 6:00PM
|||
What if i have an input like:
time
0910
1000
0530
0620
How do i convert this input into smalldatetime?
|||Here is one option (uses the default date of [01/01/1900]):
Code Snippet
DECLARE @.MyTable table
( MyTime varchar(4) )
SET NOCOUNT ON
INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )
SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable
Hi all,
I have a time column in CHAR(4) format. The contents are stored as 'military time':
1800
1830
2130
I tried using various functions, but was not able to convert to standard time.
I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:
2006-05-31 06:30 PM
Any help would greatly be appreciated.
Thanks.
- gshaf
Your military time conversion wont work because you dont have a colon separating your hours and minutes.
Try this statement:PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)
Try this one instead, this will add a zero to your hour:
IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
Which prints out
2006-05-31 09:30PM
Your time column is going to need that colon. Otherwise your military times will not be output properly.
|||
Worked! Just needed to correct the formatting.
Thanks so much Elliot!
- gshaf
