Linked server SQL 2005 to 2000

SQL Server Setup & Upgrade

Index ‹ SQL Server

- Previous
  - 1
    - Cannot Pull table with RDA Change your OLEDB connectionstring: Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;" or Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=192.168.0.199;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;" Tag: Linked server SQL 2005 to 2000
  - 2
    - How to connect to remote server Very few hosting providers will allow you to connect to their server using SSMS; rather they provide their own set of management tools accessable through their site. If you log into your account you will likely find a set of links that take you to the management console for your hosted database. From there you should be able to load the script file that you saved from the DPW and run it to create your objects in the hosted database. As others mention, contact your hosting provider if you have questions about managing your hosted database. Mike Tag: Linked server SQL 2005 to 2000
  - 3
    - SQL 2005 Standard on Vista RTM Samuel Lester - MSFT wrote: Can you read through this thread and see if it applies It suggests that there is a WMI configuration issue. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=124864&SiteID=1 Thanks, Sam Hi, Yes I went through the steps with the WMI batch etc yesterday without any difference to the outcome. Thanks. Tag: Linked server SQL 2005 to 2000
  - 4
    - Performance-Issue with variables If you are interested, here is a more 'indepth' exclication of what happens, and how to avoid 'parameter sniffing'. Stored Procedure –Parameter Sniffing http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx http://tinyurl.com/f9r2 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx Tag: Linked server SQL 2005 to 2000
  - 5
    - cannot connect to server This is a security related problem of the Windows Server or the IIS Worker process, see more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=789797&SiteID=1 Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Linked server SQL 2005 to 2000
  - 6
    - using encryption via an asp site. how is it done You can provide access to data via a view that automatically decrypts using decryptbykeyautocert, for example. You can then group your Admin users in a role and grant permissions to that role to use the key. Admin users would then be able to decrypt the data and see it; regular users would not be able to. Thanks Laurentiu Tag: Linked server SQL 2005 to 2000
  - 7
    - The '[CDbl]' function does not exist. I once had a weird situation on an IA64 machine where I could only get the VBA functions working by prefixing them with VBAMDX instead of VBA (VBAMDX is the name of the assembly on the server). I found that this worked on both IA64 and 32bit editions. Not sure why this is, as far as I understand the VBA prefix is meant to work on IA64, but I don't have any other IA64 servers that I can check this against. I'm not sure if IsNull() exists as a function or not, but you will find that the IsEmpty() function is equivalent. Tag: Linked server SQL 2005 to 2000
  - 8
    - How to acces report of other system from my system. Mahender, Your report server URL should be http://<ServerName>/ReportServer . In order to view your report, the user will need to be in the 'Browser' role. Hope this helps. Jarret Tag: Linked server SQL 2005 to 2000
  - 9
    - FTP Connection problem with "The Password was not allowed" error message I had set the PackageProtection to EncryptSensitiveWithPassword, but that didn't work either. As for an incorrect password, this can't be correct, because when I test the FTP connection it works fine Any other thoughts Thanks D Tag: Linked server SQL 2005 to 2000
  - 10
    - Problem with "Transfer SQL Server Objects Task" Oops, sorry for the chaos. The previous post was also made by me. I currently have two Passport identities. I'll try to not use the other one anymore... *edit* This is starting to become really weird. I've manually created a database (no tables, sps etc.) on the destination server. When I tell the "transfer... task" to copy the production database from the source server to this new database it gives me the following error: "Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions." Of course the table doesn't exist. The package is supposed to create it. A lack of permissions can't be the problem as well since I'm sysadmin. I'm really stuck here. SQL 2005 has been out for quite a while now. I just can't believe that this is a bug. Tag: Linked server SQL 2005 to 2000
  - 11
    - Problem running SSIS from C# program. The specified package could not be loaded from the SQL Server Database /r/n I tried both this Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null); and this Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null); I also tries adding .dtsx to the SSIS name. Tag: Linked server SQL 2005 to 2000
  - 12
    - Schema use in SQL2005 Which schema is dnn_Forum_AA_StatisticsSiteUpdate defined in Which schema is dnn_Forum_Statistics_Site defined in The behavior of SQL Server 2005 is the same as SQL Server 2000 with respect to resolution. If you do not specific a schema, it will first look for the object in the default schema of the user. If it does not find the object in the default schema of the user, it will fall through and look in the dbo schema. If it is not found in the dbo schema, it will fail. It should be noted that not fully qualifying the name of an object in SQL Server is just as bad of a practice as not fully qualifying an object in any programming language. It leads to issues and the default resolution order is not guaranteed to be preserved from one version to another. Tag: Linked server SQL 2005 to 2000
  - 13
    - Granting UPDATE for only certain columns in a table Thanks for your support. Here is a small bit of code that is able to produce the problem. It seems to be associated with having a JOIN in the UPDATE statement. An UPDATE without the JOIN works just fine. Using a DB_OWNER account perform the following table creation, insert, and grant commands: create table trs.dbo.people ( name varchar(10) , sex varchar(10) , age smallint ) insert into people values ('tom', 'male', 10) insert into people values ('jane', 'female', 18) insert into people values ('sue', 'female', 22) create table trs.dbo.grads ( name varchar(10) , grad_yr varchar(4) ) insert into grads values ('jane', '2006') insert into grads values ('sue', '2002') grant update (age) on trs.dbo.people to calc Then, from the db_datareader account, calc, run the following UPDATE statements, one at a time. update p set age = 40 from trs.dbo.people p where (age is not null) ; update p set age = 35 from trs.dbo.people p inner join trs.dbo.grads g on p.name = g.name where (age is not null) ; The first one works. The second one fails with error message Msg 230, Level 14, State 1, Line 1 UPDATE permission denied on column 'name' of object 'people', database 'TRS', schema 'dbo'. Does this help Tag: Linked server SQL 2005 to 2000
  - 14
    - Event ID: 17055 I am running a WSUS and get this error in my event log. I am running Windows 2000 with SQL 2K SP4. 1827 CREAR/ALTER DATABASE failed because the resulting cumulative database size would exceed you licensed limit of 2048MB per database. Tag: Linked server SQL 2005 to 2000
  - 15
    - Trouble with Pivot Task (multiple problems) - Long All, I would not really call this a fix, but in case anyone has similar problems... I have used a for each loop to feed the data flow task one row to pivot at a time. All rows pivot and write to the destination table without any errors being reported. Why would pivot fail on multiple expected output rows when each is able to successfully process While It would be nice to get feedback regarding the original problems, since I found a work around this is successfully completing. Thanks. Tag: Linked server SQL 2005 to 2000
  - 16
    - @@Version still shows Service Pack 1 Yeah, the SP1 in this case refers to your Windows Version: Windows NT 5.2 (Build 3790: Service Pack 1) The Service Pack level apparently isn't listed in the @@VERSION as I thought it was. Build 3054 is definitely SP2, but you can also check Add/Remove Programs report or the setup regitry hives to confirm. Thanks, Sam Lester (MSFT) Tag: Linked server SQL 2005 to 2000
- Next
  - 1
    - SQL Questions Thanks but how would you identify and resolve these issue. What you mentioned I knew it could be one of those but how actually I come to know that this is the issue and fixing it. Regards Tag: Linked server SQL 2005 to 2000
  - 2
    - Line Chart Color for each value u need to select colors. u may have different values inside data in chart properties , for each case u need to follow the same path and choose different color.it worked for me. i hope it will work for u also Tag: Linked server SQL 2005 to 2000
  - 3
    - Im new to using views, and this program they have me working on is using them quite heavily..(cant read from a view) Not a problem. We ALL could write a book on the boneheaded things we've done -even when we knew better! Tag: Linked server SQL 2005 to 2000
  - 4
    - Insert Performance Hey, I'm writing an application where I have to perform a lot of inserts/updates on a SQL Server Compact Edt. 3.1 database (up to 50.000 or even 100.000 consecutive inserts). I'm using native C++ and OleDB. I set the command text only once for each table and I increased maximum buffer size (DBPROP_SSCE_MAXBUFFERSIZE). There are also no table indices or foreign keys. The performance is quite well, but not good enough. I took a closer look at the MS Merge Replication and I observed a really good insert performance. Maybe somebody can tell me some ''secrets'' how I can speed up my query performance! best regards Tom Tag: Linked server SQL 2005 to 2000
  - 5
    - Cannot see Report Builder in Report Manager By default members of the Administrators local group should be able to administer the report server and also add you to the appropiate group. If you are an administratior, you should use your own credentials, otherwise any local administrator (if this has not been disabled) Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Linked server SQL 2005 to 2000
  - 6
    - Update = Delete & Insert? I think you are referring to in-place updates vs delete-insert updates. Delete-insert updates might occur when you change the value of a (non)clustered index field or the row no longer fits on the page after the update. In-place update is normally the strategy chosen most often by SQL Server. This behavior still applies to SQL Server 2005. WesleyB Visit my SQL Server weblog @ http://dis4ea.blogspot.com Tag: Linked server SQL 2005 to 2000
  - 7
    - odd issue with NOT IN statement NULL fails it, NULL values cant be compared......... Tag: Linked server SQL 2005 to 2000
  - 8
    - Looking for Feedback: Native 64-bit Support in SQL Express What advantages will a native 64-bit Express offer you If the 64 bit version also carried Reporting Services in native 64 bit, then the advantage would be that we could run SQL Express with Advanced services on the same box as Exchange 2007 (currently they are mutually exclusive due to E2007 not liking WoW on IIS, therefore SSRS is out). One coherent 64 bit platform with components that all play nice would be a huge headache off my plate. Does a 64-bit Express eliminate the need to support 32-bit WoW (Consider that without WoW, we would not have a single package that could install on both architectures.) No, (unless of course you can port the reporting services to 64 bit for Express as well.) If you could only have either native 64-bit Express or WoW support, which would you choose (Bonus if you tell me why. ) The answer is tricky and there would be work to do before you could cut off one or the other: 1) On exchange boxes: 64 bit native if SSRS was also delivered native (an could therefore run on IIS in 64 bit) 2) On non exchange boxes: WoW, because I can then run Sql and IIS in 32 bit WoW and still have access to the 32bit Jet provider which we still use a lot of. (Does anyone have any friends in the Jet team, would love to put a vote in for a 64 bit port of the Jet Provider ) Generally speaking, I don't think 64 bit is ready to solo yet, as there are still so many things that we would have to leave behind to do this. What user/application scenarios are driving your need for native 64-bit Express So far, nothing performance based, as mentioned in point one, at the moment with all our Data Access solutions, (not that many really) it is very hard to put together one platform that actually works cohesively. There seem to be workarounds available, but it is to the point where some workarounds mutually exclude others. The main driver is our clients are upgrading their hardware, so I guess simple evolution. FYI It is a great product, and appreciate the thought given to keeping installation simple etc. Tag: Linked server SQL 2005 to 2000
  - 9
    - Incorrect processing order for views Hi Martin Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together aero1 Tag: Linked server SQL 2005 to 2000
  - 10
    - How to insert into a table with a function? OK, I have started to built a basic function in which a sp is called. ALTER FUNCTION [dbo] . [fnImportResult] RETURNS bit AS BEGIN EXEC dbo . [TestInsert] @ControleDatum = '12-10-2007' , @BronDatabase = N 'aaa' , @Tabelnaam = N 'bbb' , @AantalRecords = 3333 RETURN 0 END If I run this function I get the following message: Only functions and extended stored procedures can be executed from within a function. When only the EXEC part is run then no error message What am I doing wrong Thanks again, Frans Tag: Linked server SQL 2005 to 2000
  - 11
    - Data disappearing during Run time This is what Mike is refering to: http://blogs.msdn.com/sqlexpress/archive/2006/07/17/668971.aspx HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Linked server SQL 2005 to 2000
  - 12
    - Store Membership information using Access .mdb database Try this one it is better than the one you used, if you still get errors then post the code conversion issue on both VB and C# forums other user can help you with the conversion. http://www.carlosag.net/Tools/CodeTranslator/Default.aspx Tag: Linked server SQL 2005 to 2000
  - 13
    - Analysis Services Roles - Dimension Data thanks Bryan setting the visual totals on the dimension data assignment has resolved this Mark Tag: Linked server SQL 2005 to 2000
  - 14
    - Why does OPENROWSET not connect? We have lots of OPENROWSET usage here, all with embedded user id's and passwords. In an attempt to find a way to eliminate such a foolish practice I created a system DSN on my PC and tried to use it to connect to our sqlserver 2000 database as follows: SELECT a.* FROM OPENROWSET('MSDASQL','DSN=TargetDB', 'SELECT * FROM table1') AS a Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. Authentication failed. [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. I thought the reason to create an ODBC system data source was to not have to enter credentials in code but use the credentials in the system DSN. I guess I was wrong. Is there any way to not embed user id's and passwords in OPENROWSET code Having user id's and passwords in code is dangerous and foolish and I am surprised people do it. Tag: Linked server SQL 2005 to 2000
  - 15
    - Order By not ordering Month names the way I want You beauty!!!! I don't suppose you can help with the England v Russia game can you Tag: Linked server SQL 2005 to 2000
  - 16
    - How to make an insert table lock With the more information you provided, I would change your INSERT into a BCP or BULK INSERT. The INSERT...SELECT * is pretty slow. If you export the data and use BCP or BULK INSERT, you will be 100 times (or more) faster and will lock the target table for a much shorter time. Also set the batch size to a number like 10,000 or 50,000 which will insert that many records as 1 transaction and drop the lock. That might let the ETL lock the table, and delay the import, but the ETL will finish. This might fix your problem. Tag: Linked server SQL 2005 to 2000

Kevin6

I have something similar...

On a SQL 2005 default instance on a box named 'machine1', I define a linked server which points to a SQL 2000 instance also located on 'machine1'. In SSMS, I right-click to use 'add linked server' to add 'machine1\sql2000' (taking the SQL Server radio button as the default). The definition works fine.

I then try to use the linked server in a four-part name, e.g. 'select * from machine1\sql2000.dbname.dbo.foobar'

and I get a syntax error complaining abou the backslash. No amount of quotes seems to help.

What's the trick for defining a linked server in a SQL 2005 instance where the linked server is a named SQL 2000 instance on the same machine and then using that linked server of the SQL 2000 instance in a T-SQL query

thx

Top

Re: Linked server SQL 2005 to 2000

Arnie Rowland

Moved to Setup and Upgrade forum...

Top

Re: Linked server SQL 2005 to 2000

Arnie Rowland

You have a couple of options.

Either 'wrap' the server/instance name in square brackets, e.g.,

SELECT

Col1,

Col2,

etc

FROM [machine1\sql2000].dbname.dbo.foobar

or, Create an Alias for the linked servername.

Top