Solved

Execute SQL Task Result Set Error

Posted on 2009-04-06
17
2,468 Views
Last Modified: 2013-11-10
Hi
I am trying to excute this SQL in the Excute SQL Task and store the value in a variable ... When i do the same i get the output as  EXISTS in the Management Studio.. But in here when i try to do the same by setting the ResultSet to Single Row, ResultName (Index) =0 and variable name to a variable of datatype Varchar ...I get the error as below..Am i doing something wrong?

Progress: Executing query "Declare @I Varchar
Set @I = ((SELECT Table_NAme FR...". - 50 percent complete
Error: No result rowset is associated with the execution of this query.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Variable": "Exception from HRESULT: 0xC0015005".
Declare @I Varchar

Set @I = ((SELECT Table_NAme FROM INFORMATION_SCHEMA.TABLES

where TABLE_NAME = 'Data_0409'))

 Select Case 

 When @I <> NULL Or @I <> '0' Then 'Exists'

 Else 'Not Exists'

 End 

GO

Open in new window

0
Comment
Question by:danny1620
17 Comments
 
LVL 22

Accepted Solution

by:
PedroCGD earned 200 total points
ID: 24079667
You dont have a name associated with the column... maybe it's the problem...
Declare @I Varchar
Set @I = ((SELECT Table_NAme FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409'))
 Select (Case
 When @I <> NULL Or @I <> '0' Then 'Exists'
 Else 'Not Exists'
 End ) AS TEST
GO

Also check this link:
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

Regards,
Pedro

0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24079686
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 300 total points
ID: 24079824
You can try something like this:
SELECT  CASE WHEN isnull(Table_Name) = 1
 THEN 'Not Exists'
 ELSE 'Exists' END
FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409'
or

select  case when a.ftable = 0
 then 'Not Exists'
 else 'Exists' end
from (SELECT count(Table_NAme) as ftable
FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409' group by Table_Name) a
And if you want to put that information in your @i variable then  
SELECT  @i = CASE WHEN isnull(Table_Name) = 1
 THEN 'Not Exists'
 ELSE 'Exists' END
FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409'  
0
 

Author Comment

by:danny1620
ID: 24080116
Hi
@Pedro: I have been trying the samething as I mentioned before.. but dont know why am i getting that Error..
0
 

Author Comment

by:danny1620
ID: 24080317
Hi Ralmada
Thanks for your suggestion and i have tried which works fine and gives me the count when i use the basic Select statement Below and  map ftable to Variablename in the result set with single row but it gives me error when i try to use any of the other queries
SELECT count(Table_NAme) as ftable
FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409' group by Table_Name
Thanks
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24080326
1) Have you tried any of my suggestions?
2) you need to add a length to @i, so when you declare it it should be something like declare @i varchar(10) or the length you want.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24080415
Another option could be:
select CASE WHEN isnull(
SELECT top 1
FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'Data_0409' group by Table_Name
) = 1 then 'Not Exists'
else 'Exists' end  
0
 

Author Comment

by:danny1620
ID: 24080460
Hey thanks ralmada for reminding me that.. It works fine... But before I close the question i want another suggestion from you guys...
I am actually trying to check the table existence (3 tables in 3 different DB's) and if they all exist take presnt month or else they would take last months date... what would you guys think is the best way to do this in the package this would be the Start of the package....
Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:danny1620
ID: 24080476
Hi Pedro...
The link you sent me sure did work.. Thanks a lot
I am actually trying to check the table existence (3 tables in 3 different DB's) and if they all exist take presnt month or else they would take last months date... what would you guys think is the best way to do this in the package this would be the Start of the package....
Thanks
0
 

Author Comment

by:danny1620
ID: 24080490
Guys
i would like your suggestions .. Its Ok even if you dnt .. I am going to divide the points equally ..
Thanks a lot guysss
0
 

Author Comment

by:danny1620
ID: 24080593
Will the same SQL statements with a union to different databases work??? Help me out
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24080708
yes, you can combine resulsets from three databases using union, provided the subqueries return the same columns.

select ... from databasename1.INFORMATION_SCHEMA.TABLES 

where ...

UNION ALL

select ... from databasename2.INFORMATION_SCHEMA.TABLES 

where ...

UNION ALL

select ... from databasename3.INFORMATION_SCHEMA.TABLES 

where ...

Open in new window

0
 

Author Comment

by:danny1620
ID: 24081121
hi ralmada,
Correct me if i am wrong.. I tried to union all the sQL statements and store the full result set value in a Object Type variable which looks like as below in Management Studio
ColumnName
  • Exists
  • Exists
  • Exists
Now assuming that they are stored the same way in the variable also how do i proceed and check if all the values in the table  are 'Exist' and change my date variable..
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24081388
This will tell you the counts of your columnname for those that are called 'Exists'. You can store that value in a variable. If = 3 then it means the table in question exists in all three databases.

select count(ColumnName) from (

select ... from databasename1.INFORMATION_SCHEMA.TABLES 

where ...

UNION ALL

select ... from databasename2.INFORMATION_SCHEMA.TABLES 

where ...

UNION ALL

select ... from databasename3.INFORMATION_SCHEMA.TABLES 

where ...

) b

where ColumnName = 'Exists'

group by ColumnName

Open in new window

0
 

Author Comment

by:danny1620
ID: 24081514
Hi Ralmada ,
Sorry to bother you ... But last fix ... Below is the query which i am trying to Execute but get an error saying ------- No column was specified for column 1 of 'b'

select count(b.ColumnName) from
(select case when X.Ftable = 0
then 'Not Exists'
else 'Exists' end
from (SELECT count(Table_Name) as Ftable
FROM [****].[INFORMATION_SCHEMA].[TABLES]
where TABLE_NAME = 'Data_0409' group by Table_Name) X
Union ALL
select case when Y.Ftable = 0
then 'Not Exists'
else 'Exists' end
from (SELECT count(Table_NAme) as Ftable
FROM [****].[INFORMATION_SCHEMA].[TABLES]
where TABLE_NAME = 'Data_0409' group by Table_Name) Y
UNION ALL
select case when Z.Ftable = 0
then 'Not Exists'
else 'Exists' end
from (SELECT count(Table_NAme) as Ftable
FROM [****].[INFORMATION_SCHEMA].[TABLES]
where TABLE_NAME = 'Data_0409' group by Table_Name) Z)
b
where ColumnName = 'Exists'
group by ColumnName
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 300 total points
ID: 24081555
Need to define column name in each subquery  :-)
(Change it for a name that suits your need).

select count(b.ColumnName) from 

(select case when X.Ftable = 0 

then 'Not Exists' 

else 'Exists' end as ColumnName

from (SELECT count(Table_Name) as Ftable 

FROM [****].[INFORMATION_SCHEMA].[TABLES] 

where TABLE_NAME = 'Data_0409' group by Table_Name) X 

Union ALL 

select case when Y.Ftable = 0 

then 'Not Exists' 

else 'Exists' end  as ColumnName 

from (SELECT count(Table_NAme) as Ftable 

FROM [****].[INFORMATION_SCHEMA].[TABLES] 

where TABLE_NAME = 'Data_0409' group by Table_Name) Y 

UNION ALL 

select case when Z.Ftable = 0 

then 'Not Exists' 

else 'Exists' end  as ColumnName

from (SELECT count(Table_NAme) as Ftable 

FROM [****].[INFORMATION_SCHEMA].[TABLES] 

where TABLE_NAME = 'Data_0409' group by Table_Name) Z) 

b 

where ColumnName = 'Exists' 

group by ColumnName

Open in new window

0
 

Author Closing Comment

by:danny1620
ID: 31567136
Thanks a Lot Ralmada and Pedro...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now