Hi,
When ever I trying to run a select statement from one server to a linked server (remote server), like:
select * from servername.dbname.owner.table
It fails saying there are too many prefixes. Only 2 are allowed and there are three above.
Any one knows what's the issue here.
Thanks
I was just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.
View 2 Replies View RelatedFor zipcodes where the values is not null and less than 3 i have to add two 0's,for zipcodes where value is 4 digit i have to add one 0.this is in a case statement and my zip code format is "02814-1473" if it is ''342"then it should be "00342" if it is "1342" then it should be "01432" in some cases there are 10 digits but i have to look for the one on left hand side of the hiphen.
here is my code :
SELECT distinct
isnull(P.id,'') PersonID
, isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') FirstName
, ZipCode = isnull(case when substring(Ltrim(Rtrim(PA.Zipcode)),6,1) = '-' then isnull(PA.Zipcode,'')
when substring(PA.Zipcode,6,1) = ' ' then isnull(PA.Zipcode,'')
when len((Ltrim(Rtrim(PA.Zipcode)))) = 3then ('0' + '0' + (PA.Zipcode))
when len((Ltrim(Rtrim(PA.Zipcode)))) = 4 then ('0' + (PA.Zipcode))
else substring(PA.Zipcode,1,5)+'-'+ substring(PA.Zipcode,6,4)
end ,'')
FROM vwPersonPrefAddr pa with(nolock), vwpersons p with (nolock)
where p.id = pa.id
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies View RelatedCan any one tell me what I did wrong? Tried to set up link server from SQL 7.0 to Oracle.I did it as it was instruct in BOL. When I tried to click on the table, I got this message "error 7399: OLE DB provider "MSDAORA" reported error. Thanks.
View 1 Replies View RelatedI have created a linked server using an userid that have access to 2 different databases. The linked
was created successfully but I am only able to see tables in 1 database but not both.
The tables of the databases that I can access is the default database of that userid. Is there a way to see all the
tables from more than 1 database that the user is authorized to? Thanks.
I need help on creating Link Servers. The error message that I get says the link server is been created but it fails to connect.
View 2 Replies View Related Error 7302: Could not create an instance of OLE DB provider 'SQLOLEDB'. OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB returned x8007007f].
CASE:
I created a link server from SQL Server 2000 to FoxPro database using vfpoledb driver. SQL server and FoxPro database are NOT being run on the same machine.
Server1 - SQL Server 2000 (I created the link server which connects to Server2)
Server2 - FoxPro database
TROUBLESHOOT
When I test the link server on the machine which has FoxPro database , It is working with no problem.
PROBLEM STATEMENT:
When a user machine sends a query to Server1/Link Server, it is getting Error:7302
Please help me.
Sql Server 7 Standard EditionMy Server was renamed and the normal steps of reinstallingand running sp_dropserver and sp_addserver were followed.Everything seems to work fine but jobs from the master server.I did defect the server as a target server since it appeared as offlineanyway.On trying to re establish the status of the server as a target serverFailed to make "serverName" a target serverEnlist into Master Server "MasterServerName" Failed(Error 14262)Your insight is appreciatedVincento
View 3 Replies View Relatedhello. I have a windows 2003 server with sql server 2000 and a public IP address and domain name
when i am on the same network as the server I can link to the sql server with just the dowmain name www.omghelp.com
but when I take that same access adp file or mdb file home it says "database can not be found "
what do i need to get it to work...help please
Hi,
I have two database one on local system and another on web both are in MS SQL 2005 Now I want to link both of them by using Link server but the System procedure ask of oledb I am not understanding what to do.
I searched the web but .....
So plz tell me some link or code ........
thanks
Is it possible to link MSDE to another server through ODBC? I want to do this like you can in Access, where you get external data by linking tables. Any ideas? Thanks!
View 1 Replies View RelatedHi,
I am considdering to link a Informix database (running on SCO Unix) to a SQL Server 2005. Only problem is ... I have no idea how to do this! The reason I'm attempring this is to try and see if it would fix a problem I'm experiencing when using Reporting Services to generate reports from a Informix data source. I've been told that setting up a linked server might fix the problem. If anyone could perhabs guide me through setting up the linked server I would be very grateful! The ODBC credentials for setting up a connection to the Informix DB as follows:
Servername : pal_tli
Hostname : server_name
Service : pal_ser
Protocal : sesoctcp
Database : db_name
Username : user_name
Password : pword
:confused:
Hi all,
I am having two databases(MS-Sql) on two different servers.Let say they are Server1 and Server2.I am having some stored procedures(sps) which are executing on Server1.Results given by these sps are 6 different tables(Theses tables are temporary tables e.g #Table1 and they are created in one of the sps on Server1). And I want to use these 6 tables on Server2.
But constraint here is, i can create link server for Server1 from Server2 but not from Server1 to Server2.So i can not access Server2 directly from Server1.
Even if i am using custom tables here instead of temp tables(#) it will take me to solution but that is again a constraint i can not do this.
Is there any alternative solution for Link server in this case?
I dont want to go for OPENROWSET and OPENDATASOURCE b'coz of performance issue.
Thank you in advance!!
Kedar Waghmode.
For some reason, over a server link, the following call to a UDF on a linked server does not work, it seems it is treating the UDF as a table.The same UDF called on the local server works fine.--call/select from functionSELECT MASTERID FROM PAUL.RISK_DB.dbo.FNTRADEFILEPRICE('10/2/2006')error:Server: Msg 170, Level 15, State 31, Line 5Line 5: Incorrect syntax near '('.Thanks.
View 1 Replies View RelatedHi,
I have two database.and i want to transfer datafrom one to another so for that I need Link server. both the database are in SQL 2005
i have altered the store procedure and login but it give connection time out error.
will u give me some link to understand and implement the same in my app.
EXEC master.dbo.sp_addlinkedserver @server = N'vidyalink', @srvproduct=N'SqlServer', @provider=N'SQLNCLI', @datasrc=N'myb_29074'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'vidyalink',@useself=N'False',@locallogin=NULL,@rmtuser=N'mybe',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'vidyalink',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'vidyalink', @optname=N'use remote collation', @optvalue=N'true'
Labour never goes waste.
hi
How to link xml to sql server 2000?
Hiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski
View 1 Replies View RelatedI am planning
to setup a BlackBerry server and connect to a remote SQL Server over a
WAN link with 150+ms ping time. Is there a known tolerance for
SQL server connection latency? For example, I have been told by
RIM that ping time for Exchange should be around 35ms so if it is
higher, a BES should be placed next to the Exchange server. Any reply would be greatly appreciated.
I have a problem that I want to solve by linking MS Access to SQL Server 2000 or 2005.
The problem is a 3 party MS Access '97 application that is no longer supported so a new system is currently being built in SQL Server. The Access database has a UI for small amounts of input and viewing but most of the work is done through a WaveLink application. The Wavelink application allows users on the shop floor via wireless devices to pick and count product which then updates Access. There has always been record locking issues that caused some delays at the wireless devices but nothing dramatic. Until now. The wireless network and wireless devices have been upgraded and now record locking (I presume that whats causing the problems) is now causing large productivity delays requiring constant intervention.
The Wavelink application is also being updated from VB6 to VB.Net to work with the new SQL Server application. One of the requests from the client is that the Wavelink upgrade somehow fix the problem with the Access database.
My idea was to link the Access database to a SQL Server so that the wireless devices go through SQL Server to Access hopefully taking the load off Access. Is this possible? Would SQL Server prevent the record locks and provide a better multi user environment or is that something that is inherent in OLEDB Jet and linking is not going to circumvent this problem?
Regards
Hi every body.
We have instaled Sql Server 2000 Standard Sp4, whit a Nortel Product. That aplication make to gestion calls using MSQL2000; in some moment the DB stoping and the Reason is "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Arithmetic overflow error for data type smallint, value = 33459."
We do not have any idea about this problem.
Thx
Eliseo Berdugo
I have a to retrieve some data from a link 2005 SQL database and load it on my 2005 SQL database. I have to use the function supplied by the db owner of the other database and I have to use a server link to do it because the net working group refuses to allow NON SQL servers to interface through this server's firewall.
I have tried the follow:
select * from linkName.databaseName.dbo.functionName('stringValue', 'stringValue', Null)
This returns a "Remote table-valued function calls are not allowed." error
I have also tried:
SELECT * FROM OPENQUERY ("linkName", 'select * from databaseName.dbo.functionName(''stringValue'', ''stringValue'', Null)')
Which returns both "Statement(s) could not be prepared." and "Invalid object name 'FusionDB.dbo.uspAPP_GetCustomerShipTo'. " errors
Does anyone know how to do this on SQL 2005? I used to do it all the time on SQL2000 with no problems.
HI..
I have two database one on my local system and othe on remote .but this time both are on local system .
Now i want to transfer some data from my local database to remote one . so for that reason i want to use Link server But I am unable to create that and use it . both database are on SQL 2005.
And I am not sure about link server whether it can solve or no.
Give me some link and some details information about the use .in asp.net with c#.
sql commmand i have used to createthe link server
USE [webvidya];
GO
EXEC sp_addlinkedserver
'dsd',
N'SQL Server'
GO
SELECT * FROM [vidya].[ASPNET].[db_owner].[institute]
Greetings,
I want to connet to Oracle databse from SQL server 2000 since I am more comfortable with SQL 2000 client package. The oracle server is 8i or above.
I am running a SQL Server 2000 on a PC with Windows XP OS.
Please help what I need to do and configuration information.
Thank you all in advance.
BPG
please tell me the best way?
thanks
:-)
hi, A and B are different asp.net projects. Each has its own sql database, and is linked to each other through socket/TCPListener.how can page B access the data in sql of A through this socket?thanks!
View 2 Replies View Relatedis it possible to link a table with odbc into sql server?
it would be nice to link an MS Access table into sql server where i could use stored procedures to access the MS Access table.
we are running SQL7.0. I execute a store procedure on Server A that insert a data into the table on Server B. I use a Link Server to connect from Server A to Server B. When I execute the SP, I got the messege 'The command(s) completed successfully'. When I select from the table was inserted, I don't see the data that was inserted. Can we update or insert tabel using Link Server.
Thanks.
I have used to following Microsoft supplied code to create a link to
SQL server from access: (Is there a way to use a DSN-less connection at comment below? If not is there a way to create a DSN using code?
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Thanks, I really appreciate the help
Sam
Hello,
I want read only connection to Sybase server from MS SQL Server 2000. I think Link server should be best solution. Can anyone guide me as help shows link of Oracle but not sybase. any help is apreciated.
I've written following on creating link server from ER
ProductName : Sybase ASE
Datasource : Server A
providerstring: DRIVER={Sybase.ASEOLEDBProvider};SERVER=unixbox,45 10;UID=sa;PWD=abc1411'
Thanks A Lot
Hello,
Can anyone guide me how to create link server to sybase from MS SQL Server 2000 cluster.
Thanks
Sejal