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
DECLARE @vsBulkSQL VARCHAR(MAX)
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'
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
~Mohan
View 4 Replies
ADVERTISEMENT
Jan 23, 2008
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.
Thanks in Advance.
View 12 Replies
View Related
Sep 19, 2000
How do I use a @variable to hold on value return from an exec ('string command') statement.
Example for:
declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)
set @db = 'DBNAME'
set @OldOwner = 'USERNAME'
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.
Thanks.
View 2 Replies
View Related
Sep 19, 2005
Hi Guys, I have been trying to get this SQL string below to run, but for some reason I carry on getting an error near the EXEC Statements.
Does anyone have any idea what I doing wrong?
CREATE PROCEDURE [dbo].[UpdateWarnings]
@EndDate DateTime
AS
DECLARE @iid varchar(100)
DECLARE @fid varchar(100)
SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')
IF @iid <> ''
BEGIN
EXEC('UPDATE memorial SET active=0 WHERE id IN ('+ @iid +')')
END
SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')
IF @fid <> ''
BEGIN
EXEC('UPDATE memorial SET warning=(warning+1) WHERE id IN ('+ @fid + ')')
END
IF @enddate <> ''
BEGIN
UPDATE warnings SET startdate=getdate(), enddate=@enddate, warnings=(warnings + 1) WHERE id=1
END
GO
View 1 Replies
View Related
May 3, 2004
HI,
WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.
BUT WE ARE GETTING A FEW HICCUPS. PLS HELP
THIS IS HOW IT GOES :-
CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN
CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP2
EXEC MY_PROC1
ST1 .........;
ST2..........;
END
THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED
NO PROBLEM TILL NOW.......
BUT,
CREATE PROCEDURE MY_PROC3
AS
BEGIN
CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP3
EXEC MY_PROC2
ST1 .........;
ST2..........;
END
THEN IT GIVES AN ERROR AS :-
"An INSERT EXEC statement cannot be nested."
CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........
WHAT IS THE NESTING LEVEL OF A PROCEDURE ?
IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
PLS HELP ME OUT IN THIS
THANKS
View 13 Replies
View Related
Nov 28, 2005
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
View 2 Replies
View Related
Nov 1, 2007
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.
CREATE PROCEDURE [dbo].[sp_try]
@TempTable varchar(25)
AS
DECLARE @SQL nvarchar(MAX)
DECLARE @SQLINSERT nvarchar(MAX)
BEGIN
--create temp table
SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +
'(
ContactName varchar (40) NOT NULL ,
ContactId varchar (30) NOT NULL ,
ContactrMessage varchar (100) NOT NULL,
)'
EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable
SELECT @sql= 'Select * from table'
SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )
SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition
EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable
View 8 Replies
View Related
Apr 10, 2008
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."
View 3 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Oct 16, 2007
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.
View 5 Replies
View Related
Dec 21, 2007
Hi,
I am using this query to execute but it didn't
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 ','.
Pls help me out
T.I.A
View 3 Replies
View Related
Apr 3, 2008
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
Thanks a lot
Mina Samy
View 3 Replies
View Related
May 23, 2007
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.
View 7 Replies
View Related
Sep 29, 2007
Hi,
i have problem executing the dynamic query.
I have straight forward query as below and works fine
declare @count bigint
declare @varcount varchar(max)
set @varcount ='5'
If convert(bigint, @varcount) <> 4
print ' not 4 '
else
print 'Its 4'
Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc
declare @count bigint
declare @varcount varchar(max)
declare @tempTable varchar(max)
declare @vsSql varchar(max)
declare @No bigint
set @No = 5
set @table = 'sam'
set @varcount = ''
select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')'
When executed it throws the follwing error
Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to bigint.
Can anyone point out what to change in the query to work
~mohan
View 1 Replies
View Related
Jun 4, 2015
DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1
SELECT @Count'
EXEC(@S)
I am trying to execute the above query but it is throwing me an error.
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
View 16 Replies
View Related
Jan 23, 2008
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
View 2 Replies
View Related
Oct 24, 2005
I am trying to do an insert statement utilizing a variable string.
something like:
Set @cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @lServer + ';User ID=' + @user + ';Password=' + @pword + char(39) + '
).myServer..
Insert into #Temp_table (field1, field2)
select exec @cString
--What is the syntax for this?
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Mar 7, 2001
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?
View 1 Replies
View Related
Aug 21, 1998
Hi All,
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:
CREATE PROCEDURE IncrementSerialIDNo
(@serial_num_temp varchar(15), @serial_num_output varchar(15) output)
AS
BEGIN TRANSACTION
BEGIN
SELECT @serial_num_temp = tblSerialNo.SerialNo
FROM tblSerialNo
SELECT @serial_num_output = @serial_num_temp
UPDATE tblSerialNo
SET tblSerialNo.SerialNo =
CONVERT(varchar(15),(CONVERT(integer,@serial_num_t emp ) + 1))
IF (@@error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
RETURN
However, if I change the table name to a variable that is passed to the
stored procedure as a argument, I need to use the EXEC statement.
My problem is that for the EXEC statement to work I need to escape the
variables and I run into a problem with the " = " ( equal sign ).
Example:
DECLARE @SerialTable as varchar(30), @CompanyNo as varchar (3)
SELECT @CompanyNo = `001`
SELECT @SerialTable = `tblSerialNo` + @CompanyNo
EXEC ( `SELECT ` +@serial_num_temp + `= SerialNo FROM ` +
@SerialTable )
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?
Thanks in advance for any and all help.
jeff alerta
jeff@nestworks.com
View 5 Replies
View Related
Dec 17, 2003
Hi.
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.
Bhit.
View 1 Replies
View Related
Aug 1, 2000
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 !!!!!
Thanks,
Judith
View 1 Replies
View Related
Aug 10, 2000
I have:
<<Select @SQL = 'Select Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like ''%' + @KeywordOrSIC + '%''' + ' order by Keyword'
exec(@SQL)>>
which works fine, but I want to modify it to do this
<<Select Replace(Keyword,',',' ') AS Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like 'real%' order by Keyword >>
which works from the query window but I can not get the right combination around the 'replace section' to make up a string for the exec.
All help greatly appreciated
Judith
View 1 Replies
View Related
Nov 23, 2006
Hey guys, needless to say I'm new at this.
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.
View 3 Replies
View Related
Aug 22, 2002
I would like to execute something like that with sql6.5 :
select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (cmd)
I tried like this but I don't know what's wrong
exec ("USE "+ RTRIM(@dbname) +"exec sp_helprotect")
Thank you
View 1 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 1 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 3 Replies
View Related
Dec 16, 2007
declare @rej_nm as varchar(50)
exec('select count(*), ' + "'@rej_nm'" + 'from ' + @rej_nm)
trying to return the rowcount AND the name of the object. return set should look like this:
12, tbl_name
i've tried single quotes around @rej_nm, nested single quotes, single/double nested, etc..
instead, i get an error stating that @rej_nm is not a valid column of tbl_name. bottomline...
how do i return a variable 'value' within a select statement...
thanks!
View 3 Replies
View Related
Aug 1, 2007
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.
View 9 Replies
View Related
Jun 20, 2006
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.
Thanks
View 1 Replies
View Related
Jan 2, 2007
hi,
can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..
Select column1, coulumn2, (select EXEC SP_NAme), blabla
thanks in advance... :)
View 6 Replies
View Related
May 31, 2005
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?
View 10 Replies
View Related
Oct 2, 2007
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?
View 7 Replies
View Related
