Hi all, I use MSSQL 2000. In QueryAnalyzer, I wrote belows: =============== Code =============== CREATE TABLE t1( [col1] ntext NULL, [id] int identity(1,1) PRIMARY KEY ) INSERT INTO t1 (col1) SELECT REPLICATE('u',4000) INSERT INTO t1 (col1) SELECT REPLICATE('u',4001) SE...more >>

Hi, Is it possible to get some additional information about Stored procedures in SQL2005 -i want to get to get the list of stored procedures which uses for example the parameter @vendor and type and size of this parameter (nchar,varchar,nvarcher..) in the SP, to detect if this parameter ...more >>

I am using closed system, which can execute store procedure via jdbc interface. The problem is that, after every execution of store procedure exist process in sleeping state with "awaiting command" command. Is exist any option to manually, in code of my store procedure, say, that process shoul...more >>

Hello, See the queries below, the first one does the clustered index scan when the 2nd one does the index seek via nonclustered index idx_Date1. Does anyone know why? If the table is large, it's less efficient to use clustered index scan. Is there a way to force it to use the index seek?...more >>

Hi, I'd like to write a small application to make life a bit easier for our software commissioner. So he can install sql server, run my application to create the tables and set up the initial records in there without having to follow crib sheets. I've got sql server manager to write the ...more >>

Hi, I have a scheduler job that's defined as below: EXEC dbo.spCheckSystemFailure @Threshold = 5 Now the Threshold is stored in a database table, "SystemParameter". The way to get @Threshold is by executing the following statement: SELECT SystemParameterValue ...more >>

Hi, Inside a stored procedure, there sometimes is a statement called "RaiseError". An example is below: IF @lsSystemParamValue IS NULL BEGIN RAISERROR ('Parameter ''%s'' was NULL.',16,1,@SystemParamName) GOTO ErrorHandler END Can anyone tell me what the "RaiseErro...more >>

Below is my stored proc that is inserting but is not returning the identity value. Below is the sp. Can anyone see what is wrong? Thanks. CREATE PROCEDURE [dbo].[rfp_insProperty] (@Address nvarchar(100), @BuildingName nvarchar(100) = NULL, @BuildingPark nvarchar(50) = NULL, @Subma...more >>

Hi I have a UDF table that takes about 10 mins to run it also calls other UDF tables and passes values to them as well. I had looked at the show execution query plan and have run index tuning wizard from QA on the query and no recommendations. I have read a few places that using the show ex...more >>

How can I achieve something like: SELECT somecolumn = 1 AS isequal FROM sometable; thanks !...more >>

SQL Server 2000 I can't change the source of my data (mainframe file) or the database table or the vendor's requirement. I need to sequentially number some rows. I wrote a cursor, but it's really slow. I have a select statement that shows what the correct result should be, but it's reall...more >>

I have a set of data where one field is a concatenated list. I would like to separate this into separate records. Each "list" could have a varying amount of items. For example: Field1 Field2 Field3 ------ ------ ------- John Smith product1+product3 Betty Smith ...more >>

When I used the statment select convert in a field datetime, for example: "select convert(varchar(20), field, 111142) from table" The service MSSQLServer is down, and in the SQL Server log shows: SqlSecurityHandler is invoked. Potential buffer overrun detected - server is terminating. U...more >>

When I execute the below statement I get 1 character returned for each column and no column headings. Please help. DECLARE CONTRACT_CURS CURSOR SCROLL for SELECT CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END FROM CONTRACTS GROUP BY CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END D...more >>

Hi, I see return code such as "RETURN 0" at the end of a stored procedure. The stored procedure is really meant to return a dataset (by a "SELECT" statement). The definition of the stored procedure is below: CREATE PROCEDURE [dbo].[spReportTypeGet] @ReportTypeID DTTypeID AS SELECT ...more >>

I have a dataset that looks like this: CREATE TABLE Scores ([ID] IDENTITY(1,1), StudentID INT, SubjectID INT, Score FLOAT) I want to get the lowest score and the subject for each student. So the results will be Student 1 MATH 50 Student 2 ENG 60 Student 3 MATH ...more >>

Is there a difference between WITH (NOLOCK) and just (NOLOCK) table hints? If there is, can you please direct me to a specific Microsoft article about it? Thank you in advance, Leon...more >>

Help, please: I get "database must be in s single user mode" when trying to restore master database. My server is in single user mode: -c -m in startup parameters. How to restore master database from .BAK file? Thank you in advance!...more >>

Hi, I use "isql" command to execute a .sql script that purges a database. However, it uses another database to set the filters besides the database being purged. I don't see how I can mention more than one database on the parameter list. Could anyone advise? FYI, the parameters I use includ...more >>

Hi All Is there any way to detect if any changes have occurred in a SQL server database since the last differential backup, other than the Database file date and time I would like to do differential backups only if data has changed Does sql server have a parameter I can read which tells...more >>

Hi all, I have to retrieve a value using TransacSQL in a Stored Procedure. Declare @amount int Declare @SQL varchar(200) Declare @Year int Set @amount= (select amount From Sale Where year=1998) /*it is working fine */ /* Now I want to use a variable in year */ Set @Year=2005 Set @SQL =...more >>

I hope the subject conveys what I'm trying to accomplish in SQL Server 2000. My question is, can it be done? I have a stored procedure that returns the results of a select query. My ASP.NET application uses a SqlDataReader to retrieve this data. But before I retrieve this data, I need to know ...more >>

Hello everyone. Despite the fact that I enabled OLE automation and xp_cmdshell in SQL 2005 Express Surface Area Config, I'm still having trouble creating a text file from SQL 2005 Express. The command line marked with *** below fails, and the variable @OLEResult returns -2146828218 ALTER...more >>

I have two databases on the same server. Using SQL Server Managment I can log and modify data the both databases. But when I use my program (C++ ODBC connection) I can login only to first database. I can't modify second database. Why? ...more >>

Hi everyone, I have a table with an incremental primary key (int). This table has about 16000 rows and I want to delete the data and only remain 20 rows. How can I do this into a single delete statement? Thanks in advance for your response, anything would be helpfull, Kostas...more >>

How can I get parts of the result query? query1 - I get 10 procent result beginin with index 0, query2 - I get next 10 procent result of the query, query3 - I get next 10 procent result of the query, .... ...more >>

I've got this where clause: (DATEDIFF(HOUR, dbo.MileageMaster.DateSubmitted, CURRENT_TIMESTAMP) > 72) basically, that is just making sure it's anything returned from the query is at least 72 hours old. That's great except now I'm told that needs to be business days only - not on a Friday ...more >>

Hi NG, I have spent 3 days searching on how to debug stored procedure in MSS 2005 but it keeps mentioning VS 2005. Does this mean that I have to buy VS 2005 just to debug a TSQL stored procedure? Micorosoft, what have you done on the debug feature of MSS 2000 why is there no simple way ...more >>