Error While Executing A A Query String Using EXEC Statement
Sep 18, 2007
Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc
The follwing statement works fine if i give the table name directly in the query
Code Snippet
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below
Code Snippet
@Category - Will be passed as a parameter to the stored proc
Print @vsBulkSQL - This prints the folliwing statement
INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
Exec @vsBulkSQL - This statement gives the following error
The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same
Hi, I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties Result Set : None
ConnectionType : OLEDB Connection : Connected to a Local Database (DB1) SQLSourceType : Direct Input SQL Statement : exec(?) IsQueryStorePro : False BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare.
Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows. Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor. Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."
HiI'm executing SP_SpaceUsed in a stored procedure like this :Exec ('SP_SpaceUsed '+ @table)This works great but when i want to execute it for a table in a otherdatabase i'm running in to troubles. Things i tried is this :Exec ('USE <DB> ; SP_SpaceUsed '+ @table) -->not working (uncorrectsyntax)Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @table) -->not working(uncorrect syntax)Exec ('SP_SpaceUsed <DB>.dbo.'+ @table) --> not working (uncorrectsyntax)Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @table) -->not working(uncorrect syntax)Could someone give me a clueThanx,Hennie de Nooijer
Does anyone know if this is a SQL Native Client bug? I've read a previous thread (posted back on Jan. 18th, 2007) about this error, but there are no replies. I am getting this error when I issue the sp_setapprole command using sqlexecdirect() ODBC api call. Is there any way to work around this? Or is there a fix to the SQL Native Client? The error 15002 message text states "The procedure sys.sp_setapprole cannot be executed within a transaction". This is on a new connection so there should be no transactions active at the time. Thanks in advance for any info anyone can provide on this.
declare @AccountID numeric(3,0) set @AccountID = 101 select * from AccountTest WHERE AccountID IN (CASE WHEN @AccountID = 101 THEN (100,101) ELSE @AccountID END)
I m getting error like
Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ','.
Hi all my problem is as follows : when i try to configure an ODBC DataAdapter using the configuration wizard
i use the query builder to select some fields from multiple tables , the displayed info depends in a user input , when i define the input parameter by writing(= ?) in the criteria column of the input field and continues , it displays an error
Error H4000 Error while executing the query , Error near ')' near character (214 for example).
so please if any one knows the solution to this error reply soon
I was having some errors from the webpage accessing the OLlinks table in the database. Error executing non query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at admin_admOLEditLinks.selectData(String strID) in e:wwwroothomeadminadmOLEditLinks.aspx.cs:line 101DateTime:5/23/2007 1:14:10 PMSource:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3ErrorMessage:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error: SQL Execution Error. Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks Error Source: .Net SqlClient Data Provider Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.
Open any other table works fine except this table only. Any help is much appreciated.
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
Dear All,I have written an update trigger that should write a message to an audittable When I try to update any field in the table I recieve the followingerror message -[ODBC Sql Server Driver] [Sql Server] Stirng or Binary data would betrunicated[ODBC Sql Server Driver] [Sql Server] The statement has been termined.I have looked and what has been suggested is to use the response.write len()to check the length of field that I am updating. Being fairly new toSQL-Server, I do not know how to do this. Any help will be most welcome.Thanks,Jeff
I am using the EXEC function in order to specify which view to access. However I can not get the datediff to work with it....does anyone have any clues?
I am using SQL Server 7 Beta 3 and I am having trouble with using a variable in an EXEC statement.
I have a stored procedure that is designed to retrieve a serial number from a table and then increment it by one(1) and return the original serial number to the calling function.
As long as the table name is hardcoded the following code works fine:
Following is the error message that I keep getting:
Server: Msg 170, Level 15, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near `=`.
No amount of testing with different qoutes or doublequotes and such, have made any difference.
I have tried numerous variations on the above SELECT statement with the same end result of the error on the "=" sign.
I cannot seem to assign one variable to another using the "=" (equal sign) in an EXEC statement.
Does anyone have example code of using the equal sign to assign one variable to another in an EXEC statement or can someone point me in the right direction?
I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.
I have a 2 server machines. Namely: ServerOne and ServerTwo
ServerOne (main server, On 1 machine.) Table - Foofoo
ServerTwo (secondary server, on another machine) Table - Booboo
I want to be able to link these two servers and work with them.
At the moment I do something like this. NB. My Stored Procedure is on ServerOne
declare @server varchar(100)
Select @server=Servername from ServerOne.systemsettings where name='secondary' -- @server is not equal to 'ServerTwo' declare @str varchar(8000) set @str = ' select * from Foofoo f join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '
exec(@str)
My problem is that this works fine but I do not like working with long strings and then executing them at the end.
I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'
Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.
Our server names also change quite frequently making hadcoding server names difficult.
Using the string exec convention also hides from sql when you do a dependency search of a particular table.
Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.
Any feedback with ideas and solutions will be greatly appreciated.
I am trying to make up a SQL string which will be executed with the Exec command
I want to add a return column that is not in the table, and the table columns:
something like Select @Ten As Ten, @Tablename.* From @Tablename ( where @Ten has an integer value. ) The statement was originally: select @SQL = 'select * from ' + @TempName exec (@SQL) which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!
What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?
Code snip:
declare @ErrMessage as varchar(1000) set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + '''' set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''
So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.
I understand sp_executesql might do the trick because it allows passing local params back and forth.
Any ideas on how to make this work? I appreciate the effort.
Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:
EXEC(SELECT QRY FROM temp_Update)
Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:
Msg 156, Level 15, State 1, Line 123Incorrect syntax near the keyword 'SELECT'.Msg 102, Level 15, State 1, Line 123Incorrect syntax near ')'.
Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.
Hi everyone, What is EXEC statement ?? What is the usage and purpose of it ?? It is really difficult to find ant resource about this keywords that's why I would like you to help me.
Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?
I have this code in a stored procedure: DECLARE @SQLString VarChar(200) SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')' Exec (@SQLString) it works fine - with just one issue - I must grant select permission on the table. Is there a way to do this WITHOUT granting the select permissions?
