I am trying to write multiple lines to a DOS file with xp_cmdshell. This works: DECLARE @cmd varchar(255) SET @cmd = 'echo line 1 > C:\outfile.txt' -- overwrites the file if present EXEC master..xp_cmdshell @cmd SET @cmd = 'echo line 2 >> C:\outfile.txt' -- appends to the file EXEC master....more >>

Crdate column on sysobjects is created Date/Time of db objects. I don't find modified date/time of objects(e.g. stored procedure) ...more >>

For part of a school project with MS SQL2000, I have to write a query to answer: Which students are enrolled in both Databases and Networking? (Hint: Use the SECTION_ID for each class so you can determine the answer from the IS_REGISTERED table by itself.) Here are the tables: * is PK, ...more >>

Whats wrong with my IF statement. I am trying to represent empty. CREATE PROCEDURE [dbo].[spQK2NGCompanyCodeLookUp] (@State varchar(2), @ShortCompName varchar(4), @LineOfBus varchar(2), @EffDate datetime) AS IF @ShortCompName = '' THEN select STATE,COMPANY_CODE,LINE_OF_BUSINESS_CODE...more >>

update mytable set field1=(select top 1 field1 from inserted), set field2=(select top 1 field2 from inserted), set field3=(select top 1 field3 from inserted) --thanks!!!...more >>

I have this trigger that creates 4 entries into my database. It creates a base, prin, prod, and admin. After that is trigger is ran how can I delete the prod and admin added? CREATE TRIGGER dbo.trig_AgentInsert ON dbo.Agent FOR INSERT AS SET NOCOUNT ON -- DROP TABLE #Agent SEL...more >>

I am looking for a method to measure SQL performance. For example: 1. execute a SQL first 2. immediately I issue a sql command command which will capture the CPU, IO and memory used by the SQL commnad which I issued in step 1....more >>

Hello All, Would anyone know how to leave the value of a column to its default based on a condition ? ie, the default for the AvgRating column is 5. Suppose if inserted value if NULL, I would like to leave the column value to its default value (in this case it is 5). One way of addressi...more >>

great informative group - Thanks Is there a was to find the Date/Time a Table is being accessed (read/update etc), and who accessed. Does SQL Server has some utility / or SP_ command to do this (I do not want Tracing or Trigger to be implemented) Thx Mk...more >>

Hi Freinds, SQL 2000 What is sp_MSdbuserpriv I catch a process : exec sp_MSdbuserpriv N'serv' and wondering what is this doing ? can't find anything about this SP in BOL eaither ? Thanks in advance, Pat ...more >>

Hello all This should print 2, but prints 8? Why? looks right declare @D1spaces integer declare @cnt integer set @cnt = 2 if @cnt = 2 set @D1spaces = 7 if @cnt = 3 set @D1spaces = 6 else set @D1spaces = 8 print @D1spaces thanks gv ...more >>

I am trying to find a way to force a print message to be sent to the client inside of a batch. Specifically to Query Analyzer. So I'm not using ADO or anything for this. I have a cursor looping through and defraging my indexes. Some of the databases are very large and so I want to let the user...more >>

From a View in my SQL Server 2000 db I need to either EXEC a pass-through query (to an Access 97 db) or EXEC a stored procedure which itself executes the pass-through query (via the OPENQUERY command and a Linked Server). I understand that it is possible to include an EXEC statement in a View...more >>

Hello, I am transferring data from Paradox to SQL Server using DTS and I have a query with syntax: SELECT * FROM ANALYSIS Where (AnalysisDate > DATEADD('hh', -1, GETDATE())) and this gets data written in last hour. When I run this function it comes up with 'Invalid function' It works wh...more >>

Hello, Can you tell me how to move the master, model, msdb databases to another drive? My current drive is being decommission. For example: my current drive for master,model,msdb is on D: drive I would like to move them to M: drive Any suggestions??? Thanks a bunch. ...more >>

select case DateDiff(d, '1/15/05', getdate()) when 0-9 then '0-9' When 10-19 then '10-19' When 20-29 then '20-29' When 30-39 then'30-39' When 40-49 then'40-49' When 50-59 then '50-59' When 60-69 then '60-69' When 70-79 then'70-79' When 80-89 then '80-89' When...more >>

I have the following scenario which causes deadlocks in stored procedures: BEGIN TRANSACTION Insert to Table 1 Used Id From Table1 to Insert to Table 2 COMMIT An Insert gets an Exclusive Lock which is held for the whole transaction. Another client needs access to this data and performs J...more >>

I have defined a table called Address containing property information. There is a identity primary key and some other fields. I do not want property duplicates. The following fields uniquely define a property in the United State: [houseNo] [varchar] (10)_AS NULL , [preDir] [char] (2)_AS NUL...more >>

I have searched the net for days trying to find a way to break a column in a table up into 5 parts. The column is of type text (not my choice) and can contain up to 750 characters. This is normal text, acutely a description of something. I need to break it into 5 - 150 character lines. But it ...more >>

Hello Everyone, I have a table that I would like to add an Insert trigger on. The trick is I want to update an existing column from the just inserted records to calculate a special Identifier column in that column for each row. This appears as though this isn't possible due to a lock bein...more >>

Since I don't always know the name of my database, I want to use something like this: declare @stmt nvarchar(4000) declare @db nvarchar(128) select @db = DatabaseName' select @stmt = 'use ' + @db exec( @stmt ) Unfortunately, this does not behave as I expected. That is, the databas...more >>

I have been trying to use xp_cmdshell with little success. I have setup a proxy account. The command creates a text file that is able to be placed on the local hard drive of the server. However, it fails to put it on a networked drive. The proxy account has access to the networked drive...more >>

Hi, I am writing a SP. I need to comapre 2 fileds from 2 tables with Names. 1 table the name is like this FirstName middleName LastName someone outhere the these 3 fields are put together as 1 field in a Name field like this someone ...more >>

How can I test if a column exists in a table? ...more >>

Hello, I am working on my query and stuck there by a kind of easy problem, but don't know how to make it work. Please help me out. I have a table that designed as: id desc 1 product 1 2 product 2 3 product3 .......... 100 ...more >>

Hello, I am working on my query and stuck there by a kind of easy problem, but don't know how to make it work. Please help me out. I have a table that designed as: id desc 1 product 1 2 product 2 3 product3 .......... 100 ...more >>

I am trying to update a database using a join to another database. This results in the following SQL Server error. Server: Msg 8624, Level 16, State 13, Line 1 Internal SQL Server error. The vRptSystemDriveCapacity is quite deep with other views that all work on the same base table. Th...more >>

Hello: I need to create a stored procedure that will insert values into a Customer Specifications table that will detail the various rooms a customer has chosen as part of their building plan. The stored procedure will look at a table called Plans Specs which will have all of the rooms for...more >>

I'm sorry if this has been beaten to death before, but I'm looking at retrieving hierarchical data in SQL Server 2k. Is this possible, and easy? I've been reading about "sets" and "adjacencies" but they seem to be overly complicated with left and right adjacencies, and inserts into the middl...more >>

Hi, What is the difference between: DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE Wich one should I use when I try to measure the performance of a statement? Thanks in advance, Jo Segers....more >>

Hi, suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them in some order and roll back the whole series of actions if an error occurs in any of them. (Basically I would like to achieve the same effect as things happen in an Oracle environment.) So: BEGIN TRANSACTION tran_1...more >>

hi, I am using Connect() method on SQLServer object. I want to know the cause of connection failure. The returned HRESULT value is always, hr = 0x80020009 Exception occurred. I tried giving invalid credentials and stopping the target SQL Server service but the hr is same. Using ::GetErrorIn...more >>

I'm trying to grant access for a particular database to an already created login. In enterprise Manager, I can double-click on the login, Hit the "Database Access" tab and check the particular database I want to grant access to. Does anybody know the particular command which would accomplish...more >>

Hi: Anyone Know How Install DTS COM, To Use This Componente To Execute DTS's..?? ...more >>

Hi: Anyone Know How Install DTS Com....????...more >>

I have the following select statement in MySql: select col1, if(col2=100, 3, 4) from mytable for those of you who don't know this statement if (condition, [Evaluated true], [Evaluated false]) Is there anyway to write this in MS SQL? ...more >>

I'd like my installed version of Personal Edition to have the name "sqllocal". Right now, it has the name "boyer-pc\\sqllocal". Does anybody know how I can install it so it is named, "sqllocal"? Thanks. -- Galen Boyer...more >>

I have a Select that brings me values of a column in a Table, in multiple rows... Something like: SELECT name FROM Contacts Wich bring me something like: name -------- Rafael Joao Marcos etc... Instead of bringing me rows, I would like it return me the values, comma separeted in a s...more >>

Hi, I have problem populating my full text indexes I have a cluster SQL server. I changed the permission for mssearch service to use the same account as my sql services. I ran the select and I got no records back. I check the full text index and ran populate. There were 0 record after I...more >>

We need to update internally used application from Access mdb. database to SQL server Standard Edition (UI and code written in Access) - unfortunatelly we don't have sources for modules ( only MDE file) . We attach tables from SQL server but chart generator create SQL statements dynamically ...more >>

I'm looking for a whitepaper (or similar) detailing all the issues involved in moving to Unicode. I'm aware of some issues, like changing data types from VarChar to nVarChar for all columns and stored procedure parameters. But I'm looking for a list of all the issues, pitfalls, gotchas, etc...more >>

Hello, Everytime I create a column with varchar type, I always wonder if there is any size out there that can be benefitial to snap the size of the new column to. Something like 256 for example. Adding more confusion to this is the 4 byte overhead needed for varchar type. Would anyone have a...more >>

CREATE TABLE [dbo].[Desired] ( [ProcessID] [int] NULL , [LastCompleted] [datetime] NULL , [Idle] [int] NULL , [Started] [datetime] NULL , [Busy] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[QuestionSchedule] ( [ProcessID] [int] NULL , [LastCompleted] [datetime] NULL , [Idle...more >>

I've created a procedure to support an ASP page where the user can filter records. The procedure works as expected, but I get a -1 for the recordcount. Is there a way around this? Even when I return @@RowCount from the procedure, I get a -1. Here's what I have: CREATE PROCEDURE dbo.sp_L...more >>

How can I write a stored procedure that runs at a particular time every day? Eg. 09:00....more >>

I am trying to push data from our production server to another server via T-SQL scripts. The scripts are working well but I have hit a snag on a table has an identity column. The scripts I am using look something like INSERT INTO LinkedServerName.DatabaseName.dbo.TableName (col1, col2...) SE...more >>

Here's the (hopefully) condensed version of my problem: I am working with a table which contains approx. 2 million rows, and grows by 1,000 to 10,000 rows each day. The data gets downloaded from another computer and fed into this table in the middle of the night, every night. Furthermore, th...more >>

If a field, named FieldName defined as varchar(300), contains the following records: 1. [Chocolate][Cake] 2. [Chocolate][Bar] 3. [Chocolate][Bar]Large How do I perform the query: SELECT FieldName FROM MyTable WHERE FieldName LIKE '%[CHOCOLATE][BAR]%'. SQL Server uses '[' to indicate a c...more >>

Can anyone supply me with some code to remove an identity constraint from a column before I pull out all my hair. Thanks J...more >>

Our company just bought an SQL data warehouse application that allows us to finally get at the Unidata database that we have been compiling for 7 years! As the IT administrator of this small company one of my duties will be extracting data in the form of reports. I am curious as to what opini...more >>

Hello Experts, What is the effect of Ctrl + Q combination in Query Analyzer? Thanks in advance...more >>

how can i almagmate these select queries into one select Count(*) from clientadmin where Software in ('web','deb') and Status = 'Ordered' select Count(*) from clientadmin where Software in ('web','deb') and Status = 'Live' select Count(*) from clientadmin where Software in ('web','deb')...more >>

hi I have a column varchar. I'm saving numbers that have two or three digits e.g Num ----- 03 234 345 49 Select SUM(Num) From T1 I need to get the sum of numbers with two digits and also the sum of numbers of three digits, Can I get that in one Select clause? How can I separat...more >>

Hi, I am trying to write a Stored Procedure that will (among other things) perform a search for specified words in a column of type text. I get the following error message: Microsoft OLE DB Provider for SQL Server error '80040e21' The text, ntext, and image data types cannot be compared...more >>

Hello, I'm trying to write a SQL script without the use of nested cursors as they kill performance on the SQL Server. First, let me explain what I'm trying to do. I have 4 four tables: AFPUNTEN AFPUNTEN_DAILY KAS_DAGBOEK KAS_DAGBOEK_DAILY The *_DAILY tables are the ones where users in...more >>

I have setup a linked server in SQL using VFPOLEDB to a FoxPro database container. The link is working fine & my .Net application can happily exchange data - however, the SQL is returning deleted records from the FoxPro data. In FoxPro records are only deleted from the database after a pack ...more >>

Hi, How can I query and get a list of the last modified SPs? Enterprise Manager only show date created. Thanks in advance Christian ...more >>