0 rows output when using SSMS Import/Export Wizard

Question

Post reply

0 rows output when using SSMS Import/Export Wizard

Kit G

SSCrazy

Points: 2888
More actions
March 5, 2010 at 11:10 am

#217244

I've got an ad hoc query that is pulling data from two different databases on the same server. I want to export that data, so I fire off the Import/Export Wizard (right click on the server name in SSMS, go to Tasks, go to Export Data) and I set everything up, FlatFile, Character Delimited, etc..
The wizard takes my SQL as a valid SQL statement. When I go to preview the data, the data I want to export to the text file appears. But when I run the query, everything works just fine except I get 0 rows output. And there is no error anywhere to tell me why I didn't get any rows.
The query when run in SSMS gives me 158k rows. Is it because I'm crossing databases? Is that a limitation in the export wizard? Or did it choke on the number of rows I was trying to export? I tried looking in BOL on the Import/Export Wizard and didn't find anything.
I don't have permissions to do an OPENROWSET or whatever and when I tried to set it up in SSIS, I again got 0 rows. I don't know much about SSIS, I just tried to follow the directions and the package executed just fine when I ran it in debugging mode, just didn't export any rows into the file (but it did produce a file).
Below is my query
use DB1
;with cte (OID, CID) as
(select o.orderid, se.campaignid
from DB2.dbo.orders o
inner join dbo.seminarevents se
on o.orderid = se.orderid
where orderdt > '10/1/2007')
select distinct
firstname as [FName],
lastname as [LName],
isnull(address1, '') as [Addr1],
isnull(address2, '') as [Ad2],
city as [City],
state as [State],
postalcode as [Zip]
from dbo.people p
inner join CTE
on cid = campaignid
inner join dbo.reservations r
on p.personid = r.personid
where cancelled = 'F'
and address1 != ''
I'm just getting my feet wet with queries that span different databases and I can't find a reference about the behavior that I expected, but didn't get from the Import/Export Wizard.
-- Kit

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Have you tried creating a log file?

You mentioned that you went into debug mode, I assume that you set breakpoints?

Double check the properties on your flat file connection manager...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I assume that you examined your project in BIDS?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Are you only having this problem with this query (datasource)?

What happens if you export the results of a small table from one of the sample databases?

What happens if you simplify your query?

When you preview your data that would suggest that something is going on with the flat file connection?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kit G SSCrazy Points: 2888 More actions · Answer 4

Welsh Corgi (3/5/2010)
Have you tried creating a log file?
You mentioned that you went into debug mode, I assume that you set breakpoints?
Double check the properties on your flat file connection manager...

Not entirely sure how to create a log file. The output from the debug has everything succeeding, there are no errors. I'm just not getting the rows output, all I am getting is the header row.

Which properties do I need to check on the flat file connection manager?

-- Kit

Kit G SSCrazy Points: 2888 More actions · Answer 5

Welsh Corgi (3/5/2010)
Are you only having this problem with this query (datasource)?
What happens if you export the results of a small table from one of the sample databases?
What happens if you simplify your query?
When you preview your data that would suggest that something is going on with the flat file connection?

I've had this problem before with queries that span two databases. They run in SSMS just fine, but won't appear through any output. I have to cut and paste from SSMS into a txt file or Excel file to get the file to output.

Not sure how to simplify the query. Thought it was pretty simple to begin with.

Don't see anything specific that would indicate I'd have a problem with the flat file connection. Doesn't mean there isn't something there, I'm having to do a raw data dump and out of the 158k rows, I'm sure there's some junky data in there.

-- Kit

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

To create a Log File all you need to do is right click from within BIDS and select Log File.

I would examine the results of the log file.

Also you may need to change your column delimiter or other property because if the delimiter is stored in the Data your Package will bomb.

I would suggest that you consider experimenting with your Package in the BIDS ENvironment.

Also change the source to a query & try pulling in the first record, does it fail?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kit G SSCrazy Points: 2888 More actions · Answer 7

Welsh Corgi (3/8/2010)
To create a Log File all you need to do is right click from within BIDS and select Log File.
I would examine the results of the log file.
Also you may need to change your column delimiter or other property because if the delimiter is stored in the Data your Package will bomb.
I would suggest that you consider experimenting with your Package in the BIDS ENvironment.
Also change the source to a query & try pulling in the first record, does it fail?

I'm using a full version of Visual Studios 2005 so I'm not sure if your advice on BIDS will apply.

I will do a bit more experimentation and let you know the results.

-- Kit

Kit G SSCrazy Points: 2888 More actions · Answer 8

Okay, found out what the problem was.

It is the "use <database>" as the first line in the query. The Import/Export wizard (and SSIS) don't like that and I got 0 rows because of it. Got rid of that statement and I got the export of the data I needed.

-- Kit