Link through 6.5 server to 2000

Is there a way to setup a link through a 6.5 server to a SQL2k database?

Here is our problem: we have a product running on Sun Solaris that connects to a 6.5 database. We upgraded the database to 2k, nad the product can no longer connect - knowledbase for the product suggests setting up a link on 6.5 server to reference teh 2k box (fooling the product into thinking it is still talking to 6.5). Unfortunately, they give no instruction on how to do this. Any ideas?

Craig DolsWhat's the product?

Been a long time since 6.5, but I didn't think (what, again!) that 6.5 had linked servers...

Or it's just that I never needed/ used them...|||The product is Datastage - I know very little about it, I'm just the SQL geek. From what the sysadm is telling me, the version tehy ahve installed does not support SQL2k - and it will be at least a month to get it upgraded - so I need to find a quick temporary fix.|||Never heard of it...

Check out this...

http://www.tools4datastage.com/|||Thanks for the link - I don't see what i need there, but maybe they can point me towards something.

Danke!|||Originally posted by Brett Kaiser
Never heard of it...

Check out this...

http://www.tools4datastage.com/

Datastage usese ODBC connections to get data from MS SQL server to and from.. ODBC is MS SQL native driver. Set up two odbc connections on the DATASTAGE server. Then in DS you can pump data back and forth applying all the rulles that you need. The ds website posted above is awsome and will get you all the help you need.
Jim

Link Server Connection failure

I have a linked server defined in my SQL 2000 server that connects to an Ora
cle databse that stops working for no apparent reason. Restarting SQL Serve
r fixes the problem. The linked server works fine for months at a time and
then stops working and give
s a login failure message. Nothing gets written to the server event log or
the SQL Server error log.Where do you see the login failed message? What is the exact text of the
login failed message? Once you start getting this message can you use the
Oracle client tools on the SQL Server box to connect to the Oracle instance
using the same login SQL Server is configured to use?
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

limit rows for a user

Is it possible to set a limit for the number of rows to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto

I don't think so unless you have access to the tool that this user is using.
@.@.rowcount being a connection specific setting, it must be set 'by the user' so to speak.

Depending on how the connection is done it may be a viable solution to sneak in a SET command somewhere?

/Kenneth

|||

One way to do this in SQL Server 2005 would be to use the TOP clause in view definition/SPs (this assumes that you are using one of these as primary data access mechanism). You can have a table that contains say the user and the number of rows. You can then add a TOP clause to your query like:

select TOP(select rowcnt from user_quota where user_name = SYSTEM_USER) ...

from ...

order by ...

You can use same technique in SQL Server 2000 in SPs and use SET ROWCOUNT.

|||Hi, thanks for your response, but the problem it's this:

our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder.

We cannot "touch" the way reporting services builds the queries (with several joins), all we need it's to set @.@.rowcount=10000 for the user (always th same) that reporting services uses to connect to sql server. In other words we need to execute a T-SQL command after user logon so that @.@.rowcount property it's set for every query the user makes.

Thanks.

Alberto|||You will have to ask this question in the Reporting Services forum then since it is not a TSQL issue. Maybe reporting services has some mechanism that will allow you to specify a pre-command.

Limit rows for a user

Is it possible to set a limit for the number of rows to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?

Thanks.

AlbertoLimiting the affected rows is possible though various mechanisms like ROWCOUNT, TOP, ROW_NUMBER() but you are not able to do this per se. You will have to put the statement in all of your queries. There is no way in the query language to change the behaviour neside the ones mentioned.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

limit rows for a user

Is it possible to set a limit for the number of rows to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto

I don't think so unless you have access to the tool that this user is using.
@.@.rowcount being a connection specific setting, it must be set 'by the user' so to speak.

Depending on how the connection is done it may be a viable solution to sneak in a SET command somewhere?

/Kenneth

|||

One way to do this in SQL Server 2005 would be to use the TOP clause in view definition/SPs (this assumes that you are using one of these as primary data access mechanism). You can have a table that contains say the user and the number of rows. You can then add a TOP clause to your query like:

select TOP(select rowcnt from user_quota where user_name = SYSTEM_USER) ...

from ...

order by ...

You can use same technique in SQL Server 2000 in SPs and use SET ROWCOUNT.

|||Hi, thanks for your response, but the problem it's this:

our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder.

We cannot "touch" the way reporting services builds the queries (with several joins), all we need it's to set @.@.rowcount=10000 for the user (always th same) that reporting services uses to connect to sql server. In other words we need to execute a T-SQL command after user logon so that @.@.rowcount property it's set for every query the user makes.

Thanks.

Alberto|||You will have to ask this question in the Reporting Services forum then since it is not a TSQL issue. Maybe reporting services has some mechanism that will allow you to specify a pre-command.