All, How can I use FOR and others like that in T-SQL (inside triggers)? What's the syntax of these FOR...? Thanks, D. ...more >>

Hi there, I have to write a Stored Procedure to accomplish moving data from bulk table (having more than 15000 records) in a 5 minutes situation to a BackUp table for history. I tried create table Table_TimeStamp as select * from PrimaryTable where false THen I tried inserting the rows from ...more >>

What is the command to restore a database. I have a .bkp file but I don't want to do it through Enterprise Manager. Thanks! ...more >>

I need to store XML data in SQL Server and am asking for suggestions regarding how to 'shred' RSS 2.0 XML to put the data into the database. -- <%= Clinton Gallagher, "Twice the Results -- Half the Cost" Architectural & e-Business Consulting -- Software Development NET csg...more >>

Hi I have 2 questions 1. Using sql query analyzer how to get permission information for the given user or role(either database role or cutom created role or serverrole)? 2. How to get list of users who belong to Server role i.e System Administrator? Thanks in advance ...more >>

Dear All, i have a table called test_table if we "select * from test_table" then name ----------- Itm A Itm Z Itm X Itm K how can i write a query such that the following table will be displayed name -------------- 1 | Itm A 2 | Itm Z 3 | Itm X 4 | Itm K T...more >>

Can someone tell me what these objects are for? dbo_sysconstraints dbo_syssegments I'm on a shared SQL environment. I had my host setup a user account that I have restricted to a few tables. Yet, these objects keep showing up. There's an outside chance the user could screw with those object...more >>

If I create a stored procedure, do I have to use all the variable I declare? For example, the SP has variable such as @FirstName, @LastName, @City, etc. But, I have a form that calls the sp but only passes the parameters to change the FirstName and LastName. Then I might have another form ...more >>

I have a table: employee table with 3 columns: -emp_id (Primary key) -supervisor_id -emp_name For example: Employee Table data: emp_id supervisor_id emp_name 1 null aaa 2 1 bbb 3 2 ...more >>

Is there a way to setup paging with a data reader? For instance, I only = want 10 records at a time, and I can move through the next ten when I = need them. From the members that I saw listed in the MSDN library, it = does not appear that there is. Can someone please help? Also, is there a = way...more >>

Is there a way to create a stored procedure that references a table that does not currently exist? For example: SELECT * FROM [linked-server].[Finance].[dbo].[ChartOfAccounts] This will produce an error if the linked server doesn't exist, or the ChartOfAccounts table doesn't exi...more >>

Hi, I'd like to ask you if you know how to substitute NULL values in the result set. When I do a select statement I have a column in which I have Null values. i'd like to replace these values with text:"There is no value!". Thanks, Viktor --- Outgoing mail is certified Virus Free. Ch...more >>

Hi, Can I have serial numbers in in my select result. Like the serial# in PL/SQL -hari ...more >>

In SQL, how can I convert a hex string like '0xff' to a hex number 0xff? thanks, Joseph ...more >>

I would like to run a backup daily using the backup command. However I want the file to be named after whatever day it is. Monday, tuesday, etc... Can anyone help me do this? How do you pull the day of the week off the system? Thanks! ...more >>

Is there any way to change all columns that are nvarchar to varchar? I have upsized about 12 Access DBs to SQL Server and all of the text fields were changed to nvarchar fields. I would like to do this quickly, instead of opening every table in design view and changing them. Thanks, Drew ...more >>

I need to modify a column, keeping only the numeric portion, and discarding all else, e.g. retaining ' 41.2' where the original value was '41.20 mm'. Any ideas about how I can do this? I know in some languages there are functions that will strip the numerical part of a string, but I cant s...more >>

Hi This question has probably been answered numerous times before, but my very limited experience with SQL has not enabled me to find the answer. Whenever a row is inserted into a table, I need to enter the date of the creation of the row into a field in the same row. I assume that the use ...more >>

idchar = "SELECT IDENT_CURRENT('TESTPERSON')"; I need to convert the data type of "idchar" to "bigint". I tried many different methods, but I am getting errors. Appreciate your help. Thanks nj ...more >>

Is there any way to get around the fact that most of my queries timeout after 30-60 seconds when I try to run a query in SQL Enterprise Manager. is there some setting or code that I can use to allow me to test my queries in the dev environment? ...more >>

Hi, I have one column named ProgramLocation that is varchar(100) datatype. Now I want to trim 10 characters from the left for all records. How can I do this? Please help, and thanks a lot in advance. Han, My data look like this: \\GC95Z01\CAVITY\10168-6 I want to trim \\GC95Z01\ off...more >>

Can I assume that the AFTER INSERT trigger created on the table will be fired for each row separately even if I insert many rows at once? D. ...more >>

Hi, I want to trim 10 characters from the left of the string of one column in my table. How can I do this? Please help me if you have ran into this problem before. Thanks alot in advance. Han, The field name is ProgramLocation Data type is varchar(100) Data stored look like this \\...more >>

Hi all. I have a column of type NText, that usually holds big amounts of text. How do I update that column, given certain criteria? For example: Create Table TheStrings ( TheStringsID Int Identity( 1, 1 ) Not Null Primary Key Clustered, TheString1 NText, Th...more >>

Hello, I want to show all the missing data by comparing 2 tables. For example, Table 1: 1 2 3 Table 2: 1 3 4 Result: 4 Can I write a join to show the missing data? Sorry for a lack of explanation. ...more >>

Hello, What is the best way to generate activitis on a database? I would like to see how my server handle when there are lots of activities on my database. Any suggestions... thanks in advance ...more >>

I have the following stored procedure @Name nvarchar(20) = 'Smith' AS SELECT ContactID FROM Contacts WHERE (ContactLastName = @Name) How can I make the default value to select all records? I mean when you don't provide a value to @Name then all records to be shown. ...more >>

I have a table with two columns Date and Time I have made their default values to GETDATE() How can I format the date in this columns so it inserts only date in DATE column and only time in TIME column? ...more >>

I have table1 with ID1 AND ID2 as primary key. Then I have table2 which include also ID1 and ID2. I would like to have all records from table 1 that are not in table t2, something like this: SELECT * FROM table1 WHERE ID1 AND ID2 NOT IN table2. Thank you for your answer, Simon ...more >>

Hi Does anyone know how to aggregate or concatenate on a text field. My problem is I have the following table: CREATE TABLE Foo (a int, b text) Table Foo has b as text data type. INSERT INTO Foo VALUES (1,'qwe') INSERT INTO Foo VALUES (1,'zxc') INSERT INTO Foo VALUES (2,'123') INSERT...more >>

I have a query that produces several copies of the same line based on a grouping by different days, product ids and product styles and I just want the first in each style group... in Access I can do this using the FIRST commmand in the query, how do I something similar in SQL server? I have lo...more >>

I have built the stored procedure below but when i try and get it to return a resultset using dates i get Server: Msg 241, Level 16, State 1, Procedure usr_sel_companynames, Line 62 Syntax error converting datetime from character string. I don't know why i have tried the line - select @sql=...more >>

I'm having trouble with a constraint, here is my constraint expression on Table TempUserDB: CONSTRAINT CK_CHECKUNIQUEUSERNAME CHECK ([dbo].[CheckUniqueUsername]([Username]) = 0) Here is my function: CREATE FUNCTION dbo.CheckUniqueUsername(@Username varchar(50)) RETURNS int AS BEGIN DECLAR...more >>

What is the best way to capture the unique id used on an insert from with in a stored procedure? I need to write it into a field in another table. Its possible that this stored procedure could get called a few times in a very short period and I'm hesitant to rely on @@identity. THanks, An...more >>

All, How this trigger should be fired if I insert for example 400-500 records? I suspect that the trigger will be fired only once when all records are inserted, is it true? I'd like to work with every inserted record separately, what should I do for that? To write this code in the trigger, ...more >>

I know that Null does not equal anything. Does that mean that I cannot do a join where column A has values and column B may have Nulls and legitimate values like this: where join on table1.column1=table2.column1 AND ***table1.columnA != table2.columnB *** Any thoughts? Thanks, Rob...more >>

I want to run a select statment that should return one particular record on the top and rest of record in the order by with that column name. Example: create table #test (title varchar(200)) INSERT INTO #test VALUES ('IBI') INSERT INTO #test VALUES ('R K Mission') INSERT INTO #test VAL...more >>

anyone have or know of a nice sql script to do a luhn check for credit card numbers and bank routing numbers??? ...more >>

I am using the DateAdd function like this: SELECT ShopCart.productID, ShopCart.imgsrc, ShopCart.href, '{6DB4A1C9-CEF0-4826-82D7-92B61911ADC4}' AS transactionID, ShopCart.categoryID, DateAdd(month, Products.dateInc, GetDate()) FROM ShopCart INNER JOIN Products ON ShopCart.ProductID ...more >>

Hello, What is the best way to move stored procedures & views from one copy of SQL Server to another? I work on my laptop doing development, and as I complete them, I need to move the stored procedures onto the 'real' version of SQL Server. Is there a simple way to do this? Thanks, amber ...more >>

Hello, Do you have any idea how a database set itself to read only mode? One of my database keeps getting set with read only mode. I look everywhere but can't find any code to start a sp_dboption. Any suggestions on how I can track that? ...more >>

Hi Do database options such as SET NOCOUNT ON have a life. If I set it on in one stored procedure and forget to switch it off does that affect all stored procedures. If so how can I get all the database options back to there defaults. IaN ...more >>

I want to count the same data two difereant ways.. now I have this SELECT COUNT(*) as cnt, Name From Allinfo WHERE Store = 'storename' GROUP by Name But I also want to put this togeather with it as another colume. SELECT COUNT(*) as cnt2, Name from Allinfo WHERE Store = 'storenam...more >>

I am creating a user defined function. I need to use the STUFF function to replace characters in a string based on a variable for the start position... STUFF(@string, @position, 1, 'A') This gives me a syntax error and I'm afraid it's because the second parameter is an int variable instead...more >>

Hi: I can't figure out how to do a select for a particular table and was wondering if someone could help. Table structure: Client int Product char(10) PurchaseDate date Price money Quantity float This table can have *many* multiple entries for 'Client' and 'Product...more >>

Table: t1 col1 col2 100 null 101 null 100 null 102 null Table: t2 col1 col2 100 150 101 160 102 170 if i link above tables and run an update stmt which updates col2 in table t1, t1.col1 = t2.col1 then i get below results results of t1 af...more >>

Hello all, I think I've found a bug in the SQL Server 2000, service pack 3a (SELECT @@VERSION reports 8.00.818 ). The following (surprisingly simple) query: SELECT NULLIF(col1, '') + ' ' FROM (SELECT NULL AS col1) AS [sub1] generates: ODBC: Msg 0, Level 19, State 1 SqlDumpExceptionHandl...more >>

I have a table with one column. For example Create table table1 (TestId int) INSERT INTO table1 SELECT 1 UNION SELECT 2 UNION SELECT 3 I want possible two combinations (not permutation). I want to extend this combination to more than two but for now two will do. The output should ...more >>

Hi, Is there a way to get just an empty result from a int column that has a NULL value inserted into it ? When I try using ISNULL I get number 0 as a result. If I do just plain select it gives NULL value. If I insert just an empty string like '' it puts a 0 in the column and that's not goo...more >>

Dear SQl Experts, I am getting following error while ececuting the Sp. "SqlDumpExceptionHandler: Process 14 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process" I have created the triggers I am running this SP on SQL 7.00.623 SQL S...more >>

I need to check if someone is part of a nt group, if they are not I need to raise an error message, how do I do this ? cheers for any help...more >>

Hello, please show me an example (real t-sql code), how to retrieve multiple rows in each fetch from rowset using t- sql cursors. Regards ...more >>