Linked Server: Works Local, But Not Remote

Nov 14, 2007

I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).

However, when I execute a query from a remote machine I get an error (see below for the exact message).

I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?

Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?


Errors:

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".


Environment: SQL 2005, Standard Edition SP2

Thanks in advance for any suggestions.

David Rueter
drueter@assyst.com

View 2 Replies


ADVERTISEMENT

Can't Connect To Remote Server, My App Works In Local Only???

Feb 3, 2004

Hello,
i've developed a website using VB.NET, and SQL SERVER 2K, in my dev environment works properly (SQL Server and WEB SERVER are in the same machine).
When i put everything in the target machine, where the webserver is but not the sql, i get the following error:
Sql SErver does not exist or access denied

I've tried so many ways to do this, ODBC, ADO.NET, OLEDB.....and everything ends in the same error.
Also if i try to connect with ODBC (from odbc manager) it seems to work (test connection succesfull), but in asp.net nothing works.
I've tried to access the server with:
INSTANCE_NAME
SERVER_NAMEINSTANCE_NAME
IP,PORT

I think (they are checking this), that the auth mode is set to MIXED MODE.

Any idea to save my life?? ;)

Thanks ppl

View 1 Replies View Related

Please Help.. Can't Get ASP.NET App To Work On Web Server, Works On Local System

Jul 27, 2006

I have an application I developled for a shopping cart function using Wrox ASP.NET book as a guide. I have everything working great on my local system using SQL 2005 Express. When I move the app to an IIS 6.0 Web Server with SQL 2005 Express installed I get the following error.
Line 103:    Public Overridable Property Cart() As Wrox.Commerce.ShoppingCartLine 104:        GetLine 105:            Return CType(Me.GetPropertyValue("Cart"),Wrox.Commerce.ShoppingCart)Line 106:        End GetLine 107:        Set
It has some comments up top about not being able to establish a connection to the SQL Express Database. I have made the changes to allow TCP/IP and Named-Pipes
The App has to be connecting to the database because it displays grid views during the order process.
The Database is located in the App Data folder
IIS is set to Integrated Authentication and configured as a Web Application.
Can someone please tell me what is wrong.
thanks.....

View 3 Replies View Related

Database Works On Local Host But Not On Web Server

Apr 23, 2008

I have a application which uses the automatic asp.net membership stuff to make it work. (In app_data). It works fine on local host, but when I use it on the web server I get the error message (after clicking the log in button):An error has occurred while establishing a connection to
the server.  When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified) Please help me fix this. Thank you for your help,Sam 

View 6 Replies View Related

Permissions To A Local Account On Which SQL Server Service Works

Mar 31, 2006

My SQL Server 2005 runs on a local account. Is it neccesary to assign this login in SQL to a System Administrator role?

And is there any difference in SQL Server 2000?



thanks

Przemo

View 1 Replies View Related

Error 15185: There Is No Remote User 'sa' Mapped To Local User '(null)' From The Remote Server 'DTS_FSERVER'.

Nov 10, 2006

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

View 7 Replies View Related

Access Linked Server: Works In SQL2000 But Not SQL2005

Aug 14, 2007

I am runing Windows 2003 which has both SQL2000 and SQL2005.

The following works in 2000 but not 20005





Code Snippet
DECLARE @sql nvarchar(4000)
set @sql = 'sp_addlinkedserver @server = N''dbAccessPO'',
@provider = N''Microsoft.Jet.OLEDB.4.0'',
@srvproduct = N''OLE DB Provider for Jet'',
@datasrc = N''C:Temppopts.mdb'''
exec sp_executesql @sql
set @sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'
exec sp_executesql @sql




Running this:




Code Snippetselect * from dbAccessPO...MyTable




Produces this error:




Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".





I login using sa for both instances.

I could really use your help.

Thanks.

View 2 Replies View Related

Universe Database (IBM) - Linked Server No Longer Works With MS SQL 2005

Sep 20, 2006

Has anyone managed to set up an IBM Universe database environment as a linked server to MS SQL 2005?

If so please convey how you got it done.



Thanks,

View 22 Replies View Related

Linked Server Errors After Failover But Works When It Is Failed Back

Feb 15, 2008

We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes.
I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.

Here's what is happening:
When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:

[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.


I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.

We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.

There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:


Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.


Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!

Thanks,
Linda

View 7 Replies View Related

Connect To Remote SQL Server From Local Web App

Apr 18, 2007

I have a SQL Server 2005 instance which resides on a dedicated server hosted by a third party. I am able to connect to this server through my local SQL Management Studio, however, my local ASP.NET 2.0 web application cannot connect. I get the error below. The connection string I use is formatted as...
Connection String: 
Server=thesever;uid=myuserid;pwd=mypassword;database=mydatabase
Error:
Failed to connect to database: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The hosting providr claims that the SQL Server configuration settings are as they should be as fars as TCP/IP and Named Pipes are concerned. Does anyone know what might be going on?
 

View 1 Replies View Related

Local VB.NET App Connection To Remote SQL Server

Nov 1, 2007

I know this is strickly not a website question, but dunno where else to post...To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting serverI have the following code: Dim StrSQLUN As String = "[UN]"
Dim StrSQLPW As String = "[PW]"
Dim StrServer As String = "[IP][INSTANCE]"
Dim StrDB As String = "[DB]"
Dim strTimeOut As String = "Connection Timeout=0;"
Dim pStrSQLConn As String = "Server=" & StrServer & ";Database=" & StrDB & ";User Id=" & StrSQLUN & ";Password=" & StrSQLPW & ";" & strTimeOut

Dim sqlConn As New SqlClient.SqlConnection(pStrSQLConn)
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?
Can they put restrictions in place for this specific sort of data access?Does anyone have any suggestions on how to resolve this issue??Thanks for any helpAdam.

View 2 Replies View Related

Restoring From Local BAK Onto Remote Server

Apr 27, 2006

Hi all, I have moved hosting companies and am trying to get my website and MSSQL database sorted on the new server. I am using an enterprise type tool and can connect to the database the new hosting company has created on their SQL server with no problem. I now want to upload the data I had in the database previously. All I have to do this from is .BAK and .TRN files on my local hard drive. Using the restore option from the menu, I tried to select the .BAK file I have saved to restore from it, and got the following error:

"EXECUTE permission denied on object 'xp_availablemedia', database 'master', owner 'dbo'."

I also tried the advice given in this post as the problem seems similar and follows the same pattern: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841

but there are loads of users listed for sp_who - it seems to be the users for all the databases on the host's server so I didn't follow the next step to disconnect them!

Any advice as to what I need to do would be much appreciated.

Cheers

Lisa

View 2 Replies View Related

Sql Server Backup - Remote -&> Local

Jul 23, 2005

Hello-I have a Sql Server 2000 database offsite that I would like to back upto a local machine. I am using Enterprise Manager on a local machine toadminister the remote db.Whats the best way to schedule backups so the remote db is backed up tothe local machine?Can this be done in Enterprise Manager?Would you recommend any 3rd party software?Thanks!MB

View 1 Replies View Related

SQL Server - Best Practice For Local And Remote Development

Aug 13, 2007

What is the usual way to develop ASP.NET websites locally using a database and then migrate everything to the real webserver?
Here's what I'm doing now -- tell me what I should be doing instead (I'm a newbie developer).


I'm using Visual Studio 2005 Standard with a SQL Server 2005 database, to develop a small C# ASP.NET website. I'm the only developer.

View 3 Replies View Related

Backup A Remote SQL Server To Local Computer?

May 4, 2005

Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?

View 3 Replies View Related

Copying Table From Local To Linked Server

Feb 17, 2004

Hi,

I'm trying to copy all the data from a single table across to a remote linked server and I'm having all sorts of problems.

The SQL query I'm attampting is this:-


Code:


EXEC sp_addlinkedserver 'GODZILLA', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'GODZILLA', 'False', 'rmtUser', 'lclUser', 'password'
Go

--DELETE TABLE
DROP TABLE GODZILLA.Racing.dbo.horse

--SELECT INTO
SELECT horse_id, horse_filename, horse_time, horse_new, horse_driveLetter
INTO GODZILLA.Racing.dbo.horse

FROM Racing.dbo.horse

EXEC sp_droplinkedsrvlogin 'GODZILLA', 'lclUser'
EXEC sp_dropserver 'GODZILLA'




Basically - this doesn't work as an error is returned:-


Code:


The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.



Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???

Please help!

View 3 Replies View Related

Querying A Linked Server With Local Database...

Mar 25, 2008

I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:

select * from openquery([hcda-storagesrvlaserfiche], 'select * from toc where parentid=358')

I would like to join one of my tables from my local database to the query. How can I do this? Thanks!

View 5 Replies View Related

INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Apr 23, 2008



Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.

Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.

What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination

RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.

The package is run thru BIDS manually so there is no sucurity restrictions attached to it.

I am not sure what I am missing. And I do not see error in my package either. It is not failing.

Thanks in advance!



View 6 Replies View Related

Copying A Table From A Local Sqlserver To A Remote Server

May 3, 2007

Hi
I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible?
If i just get the connection part explained, i can figure out the rest by myself.
Thanks
/Jonas

View 3 Replies View Related

Copy A Database From A Remote Server And Using It On My Local Machine

Sep 14, 2007

Hi and thanks in advance for your help.
 I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
 
Dollarjunkie

View 1 Replies View Related

Backups/Restores Of Remote Hosted SQL Server To Local?

Aug 21, 2005

Is there any way to backup a remote SQL Server that is on a hosted account to a local drive?  We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure.  We would like to be able to do backups and restores to our local development server if possible.  Perhaps vis DTS or some similar means?  WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.

View 2 Replies View Related

Retrieve Local Time From Remote SQL Server Computer

Feb 6, 2004

Hi all

I am about updating some fields with local date and time using blw API
Quote
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Public Function LocalDateTime()

Dim MyTime As SYSTEMTIME

GetLocalTime MyTime

Debug.Print "The Local Date is:" & MyTime.wMonth & "-" & MyTime.wDay & "-" & MyTime.wYear
Debug.Print "The Local Time is:" & MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond

End Function
Unquote
This works fine however due to it's not really easy for an administrator to keep checking every times wheter local date and time are correctly updated for each front end users computer i would that to reconsider my function and making it to read local date and time from remote SQL Server. my concern is to help administrator to save time as he should only need to ensure that Server is running with correct time...

thkx for any advise

View 1 Replies View Related

Setting Up Remote Connection To Server From Local Machine

Sep 17, 2015

I have a SQL Server 2014 installation on a server (CHRIS-PCCHRISSQL).I have SQL Server 2014 management studio installed on local server called Pootle.I have gone through the configuration on server (CHRIS-PCCHRISSQL) inc the following:

(1) I have set up a user on CHRIS-PCCHRISSQL called sqladminuser at server level with the Server Roles of 'Public' and 'SysAdmin'

(2) The computers are both on the same homegroup.

(3) On Chris-PC , I have opened up the firewall port 1433 as Inbound Rule

(4) On Chris-PC, Within SQL Server Configuration Manager,the 'SQL Server Network Configuration for Protocols for CHRISSQL' have been set up as follows:

- The TCP protocol is enabled
- I have set up IP2
as follows:

Active: Yes
Enabled: No
IP Addres: 192.168.0.3

However when I try to connect from SQL Server Management Studio 2014 on my local machine Pootle to Chris-PCCHRISSQL using SQL Server Authentication with the user sqladminuser

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)how to set up a remote connection from SQL Server Management Studio
on my local machine Pootle to CHRIS-PCCHRISSQL

View 6 Replies View Related

How To Copy Tables From Local Machine To Remote SQL Server

Jul 23, 2005

We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630

View 4 Replies View Related

Synchronize Sql Server Remote Database With Local Copy

Jan 20, 2006

hello all,

I am using vb.net in windows form. I have made a module which is connected to sql server 2000.

Now, I want to have a fresh copy of the remote database of SQL Server at my local computer and whenever there is change in remote database(insertions,deletion or updation), my local database copy may gets synchronized(i.e changes get reflected in this local copy).

Can any one help me? If it is possibel by using Vb.net code, it is very well, and if there is some Sql Server wizard that can help me do that, please reply to me.

Thanks in advance.

View 1 Replies View Related

Change Local Connection String On Remote Server

Jun 19, 2007

Hello,

I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.

I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.

Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?



My local connection string works locally:



<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />



When I uploaded to my server I changed SQLEXPRESS to (local) as advised in the forum.

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />



When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:



<configuration>

<appSettings/>



<connectionStrings>

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>



<system.web>

<customErrors mode="Off" />



<compilation debug="false" />



<authentication mode="Windows" />



</system.web>



</configuration>



Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?

View 6 Replies View Related

Use Windows Group As Local Login For Linked Server?

Mar 12, 2008

Hi all:

I have created a linked server that connects a SQL 2000 database to a SQL 2005 database. If I use individual SQL or Windows accounts as local logins on the SQL 2000 instance, I can successfully query the linked SQL 2005 database.
(For security we use the setting "For a login not defined in the list above, connections will: not be made")

If I try to use a Windows group as the local login, remote queries fail with the error
"Access to the remote server is denied because no login-mapping exists"

Is it not possible to use a Windows group for the local login of a linked server?

If I run 'exec sp_linkedservers' the setup appears valid

Linked Server Local Login Is Self Mapping Remote Login
SQL2005Serv DomainBRubble 0 SQL_Read_Access
SQL2005Serv DomainWindows_Group 0 SQL_Read_Access
SQL2005Serv DomainFFlintstone 0 SQL_Read_Access

Thanks in advance
Grant




View 5 Replies View Related

Need To Establish A Remote Connection To An Sql Database On A Local Server (same Domain)

Feb 16, 2007

Hi, I have been creating a web store, which we will be hosting on our Windows Server 2003 system.  I am also writing a windows application that will be running on our client systems on the same network/domain.  I have no need to allow internet access to the database, just within our domain.  Pretty much the windows application will be running and whenever a customer places an order, the program will automatically pop-up the needed information for our credit card processing, and shipping department.  So far I have been able to establish a connection to our server by creating a data connection under the server explorer.  However, it does not list any databases whatsoever, the web site has been published using Visual Studio 2005 Professional on the server, and is in 100% working order.  I have searched and haven't found anything concrete on this, i'm sure its just something simple that im missing.  It appears that the server is definetely connected to the database (since the website is fully functional), but it is not listing the information for other systems to access it.  If anyone has any ideas, please let me know.  Thank you

View 8 Replies View Related

Create Temp Table On Linked Server From Local Server

Jan 15, 2004

Hi,

I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.

If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.

I appreciate any advice / guidance anyone can offer me!

View 1 Replies View Related

Smalldatetime Comparison Between Local Server And Linked Server Fails?

Mar 8, 2006

Hi all,

I have the following select that is designed to compare values on parallel servers.SELECT TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate,TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate
FROM TradeAnalysis.dbo.SalesIncome_Quarterly AS TA1
FULL OUTER JOIN L_TA_MIRROR.TradeAnalysis.dbo.SalesIncome_Quarterl y AS TA2
ON ((TA1.OSID = TA2.OSID) AND (TA1.QtrYear = TA2.QtrYear) AND (TA1.QuarterNo = TA2.QuarterNo))
WHERE(BINARY_CHECKSUM(TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate) <>
BINARY_CHECKSUM(TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate))Disregard the full outer join, it is necessary in the full select, I have just pared it down to show here and for testing. A standard join will also have the same result.

The trouble is that the comparison (the BINARY_CHECKSUM) is failing on the EarningsReportDate column, which is defined on BOTH servers in DUPLICATE databases as a smalldatetime.

The data is actually the same in both rows of each table (as is all the other data being compared). What I am seeing is that the select returns milliseconds on the LINKED server (the L_TA_MIRROR reference), but not on the local database?

for example,
Local server data returned in above select is 2000-01-28 00:00:00
Linked server data returned in above select is 2000-01-28 00:00:00.000

Wassup with THAT? If I remove the date from the select/comparison, it says everything matches, so I am pretty sure this is the culprit that is causing the BINARY_CHECKSUM comparison to fail.

Is there some setting that I need to put on the Linked Server reference that says "treat this as the smalldatetime data type it IS, dammit!"????

I can get by the issue if I replace the date reference in the linked server part of the select with CAST(TA2.EarningsReportDate AS smalldatetime) but why do I have to do this?

Yes, I am SURE both servers have the column in question defined as a smalldatetime, as running the same select on either server causes the same failure, but only when referencing the LINKED server column.

View 1 Replies View Related

Retriving Data From A Remote Sql Server Database And Storing It In A Local Sqlserver Db

Aug 1, 2001

Is it possible for retriving data from a remote Sql server database and storing it in a local sqlserver database.

View 1 Replies View Related

Registering Remote Database Server With Local Dbserver Enterprise Manager

Nov 15, 1999

Hi
I amtrying to register remote database server with my local machine Enterprise Manager. It says server doesnot exist or not available.

I am putting server name,Tcp/Ip, i.p,port in Sql client configuration utility
and then trying to register it with Enterprise Manager.

Pl help me out.

Thanks

Shaurav

View 1 Replies View Related

Named Pipes Provider: Could Not Open A Connection To SQL Server [53], But Only On The Remote, Not The Local

Jun 18, 2007

My connection string (to a remote instance of SQL Server Exress 2005) is exactly the same when the web files are located on my local machine or my remote machine, and while it works fine when I use the local web files, I get the following error when I use the remote files:

"Named Pipes Provider: Could not open a connection to SQL Server [53]"

As you can see, since it works from the local version, I've setup SQL to accept remote connections via TCP/IP and Named Pipes. I'm using IIS7 on the local, and IIS6 on the remote, in case that makes a difference. I have other sites setup the same way, which work fine.

Any thoughts?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved