May 13, 2008 at 8:28 am
Hi all, I have stumbled on to an error I can't imagine how to fix.
I am using an SQL2000 warehouse, and need to push data to a SQL 2005 data base.
Here is where the problem started. The 2005 DB is a vendor installation, and installed on a VM. The production DB called App1 I can connect to with DTS and push the data to fine.
But...
Our hardware/VM folks were asked to make a VM instance of App1 for testing. It appears they cloned (if that is the term) the App1 VM, and named it App1T.
Now when I use the same DTS and change the server the DB objects point to I have no issues. However, when I attempt to access the DB on App1T, I get the following error "Server "App1" is not configured for DATA ACCESS".
The App1 is not a typo, the DB connections point to App1T, but any attempt to use the connection points back to App1 and gives the error.
I have tried creating a new DTS and found the same issue.
My guess at this point is the VM folks did a copy of the VM instance, and there is something in the SQL2005 configuration or DB that points to the server name being App1, rather than App1T.
Anyone know of anything to look for or check on this?
Thanks!
May 13, 2008 at 9:05 am
hello,
sounds to me like you should use sp_helpserver to see if the name matches the physical computer name and change it if needed with sp_dropserver and sp_addserver restart sql server and try to get the connection working and check the network configuration in configurations manager and any psosible alias setup there
May 13, 2008 at 9:54 am
The sp_helpserver did show the wrong server name, and network name.
Having not used sp_dropserver, I looked it up in BOL, and it is talking of a remote server? I ran the sp_helpserver on the App1t server and it returned App1. Is sp_dropserver a function to use on the actual instance of the DB?
May 13, 2008 at 10:05 am
the procedures are defining the names of local and remote sql server instances but for remote servers you will use sp_addlinkedserver these days
May 13, 2008 at 12:12 pm
I had this thought, which perhaps someone can answer for me. This App1 server and App1t server, which based on the sp_helpserver thinks it is App1... both machines are active and on the network. IF that database name is App1, rather than App1t, and I use the sp_dropserver, is it possible it will run against the "real" App1 server rather than the App1t?
May 13, 2008 at 1:35 pm
it will not make any problems and run on whichever server you execute it
May 13, 2008 at 3:22 pm
Well that is different. The procs did change the name of the server correctly, however, when I try to connect I still get the error in the DTS tools.
I am continuing to dig through ideas, but would be happy for any suggestions or pointers.
May 13, 2008 at 4:18 pm
see if there are server listed under linked server tab in enterprise manager check security settings for them and the remote servers
May 14, 2008 at 9:35 am
I have partial success presently. The error message no longer appears, however, I still can not connect to anything but the master DB on the 2005 server.
There are no linked server's being used on this process. I have a DTS on 2000 which will push data to a table on the 2005 server. This process works fine, and I can see tables from the database on our production side. On this new test side, I am now able to connect to the master db on the 2005 test (app1t) server, but no other DB's on that server.
I have verified that RPC is on in the 2005 server. I can even run the DTS package without errors, however it does not actually place the data in the 2005 server.
Something is telling me that when the VM folks created the VM test server, they simply copied the whole thing and changed the server name, rather than say restoring the DB to the new VM server.
There must be some flag or setting I am still missing to allow the 2000 server DTS to acess this 2005 server. Still digging away at the issue. I could have the VM people create a new server the more typical way, but I still would like to know how to fix this issue, as I don't doubt this will happen again, as we are just starting to use VM here.
May 14, 2008 at 9:47 am
Yes that's likely what they did. I would recheck all permissions now to try find out the problem but you say you can run the package without error but it doesn't actually import the data? How can this be? I would check a detailed log of the package too and try to execute the steps manually under the user context.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply