March 7, 2011 at 12:48 pm
I can do following
Select * into #tmpTable
From employee
This will create a #tmptable like employee with the data.
Now I want to do
Select * into #tmpTable
From
Exec GetEmployeeSp '1/1/2010'
This give me error saying incorrect syntex.
Here I am trying to create a #tmpTable same as result to Stored procedure.
Is it possible ? HELP.
March 7, 2011 at 1:03 pm
not like you are asking, no ...you cannot dynamically create a table by calling your procedure.... have to create a table with the appropriate columns and datatypes first.
a work around to getting the table definition is to use an openrowset trick: but that wanders into dynamic SQL. so you can get the definition of the model like this, and then create the temp table with that exact definition; script the MyModelTable, chang ethe name to #temp and you've got what you need.
--using a trusted connection
SELECT *
INTO MyModelTable
FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
--using an explicit username and password
SELECT *
INTO MyModelTable
FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master',
'Set FmtOnly OFF;EXEC dbo.sp_Who')
once you have the table, the syntax is like this:
CREATE TABLE #TMP(columnlist...)
INSERT INTO #TMP(columnlist...)
EXEC StoredProcname @parameters...
Lowell
March 7, 2011 at 1:44 pm
Here is what i want to do.
declare @IpStoredProcName as varchar(200)
DECLARE @IpParms AS VARCHAR(4000)
declare @IpFileName as varchar(100)
SET @IpStoredProcName = 'Rpt_AccountsReceivableAgingSp'
SET @IpParms = '2011-01-31,2011-01-31,,,A,1,,,,,,,,,0,B,1,0,1,1,D,1,0,0,A,12345,I,30,Current,60,31-60,90,61-90,120,91-120,999,Over 120,Site1,1,1'
SET @IpFileName = 'c:\temp\test1tt.txt'
/* Other Variables */
DECLARE @OpParms AS VARCHAR(4000)
DECLARE @Cmd AS VARCHAR(4000)
SET @OpParms = dbo.KI_ProcessParameters(@IpParms)
--@OpParms Looks like this , it just puts the single quotes and put NULL in the blank parameter
-- '2011-01-31','2011-01-31',NULL,NULL,'A','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','B','1','0','1','1','D','1','0','0','A','12345','I','30','Current','60','31-60','90','61-90','120','91-120','999','Over 120','Phil','1','1'
SET @Cmd = @IpStoredProcName + ' ' + @OpParms
SET @Cmd = 'Set FmtOnly OFF; EXEC ' + @Cmd
-- select @Cmd
SELECT *
INTO tempdb.dbo.TempAging
FROM OPENROWSET('SQLOLEDB','Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app', @Cmd)
-- select * from tempdb.dbo.TempAging
SET @cmd = 'bcp '
+' "SELECT * from Tempdb.dbo.TempAging " '
+ ' queryout '
+' "' + @IpFileName + '" '
+' -T -c -t^|'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
DROP TABLE tempdb.dbo.TempAging
March 7, 2011 at 2:37 pm
You cannot pass a variable into the OPENROWSET function for the query parameter. This is what you have:
...
SET @Cmd = @IpStoredProcName + ' ' + @OpParms
SET @Cmd = 'Set FmtOnly OFF; EXEC ' + @Cmd
SELECT *
INTO tempdb.dbo.TempAging
FROM OPENROWSET('SQLOLEDB', 'Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app', @Cmd)
...
This is what you need to consider:
...
SET @Cmd = @IpStoredProcName + ' ' + @OpParms
SET @Cmd = 'Set FmtOnly OFF; EXEC ' + @Cmd
-- select @Cmd
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT *
INTO tempdb.dbo.TempAging
FROM OPENROWSET(''SQLOLEDB'', ''Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app'', ''' + @Cmd + ''')'
EXEC (@sql) ;
...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 5:33 pm
THIS IS THE STRING I GOT FROM @sql
SELECT * INTO tempdb.dbo.TempAging FROM OPENROWSET('SQLOLEDB', 'Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app', 'Set FmtOnly OFF; EXEC Rpt_AccountsReceivableAgingSp '2011-01-31','2011-01-31',NULL,NULL,'A','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','B','1','0','1','1','D','1','0','0','A','12345','I','30','Current','60','31-60','90','61-90','120','91-120','999','Over 120','Phil','1','1'')
WITH ERROR
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '2011'.
March 7, 2011 at 5:47 pm
You're passing a string into a function as a parameter. That string must be surrounded by single-quotes which means that any single quotes that exist within the parameter value must be escaped. Welcome to string manipulation for dynamic-sql. You'll need to make sure all single-quotes are escaped as two single-quotes in a row. When I extract the query portion from your post and paste it into SSMS the syntax highlighting makes it easy to see what needs to be done.
SELECT *
INTO tempdb.dbo.TempAging
FROM OPENROWSET('SQLOLEDB',
'Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app',
'Set FmtOnly OFF; EXEC Rpt_AccountsReceivableAgingSp '2011-01-31',
'2011-01-31',NULL,NULL,'A','1',NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,'0','B','1','0','1','1','D','1','0','0','A',
'12345','I','30','Current','60','31-60','90','61-90','120',
'91-120','999','Over 120','Phil','1','1'')
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 6:12 pm
result of @sql is
SELECT * INTO tempdb.dbo.TempAging FROM OPENROWSET('SQLOLEDB', 'Server=phlsl801;Trusted_Connection=Yes;Database=phil_pilot_app', 'Set FmtOnly OFF; EXEC Rpt_AccountsReceivableAgingSp ''2011-01-31'',''2011-01-31'',NULL,NULL,''A'',''1'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,''0'',''B'',''1'',''0'',''1'',''1'',''D'',''1'',''0'',''0'',''A'',''12345'',''I'',''30'',''Current'',''60'',''31-60'',''90'',''61-90'',''120'',''91-120'',''999'',''Over 120'',''Phil'',''1'',''1''')
Error When i execut it
Msg 7357, Level 16, State 1, Line 1
Cannot process the object "Set FmtOnly OFF; EXEC Rpt_AccountsReceivableAgingSp '2011-01-31','2011-01-31',NULL,NULL,'A','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','B','1','0','1','1','D','1','0','0','A','12345','I','30','Current','60','31-60','90','61-90','120','91-120','999','Over 120','Phil','1','1'". 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.
March 7, 2011 at 6:18 pm
Try removing Set FmtOnly OFF;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 6:39 pm
After removing Set FmtOnly OFF;
i got the same message
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC Rpt_AccountsReceivableAgingSp '2011-01-31','2011-01-31',NULL,NULL,'A','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','B','1','0','1','1','D','1','0','0','A','12345','I','30','Current','60','31-60','90','61-90','120','91-120','999','Over 120','Phil','1','1'". 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.
March 8, 2011 at 6:34 am
Hmmm...I know the technique you are trying to use to derive the table definition of a temp table from the resultset of a procedure works...I just confirmed it locally to make sure we weren't chasing a ghost.
You might be running into some permission issues or something with your procedure's output interface in certain conditions. Can you check:
1. Does the proc you are trying to execute exist in the DB you have in your connection string?
2. Does the login you're using to authenticate to the remote server have execute permissions on the proc?
3. Does the procedure you're calling have any logic paths where it does not return a resultset?
4. What does the proc call do when you run it directly in SSMS with the set of parameters you're trying to use?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 8:04 am
1. Does the proc you are trying to execute exist in the DB you have in your connection string?
It does exist in the database
2. Does the login you're using to authenticate to the remote server have execute permissions on the proc?
i am using 'sa' login so it has all the permission
3. Does the procedure you're calling have any logic paths where it does not return a resultset?
I am not sure about this question
4. What does the proc call do when you run it directly in SSMS with the set of parameters you're trying to use?
I can run the procedure in SSMS and it gives me desired results.
March 8, 2011 at 10:09 am
skb 44459 (3/8/2011)
3. Does the procedure you're calling have any logic paths where it does not return a resultset?I am not sure about this question
What I mean by this is: can your procedure ever exit without returning a resultset?
For example, does the proc validate any of the input parameters and throw an error or return without returning a resultset if the parameter value is not within an acceptable range or is NULL?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 11:42 am
The procedure never exits without the results.
I always get good results.
The Procedure calls some other procedures but they all are available in the database.
March 8, 2011 at 12:16 pm
Is the outer proc Rpt_AccountsReceivableAgingSp the one returning the resultset? Or is it one of the called procs that is actually returning the resultset?
I have seen articles online that talk about cases where SQL Server cannot produce the shape of the resultset expected from a stored procedure for some complex stored procedures with multiple return paths, especially where temp tables are involved and the outer proc is not the one actually returning the resultset.
New features in SQL Server "Denali" (SQL version 11.0) under the heading "Metadata Discovery" promises to do a better job of this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 12:47 pm
Is the outer proc Rpt_AccountsReceivableAgingSp the one returning the resultset? Or is it one of the called procs that is actually returning the resultset?
The Procedure Rpt_AccountsReceivableAgingSp is where the result set is coming from.
The calling procedures are just for checking some stuff. Main Processing is happening in the Rpt_AccountsReceivableAgingSp.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply