Linked Server Not Wanting To Connect
Sep 11, 2007
Morning ALL.
I have a utility server that I am running SS2K5 SP2 w/ the latest patches.
It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.
I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.
It DID created the linked server BUT when it finished up it generated the following message:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
============= ERROR TEXT END ==============
Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
============= ERROR TEXT END ==============
Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)
=========== CODE BEGIN ========
/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB19', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB19',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB19', @optname=N'use remote collation', @optvalue=N'true'
=========== CODE END ==========
Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:
=========== CODE BEGIN ========
/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DC:AUS02DB21', @srvproduct=N'OLE DB for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DC:AUS02DB21',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQL_',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DC:AUS02DB21', @optname=N'use remote collation', @optvalue=N'true'
=========== CODE END ==========
Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.
@provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'
SO ... what am I missing?
Thanks ALL
View 5 Replies
ADVERTISEMENT
Apr 13, 2008
Dear Friends,
select name As Name, sAMAccountName As UserName, title As Title,
physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber,
mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email,employeeid as emp_no from openquery
(
ADSI,'SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM ''LDAP://fp1-srvr.sidf.gov.sa''
WHERE objectCategory = ''Person''AND objectClass = ''user''
')
on sql server 2000 it was working but when migration to sql server2005
I got the following error when trying to excute on query analyzer:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM 'LDAP://fp1-srvr.sidf.gov.sa'
WHERE objectCategory = 'Person'AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Could you please help me in that
View 4 Replies
View Related
Oct 16, 2007
Hi,
I have a SQL 2005 server (SQLA) on which I have created a linked server to a microsoft Access DB (LinkedAccessDB)
When I login (or remote desktop) directly into SQLA and run the following query, it works just fine:
select * from LinkedAccessDB...Table1
I also have SQL server 2005 installed on my workstation (WKSTA) and when I bring up Management Studio and connect to SQLA and try to run the previous query, I get the following error message:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedAccessDB".
I've researched articles for the better part of a day, and haven't found anything that would help. Any ideas would be much appreciated.
NOTE: My workstation, the SQL Server (SQLA) and the file server where the AccessDB is located are all in the same Domain.
View 3 Replies
View Related
Sep 17, 2015
Here are some details:
Domain = dm
Windows User = dmTestUser, member of sysadmin on SQLFL and SQLNY
SQL Server 2012 = SQLFL
SQL Server 2012 = SQLNY
Table = Product (exists in SQLNY and SQLFL)
SP = spGetProduct() (resides on SQLFL)
SQL Login = dbuser (on SQLNY, member of dbo on Product table)
I created a linked server on SQLFL pointing to SQLNY. The local server login to remote login mapping is as follows:
Local Login = dmTestUser
Remote Login = dbuser
with "Be made without security context" option selected. Run spGetProduct() to get Product records from SQLNY and populate Product table on SQLFL via the linked server.Here are tests I did:I connected to SQLFL using dmTestUser in SSMS. I manually executed the stored procedure spGetProduct() without any issues. Procedure gets Product records from SQLNY and populates the Product table on SQLFL. Everything is good.
I created a SQL Agent job called "Get NY Product" on SQLFL to execute spGetProduct(). I invoked this agent job and it executed successfully.However no data was loaded to the Product table on SQLFL. My stored procedure caught this error: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQLNY".
1. When sql agent job runs, which account does it use? Is the SQL Server Service or SQL Agent?I believe it's the sql agent.
2. What do I need to do to make this work?
View 4 Replies
View Related
Dec 29, 2006
Interesting issue.. I think
I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas???
Here is the error I get:
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "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.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
View 4 Replies
View Related
Mar 8, 2007
I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber
originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.
Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...
Any ideas??
View 1 Replies
View Related
Apr 18, 2007
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
View 1 Replies
View Related
Oct 4, 2015
We have an Enterprise setup with multiple domains.
We a Domain A with SQL server 2008R2 say SQL-A and Domain B with SQL server 2008R2 ( SQL-B). It will be like SQL-A will always access SQL-B. Reverse is not required.
In Domain A we have a .Net Application which accesses SQL-A to perform CRUD operations which ultimately come to SQL- B since all SQL tables exist in SQL- B only.
Need to know the right way to implement Linked Server from SQL-A to SQL-B, so that Domain issues as well as DML execution work smooth.
I need not worry about DDL in SQL-A And SQL-B as it will taken care of by network and H/W team. I just need to focus on as developer.
View 7 Replies
View Related
May 23, 2007
I find this most perplexing.
I have two servers, DEV and PROD. Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.
So I'm setting up my PROD server and when I go to add the linked server I get:
Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.
Now I am running SQL Server 2005 and connecting to an SQL 2000 server.
The odd part is that this works just fine on DEV.
When I go to create the linked server I set:
Linked Server: "LinkedServerName"
Server Type: "SQL Server"
and that's it.
I go to Security and enter my DOMAINUSER.ACCOUNT and then enter the login creds for the linked server.
When I click "OK" I get the above mentioned error code.
Any thoughts?
View 2 Replies
View Related
May 2, 2007
Hi,
I like to use impersonation using multiple databases and a user with no login.
I'm working with Powerbuilder 10. I can change users using the command Execute Immediate "EXECUTE AS USER = 'username'". Unfortunately, I can't execute the command 'REVERT' from Powerbuilders Execute Immediate command. The Execute Immediate command prefixes the 'REVERT' command with a exec. ie. exec REVERT.
I thought I could encapsulate the REVERT command in a procedure and run the procedure using Execute Immediate. But, I'm new to SQL Server and I'm not sure if I can.
Does anyone know how to solve this problem? Thanks.
TF
View 3 Replies
View Related
Sep 11, 2004
Hi All,
I am totally new to databases. I am starting from the absolute beginning. I want to learn MS-SQL and was wondering how/where to start. I need info on everything from installation and set-up on an Windows XP PC to programming in SQL. Is there a book or website that can guide me. I am fairly decent at programming and am able understand technical books. Installation and set up are my main concerns right now, since I believe that once I have a stable system to learn on, SQL should be easy.
Thanks!
View 6 Replies
View Related
May 4, 2007
Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. "
on the lne:
Line 95: DR_IndJobPostings = oComm_IndPostings.ExecuteReader();
I have closed the DR_IndJobPostings object after every use of it as see n on line number 175
----------- Code--------------1 /// <summary>
2 /// Will generate and email job alerts based on the frequency
3 /// </summary>
4 /// <param name="frequency">WEEKLY or MONTHLY</param>
5 /// <param name="oServerIN">Instance of Server from ASPX page</param>
6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN)
7 {
8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]);
9 oConn.Open();
10
11 SqlCommand oComm;
12
13 emailSystems oEmail = new emailSystems();
14 HttpServerUtility oServer = oServerIN;
15
16 bool validCall = false;
17 bool industryHasPostings = false;
18 string sEmail = "";
19 string sEmailTemplate = "";
20 string sVacListForEmail = "";
21
22 int IJPost_VacId = 0;
23 int IJPost_EmpId = 0;
24 string IJPost_Req = "";
25 string IJPost_KeyRes = "";
26 string IJPost_VacTitle = "";
27 string IJPost_VacJobTitle = "";
28 string IJPost_VacUrl = "";
29
30 int loopCounter1 = 0;
31
32 string CandEmailAddress = "";
33
34 oComm = new SqlCommand();
35 oComm.Connection = oConn;
36 oComm.CommandType = CommandType.Text;
37
38 SqlCommand oComm_IndPostings = new SqlCommand();
39 oComm_IndPostings.Connection = oConn;
40
41 SqlDataReader DR_Industries;
42 SqlDataReader DR_IndJobPostings;
43 SqlDataReader DR_AlertList;
44
45 if (frequency == "WEEKLY" || frequency == "MONTHLY")
46 {
47 validCall = true;
48 }
49
50 if (validCall)
51 {
52 if (frequency == "WEEKLY")
53 {
54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt");
55 }
56
57 if (frequency == "MONTHLY")
58 {
59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt");
60 }
61
62 sSql = "" +
63 "SELECT [id],[industry] FROM S_Utils_Industries " +
64 "WHERE [active] = 1";
65 oComm.CommandText = sSql;
66 DR_Industries = oComm.ExecuteReader();
67
68 if (DR_Industries.HasRows)
69 {
70 //
71 // Loop through each active industry
72 //
73 while (DR_Industries.Read())
74 {
75 industryHasPostings = false;
76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0);
77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString();
78
79 // Get all active vacancy postings for this
80 // industry
81 sSql = "SELECT [id]," +
82 "[emp_id], " +
83 "[vac_Requirements]," +
84 "[vac_KeyResp]," +
85 "[vac_VacTitle]," +
86 "[vac_VacJobTitle]," +
87 "FROM [S_Vacancies] " +
88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " +
89 "[status] = 1 AND " +
90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " +
91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'";
92
93 oComm_IndPostings.CommandText = sSql;
94
95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader();
96
97 //
98 // If there are job vacancy postings for the industries
99 //
100 if (DR_IndJobPostings.HasRows)
101 {
102 industryHasPostings = true;
103 sEmail = sEmailTemplate;
104
105 //
106 // Loop through the job postings for this industry
107 //
108 while (DR_IndJobPostings.Read())
109 {
110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0);
111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1);
112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString();
113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString();
114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString();
115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString();
116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs";
117
118 sVacListForEmail += IJPost_VacTitle + @"
119
120 Job title: " + IJPost_VacJobTitle + @"
121
122 Key Responsibilities
123 " + IJPost_KeyRes + @"
124
125 Requirements
126 " + IJPost_Req + @"
127
128 " + IJPost_VacUrl + @"
129
130 ============================================================
131
132 ";
133 }
134
135
136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail);
137
138 // If there are job postings for this industry
139 // get all the people who signed up for a job alert
140 if (industryHasPostings)
141 {
142 sSql = "SELECT [S_JobAlerts].[IndustryId]," +
143 "[S_JobAlerts].[candidateEmail] " +
144 "[S_Cv_Status].[Cv_Online], " +
145 "[S_Cv_Status].[usingShortResume], " +
146 "[S_Cv_Status].[iHasHadIntro] " +
147 "FROM [S_JobAlerts] " +
148 "INNER JOIN [S_Cv_Status] ON " +
149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " +
150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " +
151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + "";
152
153 oComm.CommandText = sSql;
154
155 DR_AlertList = oComm.ExecuteReader();
156
157 // If there are candidates who signed up
158 // for a job alert
159 if (DR_AlertList.HasRows)
160 {
161 //
162 // Loop through each job alert for this industry
163 //
164 while (DR_AlertList.Read())
165 {
166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString();
167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail);
168 }
169
170 }
171 DR_AlertList.Close();
172
173 }
174 }
175 DR_IndJobPostings.Close();
176
177 }
178
179 }
180 DR_Industries.Close();
181
182
183 oConn.Close();
184 } // END: if (validCall)
185
186 }
View 1 Replies
View Related
Mar 14, 2008
I have an issue with an SSIS package I was hoping to get some commentary on.
I am taking a flat file, scrubbing it in SSIS, and exporting it into a different package.
The files are fixed width. Both Fixed Width, and Ragged Right settings are not working, I've tried both.
My current config on the flat file connection is set to use the file name as variable. The format is "Ragged Right". The Text Qualifier is "<none>". The Header row and Header rows to skip are <CR><LF> and 0. The column names in first row are not checked.
In the advanced tab, 43 columns are defined. The output and input have all been verified numerous times to conform to the file spec.
When previewing the file in preview, only one record shows up even though the file contains multiple.
What it's doing is the last column contains the CR and LF characters and then it continues putting the other rows in that column (it is ignoring the CR LF and not going to the next row)
WHen I click on "Columns" in the flat file connector, it displays the rows of information as it should. When I click back down to preview a second time, the rows are displayed as they should.
The initial time you preview, the rows are jacked up and all smashed onto the first row. WHen trying to execute the package, I get a truncation error because the last column is supposed to be 7 in length, and contains multiple data rows in it.
When trying the option "Fixed Width" it does the same thing. It ignores the CR LF and makes everything one row.
Can someone please explain what it is I'm doing wrong? Or why when I click to preview the first time it is broken, but when clicking on columns and then back to Preview it is fixed?
View 6 Replies
View Related
Aug 27, 2007
Hi All,
Not sure if this is exactly the place to post this, but here it goes anyways.
I am writing a ASP.Net/C# program and I am interacting with a MS Access database in order to derive data on user login and logout times. Basically, I am trying to create a line graph that will display the number of users over the course of a user specified timespan. Currently, I am doing this by look at the number of users that were logged on during each minute of the timespan.
My database table setup consists of a EmployeeID column (Text), Logon Date (Date/Time), and Logoff Date (Date/Time). I have also created an index on the Logon Date and Logoff Date columns.
In order to view the number of users during a minute of the timespan I use a Jet SQL query of the following format
Code Snippet
SELECT DISTINCT Count (EmployeeID) AS [User Count]
FROM ProgramName
WHERE ([Logon Date] < #August 27, 2007 11:45:00# OR [Logon Date] Is Null) AND ([Logoff Date] > #August 27, 2007 11:45:00# OR [Logoff Date] Is Null)
The problem is that using this method I have to execute 1,440 queries for each day in the timespan. Currently this takes about 25 seconds to execute if the timespan is a full workweek (7,200 queries).
Now the question. Is it possible to create a SELECT statement that will return user counts for multiple minutes? Like maybe a SELECT statement that returns a column of counts for every minute in an hour? If it is possible, does anyone have any examples? I am hoping by lowering the number of queries my program has to execute I will also cut down the time required for the code to run.
I am pretty new to SQL, so any guidance or advice is very appreciated.
Thanks!
View 4 Replies
View Related
Jan 7, 2004
Hi everyone.
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this:
SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
Thanks
Andrew
View 4 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
View 6 Replies
View Related
Apr 25, 2008
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
View 2 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Dec 12, 2006
I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both (using the same database on SQL Server Express 2005).
The error I get is: "com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed." An interesing note - I get the same message if the database is not running.
SQL Server Express 2005 is installed in mixed mode.
Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.
These are my values in my Java app web.xml -
<init-param>
<param-name>DBDriver</param-name>
<param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>
</init-param>
<init-param>
<param-name>DBURL</param-name> <param-value>jdbc:sqlserver://localhostsqlexpress:1055;databaseName=CORNERS</param-value>
</init-param>
<init-param>
<param-name>DBUser</param-name>
<param-value>sa</param-value>
</init-param>
<init-param>
<param-name>DBPwd</param-name>
<param-value>myPasswd</param-value>
</init-param>.
And yes, the port is 1055 - I checked to find it.
I am using Microsoft SQL Server 2005 JDBC Driver 1.0 (sqljdbc_1.0.809.102).
Does anyone have any idea what is wrong so that the login fails in the Java application but works in the .Net application?
View 7 Replies
View Related
May 4, 2007
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
View 3 Replies
View Related
Jul 22, 2007
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
View 5 Replies
View Related
Dec 28, 2005
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
<code>
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
View 10 Replies
View Related
Oct 20, 2007
I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist
View 6 Replies
View Related
Apr 3, 2008
Hi,
I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error.
Here is the error:
System.Data.SqlClient.SqlException: 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)
Here is the Environment:
App Server:
Windows Server 2003 Standard Edition
Inside Company's Firewall/ Network
Database Server:
Windows Server 2000 Advanced Edition
SQL Server 2000 SP4
Remote Connections to the Server is checked
Enable Protocols: Named Pipes & TCP/IP
TCP/IP Port: 1402 (I don't know why it isn't the default of 1433)
The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition.
I can ping the db server from the app server using the IP address.
I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server).
I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP?
What do I tell the network admin to change?
Any help would be appreciated.
Thanks,
Oran
View 4 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Sep 11, 2015
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
View 7 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Feb 23, 2015
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
View 2 Replies
View Related
Apr 16, 2015
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
View 0 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Oct 4, 2007
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
View 8 Replies
View Related
