April 8, 2024 at 11:38 pm
I have a package that loops through a bunch of SQL Servers and executes a stored procedure that uses temp tables.
It was build in 2008 and I am now moving it to 2016 but having issues getting it to work
in 2008 the OLE DB sql was simply "uspSystem_GetDatabaseInfo"
That was giving me grief in 2016 and after some research someone suggested to use the below syntax.
EXEC ('[dbo].[uspSystem_GetDatabaseInfo]')
WITH RESULT SETS
(
(
ServerName NVARCHAR(25) NOT NULL,
dbName NVARCHAR(25) NOT NULL,
dbStatus VARCHAR(25) NOT NULL,
CompatibilityLevel INT NOT NULL,
dbSizeMB INT NOT NULL,
SpaceUsedMB INT NOT NULL,
LogSizeMB DECIMAL(18,2) NULL,
LogSpaceUsedPerentage DECIMAL(18,2) NULL,
LastBackup DATETIME NULL,
SampleDateTime DATETIME NULL
)
)
This works fine in my newer databases but does not work in 2 legacy databases 2008.
I have tried every combination I think of for settings but it works only for my newer databases..the moment the loop hits a 2008 database it does not like it
April 9, 2024 at 8:19 am
I think that you might have to split things up, such that one of the two different versions of the T-SQL is executed, depending on SQL Server version.
If you're not sure how to do that, feel free to post back.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 9, 2024 at 4:36 pm
I have had situations where I simply couldn't make a execute using WITH RESULT SETS work.
I solved the issue by changing the sp to specify a dummy table variable with the same format as the resultset that the sp returned at the start of the sp code. Apparently that makes it possible for the executing component to fetch the correct layout. Go figure!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply