I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:
> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
migrate linked servers with passwords
This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
migrate linked servers with passwords
This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a different
> server. I can get this to work on the same server with the script below but I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a different
> server. I can get this to work on the same server with the script below but I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
migrate linked servers
Hello everyone,
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
Chieko
Hi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>
|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>
|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during[vbcol=seagreen]
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on
>
sql
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
Chieko
Hi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>
|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>
|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during[vbcol=seagreen]
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on
>
sql
migrate linked servers
Hello everyone,
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > Hello everyone,
> > I'd like to learn how I should go about moving my linked servers from on
> sql
> > server to another sql server. Has anyone done this before?
> > Thanks
> > Chieko
> >
> >
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
--
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> >
> > Execute the script in query analyzer with text output and copy the
result
> to
> > destination server and execute it.
> >
> > (Script from old post)
> >
> > select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> > isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> > isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> > isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> > '')+char(10)+'go'
> > from master..sysservers
> >
> > This will help you to move the linked servers, but the security
> credentials
> > you need to define manually.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> > news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > > Hello everyone,
> > > I'd like to learn how I should go about moving my linked servers from
on
> > sql
> > > server to another sql server. Has anyone done this before?
> > > Thanks
> > > Chieko
> > >
> > >
> >
> >
>
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > Hello everyone,
> > I'd like to learn how I should go about moving my linked servers from on
> sql
> > server to another sql server. Has anyone done this before?
> > Thanks
> > Chieko
> >
> >
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
--
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> >
> > Execute the script in query analyzer with text output and copy the
result
> to
> > destination server and execute it.
> >
> > (Script from old post)
> >
> > select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> > isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> > isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> > isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> > '')+char(10)+'go'
> > from master..sysservers
> >
> > This will help you to move the linked servers, but the security
> credentials
> > you need to define manually.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> > news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > > Hello everyone,
> > > I'd like to learn how I should go about moving my linked servers from
on
> > sql
> > > server to another sql server. Has anyone done this before?
> > > Thanks
> > > Chieko
> > >
> > >
> >
> >
>
migrate linked servers
Hello everyone,
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on[vbcol=seagreen]
>
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on[vbcol=seagreen]
>
Monday, March 12, 2012
migrate adp projects from SQL 7 to SQL 2000
We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a Windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a Windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
migrate adp projects from SQL 7 to SQL 2000
We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.
Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>
|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.
Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>
|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
migrate adp projects from SQL 7 to SQL 2000
We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When I restore complete DB from old server to new server all objects
have (dbo) suffix and when we test the adp - through running a saved
report the report fails because it is looking for a table without the
(dbo) suffix.
Any help would be appreiciated.Not sure if I understand the question but ...
Are these linked tables? Just rename the links and that will work.
For example if the new link is dbo_Customers just rename it to Customers.
Please let us know if this was the issue or something else.
Ben Nevarez, MCDBA, OCP
Database Administrator
"dkordyban@.gmail.com" wrote:
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When I restore complete DB from old server to new server all objects
> have (dbo) suffix and when we test the adp - through running a saved
> report the report fails because it is looking for a table without the
> (dbo) suffix.
> Any help would be appreiciated.
>|||Yes, SQL tables are linked to project. When viewing the tables, views
and stored procedures from the project, they all appear with (dbo)
after the object.
It may be somthing with sql 2000 sp4. I restored the exact same .bak
file to a windows 2000 with sql 2000 sp3 and it worked fine - no (dbo)
after every object
Thanks for your time
Middleware to access Sybase databases?
Does anyone use some kind of middleware or linked server
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...
> Does anyone use some kind of middleware or linked server
> configuration to combine their MSSQL Server data with data
> from other relational databases such as Sybase? Thanks
> for any help.
> Sondra
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...
> Does anyone use some kind of middleware or linked server
> configuration to combine their MSSQL Server data with data
> from other relational databases such as Sybase? Thanks
> for any help.
> Sondra
Middleware to access Sybase databases?
Does anyone use some kind of middleware or linked server
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...
quote:
> Does anyone use some kind of middleware or linked server
> configuration to combine their MSSQL Server data with data
> from other relational databases such as Sybase? Thanks
> for any help.
> Sondra
Wednesday, March 7, 2012
Microsoft.Jet.OLEDB.4.0 Linked Server Transactions
Hi All,
I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)
BEGIN TRAN
INSERT INTO ACCES...Table
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "HES" does not support the required transaction interface.
I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface.
The main question is - does MS Access linked servers support transactions?
Thank you for your help,
I have exausted all resources available to me in finding out the answer.
Thanks,
Eric
Hi,
as far as I can remember Access does not support DT.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens,
This really is bad news for me. The previous interface was just dropping a file on the network and then access had a utility to import and update, however this methodology was horrible. If any step errored then it was a nightmare to clean up the mess and restart, and the data is so out of sync it is ridiculous. I am trying to find a more ACID way.
Maybe I can use the linked server to prepare some temp tables, then write a utility in C#.net to do the update. I bet the connection in .net can support non-distributed transactions.
Microsoft, I love you and hate you.
Friday, February 24, 2012
Microsoft SQL-DMO error 7405 on linked server query
I am attempting to execute a query on a linked oracle server. The link
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
WARNINGS
options
to be set for the connection. This ensures consistent query semantics.
Enable
these options and then reissue your query.
I have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
> WARNINGS
> options
> to be set for the connection. This ensures consistent query semantics.
> Enable
> these options and then reissue your query.
>
> I have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
WARNINGS
options
to be set for the connection. This ensures consistent query semantics.
Enable
these options and then reissue your query.
I have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous queries require the ANSI NULLS and ANSI
> WARNINGS
> options
> to be set for the connection. This ensures consistent query semantics.
> Enable
> these options and then reissue your query.
>
> I have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric
Subscribe to:
Posts (Atom)