I am using SQL Server 2000 and trying to add linked server to a paradox server. First I have created DSN and used this dsn name in the linked server connection. It failed. Then I tried creating the linked server using the server name instead of dsn. It failed. I used OpenRowSet and SQLOLEDB prov...more >>

I need to give my users the ability to change default values. I can obviously use a hard-coded value here, but I cannot use a variable to re-add the constraint. Any ideas? ALTER TABLE Sales ADD CONSTRAINT DF_Sales_EstSubManDays DEFAULT @EstSubManDays FOR EstSubManDays ...more >>

Hi there, I'm writing a C# application and using a C#Express Beta2 and SQLExpress Beta2: I've created an object (say 'Human') which I persist to a SQL DB table. Human has a DateOfBirth property of the (.Net) type DateTime. The apropriate field in the table is of SQL type DateTime. Now I ...more >>

Hi there.. Sometimes, when reviewing SQL ErrorLog file, I found many 7105 Error like this, Page (1:110801), slot 3 for text, ntext, or image node does not exist.. there is no enough explanation about this error in the BOL. so, How come this type of error occurs? and How can I fix th...more >>

I need export data with filed terminator | (Pipe Delimitated) bup ........... ' /c /t, /r /SLoad2' +' /U sa' + ' /P sa' i know If use /t, I am able get below data. Unknown,360,7563944,Chirs,Test,200 fair road,test,canada,54110,CA,100 If I want below data format what needs to chage. I ...more >>

Hi all, I have written a update trigger on contact1 table of db1 database to update contact1 table of db2 database. Both tables need to have same values in some of the fields. I have also written a similar trigger on contact1 table of db2 to update contact1 table of db1. I get the following e...more >>

i have some fields.. name ,surname,studentno select name ,surname,studentno order by name i want to create a visual field to keep my resultset's order number like this after query visual column name 1 ali 2 ibo 3 saban 4...more >>

Hi All, I am New to SQL Server Triggers. In Oracle there are Before | After Triggers. BUT In SQL Server All Triggers are By default AFTER. And Again a Instead Of Trigger Will Override the Triggering Statement. Then How Do I write a Before Trigger? Suppose I want to Store the Employee Rec...more >>

sql2k sp3 Probably not the best title, but couldnt think of a better one. create table #tmp (CustomerKey int,CallDate datetime) insert into #tmp values(1,'01/01/04') insert into #tmp values(1,'01/02/04') insert into #tmp values(1,'01/03/04') insert into #tmp values(2,'01/01/04') ins...more >>

does anyone have a stored procedure they would like to share with me that does a mod 10 check to validate credit card numbers and 9 digit bank routing numbers?? im feeling lazzzzy. ...more >>

Hello, I have a big problem (for me). I have one extended SP, very simple this xp has two input param and 1 output param The param1 is for the Subscriber Id, and param2 is for the Subscriber password. Param3 is the output where I put the result of the transaction. ("Ok" or "Er") I have also ...more >>

This query helps me get all the identity values in all tables in a database.. SELECT db_name() DBNAME,TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENT_CURRENT(TABLE_NAME) CURR_IDENT_VALUE, FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME...more >>

Hi I have a table with its PK as an identity column. I want to alter the table to remove the identity attribute from the PK column. Can I do this using T-SQL ? Thanks & Regards Imtiaz...more >>

Hi, Using SQL Analyzer run this : SET TRANSACTION ISOLATION LEVEL READ COMMITTED Go if Object_ID('Table1') Is Not Null Drop Table Table1 Go Create Table Table1 (Col1 Int Identity(1,1), Col2 Int) Go Alter Table Table1 Add Constraint PK_TABLE1 PRIMARY KEY (Col1) Go Insert ...more >>

Hi - I have a VB.NET Windows app that uses an MSDE database. One of the functions that users can execute is a database repair. To implement this, I am using the DBCC CHECKDB statement: strSQL = "DBCC CHECKDB ('DBName', REPAIR_REBUILD)" SQLcmd = New SqlCommand(strSQL, frmMain.c...more >>

As the subject line says. FOR Example the table I start with would look like this: USER_ID TEST_ID SCORE User_1 TEST1 89 User_1 TEST2 69 User_2 TEST1 54 User_2 TEST2 64 User_3 TEST1 73 User_3 TEST2 83 and I would like my results set to look like this. <USERID <TES...more >>

I'm having trouble getting my head around this, and no one in the groups has posted exactly the problem. The table below tracks site traffic across a network. There is 1 row per pageview and UUID is that user's unique cookie. CREATE TABLE [dbo].[Stats_Working] ( [inac_stats_id] [int] NOT ...more >>

Hi, I've got SQLExpress Beta 2 on my maschine installed and I'm trying to connect to it. The Service is name 'SQL Server (MSSQLSERVER)' and I was trying to connect to it using 'MyComputerName\MSSQLSERVER' but I always get an error telling me that the Server does not exist or access is den...more >>

Hello, I am working on an application that would enable user to copy certain database objects from one db to another via SQL-DMO. I am working on Windows XP, project is in C#, SQL Server 2000, all with latest service packs. Problem #1: I have problems copying objects that have quoted ide...more >>

What is the convention of quoting the fields including space ? eg. SELECT OrderID as [Order ID] FROM Orders SELECT OrderID as 'Order ID' FROM Orders SELECT OrderID as "Order ID" FROM Orders Which one is the norm ? ...more >>

Hi, We are having a problem with restoring the trasnaction log on a SQL 2000 server. We have 2 backup files. A backup containing the database and a backup of the trasnaction log file. Restoring the backup is no problem but when we want to restore the transaction log we get several error...more >>

Hi guys! Can you please point me out the steps required for tuning a Db? SELECT is taking a really long time , though don't have that much of data. I have close to million rows... Also have indexes on the table with no PK. Please advice. TIA ...more >>

I just renamed a stored procedure via Enterprise Manager by right-clicking on the sp name and then selecting Rename from the pop up menu. The new name shows up in the EM tree view - leading me to think that the sp was in fact renamed, but the sp still retains the original name (when scripted o...more >>

I'll pose the question this way... :-) Which is better to use, spaces or tabs in code (SQL, c++, ...)? The applications developers here are split between the two. The SQL developers here have a consensus on spaces (4 spaces per tab key). And there are lots of pros and cons to boths sides ...more >>

using this ddl CREATE TABLE tbl1 (job int,sname varchar (10),type char (1) ) GO Insert Into tbl1 values(3000, 'chris', 'a') Insert Into tbl1 values(3001, 'doug', 'b') Insert Into tbl1 values(3002, 'esteban', 'c') Insert Into tbl1 values(3003, 'frank', 'a') Insert Into tbl1 values(30...more >>

I have a store procedur witch puts many data into a table Table defenition is: create table Customer ( Name char (30), First Name char(30), Street char (30), and so one. Store Procedure definition is: Store Procedure Put_Cust @Name, @First Name, @Street, .... AS Insert Into ....more >>

Hi, when using SQL Server 2000 one has the option of creating scriptfiles for all database objects, such as tables, sp's, views, users, roles, etc. I need to write a tool which can perform the same thing (analyze a database and spit out a couple of properly formatted script files) and I ...more >>

Hi all, Does anyone have a SQL or Stored Proc example of how to loop through text looking for a specific character. I.e. In the example below I'm trying to extract the unique Id's: 2336 and 133 respectively. We have a weird database system which concatenated the ids to the text in diff...more >>

Hello there I have some store procedure that run update on table As a result of that a triggrer run On that trigger an error occured Is there a way on the store procedure to know which error occure? ...more >>

This SQL statements works how i expect but i dont have any joins or anything specified and I am wondering if it is bad practice to do it like this, the result set is returned instantly so that isn't a problem, the only thing i can see that the tables have in common is the FK_ApplicationID SELE...more >>

I tried the following statement in Query Analyzer DECLARE @catname nvarchar(15) UPDATE Categories SET @catname = CategoryName = 'BeveragesNEW' WHERE CategoryName = 'Beverages' SELECT @catname The result printed out the new value but not the old value of the CategoryName. BUT according to...more >>

Hi all, I am creating a FOR UPDATE trigger on a table with several text columns. I need to check which fields have been updated in this trigger. I can do this using COLUMNS_UPDATED(). But would this work for the text fields as well? Thanks... -Nikhil ...more >>

Hi. I'm quite newbie with that. I have 3 tables in my database and I think they are normalized, but I did nothing to ensure that. Do you think they are normalized? Tables are: dish (dish_id, name, kind, dish_price): dish_id is the pk. order (order_id, table, price, date): order_id is the pk...more >>

i have some fields.. name ,surname,studentno select name ,surname,studentno order by name i want to create a visual field to keep my resultset's order number like this after query visual column name 1 ali 2 ibo 3 saban 4...more >>

Hi, I would appreciate some opinions on how to best go about working with Identity data. (I have a strong Oracle background, however I am now working with SQL Server 2000 and have not been able to determine a best approach for this.) I have 2 tables, tbl_orders and tbl_order_details. When...more >>

Here is my Proc: ALTER PROCEDURE dbo.FetchFederalComment @IVDNO Int, @EmpNo Char(4), @FromDate SmallDateTime , @ToDate SmallDateTime , @CommentType Int = NULL -- @IVDNO,@EmpNo Required -- @FromDate Required in SmallDate Format MDY (ie.. 02/01/1982) -- @ToDate Required in SmallDate Format M...more >>

I am fairly new to this so this might seem a silly question. What I have is a varchar with the format CCCCCC.NN.NN.CC.C.NNN where N is a number and C is a character. What I am wanting to do is change the first set of characters up to the first '.' (the amount of characters varies). I have looked ...more >>

I need to convert a varchar to an int, but sometimes the varchar is not completely numeric. I need to convert it to an int by ignoring the non-numeric characters. In other words, "9V" becomes 9 Both CAST and CONVERT will produce an error in this case. Thanks, John...more >>

I have a query where I want to retrieve the second of the top 2 records: SELECT TOP 2 col1, col2 FROM table1 ORDER BY col1 DESC This will give me two records, but I only want to get the 2nd of the two...how can I do that? -- _____ DC G ...more >>

Example: Two tables - Customers and orders A customer may have many orders - If I make relationship between these tables to find all order for customer. This is One-to-many relationship, right? Example 2: Two tables - Customers and Companies A customer may be from only one company, but he c...more >>

Hi all SQL 2k I have a stored proc that gets a list of forums from the table In the select statement I have the following: SELECT .... f.ForumType, CASE WHEN f.ForumType = 1 THEN 'News' WHEN f.ForumType = 2 THEN 'General Chat' WHEN f.ForumType = 3 THEN 'Support' END As For...more >>

when a create a table in sql 7.x or 2000, sql server automatically creates a clustered index on primary key. is there any way I can remove this index and have clustered index on another column? Also, is it possible to change sql behavior to not create clustered index on primary key? t...more >>

Hi, I make these queries : select LD_String_Translation_Id from LD_String_Translation where LD_String_Translation_Id = 0x800000000000124D And : select LD_String_Translation_Id from LD_String_Translation where LD_String_Translation_Id in( select cast(...more >>

Currently I'm working on large historical data. Some of the data on some date is missing (<null>) and I want to fill it with the value from closest previous day. As an illustration: mytable TradeDate | Indicator 3/5/2004 | 200 4/5/2004 | <null> 5/5/2004 | 150 6/5/2004 | <null> 7/5/20...more >>

Hi there, I would like to recreate a stored procedure which uses the result set returned from a stored procedure in a subquery, something like the following: Create procedure GetLockInfo (@DoorID int) SELECT OperatorName, WorkstationName, RowID FROM ComponentLocks where RowID in ( exec s...more >>

I have two separate queries that I'd like to consolidate. The first gets a count of how many times a URL shows up in the SourceURL field: SELECT SourceURL AS "Referring URL", COUNT (*) as "Count" FROM TBLCOMMENTS GROUP BY SourceURL ORDER BY COUNT(*) DESC Many of the URLs are similar;...more >>

I have a table which has an id field. I was told NOT to use identity options for any fields in my database (i know). So i have instead a UDF which does a (SELECT MAX(ID)+1) from the table, and i have that in as the default value for the field. If i insert a record in a sproc, doing an INSE...more >>

sql2k sp3 select (1 / 3) gives me the result of 0. I dont need precise results, but would at least like to see .33. How can this be accomplished? TIA, ChrisR...more >>

Hi, I was wondering if it's possible to pipe the output of a DBCC command to a file. For instance, I'd like to have the following DBCC command... dbcc checkdb(Pubs, noindex) ....output its result to a file on my D: drive. Something like: dbcc checkdb(Pubs, noindex) > d:\dbcc.out...more >>

I have several stored procedures that take some time to run so I'd like to automatically run them as a scheduled job over night and store the results in a table. The following code works: CREATE TABLE tblham_Alert_Memory ([Computer Name] varchar (50), [New Memory] varchar(50), [Old Memory...more >>

Hi group, Got a question that has been bugging me for way too long ... We apply database changes by creating batch files that execute the ISQL utility with an input query file, much like: isql /Umyname /Pmypassword /Smyserver /dmydatabase /i%1 where the %1 is the name of an input .Q...more >>

hello I want extract all the records that were entered into my table since 19:00 the previous night, my sql is : DECLARE @datevar datetime SET @datevar = CONVERT(SMALLDATETIME,GETDATE() -1) print @datevar SELECT [DailyProdTotals].[PartNo], [Description], SUM([DailyProdTotals].[Qty]) AS To...more >>

I have a table PBS_InspectieVat wich is referenced by two other tables PBS_Verwerking and PBS_Afvoer. How can I enforce that the key 'InspectieVatID' is only used once accross the two referencing tables? I know I could create triggers on the two tables to do the check but it seems to me there...more >>

Hi all, I insert a record to the parent_table, and then insert some records to the child_table. To get the parent_table pk, I used SELECT @@IDENTITY right after I insert the record to the parent_table. Problem was the SELECT @@IDENTITY statement doesn't return the correct value. After banging...more >>

Can anyone tell me if rebuilding an index causes recompilation of a stored procedure the next time it is run, or do I need to amnually run sp_recompile? Also, how about adding a column to a table - does that cause recompilation? TIA, JB...more >>