March 6, 2017 at 6:15 pm
Hello, I have 2 servers. I've created a select query to import data from server host to server destination. When I log into server host, and execute the query, it produces 5000 records. When I log into server destination, then use the import wizard with the above used query, the import produces 0 records. I can't figure out why. Thank you in advance for your assistance!!
March 6, 2017 at 7:40 pm
crowegreg - Monday, March 6, 2017 6:15 PMHello, I have 2 servers. I've created a select query to import data from server host to server destination. When I log into server host, and execute the query, it produces 5000 records. When I log into server destination, then use the import wizard with the above used query, the import produces 0 records. I can't figure out why. Thank you in advance for your assistance!!
Could you post the query? I don't think anyone will be able to guess at the issue without seeing the query.
Sue
March 7, 2017 at 7:14 am
+1 for what Sue posted. The big question here for me is how, exactly, the one query connects to the other server. Is there a Linked Server involved? Does the query use 4-part naming? Is there any use of either OPENQUERY or OPENROWSET in the query? Does the table (or tables?) in the query have any explicit permissions granted?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 9:08 am
Thanks for replying.
Here's an example of a query. I used the term example because the problem occurs with every query.[
use DemandManagement
SELECT
cast(Id as int) as [ID]
,cast(ParentId as int) as [ParentID]
,cast(Name as varchar(80)) as [Name]
,cast(IsActive as bit) as [IsActive]
,cast(Created as datetime2(0)) as [Created]
,cast(Modified as datetime2(0)) as [Modified]
,cast(CreatedBy as varchar(40)) as [CreatedBy]
,cast(ModifiedBy as varchar(40)) as [ModifiedBy]
FROM demandMgmt.FunctionalGroup]
To provide enough detail, here goes.
If I log into server A, I can run the query above. It will produce some number of records.
If I log into server B, use the import wizard. Within the wizard, enter server A as the source, Server B as the destination, it then shows 0 records imported. I receive no errors during the import process.
Thank you again for your replies!!
March 7, 2017 at 10:06 am
crowegreg - Tuesday, March 7, 2017 9:08 AMThanks for replying.Here's an example of a query. I used the term example because the problem occurs with every query.
[
use DemandManagementSELECT
cast(Id as int) as [ID]
,cast(ParentId as int) as [ParentID]
,cast(Name as varchar(80)) as [Name]
,cast(IsActive as bit) as [IsActive]
,cast(Created as datetime2(0)) as [Created]
,cast(Modified as datetime2(0)) as [Modified]
,cast(CreatedBy as varchar(40)) as [CreatedBy]
,cast(ModifiedBy as varchar(40)) as [ModifiedBy]
FROM demandMgmt.FunctionalGroup]To provide enough detail, here goes.
If I log into server A, I can run the query above. It will produce some number of records.
If I log into server B, use the import wizard. Within the wizard, enter server A as the source, Server B as the destination, it then shows 0 records imported. I receive no errors during the import process.
Thank you again for your replies!!
A few things to check -
Did you specify the database for both the source and destination?
Does the query parse without errors?
Does the data show when you do a preview?
Sue
March 7, 2017 at 10:14 am
There must be errors involved such as identity insert or something to that extent.
March 7, 2017 at 10:29 am
Possibly a dumb question, but are you sure you are putting it into the proper table on the destination side? I think by default it dumps it into dbo.query.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2017 at 10:33 am
crowegreg - Tuesday, March 7, 2017 9:08 AMThanks for replying.Here's an example of a query. I used the term example because the problem occurs with every query.
[
use DemandManagementSELECT
cast(Id as int) as [ID]
,cast(ParentId as int) as [ParentID]
,cast(Name as varchar(80)) as [Name]
,cast(IsActive as bit) as [IsActive]
,cast(Created as datetime2(0)) as [Created]
,cast(Modified as datetime2(0)) as [Modified]
,cast(CreatedBy as varchar(40)) as [CreatedBy]
,cast(ModifiedBy as varchar(40)) as [ModifiedBy]
FROM demandMgmt.FunctionalGroup]To provide enough detail, here goes.
If I log into server A, I can run the query above. It will produce some number of records.
If I log into server B, use the import wizard. Within the wizard, enter server A as the source, Server B as the destination, it then shows 0 records imported. I receive no errors during the import process.
Thank you again for your replies!!
The import wizard is pretty terrible if you try to specify a query, can you try making a temp table on the source server with the data and importing from that?
March 7, 2017 at 10:40 am
Yes, I'm making sure that I'm running the query to insert the records into the proper table. I have approximately 20 queries that go into 20 tables, and the problem occurs on each query. If I work on another machine, and use SQL Server 2012 to do the import, they all work.
March 7, 2017 at 10:50 am
crowegreg - Tuesday, March 7, 2017 10:40 AMYes, I'm making sure that I'm running the query to insert the records into the proper table. I have approximately 20 queries that go into 20 tables, and the problem occurs on each query. If I work on another machine, and use SQL Server 2012 to do the import, they all work.
That is a pretty good hint (in my mind) - are both SQL instances 2008 or lower? I've not used that import wizard myself, but I wouldn't be surprised if it behaves strangely when working with data in higher versions.
I would probably build this up in an SSIS package. If it is a run-once thing, then you can just do it from visual studio. If this is a multi-run thing, you can schedule it as a SQL Agent job.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2017 at 11:03 am
I'd also suggest you run your import on the server with the higher version, as it's less likely to have a problem. SQL 2008 may not be capable of properly operating with an instance that's at a higher version than itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 11:16 am
bmg002 - Tuesday, March 7, 2017 10:50 AMcrowegreg - Tuesday, March 7, 2017 10:40 AMYes, I'm making sure that I'm running the query to insert the records into the proper table. I have approximately 20 queries that go into 20 tables, and the problem occurs on each query. If I work on another machine, and use SQL Server 2012 to do the import, they all work.That is a pretty good hint (in my mind) - are both SQL instances 2008 or lower? I've not used that import wizard myself, but I wouldn't be surprised if it behaves strangely when working with data in higher versions.
I would probably build this up in an SSIS package. If it is a run-once thing, then you can just do it from visual studio. If this is a multi-run thing, you can schedule it as a SQL Agent job.
March 7, 2017 at 11:22 am
In the steps of creating an SSIS package, I'm running the query immediately to confirm everything is working properly, and it's not.
When I parse the query, it shows the statement is valid.I've reconfirmed that all the field mappings are correct. But when the query completes, it shows 0 rows transferred.
March 7, 2017 at 11:27 am
I forgot to write, when I test the preview, it displays the data records. When I create the SSIS rather than run immediately, it still imports 0 records.
March 7, 2017 at 11:46 am
crowegreg - Tuesday, March 7, 2017 11:22 AMIn the steps of creating an SSIS package, I'm running the query immediately to confirm everything is working properly, and it's not.When I parse the query, it shows the statement is valid.I've reconfirmed that all the field mappings are correct. But when the query completes, it shows 0 rows transferred.
Is the package being run on the SQL 2008 box or the SQL 2012 box? And which version of BIDS was used to create it?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply