Hello All, Would anyone know how to convert the hexadecimal value of the first & root column of the sysindexes table. I need the decimal equivalent as I would want to use DBCC PAGE to trace the Index Pages. Looks like I'm making a mistake as I get the following error when I convert 0x9E0...more >>

WHERE (tblTjanster.iKundID = @iKundID) AND (tblTjanster.iHsID = @iHsID) AND (tblTjanster.btAvslutad = @btAvslutad) AND tblTjanster.iKundTjanstID IN (CASE WHEN @iKundTjanstID = 1 THEN (SELECT iKundTjanstID FROM tblUsersMemberKundTjanster WHERE sUserID = @sUserID)ELSE @iKundTjanstID END) ...more >>

Is it possible to specify in a compact fashion, a check constraint for SQL2K for a column which is VARCHAR(5) which can be any of the following where x is any number between 0 and 99 or blank and y and z are both any number between 1 and 99 but z is always greater than y? x y+ y-z -...more >>

Hi all, I'm kind of newbie in SQL programming and have a small problem. I'm trying to fill in test database through SQL Server Enterprise Manager. I opened a table, put some value into one of the columns and then I'm trying to get rid of that value and want it to be <NULL> (not blank). Ho...more >>

SQL Server 2000 -------------------- master.dbo.xp_fixeddrives stored procedure is reporting different values when executed by 'sa' and a regular user. Results: (When executed by 'sa') drive MB free ----- ----------- C 18432 D 118784 E 16384 (3 row(s) affected) (W...more >>

Dear expert, I create a view as below: Code: Select Studn as NAME, Studs as SEX, Studca as [CHINESE ADDRESS], Studea as [ENGLISH ADDRESS] FROM Student_Record : : Screen Output: NAME SEX CHINESE ADDRESS ENGLISH ADDRESS ------- ---- --------------------- --------...more >>

Hi I have a UDF "fun " which takes 4 parameters and returns a table(col1,col2,col3) . --i was trying to execute the following code select A from dbo.fun ( parameter1, parameter2, (select count(*) from table1), (select sum(*) from table2 ) ...more >>

I've got a table that contains timesheet data (tablename is manhoursactual). I want to count the number of times that work is done on a given project each month. This is easy enough with the following query: select p AS ProjectID, t AS TaskID, count(p) AS Occurrences, DATENAME(month, w) ...more >>

Hi, I just tried to restore a "last night backup" to a point in time. The backup has been made at 2am. The transaction log backup has been made at 12:00 am My steps were: - Make a backup of the active transaction log - Restore the 2am full backup with NORECOVERY - Restore the active tra...more >>

I am having an issue with bcp when I specify a field terminator while using the -w (nchar data type) switch. I am exporting data as a csv file so this can then be easily opened in excel. For this I need to specify a comma (,) as the field terminiator to override the default tab terminator. ...more >>

Hi, I have a table with 2 text fields (A, B), I need to write a query, one of the condition is to list records which have different value of A and B. select * from table where IsNull(A, '') not like IsNull(B, '') When total number of characters is over 8K, query won't be able to run...more >>

Hi, I have 2 tables - main & temp, All I need to do is update the modified date of main where main.email = temp.email, I wanted to use joins but I got errors.I am not sure how to use joins in this case. This works - update Main SET modified_date='08/31/2004' where Main.email = Any(select ...more >>

Hi I wrote one stored procedure which accepts ServerName as parameter, then I used sp_addLinkedServer to connect that server and I wrote a query to return all databases ,users and their permissions information in that linked server. From the front end to connect to the database I am...more >>

In testing by development application, I backed up the real data base from the user's system, and restored it over by test database. In the course of development, I have been adding new tables. Inadvertently, the owner of one of the new tables is a user on the 'real' database, and when I restor...more >>

Hi I need to know the datatypes of the resultset of a Stored Procedure in the Microsoft SQL Server. E.g. The SP CustOrderHist of the database Northwind Call: CustOrderHist 'CACTU' Return: Productname = nvarchar(40) Total = smallint(2) Does anybody know how to do this? ...more >>

All, How can I get the string with Initials? I'm having for example 2 columns with varchar and I need to create a value for a new column with the first characters from these two columns if the strings in these columns are existing? Is it a simple way to do that? Regards, Just D. ...more >>

Please help...this is what I have so far... Creating a temp table to hold all the ids from table1 that match my criteria. Then would like to delete all the records in table2 that have that id(from the temp table), but I don't know how to do it??? THANKS!!! SELECT [ID] INTO #TempAnn FROM ...more >>

I am trying to replace a text string using like but can't seem to get it to work. SQL won't accept the first instance of like. What I have so far: update note set contact_1 like '%, %' where contact_1 like '%&%' Any help would be greatly appreciated....more >>

I'm in a quandary. I have an application where one row of a table needs to be related to zero-to-many two-character state codes. One idea that has been brought up is to store all of the necessary state codes in a varchar field in a delimited format ('AK:AL:AZ') But this obviously violat...more >>

Hi, Is there anyway to override the 8k rowsize limit in SQL Server 7.0 service pack 4.0. I have a Query with several nvarchar fields. The same query runs fine in SQL Server 2000. In SQL 7.0 it will return the message 2000 rows affected but there is no data. Is there anyway to bypass this probl...more >>

Hi, I was testing the Beta Release of SQL Server 2005, error when detach database from sql server 2000 -> i attached database to SQL Server 2005 -> detached it -> error when tried to attach to SQL Server 2000 Getting an error like Microsoft SQL-DMO (ODBC SQLState: 42000) Error 1831: C...more >>

We've been using Application Roles for more than 6 months. Up to few days ago it worked very well. Suddenly it got very slow. We tried to connect to DB in the old way (Windows only security) and it works great. I am trying to see what can I do to speed up the performance using AppRoles. ...more >>

I am trying to search a column using a pattern search but haven't found a solution and thought I would query the group for an answer. NOTE: I can't stored UDF's nor temp tables on the server. This has to happen in a single SELECT statement. Records are stored in a table field as: \\PR_MM31...more >>

Hi, I have a table Notify ------------ NotifyID int Primary Key UserEmail Varchar(64), EstateId int I would like to ask if someone knows how could be written a stored procedure which must do the following: 1. select UserEmail, EstateId from Notify 2. delete the rows which were in th...more >>

sql2k does anyone know a good select statement or one command line to display indexes count for each table in a database? thank you. ...more >>

Hello, I have a SQL Server stored procedure which will return several recordsets back to the caller (multiple SELECT statements). By default when you call this stored proc from ADO.NET, you get back the tables in the DataSet as Table, Table1, Table2, etc.... Is there anyway in the sproc tha...more >>

Hi, I have a table Notify ------------ NotifyID int Primary Key UserEmail Varchar(64), EstateId int I would like to ask if someone knows how could be written a stored procedure which must do the following: 1. select UserEmail, EstateId from Notify 2. delete the rows which were in th...more >>

On SQL Server 2000, can you pass a table variable as a param to a stored procedure as either input or output? ...more >>

Hi, I'm new to MSSQL world and I have a little question: I have just added a new field to a table containing some 3000 rows... The new field has the same value for all the existent rows... How can I automatically update all rows ? I would like to put "EN" string in all rows... What to...more >>

Hi, I use a cursor in my SP. I have no problem with the FETCH NEXT instruction but I don't know why, the FETCH FIRST and FETCH PRIOR always return -1 DECLARE OF_LIST CURSOR FOR SELECT num_cr_of FROM cr_of; OPEN OF_LIST FETCH FIRST FROM OF_LIST -- always return -1. works if I write ...more >>

I think i'm making this more difficult than it is, but i'm trying to get the following scenario to work: I have a single table of various project progress data: ID, Employee, Comments, EnteredDate, etc I want to pull the most recent comment for each employee assigned to a particular proje...more >>

I would like to conactenate data from 2 numeric columns into a 3rd field with the following fixed format: XXX-XXXX Here's my syntax: update bfc_inv set export_itemcode = cast(dept as char(3)) + cast('-' as char(1)) + cast(item as char(4)) go The results are not very encouraging. I got ...more >>

Is there some "global" variable that contains the affected rows from a SQL execution? In other words, where is Query Analyzer getting the info for the response: '(x row(s) affected)' TIA, Larry Woods ...more >>

I am not certain how to accomplish this task and would appreciate any suggestions with specifics. I currently have SQL script that inserts about 25000 records into a table. One of the columns is a "BatchID" column that is constructed using the current date and time (ex.- 20040830173520 (yyyy m...more >>

Can a computed column use case logic ? Given two column X and Y can I create a computed column Z which basically says if column Y is Null populate it with X, else Y ? Thanks ...more >>

I am using ADP front end and SQL on the back end. I want to run a report based on these 2 tables. Table1 Data: Client Id Date Employee ID Record Source 23376 1/9/2004 128 Table1 23376 1/8/2004 30 Table1 23379 1/6/2004 ...more >>

Hi, in one of the tables I have a column of image data type where data is stored in binary format (encrypted). Is there a way of converting it to text (something redable)? Thanks very much ! Mirna...more >>

I have various UPDATEs and INSERTs that I would like to "test run" before I execute them "for real" in order to at least see that them are touching the right number of rows. I have been burned in the past by executing bogus commands that have done real damage to my data and I would like to get s...more >>

When attempting to run the following query to generate and then execute dynamic SQL I am getting the error message "Syntax error converting datetime from character string." DECLARE @sql varchar(5000) DECLARE @StartDate datetime DECLARE @EndDate datetime DECLARE @Media nvarchar(500) DECLARE ...more >>

Say I have a group of tables that are all related to each other in a nested tree like manner, where each table has a 'parent id' column that points you all the way up to the very top of the "tables tree". If I do a search at the bottom (leaf end) of the tables tree, is there a nifty shortcut w...more >>

I need SQL to insert the date when a record is created, but it keeps reverting to full date & time (31/08/2004 09:54:23) - I only want the date, not the time. What do I need to do to get this to work?...more >>

Hi, I have the following table: IDSupplier | Year | Sales ------------------------------------------------------------- 1 2004 12,5 2 2004 33,9 1 2005 11,5 1 2006 34,6 2 2006 39,8 .. . . .. . . .. . . My question: How could I "transform" the data to a structure l...more >>

how can i get the following sample query to insert if 'licence' is not found, but only updatye if it is found update table1 set name = ' TEST' Where Licence = '123456' If licence 123456 exists then update name = 'TEST' and if 123456 is not found insert the record ...more >>

Hi experts I posted the 'dates in sql' question a while ago but it dosn't seem to have been replied to. I'm still having problems calculating vacation duration. Here is the test code I have so far. I’ve included data for decDuration to show examples of the correct values if vacation is...more >>

I've been working on a stored procedure doing lots of little changes trying to get that little bit more speed. I've been making the change and then running the stored procedure twice, as I understood that the first time it is slower as it needs to spend more time "compiling it". However ...more >>

Hi all, I have an asp page that writes to an ntext field in SQL Server 2000. All was going well until I had to put in a section of text greater than 8000 bytes. Then I got a timeout error and the update wouldn't go through. After reading that 2000 only accepts chunks of 8000 bytes or under at...more >>

Hi, I have two clients that run my VB app and both connect to one sql server. I want to display new records in the client as soon as they're inserted from another client. What's the best solution to this need? I am thinking of using DCOM but I'm not sure that whether this is the best option. A...more >>

Dear Experts, Case description: At the local server (Server A), I have created a stored procedure (SP) to get the data from 2 oversea servers (Server B and C) and insert the result data in a table of Server A; So I create a scheduled daily job to run that SP, but I found that it failed on...more >>

Hi, I have got a performance problem with one of my SQL 2000 DB table. The table stores invoice details. The query uses "Like '%Invoice Number%'" to retrieve all the matching invoices from the table. As it uses wildcard, no matter how I index the table it will always do a table scan. The re...more >>

I have two stored proecedures that i wish to have NEVER run at the same time to avoid loosing all my data: sp_archive and sp_restore if they called together at the same exact time there is a possible race condition that would result in the entire db being whiped out. e.g. archive and restore bot...more >>