Hi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.
I have set the dynamic paramters option on, and use collation compatible.
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL
SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
Thanks in advance for your help.
Hi, don't know if anyone can help me but I desperately need to roll a report out which relies on an SQL 7.0 datasource.
Current situation - Reporting Services 2000 up and running on an SQL 2000 box - no problems getting reports to run from the native machine but I cannot get the thing to make a hop to our SQL 7.0 box.
I've created three data sources - OLEDB, Native and ODBC. For the OLEDB and Native I am getting a DATABASEPROPERTYEX error - I assume here that reporting services is polling the SQL 7.0 box with an unsupported method.
For ODBC I CANNOT get the parameters to work - I've read all sorts of stuff about using unnamed parameters, so I've put a ? in the query and I'm getting Invalid parameter number and invalid parameter number errors (I suspect it is getting a bit confused, but then so am I).
So I thought I'd try a linked server, sure enough with a fixed parameter I can get through the linked server no problem, but again I can't work out the syntax/series of events needed to use a parameter with this, I'm starting to suspect it can't be done with an Exec statement.
So - first question - is there any way around the DBPROPERTY EX problem so I can use OLEDB/SQL Client connectivity and secondly please can someone explain what exactly has to go in the parameters box when I press ... on the data source box, 'cos it's doing my head in.
Thanks
Rich
Using a query through a linked server is giving tremendously reduced performance.
Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?
Are there any other tips out there?
Thanks.
Hi,
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and
run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
Thanks in advance.
Keith
Anyone have any suggestion on increasing the performance going over a linked server? IS this connection persistent? Or does it establish a connection with each reference? Any other Providers for the connection other than Microsofts OLE DB Provider for Oracle? I imagine that building index's on the referenced oracle tables would help?
Any help, input or suggestions would help.
David
I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
Any ideas would be appreciated
Hello,
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
this.
I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following
string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"
I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.
So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.
So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
going on.
Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';
Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')
From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?
Anybody have any insight as to whats going on?
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies View RelatedHi ,
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
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'Â Â
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
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.
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
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??
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?
Hi,
I have a user defined function which has 5 parametes to pass, now while running the Table Valued Function using hardcoded parametes value its taking only 10 seconds , but while running the UDF by declaring the parametes value and
assigned them value its taking more than 3 minutes.
here is the example
First case
select * from UDF_Test('A',76,'C',987,''X')
This one taking only 10 minutes to run.
Second case
Declare @P1 varchar,
@P2 Int,
@P3 varchar,
@P4 int,
@P5 varchar
SET @P1 = 'A'
@P2= 76
@P3='C'
@P4 = 987
@P5='X'
Select * from TVF_Test(@P1,@P2,@P3,@P4,@P5) as test
where test.column1=@P1
Any help will be appreciated.
Thanks
Dear all
Can any one provide me details of the parameters to be monitored in sql server 2005
Currently i am monitoring
SQLServer : Buffer Manager : Buffer Cache Hit Ratio
SQLServer : Databases : Log Flushes / Sec
SQLServer : Databases: Transactions / Sec
SQLServer : Access Methods : Page Splits / Sec.....
1) Please provide if any other relevant parameters to be monitored by perfmon.
2) Which are the most essential parameters to be monitored.
3) Is there any query or trace that will provide me performance details in SQL.
4) Can i have a query that get me the most resource consuming process or query in sql server.
5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.
It would be very helpful if you guys could provide me help on these.
Thanks in advance
Hi,
I have a report that needs to use two multi-value parameters that are linked.
The report shows stock availability for the production of trial kits and the requirement is to enter multiple quantities of multiple kits as shown below:
Parameter 1 Parameter 2
Kit A 10
Kit B 15
Kit C 25
Is there a way I can select multiple kits and then enter a specific quantity for each selection?
Any help or advice is greatly appreciated.
Thanks.
I'm Trying to create a linked server from an SQL Server to another SQLServer on another machine.Can anyone suggest some good reference material or tutorials?? So farI'm only finding examples for a linked server to an ACCESS database.Thanks in Advance!
View 1 Replies View RelatedI have run into a very odd issue with the performance of one of our stored procedures. The SP seems to perform very poorly if I embed the parameters directly into the query. If I declare local variables to hold the parameter values, the SP runs a lot faster.
This stored procedure takes about 26 seconds:
CREATE PROCEDURE dbo.jk_ReportData_Get
@startDate datetime, @endDate datetime, @entityID int, @reportID int
AS
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
When I change it to the following, it takes less than one second:
CREATE PROCEDURE dbo.jk_ReportData_Get
@temp1 smalldatetime, @temp2 smalldatetime, @temp3 int, @temp4 intAS
declare @startDate datetimeset @startDate = @temp1declare @endDate datetimeset @endDate = @temp2declare @entityID intset @entityID = @temp3 declare @reportID intset @reportID = @temp4
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
Can anyone explain this please??? :) Notice it is the same query in both cases. The only difference is in the parameters...
Hi all,
I have a report which uses two different datasets. The first one is used to get the required data and the second one is to get the customer name to be used in a parameter.
I had a major performance issue with the report as the first dataset is very complex. And after I did a lot of SQL tuning to the first dataset, it is now working fine with an acceptable performance.
The problem now is when I tried to add a condition in this dataset to be used by the parameter like
WHERE CUSTOMER_NAME IN (@CustName )
The performance of the report is getting very bad again and the report can take more than 15 min. to get the result.
The second dataset is a simple query that is used to get the customer name from the customer table so I don€™t think it is the problem.
Any ideas? Do you think it has anything to do with the SSRS parameters.
Thanks all.
Can I change the SQL statement on a linked report without modifying the original? I'm trying to limit a category by userid. I basically want to add an additional line to the where clause. Any ideas?
View 1 Replies View RelatedHi,
I updated a report adding parameter expressions. A linked report based on the report I updated didn't get the parameter expressions when I deployed the base report. Anybody else having that issue? It's not much of a problem. I just have to recreate the linked report which is a nusance.
Thanks,
DD
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 RelatedWe 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>".
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.
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.
I am transferring data from SQl Server 2000 to Oracle through LinkedServers.It takes considerable amount of time while transferring datafrom SQL Server to oracle.Both these databases are at remotelocations.I am executing a Stored procedure on SQL Server and what itdoes is,It has an Insert statement which reads the data from a table inSQL Server and inserts it into the Oracle table. It is a very timeconsuming process.Vice-Versa it takes very less time for Oracle-SQLServer transfer.If anybody has a clue towards reducing the time in SQLServer-ORACLE transfer.
View 3 Replies View RelatedI 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.
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".
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.
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?