I need to execute some store procedures I have in the SQL editor but I seem to been having problems with the formatting for datetime variables needed for the execution of my code. can anyone please help?
I want to write some SQL which results in an automatic conversion of adatetime to a string in a format suitable for the Language of theconnection (either by explicitly setting the Language in theconnection string, or by setting the default language in for the userused for the connection.)The casting from string to datetime uses the language setting:Data Source=localhostsqlexpress;Initial Catalog=master;PersistSecurity Info=True; Language =BRITISH ENGLISH; Trusted_Connection=yes;Application Name = Test Application;select cast('13/01/2008' as datetime) --WORKS as expectedselect cast('01/13/2008' as datetime) --FAILS as expectedandData Source=localhostsqlexpress;Initial Catalog=master;PersistSecurity Info=True; Language =ENGLISH; Trusted_Connection=yes;Application Name = Test Application;select cast('13/01/2008' as datetime) --FAILS as expectedselect cast('01/13/2008' as datetime) --WORKS as expectedbut implicit casting the other way ignores the "Language setting" (iethe format is the same for both):BRITISH ENGLISHselect cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Jan 122008 12:00AMselect convert(nvarchar(max),cast('12/01/2008' as datetime)) --Jan 122008 12:00AMENGLISHselect cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Dec 12008 12:00AMselect convert(nvarchar(max),cast('12/01/2008' as datetime)) -- Dec 12008 12:00AMIs it possible to tell SQL Server "For language w convert datetimes tostrings using format x, but for language y use format z?"Regards,Dan
Hi,I want to change 2 default behaviors of ms-sql server within a connection.1. All varchar (etc.) fields should be returned as UTF-8.I know about field attribute collation and convert function,but I want simple query like:SELECT * FROM table;to return all varchars as UTF-8, no matter how the fields weredefined and which collation.In MySQL I execute: "SET NAMES 'utf-8';" after connecting to serverIn PgSQL I use native function: pg_set_client_encoding()2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"I just want all datetimes fields were returned as:"2008-04-20 12:01:02"I can do that with function convert() (style=20) but I want(as in question above) a simple query like:SELECT * FROM table;to return date and time formatted as described above.In MySQL and PgSQL the format "2008-04-20 12:01:02" is the defaultone.Is it possible to do that? If yes, please tell me how can I do that?Wladyslaw
Part of my query is : SELECT * FROM TableA WHERE ColumnA >= DATEADD(DAY, - 30, GETDATE())
With the expression at the where clause above, you can pull a rolling 30 days data without having to supply values. Now users of the report want to see it represented like: 2nd April – 1st May
when the report is ran. the requirement is not to use a parameter for the reportKnowing that I have no parameters, how do I reference ">= DATEADD (DAY, - 30, GETDATE())" to reflect the start date and the end date in the report?
SET IDENTITY_INSERT table name ON I use this to run it:
Code Snippet
.
.
.
Dim command As SqlCommand = New SqlCommand("SET IDENTITY_INSERT table name ON", msSqlConexion) command.ExecuteNonQuery() It doesn't throw any kind of errors, but it actually don't execute it, and If I run it on console it works ok.
I was not able to find the solution myself so here I am to ask you.
I'm using an Ole DB Source with the SQL command from variable.
My variable expression needs to look like this:
select * from dbo.fx_function (SomeDateVariable)
My sql server fx_function recieves as a parameter datetime, my variable SomeDateVariable is datetime type.
I tried all different types of cast on @User :: SomeDateVariable but I either can't evaluate the expression or I have errors trying to execute the package.
I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database. It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message Line 2: Incorrect syntax near 'GO'. Line 4: Incorrect syntax near 'GO'. Line 8: Incorrect syntax near 'GO'. 'CREATE FUNCTION' must be the first statement in a query batch. Must declare the variable '@usb'. Must declare the variable '@usb'. Must declare the variable '@i'. A RETURN statement with a return value cannot be used in this context. Line 89: Incorrect syntax near 'GO'. Line 91: Incorrect syntax near 'GO'. Line 94: Incorrect syntax near 'GO'.
Protected Sub Install() Dim err As String = "" While err.Length < 1 ' Dim your StreamReader Dim TextFileStream As System.IO.TextReader 'Load the textfile into the stream TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql") 'Read to the end of the file into a String variable. executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful" End While If err = "Susscessful" Then Response.Redirect("Default.aspx") Else Me.lblError.Text = err End If End Sub Private Function executesql(ByVal s As String, ByRef err As String) As Boolean Try Dim conn As New Data.SqlClient.SqlConnection(GenConString()) Dim cmd As New Data.SqlClient.SqlCommand(s, conn) conn.Open() cmd.ExecuteNonQuery() conn.Close() Return True Catch ex As Exception err = ex.Message.ToString Return False End Try End Function
Example sql file SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[MyFunc] GO CREATE FUNCTION [dbo].[MyFunc] ( -- Add the parameters for the function here
) RETURNS varchar(1000) AS BEGIN -- Declare the return variable here DECLARE @Result varchar(1000) -- Add the T-SQL statements to compute the return value here -- Do something here -- Return the result of the function RETURN @Result END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command, operable program or batch file." This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full.. waiting for reply
I wanted to know if there is a way to execute sql commands on the operating system's command line. If it is possible, then how do we do it ? For example to execute a SELECT * from Table statement what are we supposed to do ?
I'm wondering if there is any way for me to execute any type of command (delete, insert, create, alter, etc) on management studio without having to wait the server answer.
On Oracle, I use DBMS_JOB. On SQL Server, do I have to create a SQL SERVER Agent Job? What if I don't have permission to create that kind of jobs?
Hello, the following code works perfectly in SQL Server 2000 and SQL Server 2005 Express over WinXP but when run against an instance of SL Server2005 Express over Win2003Server, the first time Command.Execute is invoked returns no error (even though no action seems to be take by the server), subsequent calls return the error -2147217900 couldn't find prepared instruction with identifer -1 (message may vary, it is a translation from may locale)
Any ideas? Thanks
Code Block Public Sub Insert_Alarm(sIP As String, nAlarm As Long) Static cmdInsert As ADODB.Command Static Initialized As Boolean On Error GoTo ErrorHndl If Not Initialized Then Set cmdInsert = New ADODB.Command Set cmdInsert.ActiveConnection = db cmdInsert.Parameters.Append cmdInsert.CreateParameter("IP", adVarChar, adParamInput, Len(sIP), sIP) cmdInsert.Parameters.Append cmdInsert.CreateParameter("Alarm", adInteger, adParamInput, , nAlarm) cmdInsert.CommandText = "insert into ALARMS(date_time,ip,alarm,status) values (getdate(),?,?,1)" cmdInsert.CommandType = adCmdText cmdInsert.Prepared = True Initialized = True End If cmdInsert.Parameters(0).value = sIP cmdInsert.Parameters(1).value = nAlarm cmdInsert.Execute Exit Sub ErrorHndl: ... End Sub
Hi! I try to find out how to write an tsql program that sends dmx-ddl to SSAS. this works: select * from openquery(bdjOLAP, 'select <col> from <modelname>.content') because it returns an resultset.
but how to something similar this (would fail, because no result set is returned): select * from openquery(bdjOLAP, 'create mining structure...')
We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure @Name varchar(50) AS INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate()) SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a SELECT.
Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement. However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.
I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.
I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.
I'm looking at http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx and http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx trying to understand where we are at using datetime overrides in ssis from the command line. One of these articles suggests something has been fixed in ssis sp1, what part of this issue was fixed?
Can you override (from the command line) a user var in ssis whose ValueType property is datetime? Or do some of the older articles still apply, ie the user var must be string so that ssis wont create a separate user var with the same name etc etc? And if it is string, does some kind of conversion need to be done to compare it against datetime cols in t-sql inside the pkg?
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this: sqlCmd.CommandType = CommandType.Text sqlCmd.Parameters.Add(sqlPar) sqlCmd.ExecuteNonQuery()
With this sql command: "exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question: if it's not possible, how can I pass a Null value to stored procedure? This code: sqlPar = new SqlParameter("@id", SqlDbType.Int) sqlPar.Direction = ParameterDirection.Output cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull) cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error: BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this? Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Hi, I need to send a table data into flat and then ftp into different location. I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error. Could you advice me regrding network engineer thought and any solution for avoiding this error.
--------------------------- Execute Process Task: C:WINDOWSsystem32ftp.exe --------------------------- CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0. --------------------------- Thanks,
Hi, I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method.
Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute().
Am I missing something or is this just not possible?
We connect to the remote database servers through the network from loca by using Query Analyzer. Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data. But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer We do not know what happened but i think due to network updates this command is not able to execute...
For ex: Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute
Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?
Note : we are able to execute this command on remote Query Analyzer but not from local QA
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:
cd C:Trandev otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I C:Trandev represents the remote environment
I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.
SSIS:
I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.
Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP
I'm very new to SSIS and I€™m trying to do the following in a SQL task
RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10
I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?
I am designing an SSIS package and need to change the value of a DateTime global variable with the value returned from an Execute SQL task. The queary is as follows:
Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock) where name = '@name'
The error message is as follows:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at VersionDate_Set, Execute SQL Task: Executing the query "Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'" failed with the following error: "The type of the value being assigned to variable "User::VersionDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: VersionDate_Set
SSIS package "Package.dtsx" finished: Success.
The variable is set to data type DateTime, but the value will not change. Any ideas?
How to execute ssis package from command prompt and also pass configuration file to it and set logging to ssis log provider for sql server. Writing all those options with cmd.
