SQL 7.0 Compatibility &&/or Linked Server Parameters
Jan 31, 2007
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
View 2 Replies
ADVERTISEMENT
Apr 20, 2001
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.
View 1 Replies
View Related
Jul 8, 2004
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.
View 5 Replies
View Related
Apr 3, 2008
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.
View 1 Replies
View Related
Dec 17, 2007
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 Related
Jul 17, 2007
Hi,
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
View 5 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
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
May 13, 2008
Hi,
I am trying to install SQL server 2000 on my laptop which has vista as OS. I am not able to install it , somehow it fails. Can anyone please suggest me how can i accomplish this.
Thanks
Divya
View 13 Replies
View Related
Jul 20, 2005
Is SQL Server 7.0 compatible with the Windows 200 operating system? Arethere certain patches or service packs that need to be applied to SQL tomake this compatibility work? Thanks.Larry
View 2 Replies
View Related
Jun 12, 2006
Are there any known issues with using RDO with SQL Server 2005?
My company is in the process of updating our SQL Server 7 database to
SQL Server 2005. Most of our applications are several years old and
written using RDO to access the database. I have since started the
daunting task of updating all the applications to ADO, but it's only
about a quarter of the way done. We need to update the database ASAP,
but we cannot wait until all the applications have been updated to ADO.
While testing the applications (written in VB6) against the new SQL
Server 2005 database, I have come across a problem with the RDO queries
not returning the correct data. The query returns 4 records. In the
SQL7, it properly shows 4 for RowCount and 4 records are present in the
recordset (as seen by looping through the recordset using rs.MoveNext).
In SQL2005, it properly shows 4 for RowCount, but looping through the
recordset shows that the recordset only contains 1 record. The problem
seems to be linked to a Left Join in the Query. (see below) Table1 has
a column that has a RecID for Table2. In the case where the Table2RecID
column in Table1 is 0, the row is just not added to the recordset even
though it increments the RowCount.
Code:
SELECT * FROM Table1 As T1
LEFT JOIN Table2 As T2 On T1.Table2RecID = T2.RecID
I converted the query to ADO and it ran fine with no problems. The issue has something to do with SQL Server 2005 and RDO.
View 11 Replies
View Related
Jul 11, 2002
We are going to upgrade to windows 2000 in the near future.
We currently use sql server 6.5.
Can anyone tell me if SQL 6.5 will run on windows 2000?
Also will SQL server v7 run on windows 2000 or do I need to upgrade to SQL 2000?
Many thanks.
View 1 Replies
View Related
Dec 7, 2007
I am a beginner with SQL Server and SharePoint. I am trying to migratethe database to other servers that may be running differentplatforms. I know that the other servers have SharePoint so they arerunning SQL Server 2005, SQL Server 2003, and Access. Is there a way Icould get data from my system (with SQL Server 2005) to another system(that may be running 2005, 2003, or Access) without affecting my dataor SharePoint workflow? I want the people running the other servers tobe able to access the same data and see the same workflow I do. Thanksin advance!
View 1 Replies
View Related
Mar 13, 2007
A client just purchased a new notebook, pre-installed with Vista. He says he is having trouble installing SQL Server 2000 (database and client tools). I am not certain (yet) if this is the standard edition or the desktop version.
What, if any, are known compatibility issues between SQL 2000 and Vista?
View 5 Replies
View Related
Jan 6, 2006
Hallo Everyone,
I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work
Any ideas?
Nigel...
View 12 Replies
View Related
Oct 24, 2006
Hi. I am using visual studio 2005 with Sql Server Express Edition. I haven't install sql server 2005 Professional edition on my machine. It works fine. The connection string is:
<add name="RealEstateConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|RealEstate.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
You can see I haven't hardcoded machine name and there is no absolute path.
I have open the website on another machine and it works fine. Now , i have open this project on third machine but it doesn't work and gives the following error:
"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)"
I think there is some problem with sql server configuration . Please help me in this regards. I will be very thankful to you.
View 3 Replies
View Related
Jan 5, 2001
I'm running SQL Server 7.0. I have a DB running with 6.5 compatibility mode.
Do INSERT, UPDATE or DELETE queries use row level locking in this DB ?
(I know if I set the db compatibility mode to 7.0 row level locking will be enabled)
Thanks in advance for your help.
View 1 Replies
View Related
Jan 31, 2006
Can someone tell me if I can use the Gui tools which come with SQL Server 2005 against 2000 databases ?
View 1 Replies
View Related
Mar 10, 2008
Hi to All,
I want to choose between Windows Vista editions. Please help me I should go for Windows Vista Business or Windows Vista Home Premium as I intend to install SQL Server 2005.
Thanks to All
View 3 Replies
View Related
Sep 11, 2007
We have the following trace flags present in startup in SQL server 2000:
809
1204
3605
3913
Need to understand if these should be required in SQL Server 2005 + SP2 version. I have run the upgrade advisor tool which indcates that the behaviour of some flags has changed and some other TFs are no longer applicable. Hence, I want to know about the above mentioned TFs.
Regards,
Chetan
View 1 Replies
View Related
May 1, 2007
Sorry.. but.. sql server express has compatibility with windows vista????
View 1 Replies
View Related
May 16, 2006
Hi,
When using an Access Project 2002 connected with SQL Server Express in OLE DB it's impossible to work in creation mode. A message explains that this Access version is not compatible with this SQL version.
Is there a service pack ?
View 1 Replies
View Related
Oct 23, 2007
Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
Thanks in Advance,
Mitesh
View 4 Replies
View Related
Feb 18, 2008
Hello everybody,
I have question ablout the installing SQL Server 2005 in the windows XP profesional, I always install sql server on my machine with the Windows XP professional edition as usual without any problem but now
I have a strange problem, first on the System configuration stepeverything is OK except €œSQL Server Edition Operating System Compatibility (Warning) €œ ,with this message :
- SQL Server Edition Operating System Compatibility (Warning)
Messages
·SQL Server Edition Operating System Compatibility
·Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
Meanwhile the main problem is in the €œ Component to install €? window as I don€™t see these serises of component checkbox enable! Except the €œWorlstation component, books online and development tool €œ,
sql server database services -->disable
anlysis services -->disable
reporting services -->disable
notification services -->disable
integration services -->disable
Worlstation component, books online and development tool -->enable
And after that I make sure about the serious problem and then I decide to continuing installation with clicking on the enable check box and then I have an instance of SQL Server on my machine but it isn€™t compelete .
I tried to solving my problem with unistalling the SQL Server and reinstalling the this software again but this problem still exist, and then I decide to change my window and reinstalling this again , I do that but
It is not work out!!
Therfor if it possible for you please help me to solving my problem, thank you very much
View 10 Replies
View Related
Jun 27, 2014
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies
View Related
Jul 13, 2007
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
View 12 Replies
View Related
Sep 17, 2007
Can you run SQL Server 2005 in SQL Server 2000 compatibility mode and still use it with Reporting Services 2005?
View 4 Replies
View Related
May 19, 2006
Not sure if this gets fixed on a restart, but the Backward Compatibility upgrade/piece failed on service pack 1 install for SQL Server 2005. Here is the log output. Anyone experience this?
05/19/2006 08:58:27.648 ================================================================================
05/19/2006 08:58:27.648 Hotfix package launched
05/19/2006 09:00:04.368 Attempting to install instance: SQL Server Native Client
05/19/2006 09:00:04.383 Attempting to install target: CLARITY
05/19/2006 09:00:04.383 Attempting to install file: sqlncli.msi
05/19/2006 09:00:04.399 Attempting to install file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi
05/19/2006 09:00:04.399 Creating MSI install log file at: C:WINDOWSHotfixRedist9LogsRedist9_Hotfix_KB913090_sqlncli.msi.log
05/19/2006 09:00:04.415 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
05/19/2006 09:00:04.415 Failed to read registry key: Debug
05/19/2006 09:00:51.040 MSP returned 3010: A restart is required to complete the install. This message is indicative of a success. This does not include installs where the ForceReboot action is run. This error code is not available on Windows Installer version 1.0.
05/19/2006 09:00:51.118 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
05/19/2006 09:00:51.118 Failed to read registry key: Debug
05/19/2006 09:00:51.118 Pending reboot, successfully installed file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi
05/19/2006 09:00:51.134 Successfully installed target: <SERVER>
05/19/2006 09:00:51.134 Successfully installed instance: SQL Server Native Client
05/19/2006 09:00:51.134
05/19/2006 09:00:51.134 Product Status Summary:
05/19/2006 09:00:51.134 Product: SQL Server Native Client
05/19/2006 09:00:51.134 SQL Server Native Client (RTM ) - Reboot Required
05/19/2006 09:00:51.134
05/19/2006 09:00:51.134 Product: Setup Support Files
05/19/2006 09:00:51.134 Setup Support Files (RTM ) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Database Services
05/19/2006 09:00:51.150 Database Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150 Reporting Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Notification Services
05/19/2006 09:00:51.150 Notification Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Integration Services
05/19/2006 09:00:51.150 Integration Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Client Components
05/19/2006 09:00:51.165 Client Components (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: MSXML 6.0 Parser
05/19/2006 09:00:51.165 MSXML 6.0 Parser (RTM ) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: SQLXML4
05/19/2006 09:00:51.165 SQLXML4 (RTM ) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: Backward Compatibility
05/19/2006 09:00:51.165 Backward Compatibility (RTM ) - Not Applied
05/19/2006 09:00:51.181
05/19/2006 09:00:51.181 Product: Microsoft SQL Server VSS Writer
05/19/2006 09:00:51.181 Microsoft SQL Server VSS Writer (RTM ) - Not Applied
05/19/2006 09:00:51.181
View 5 Replies
View Related
Jun 15, 2015
We have recently migrated quite a databases around 20 from SQL 2000 and 2005 to SQL server 2008R2.
I am using Ola's script for index maintenance for those with compatibility level above 80 as i heard it supports that way.
Hence separated in 2 way job where for those with compatibility level 80, we are running job with below query on each database with 80 as compared
USE ABC
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
I am not sure if this is the only way in for those databases, because we are seeing the database getting because of that somewhere using above query.( seems log file filling very rapidly).
But above is not the case with those databases , with compatibility 90 and above.
View 5 Replies
View Related
Apr 3, 2007
Ok guys, I am trying to install Sql server 2005 on Vista but I am still stuck with this warning message in the System Configuration Check during Sql server 2K5 installation :
SQL Server Edition Operating System Compatibility (Warning)
Messages
* SQL Server Edition Operating System Compatibility
* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
Now, I know I need to install SP2 but how the hell I am going to install SP2 if Sql server 2005 doesn't install any of the components including Sql server Database services, Analysus services, Reporting integration services( only the workstation component is available for installation)?
Any work around for this issue?
P.S.: I didn't install VS.NET 2005 yet, can this solve the problem?
Thanks.
View 8 Replies
View Related
Apr 1, 2006
Hi All,
The IP company that host my site is installing SQL 2005 soon and I my building a web application with SQL 2005 Express. So the question is has anyone heard of any problems with uploading an 2005 express onto a sql 2005 server? I would like to find out now before I finish all of the work. Don't want to find out later that you can't run a sql 2005 express database on a sql 2005 server.
Thanks for taking the time to read this.
View 1 Replies
View Related
Mar 18, 2002
Is it possible to backup databases from a 7.0 box and restore them to as MSSQL 2k box? My question really is this: Does it matter what type of MSSQL server your database resides on or can a dump be created of a database, restored to a different version MSSQL server, and used interchangeably?
View 1 Replies
View Related
