I have two cursors insertíng quite often into a table with identity on the ID. Can anyone confirm that by calling select @@IDENTITY AS 'Identity' i get the last Id for the record inserted by a specific cursor. ex listed in order of execution: 1) Cursor1 : insert blablabla 2) Cursor2 : inser...more >>

I'd like to set up a constraint on a field such that it must be either zero or equal to a foreign key. Is there a way to do this in SQL Server? Thanks! Michael Carr ...more >>

Hi, I have a select statement that selects a number of rows, and concatinates this into a single value returned to the client. ie Select a + b + c + d, c, d ,e .... For formatting reasons I have included a carraige return in the select statement so that each record is on its own line. ...more >>

Hi, I have a table called "Invoice" with "InvoiceDate" field defined to be "DataTime" data type with length 8 and Allow Nulls true. I need to change the Datatype of "InvoiceDate" field to Varchar(20), Not NULL with Default "---". Can this be done through SQL statements. Regards, ...more >>

does anyone have some tested t-sql they use to copy tables from one db to another, possibly on different sql-svr-instances ....with option to completely overwrite target table metadata & data ? thanks. ...more >>

I am writing a script tha will insert some data into a file on a daily basis, but I want to have the month attached to the file name like 'Sales_October'. I have tried a couple of different things but nothing seems to work. Can anyone help me with the syntax for creating this file name ...more >>

Hi All, Please any one help me to find the command to find the particular column in table. If the column not available i need to add the column. The actual problem is given below If NOT Exist (dbo.<Column Name>) ALTER TABLE dbo[<table Name>] ADD <Column Name> INT NULL Please pos...more >>

Hi All, Can you please suggest an easy and efficient way to multiple (Make replicates) of my result set? For Example: If I have the following TSQL: SELECT OrderDate, cost From Orders Where OrderID=7 Which obviously return 1 record: Oct 29 2003, 700 But I need to multiply it (with...more >>

All of a sudden, I cannot get into enterprise manager. I am getting following error. Can someone please help me to fix it. Thanks --------------------------- Microsoft Visual C++ Runtime Library --------------------------- Runtime Error! Program: C:\WINNT\system32\mmc.exe R602...more >>

Hai all, My database contains 24 tables. I accidentally dropped a table(static).The Dropped table is parent table for other two tables . I have back up of the database yesterday's night The table I dropped didn't changed from yesterday's night .Is it possible to restore only the dropped table...more >>

I have the below ASP code that calls a stored procedure that is supposed to delete a record from the database. The stored procedure is below also. The query works in the query analyzer and the SP checks OK syntax wise. But when I run the code, it does nothing. No records are deleted. Does anyone ...more >>

Hello from Spain. I know that the transaction log file is important if you want to rollback the transactions you have done to data... But I have a question about that. Is the mdf file the only one who has the data? I mean: imagine that I want to copy a database from one server to another, and ...more >>

We have two different Databases on one SQL Server. I would like to create a view containing data from two tables in one database and one table in the other database. Is this possible and if so, what is the syntax for this relationship in the Select Statement of a View....more >>

I have a table with a NAME field that I need to break apart. I need to take these fields and update another table. data sample: Doe,John E Doe,Andy Doe,Jim T With the code below(this was passed to me) if MiddleInit doesn't exist is pushes Firstname where MiddleInit goes. Also how do I d...more >>

Can you use xp_fileexist to check a partial file match ? c:\dataload\itm* ...more >>

I need to use variables for the table and column names in a SELECT statement somthing like - DECLARE @Table varchar(50) DECLARE @Field varchar(50) Select @Table = (select NameTable from Config) Select @Field = (select NameField from Config) SELECT a.CUSTNMBR, a.CUSTNAME, FROM @Tabl...more >>

I don't know how in a stored proc, but need to do a transaction in the transaction I want to INSERT A RECORD IN ONE TABLE AND UPDATE A RECORD IN ANOTHER TABLE both pass or both fail. so I need a transaction. Could anyone give some sample stored proc that would do an insert and the...more >>

Is there any way to use the bcp_batch type processing to copy data from program variables in a .NET language? Thanks, Glen ...more >>

I would like to know how to update rows from a staging table to a live table. test for job number then test for job desc then test for percent complete then test for der_date is they are the same as the staging table then end else update the the row that has changed. This is to be run over a whole s...more >>

In this view i want to set a flag(boolean) column value based on whether the C.email_addr is null. N for empty and Y for populated. I can't find a solution in any of my books. Any help would be greatly appreciated. TIA Joe... create view UPS_SQL as select O.Ord_No, O.Cus_No, O.Bill_To_Na...more >>

Hi, Id like to collect some information about SQL server itself like "% of CPU Usage" or "% of memory usage". Something like SELECT @@CPU_Usage ........ ? Thanks, Tim. ...more >>

Hi - When using linked SQL Server definitions, and using sp_addlinkedsrvlogin, is there any way to designate the remote user as a Windows Domain login? Given that OHIO is the Domain and OHIO\Joe is in the System Administrator role on the remote server; sp_addlinkedsrvlogin @rmtsrvname ...more >>

I'm trying to setup replication on a fairly big database (100 tables ish), of which most tables have PK as IDENTITY. So i have to set them to be NOT FOR REPLICATION on the table design. But going through Enterprise Manager takes *ages*, and the script that it generates is really complicated as ...more >>

Which is most effective (consider the query below for an example)? Please give me some explanation (FMI:) as to why one is faster over the other. --Both Queries are designed to get the users information of users that received passes in the Year of 2003 --** *************** --** Q...more >>

Many moons ago, I did an SSADM course... in it I learnt the formal meanings of 'Compound' & 'Composite' keys... Recently, I was talking with a colleague, and where he used 'Composite', I used 'Compound', but strangely neither of us noticed the discrepancy and both of us were clear and in agree...more >>

We are on SQL 2000 with a few case-sensitive databases (vender requirement). Is there some sort of SET command or directive that we can include in our stored procedures that would ignore the case and search on the content? -- Any and all contributions are greatly appreciated ... Regards TJ ...more >>

I have a large query that exceeds the configured threshold of the sql server. The error msg is: The query has been canceled because the estimated cost of this query (76) exceeds the configured threshold of 60. Contact the system administrator. Changing the threshold is not an option, and I ...more >>

I know this question should be posted in Windows Newsgroup...but no one is reply here.... and I know people here are very smart so may be someone tell me very quickly here... I forgot the password of Administrator user of Windows 2000 Server. Could any one please tell me how do I change it and...more >>

IF EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 'Contributions' AND table_schema = 'dbo') DROP TABLE dbo.Contributions GO CREATE TABLE dbo.Contributions( ContributorName VARCHAR (50) NOT NULL, ContributionDate DATETIME NOT NULL ) GO INSERT INTO dbo.Contrib...more >>

Using PHP and MSSQL, what I want to accomplish is to SELECT a field and have it automatically translate language based on content in another table. What I would normally do is have a few function wrappers in PHP that would take care of checking & updating the language translation table with a...more >>

I am currently trying to write a view that displays data from the database. Some of the dates have been entered into the database in UTC format. Does anyone know how I can take any date and transform it back into the correct local time for that date. ie. A date in the summer might only be altered by...more >>

Consider the following two Updates. ----------------------------------- -- Conference Credits ----------------------------------- UPDATE [LinkedServer].Subscriber.dbo.tblConferenceCredit SET fldBillingEventID = @BillingEventID WHERE fldCreditID IN (select fldCreditID from tbl...more >>

I have some data in a varchar column. The data looks like 0.7049. I'm trying to convert this data to float and populate another column. I have tried using CAST and CONVERT with the same result. I get this error, "Syntax error converting the varchar value '0.7047' to a column of data type in...more >>

I know this is a silly question and I need to learn VB. But until then I would appreciate any help on this. Following is the script I need to write and I don't know how to do create the global variable and set whatever I need to have to make this script run. I haven't even been able to par...more >>

Hi all, This sample is from BOL: BULK INSERT Northwind.dbo.[Order Details] FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n' ) in my environment "f:" is a network drive and when I run the statement the error is: "...more >>

Hello! How can I set a trigger to trig only when one field has changed? I want to update a datetime field "chgDate" when the Password field has changed. How can I set that trigger? -- - Lars ...more >>

Hello! I am searching for a easy possibility to check wether a certain record in the table is referenced by an record in another table using defined foreign key constraints! Thanks! Martin ...more >>

Is there a funtion or combination of functions in sql server that can be used to spell out numbers? If not, does anyone know a work around? For example, I need to convert 123 to be "One Hundred Twenty Three". Thanks....more >>

How to use SQL to get a list of file names under certain directory in SQL server and load the file names into a table or cursor and loop through the file name from there? Thanks a lot, NewBee...more >>

Hi folks, I'm trying to insert/update a table with data where three fields combined are the primary key/unique identifier. Here's the destination table layout: DeptCode Varchar(6) Type VarChar(10) Period VarChar(6) Labor float Expense float Supply float A combination of ...more >>

Hai all, I have created a table as below create table( TileId int not null unique, TileName varchar(20) not null, TilePreview image, Price money not null ) Is it possible to store images in the above table using T-SQL , if yes how? ...more >>

I have a table (HOTEL_STAY ) that captures client apartment stay data. Here is a simplified table structure: ID_STAY ID_CUSTOMER DT_STAY_BGN DT_STAY_END ID_STAY is the PK which is just a sequenced integer. I need to find out which customers have gaps between...more >>

Has Beta 2 been made available yet? If so, how can I download it? I don't see it as an MSDN Universal download. I enrolled in Rosetta program, have confirmed beta ID from BetaPlace site, but haven't received email regarding download. I may have missed confirmation email with download inst...more >>

How do I get the max date of five date fields? I lookup up the Max function in the help and they give this example: Max({USA, CANADA, MEXICO}, Sales) I don't think it is appropriate for what I want. Thanks in advance, Stijn Verrept. ...more >>

I have a stored procedure that issues an UPDATE on a table. The appropriate groups were granted EXEC permissions on the procedure. The procedure builds a dynamic UPDATE string, which is then executed as: EXECUTE @sz_sql When a user calls the proc, it fails with UPDATE permission den...more >>

MSS2000 Is there a way to know, at the moment you are getting a local date, if you are in Daylight Savings Time or not? Thanks! Jason ...more >>

I have 3 months data in my db, but I only want to pull last 30 days data. trying to pull by mydate, date format is: 2003-10-06 15:43:44.000 syntax i am using: mydate between date() and (date() -30) but no luck? Also, I want mydate back in mm/dd/yyyy format. thanks ...more >>

Hi All, Any help on this one much appreciated..... I have the following table. CREATE TABLE [dbo].[tblIWFactFile] ( [Sedol] [varchar] (6), [DateID] [int] NULL , [Date] [smalldatetime] NULL , [ValuationDate] [smalldatetime] NULL , [StartPrice] [float] NULL , [EndPrice] [float] NU...more >>

1. Is there a way to use the File System Object from a T-SQL stored procedure? I want to read in the filenames from a folder into a table of filenames. 2. I have this working in Access VBA with an Access table using the abbreviated code below, but need to switch to SQLServer. 2. A sea...more >>

I have 2 tables, Assets and Assets2, which are both exactly the same except that Assets2 is full and Assets only has field ItemNo filled. Basically I'd like to update Assets with the data in Assets2 based on the ItemNo Update Assets.Serv_date, Assets.Account, Assets.purch_date *with fields* A...more >>

Right now SP3a is installed on my SQL Server 2000. As said by someon that : "......You can install the samples on an existing SQL Server installlation by choosing the Upgrade option, doing a Custom setup and adding the components......" then whether I would also have to install the service pack ...more >>

I am looking at a new database that has all of its times in seconds, apparently from 1/1/1970. I have the number 1024917951 and want to see what the date would be if I added 1024917951 seconds to 1/1/1970. How would I accomplish this? Thanks for any help....more >>

Hello! Having some problems to calculate datetime in sql server and vc. My date now is 29.10.2003 10:00:00 If I should add three months to this date - I would have 29.13.2003 - which of cource should be 29.01.2004. How can I add three month for this date, and the result date is calculat...more >>

Hi, I'm having a problem with SQL Server using up 100% CPU time when cascading deletes kick in with high numbers of rows (200,000) or more. I also have a huge trigger running on insert but the CPU time only goes up when the main table goes over 200,000 rows. I'm including the SCRIPT for...more >>

Hello all I have a problem in a SQL Server system I have a sentence like this one Select Table1.Field1, Table1.Field 2, Table2.Field1 From Table1 , Table2 Where Table1.Field1=Table2.Field1 This sentence returns only the records that match between table 1 and table 2. I w...more >>

Hi, Is it possible to use xp_sendmail just to save attached file to local disk? I need this as xp_sendmail can make many file types and formats (like Excel). I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

Maybe someone will see something that I'm not.... If I can get these 2 query as 1 so the result set shows like.... Username Post Non-Post -------- ---- -------- User1 10 2 User2 5 1 ...that would be awesome. The only diffence between the 2 queries is th...more >>

Hi: Is there a way to generate a user log report with his/her last access date into each SQL Server database within the same server? Thanks!...more >>

hey all, sorry if this is slightly off topic, but I'm not getting anywhere. Is there a reason I'm not able to install Sql Server 2000 on Windows 2003 Server Web Edition? The CD autoruns, I click on SQL Server 2000 Components, then click on Install Database Server, I see a quick InstallShiel...more >>

Having some problem to pulling 30 days data: Mydate format is mm/dd/yyyy This is what I am using in my sql: where Mydate between date() and (date() + 30) I want to pull 30 days data from my database. This is the code I use in ORACLE? what should I use in sql?...more >>

Does anyone know why the Show Server Trace option would be unavailable (grayed out) on SQL 2000 query analyzer? My login is an admin for the database in question. Are there any other settings that may affect this? Thanks for any help that you can provide, Joe ...more >>

i have a web site using a sql server. I use a where clause to search on about 10 fields. When i use AND in my where statment unless i fill in every search field with the exact search criteria i get nothing back (that is what i would expect). If i use OR i get everything back, for example i...more >>

How would I write a select statement with the following info? I need to pull info from the table, if I put in a date (ie. 10/22/03), I need to pull all records by the StartDate in the table that are 2 months prior to the date I inputted. I beleive I have the correct formula to go back 2 months (...more >>

Hi, I have an app which from time to time runs into a blocking lock. When I use the Enterprise manager to examine the statement which causes the lock (blocking process, properties), I find a statement which can´t be the cause. (e.g. simple select on a small table or an update on a single row) ...more >>

I'm new to SQL Programming. Suppose that I have columns A,B each are float. How can I creat a third column C where C=A/B. How to make any new entry or any updates in A or B makes a new record or updates the existing records in C. What if the equation in C is more complex (Not Just A/B), for exa...more >>

hi, I must use sp_oaMethod to call a method inside another method. An example of this, maybe the following script ( where OpenTextFile method contain Write method ) : Dim fso, f Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.OpenTextFile("c:\testfile.txt", ForWriting, Tru...more >>

I'm using the following code for a bulk insert that works perfectly, however, the data in the table still contains the double quotation marks of any text fields. How can I remove those while inserting the data into the table CREATE TABLE [#Temp] ( [ID] [int] NULL , [FirstName] [nvarchar] (50) NU...more >>

Hi all, I need to export data from a SQL 2000 database, to any DSN on the client machine (DSN is selected by the user in a VB app). I Can't seem to export data from SQL, using OPENROWSET, to the DSN: The following works just fine: INSERT OpenRowset( 'Microsoft.Jet.OLEDB.4.0', 'E...more >>

I trying to sort a UNION Query. When I run the query I get a result set but not a sorted one. I am also being forced to add a TOP clause to the select statement... IF I highlight just the SELECT ... UNION ALL .... SELECT ... part of the code, I get a sorted result set ??? Any Ideas an...more >>

Hi, i am trying to search on dates using varialbes. If i replace the date variable in the where clause with an actual date it works. I have tried al diff ways of converting my input value to dates etc. here is my code ----------------------------------------------------- DECLARE @VehicleReg ...more >>

Hi, Iam making a data table in sql server, which is getting data from a text file through DTS package which is running every 10 minutes. Text file is getting data from a weather station which is updating the textfile every minute. Text file is storing all data. I want to put this data i...more >>

How Do I access a particular contact's Business Phone number from the Global Address List through Sql Server 2000? ...more >>

Hello All, I have a table with column called Amt I want to write a query which says if Amt =0 then 'Nil', if Amt>0 'Greater', if amt<0 then 'Lesser' CREATE TABLE tblAmount(ID INT NOT NULL IDENTITY(1,1), Amt INT) INSERT INTO tblAmount(Amt) VALUES(1000) INSERT INTO tblAmount(Amt) VALU...more >>

On my computer SQL Server is already installed. However, Sample directory is not there in C:\Program Files\Microsoft SQL Server\80\Tools\DevTools. (1) How should I copy these samples? (2) When we install SQL Server it doesn't ask anywhere as to whether we want to install or not Samples. At whi...more >>