For MSSQL 2005, if I am storing XML documents in a table and at some point want to query against those documents, an XML column type is best. Each XML document also has an associated XSD file. If I want to ensure data integrity and have typing available with xml sql, how should I store the XS...more >>

Using SQL 2000. I need to be able to provide a report that shows employees with 1, 2, 3, 4, 5, 6 errors. Table is like this. Associate----error_type-----error_date. There are five error types. My report needs to look like this: --------------------------------------------------------------------...more >>

We have upgraded from SQL 7 to SQL 2005. Version 7 came with a manual named: Microsoft SQL Server Transact-SQL and Utilities Reference, Volume 1 that basically shows all the sql commands and gives examples. Is there one of these for SQL Server 2005? If not, is there something similar? J...more >>

I am trying to help a client who has customer information stored in a SQL2005 database that they want to correct using Access, and then restore to the SQL2005 database. We don't have a lot of experience with SQL, other than running queries, but we tried using the import/export wizard to exp...more >>

sorry for the cross post. noticed I posted to wrong group after I sent it the first time. I have a package that works fine when fired from within BIDS. It also runs fine if I connect to integration services and RUN the package. When I add the package to a job, it executes successfully, but...more >>

We are doing a simple SELECT query in SQL Server 2005 that is returning the following error: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object '' with unique index ''. The statement has been terminated. This error message is highly suspicious for two reasons...more >>

Can anyone help me re-write this query without using the table variable? Using SQL 2000. Sorry about the formatting, it got lost once I pasted it here. Thanks in advance! Liz --DECLARARTION FOR VARIABLES declare @Claim_Service_Date_Start datetime declare @Claim_Service_Date_End dateti...more >>

Hi, I have a table like following: Column1 Column2 Partnumber PartnumberID ADDG-1123 1 ADDG-1124 2 ADDG-1125 3 How to write a query to outp...more >>

It was a very interesting external DOS command many years ago allowing us to add some external directory to the current one to make the files located in this external directory visible like they are located in the current directory. Is there any way to join two databases on the same MS SQL ...more >>

Hello EveryBody, How can I set memory allocation to maximum (Of windows server) in SQL SERVER 2005. We do have SQL SERVER 2005 running on Windows 2003 server which has 4 GB of memory but I am not able to set all 4GB memory to SQL SERVER 2005 in setting (From prpperty of sql server). I can s...more >>

I'm having some issues getting the days between two dates. I need to: 1.) subtract the begin date (cov_effective_date) from the end date (cov_expiration_date) + 1 2.) Then, add the total days covered 3.) If it's greater than 20, then 20 will be added to my report 4.) If it's less than 20, the...more >>

Using SS2000 SP4. I found this code on this site to remove dupes. DELETE FROM YourTable WHERE EXISTS (SELECT * FROM YourTable AS T WHERE addrkey = YourTable.addrkey AND row_id < T.row_id) I'd like to flag the duplicates first. Is this correct? update YourTable set dupe_keep = 'Y...more >>

hi, i have a table products. fields are: productID productCode productName productPartNumber price .... the primary key is productID but there is a unique constraint for productCode, productPartNumber. The staging table that the data is in prior to being inserted into this table has d...more >>

Is there a way of kicking off a DTS after successful completions of other DTS's? I have a single DTS that's dependent on the successful executions of 3 other DTS's. This job should not execute if any of those previous 3 jobs failed. Can anyone point me in the right direction? Thanks! ...more >>

Hi all, Trying to bulk insert a csv file from a bcp command generated file but, get this error: Server: Msg 4839, Level 16, State 1, Line 3 Cannot perform bulk insert. Invalid collation name for source column 45 in format file '\\MyServer\drive_e\temp\usage.fmt'. First Column in Distin...more >>

Given a number of numeric ranges, I need to find the first available row (range) that can accomodate a requested block of contiguous values. For example, given the following table definition: CREATE TABLE [dbo].[tbl_AllocatedRange]( [RangeID] [int] IDENTITY(1,1) NOT NULL, [RangeStart] [bi...more >>

Hello All, I would like to know if there is a list with changes on the locking behavior between SQL Server 2000 and SQL Serve 2005. Just as an example, In SQL Server 2000, declaring a cursor "FOR UPDATE" automatically adds a UPDLOCK hint to the cursor. In SQL Server 2005, this does not happ...more >>

Hi I have a function with a start date, start time, end date and end time. I want to add a line in the function using Datediff () where I can get the difference in hours between (start date and start time) and ( end date and end time). Is it possible to do this from within the function. I'm t...more >>

I have a trigger as follows that I want to update a column in the same table on an insert...is this possible? CREATE TRIGGER T_Insert_TableA ON dbo.TableA FOR INSERT AS DECLARE @Id INT SELECT @Id = inserted.TableA FROM inserted BEGIN UPDATE TableA SET ColumnA = ColumnA * 1.25 W...more >>

I am running SQL Server 2000 in Simple Recovery Mode. Memory and Disk Activity is not an issue. I am working on a Reporting Solution and the DB is primarily used for Reporting, i.e., not a lot of transactional processing on the tables ... the inserts and updates only occur when data is bul...more >>

I have a customer that says they want all of there data encrypted. That would be 40 plus odd fields in a 96 field table. We will be using Symmetric (AES) I am wondering if there are pros and cons regarding encrypting ONLY the most sensitive data vs maybe some data that is not as sensitive. ...more >>

How do you view triggers in SQL Server 2005? *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi, I would like to download SP2 for sql server 2005. On microsoft web site: http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en 1) Not sure which one of the three .exe files i should download. Any suggestions please? when runn...more >>

I have a table that contains approximately 2 million records on any given day. I the table has 96 fields and 40 or so are customer data that needs to be encrypted. We upload the data using the Bulk Insert. Can anyone shed some light on what might be the best to get the data being bulk inse...more >>

Hello Everybody, We have upgraded database to sql server 2005 from 2000 in QA environment. Still we have 2000 running in production with the exact same data what we have in QA database. We have found that after upgrading QA database to 2005 queries are running extremely slow. All the indexe...more >>

I am triing to compare 2 strings and return what is different....more >>

Hi, I am experiencing a strange problem with the query below: SELECT DISTINCT(TABLE_SCHEMA) AS [Name], IDENTITY(INT, 1,1) [Id] INTO ##TEMP_SCHEMA FROM [INFORMATION_SCHEMA].[TABLES] ORDER BY [TABLE_SCHEMA]; In SQL Server 2005, this query produces a temporary table with one record for eve...more >>

Can someone please explain to me the diff. between those two and when the Logical and Physical Read happen from Indexing standpoint? Thanks Ed...more >>

Hi,I am new to .net 2.0 and have just built one web application and deployed the application on web server. The problem which i am facing is that if i make any changes in the .cs files or aspx files then everytime i have to publish the site and give new upload for whole site everytime which i fe...more >>

from what table and from what database is the error log contained? Thank you....more >>

Is there a way of doing this without a case (just a update statement)? I would be able to join tblQSMLoadItem_1 to tblLocation on AssetRef (to MachineName) except for the EffectiveDate as there can be multiple location for each item! declare @AssetRef varchar(32) declare cur cursor fast_f...more >>

Me totally new to SQL. I created a backup job to backup MOM DB to D:\ drive. It works fine but I noticed when the backup completes successfully daily it is adding to the existing file I want it to overwrite the exsiting backup file instead of adding. Do I need to delete the job and ...more >>

Hi all, I have an indexed view which is a join on three tables. The indexed view also defines a column which is calculated from a few fields across two of the tables. I have an index on this calculated column. When I run a query which is filtered on the calculated column the index isn'...more >>

Hi, I'm wondering if there is a way to get info that an error occurs when executing multi request with an Execute ? I was using @@ERROR but it seems that is only contains the last request execute in the muti-request ... Example : My Stored proc receive a string (VARCHAR 8000) that con...more >>

I have a table where one of the fields ("Filename") contains ftp urls and looks like this: "ftp://Engineering/OldRev's/230273A0.TIF" When I do a query, it looks like this: SELECT FileName, PathName FROM FilesList WHERE Filename Like '230%' AND PathName Like '%engineering%' ORDER BY File...more >>

I need to schedule the migration of some data each night from one sql server to another. The databases has the same structure, but tables are filled by applications on the first server, and each night inserted records have to be moved to the second server. This way the first server will restart...more >>

Hi, I have an SSIS package which calls a web service and returns a Dataset object in the form of an xml file (it does this successfully). The Data Flow then picks up the file using an xml source task (in which use inline schema is specified) , and passes it to an SQL Server Destination task ...more >>

Hi Experts, If i delete Master Table Data, automaticaly delete the reference table also. is it possible in sqlserver2000 regards Sundara Murthy S...more >>

can any body tell me in which scenarios i can use named query thanks in advance surya...more >>

when i mention at sp level as it SET TRANSACTION ISOLATION LEVEL READ COMMITTED is that equivalent to using no lock on all update and select? ...more >>

I have a UTF-8 encoded file. How do i bulk insert this file into SQL server. When i do the insert it gives me some special characters. I've tried DataFileType='widechar' and codepage=1252 but dosen't seem to work I'm sure that SQL Server supports UTF-2 and UTF-4 files, but not sure for UTF-...more >>

Now that ORDER BY doesn't work the same in views as it did in 2000, is it possible to get a view to return a sorted result? I need something that will work like this did in 2000: SELECT TOP 100 PERCENT * FROM tblData ORDER BY columnID -- Ray Greene...more >>

We have an application which has its settings stored in a database (SQL Server 2000) We have (strong) disagrements on the best way to store these values, and I am looking for advice on doing this. Most of the values are not used by the stored procedures, just the applications running on the c...more >>

Hi I'm interested to know what the best way would be to create a sequenze number. For creating the number I would prefer not to use the Identity function from a table. I'm more thinking to use a stored procedure. How can I prevent that a number will be created twice? What are your thoughts...more >>

I have to migrate around 15-20 databases that currently reside on SQL 2000 server, what is the best way to migrate them to SQL 2005? I've read some posting on scripting the database, attaching to the sql 2000 database, etc. what is the best way to migrate the db's to 2005? ...more >>

Is there a reliable, built-in (even if not documented) way of retrieving the names and types of columns output by a stored procedure in SS25K? One could dynamically generate SQL based on the required parameters, and then execute it with guesses at acceptable default values. I don't exactly ...more >>

I would like to know, how to stop analysis service from job or from cmd prompt? thanks,Simon ...more >>