OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str)
AS a;
Incorrect syntax near '@str'.
------------------
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC
SP_HELPTEXT ''createReport''') AS a;
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE
DB provider "SQLNCLI" for linked server "(null)" indicates that either
the object has no columns or the current user does not have
permissions on that object.
I have the following sp:ALTER PROCEDURE myspPrepareTextForHTML @MessageContent nvarchar(1400), @returnval nvarchar(1400) outputASBEGINSET NOCOUNT ON;SET @returnval='something'RETURN @returnvalENDWhen I try this: EXEC myspPrepareTextForHTML @MessageContent='dfgsdfgdf', @returnval OUTPUT print @returnval I get the error:Must declare the scalar variable "@returnval". How can I get this to work?
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.
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?
It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.
I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure, When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.". In Fire bird /Interbase store procedure we can nested. Below are the code; declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) Insert Into @dtReturnData Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES' Select doccode, docdate, debtoraccount From @dtReturnData Inside the GetPickList It will do like this, but most of the code I not included; ALTER PROCEDURE GETPICKINGLIST @doctype nvarchar(2), @datefrom datetime, @dateto datetime, @includegrn char(1), @includesa char(1), @includedata nvarchar(5) AS BEGIN declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) IF (@DOCTYPE = 'SI') BEGIN Insert Into @dtSALESINVOICEREGISTER Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END ELSE BEGIN Insert Into @dtDELIVERYORDERREGISTER Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END Select doccode,docdate,debtoraccount From @dtReturnData END
So how can I select a nested store procedure? can someone help me
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
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
I have a trigger on a table. I am trying to dynamically log thechanged fields on the table to another table, so I am iteratingthrough the bits in COLUMNS_UPDATED() to find what's changed, andgetting the column name programatically. This is all working fine.If I do a regular insert command in my trigger then everything worksfine. However, since I want to retrieve data from the column namewhich I got programatically from the inserted and deleted tables (toget the old and new values) I wanted to do something like this:insert into auditTransactionLog (TableName,PrimaryKeyId,ColumnName,OldValue, NewValue, ActionType) EXEC( 'SELECT(''cmContactInfo''), I.contactID,'''+ @colname+''', D.'+@colname+',I.'+@colname+', '+@action+' FROM inserted I INNER JOIN Deleted D onI.ContactId = D.ContactId')The presence of this line of code appears to be preventing theupdating of the table with the trigger. Is there some reason why Ican't do the EXEC in the trigger? If I did it without EXEC it worksfine but I have no idea of getting at the D. and I. @colname columnsotherwise.Thanks for any help!Rebecca
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
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.
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.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
Hi there, I am trying to use and SQL statement with a LIKE query with % at the begining and end of the parameter to get any combination of the search parameter i.e. last name. My piece of SQL looks like this: WHERE userUsername=@Username AND contactName LIKE @SearchTerm I need something like this: WHERE userUsername=@Username AND contactName LIKE %@SearchTerm% However this does not work. Any suggestions would be greatly appreciaed. Regards, TCM_
Hi, I have a query that uses a parameter as criteria e.g. type = :_type (i set p_type = 'Trad') I want to change this parameter to be a range of values. However when I change the query to be type in (:_type) I get nothing returned if I set p_type to be 'Trad,New' but I do get something if it's just Trad. Can anybody advise on the syntax? I've tried a number of variations.
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
