sql-server-2000

Exposing SQL Server 2000 to the Internet safely

Hello Everyone, I've got a SQL Server 2000 box that I'd like to put on "the Internet" so that developers could connect remotely without VPN access. What's the safest way to do so? It might be temporary, e.g. every once in a while, but it's definitely necessary. Thanks, Rob ...

SQL Server Mgt Studio not showing table?

I have a table in SQL Server 2000 Standard Edition called "dbo.T668" (don't blame me for the naming convention). I'm working on the server using SQL Server Management Studio 2005, and for some strange reason, I can't see the table at all in the list of tables. I see all the other tables, but not this one. If I open up a query editor a...

Simplest way to get the number of calls to an MSSQL2000 Server

Hi All, Does anyone know how one can get the total number of calls to an MSSQL2000 server during a specified time, let’s say 24 hours? We want figures of how many calls our production machine gets per day, but we can’t find any good tools/strategies for this. Best regards Fredrik ...

How do I store XP_SMTP mail result code messages?

I am trying to keep a record of all of the emails that get sent via xp_smtp mail component, but I don't know hw to store the message. Storing a 1 because it failed doesn't do me much good. DECLARE @rc INT EXEC @rc = master.dbo.xp_smtp_sendmail... --Once the email is sent put the results (pass or fail) into another table: EXEC pr_Inser...

Does somebody knows a T-SQl query to obtain the SQL Server Users and their DB associations and permissions?

Hi, Does somebody knows if its possible to build a T-SQL query to obtain the SQL Server Users ,their DB associations and permissions? this is in SQL server 2000 i will appreciate!! Thanks, ...

sql server 2000 backup directly to a separate server

SQL Server 2000 on Windows Server 2003. I am trying to run a backup, from within EM, directly over to another server, as follows: backup database AbraEmployeeSelfService to disk = '\\servername\f$\Backup\myDB_backup.bak' I get this error: Cannot open backup device '\\servername\f$\Backup\myDB_backup.bak'. Device error or device off-...

MS-SQL 2000: Turn off logging during stored procedure

Here's my scenario: I have a simple stored procedure that removes a specific set of rows from a table (we'll say about 30k rows), and then inserts about the same amount of rows. This generally should only take a few seconds; however, the table has a trigger on it that watches for inserts/deletes, and tries to mimic what happened to a l...

SQL Server 2000 - Query a Table’s Foreign Key relationships

Nearly identical to http://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships, but for SQL Server 2000 For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. ...

What is a good way to paginate out of SQL 2000 using Start and Length parameters?

I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting optio...

LINQ Incompatibility Issue with SQL Server 2000

Hello, I have a Linq to SQL query that was working just fine with SQL Server 2005 but, I have to deploy the web app with a SQL Server 2000 and, when executing that query, I get his error: "System.Data.SqlClient.SqlException: The column prefix 't0' does not match with a table name or alias name used in the query." I have more queries b...

MSSQL2000: Using a stored procedure results as a table in sql

Let's say I have 'myStoredProcedure' that takes in an Id as a parameter, and returns a table of information. Is it possible to write a SQL statement similar to this? SELECT MyColumn FROM Table-ify('myStoredProcedure ' + @MyId) AS [MyTable] I get the feeling that it's not, but it would be very beneficial in a scenario I have wi...

Do I need full text search and if so how to implement full text search on sql2000?

I'm using Linq2Sql for my asp.net mvc project and so far it has worked out just great. Now however I need to implement a "key word search" that searches for x key words over about 20 fields spread over 10 joined tables that are joined with a maximum depth of 3 levels. The linq function is really simpel, but the generated query is just to...

Update Statements

Hi. I am a beginner SQL user (not formally trained; OJT only) and need some assistance with a simple update statement. I would like to write an update statement that allows me to list ID's. The statement shown below is how I am currently writing it. Ideally, the statement would allow me to write it like "where plantunitid in (49-57)....

TSQL: Howto add a char to a select statement.

I have the following statement SELECT id, descr from mytable which returns 1, 'Test1' 4, 'Test4' 6, 'Test6' 8, 'Test8' 22, 'Test22' Now I want the display to Add a sequential character to the first 4 results... 'A', 1, 'Test1' 'B', 4, 'Test4' 'C', 6, 'Test6' 'D', 8, 'Test8' '',22, 'Test22' Thoughts? Edit: Would prefer a SQL Ser...

T-SQL absence by month from start date end date

I have an interesting query to do and am trying to find the best way to do it. Basically I have an absence table in our personnel database this records the staff id and then a start date and end date for the absence. End date being null if not yet entered (not returned). I cannot change the design. They would like a report by month on n...

Efficient Paging (Limit) Query in SQLServer 2000?

What would be the most efficient way to do a paging query in SQLServer 2000? Where a "paging query" would be the equivalent of using the LIMIT statement in MySQL. EDIT: Could a stored procedure be more efficient than any set based query in that case? ...

Stored procedures are timing out intermittently!

I have a number of stored procedures I call from code with ExecuteNonQuery. It was all good but 2 of my stored procedures started timing out intermittently today with: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. If I e...

Is there DDL Auditing in SQL Server 2000

And if not, is there a way to tell when a trigger was disabled/enabled? FOLLOWUP: It's a rather interesting diagnostic case. I was only involved from the periphery, and the guy doing the diagnostics isn't a database guy. Anyways, he had a trigger that would move data from one table to another. He did a comparison and not all the data ...

How do you simulate frequent database activity for realistic testing?

I'm building a web app against a database where a small number of records (about 5000) are active at the same time. Each active working record probably experiences 50-300 changes by 30 users over a 4 hour period ... which is thousands of changes per minute. Because our testing environment is so static, testing is not realistic, and som...

How to call Inline Table Function with output parameter from scalar function?

Hi guys, I would like to know if it's possible to call a inline function with an output parameter from a scalar function? For example: My inline function is called test_1(input parameter format bigint), the following example works great: SELECT * FROM MM.test_1(4679) But now I would like to use it with an output parameter ...