Command Execute Fails The Second Time It Is Invoked
Jan 15, 2008
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
Hey everyone --I've been having this problem for a couple weeks and was hoping maybesomeone can help out a little bit.Synopsis:This SQL agent job contains code to perform a full database backup withverification and is writing to a local disk array. I typically also usesome code for reporting - declaring variables and inserting them intoa linked server database that I use to monitor all my database backups.I have tested and used the code repeatedly and am confident it works.I have ran the code through Query Analyzer and it completes withsuccess.I have removed reporting code & used only the backup statement & itfails.I have 4 other Agent jobs doing other various tasks that function asexpected.I have tried deleting & recreating the job & it still fails.When I enable logging the application log has only one entry for SQLAgent:Event Type:WarningEvent Source:SQLSERVERAGENTEvent Category:Job EngineEvent ID:208Date:1/4/2005Time:9:36:00 AMUser:N/AComputer:ComputerNameDescription:SQL Server Scheduled Job 'DBName Full DB Backup'(0xA4B14FB5A5DE964B8340423E4AC4B67B) - Status: Failed - Invoked on:2005-01-04 09:36:00 - Message: The job failed. The Job was invoked byUser domainUserName. The last step to run was step 1 (Step 1).Any help or ideas would be greatly appreciated. I'm racking my brainhere and for some reason just not able to think of what the problem is.ChaddJoin Bytes!
I have the code as fallows to update my SQL data ;Sub Kaydet(ByVal TickerKod As String, ByVal op1 As Double, ByVal op2 As Double, ByVal op3 As Double, ByVal op4 As Double, ByVal op5 As Double, ByVal op6 As Double, ByVal op7 As Double, ByVal mov1 As Double, ByVal mov2 As Double) 'Dim nop1 As Decimal = FormatNumber(Replace(op1, ",", "."), 2)Dim mysource As New SqlDataSource mysource.ConnectionString = ("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Stock.mdf;Integrated Security=True;User Instance=True")mysource.UpdateCommand = ("UPDATE StockParametre SET OPT1 = " & FormatNumber(op1, 2) & ", OPT2 = " & FormatNumber(op2, 2) & ", OPT3 = " & FormatNumber(op3, 2) & ", OPT4 = " & FormatNumber(op4, 2) & ", OPT5 = " & FormatNumber(op5, 2) & ", OPT6 = " & FormatNumber(op6, 2) & ", OPT7 = " & FormatNumber(op7, 2) & ", MOVY = " & FormatNumber(mov1, 2) & ", MOVD = " & FormatNumber(mov2, 2) & " WHERE (Stock = '" & TickerKod & "')") mysource.Update()Response.Write(mysource.UpdateCommand & "<br>") End Sub When I call this code inside of Virtual Web Developer it functions perfect and updates the data..There is no problerm But when I call it like http:// .......myip/updatedata.aspx then I have the error " System.Data.SqlClient.SqlException: Incorrect Syntax near 32 I see that 32 is the value inside the update command like SET OPT1=107,32, OPT2=25,00, ........and so on Where do I make wrong ? Thanks
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.
Hi everyone: I have a very simple FTP Task that is supposed to grab two files from an ftp server and save them locally on my machine. The problem is that when I test the connection everything works okay (I know that this connection "hides" the password when seen on the Connection Manager Editor, so I re-enter it everytime I want to test the connection). I can see in the Task property that the username and passwords are set, but regardless I always get this error message: Error: 0xC002918F at Cost Files Transfer, FTP Task: Unable to connect to FTP server using "FTP Bread".
I have a SQL DTS package that imports a text file as one wide column into a two column SQL table, one is an identity seed. So essentially I import then parse the data using the index key as it is 3 seperate lines of data. All works fine by running the DTS package using execute. When I schedule this as a job it fails. It indicates that I have additional white space after a column. Any thoughts on why this can ocurr?
I have created a DTS task that i can right click on and execute and it works fine on the server. However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
I've got here a strange Problem. If I try to execute the SQL Server Agent Job, that executes my SSIS Package, it fails. The job succeedes when I run the Job as the Proxy, that maps on the User, that has deployed the Package, or when I run the Job under an System Administrator Proxy. Now my Question - how must I set up an Service-Account, which is no Admin and not has deployed the Package?
I already know, that the User has to be in the sysadmin role, and in all msdb SQLAgent*
I've managed to get the basics coded into my package. Nothing fancy, it is all quite literally embedded inside the package. No config files, parameters, variables, etc. I've imported the package into SSIS. I can right click on the package and select Run Package. The package fires off, validates, runs through the entire process, and completes successfully. However, when I set up the package to run as a scheduled task, it fails almost immediately with the following output.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:28:07 AM Error: 2007-04-25 03:28:07.41 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSroperty" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-04-25 03:28:11.36 Code: 0xC001602A Source: Package Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-04-25 03:28:11.36 Code: 0xC002918F Source: Card10 FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:28:07 AM Finished: 3:28:11 AM Elapsed: 4.297 seconds
I have added a package from SQL Server to another package as an "Execute Package Task" it fails with this error:
Error: Error 0xC0014062 while preparing to load the package. The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'nVision_AgentPump' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed. .
I tried setting the Protection Level to "don't save sensitive", and running the package in process and out. Any idea where to look?
I have a package (i.e. child package) which runs itself perfectly fine without displaying any error.
However when I embeded this package inside another package (i.e. parent package) using Execute Package Task. The task always fails. It seems strange enough.
The child package has two variables that need to be passed in from parent package.
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 have a Execute SQL Task that calls a stored procedure. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. It has a date parameter, but hard coding the date parameter does not work either. I checked the permissions on the Stored Procedure and SQL Agent, which are ok. When I change BypassPrepare to True, the query will execute and insert the rows to the required table. If someone has an explanation for this, I would appreciate some sql enlightenment. Thanks.
I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.
Hi, 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? thanks in Advance
THE STORED PROCEDURE SAMPLE ALTER PROCEDURE [dbo].[usp_CMSTemplateCreateNewTemplate] @SiteID AS INT, @PageID AS INT, @Title AS NVARCHAR(50), @EditHREF AS NVARCHAR(512), @CreatorID AS INT, @StartDate AS DATETIME, @EndDate AS DATETIME, @Child AS INT,@PageTemplateID AS INT OUTPUT AS
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?
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.
We executed windows scheduled batch failes that execute as DOMAINuser1 to dtsexec packages.
I moved these packages to run in sql jobs.
The agent runs as local system. In order to get the packages to run since they are encryoted with DOMAINuser1 key.
I created a DOMAINuser1 credential and a proxy.
I execute the SSIS package job steps under this proxy.
One day it all works the next day they fail randomly with the "unable to decrypt with user key" error.
I am very familiar with this error, however it makes no sense, thses packges are not being touched, no one is altering them and perhaps saving them with different user keys. They are all encrypted with the DOMAINuser1 key. One day everything works the next day they fail.
A master package will run and in the middle of executing other package it suddenly gets this error, like the proxy suddenly stops working.
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
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
