I am importing text files into a staging table, then doing a number of checks on the data to flag potential erroneous rows. I am getting stuck when trying to check if Date_A is a later date then Date_B, which business rules don't allow. However, if some of the other rows contain data which ...more >>

I encountered an interesting anomaly in a sort operation with SQL Server. It appears that the dictionary sort order will place a lower case b ahead of a upper case B. I ran the following script and got the strange results: CREATE TABLE MySortTable (DictionarySort nvarchar(10) COLLATE Latin1_...more >>

Hi, thanks for the help. Now things got more complicated. In the table I have... id profile_id attribute_id 1 3 74 2 3 116 3 3 57 4 3 42 5 4 ...more >>

Environment: SQL Server 2005 Developer Edition (SP1) --------------------------------------------------------------- I was expecting same answer from the following sets of queries. But, I have got two different results. I would appreciate, if someone can explain the reasons for the diffrence. ...more >>

Hi, The output of sp_who2 shows the following: 65 SQLDMO_1 0 DB [BULK-OP-LOG] X GRANT 65 SQLDMO_1 0 DB [BULK-OP-DB] NULL GRANT 65 SQLDMO_1 0 DB S GRANT I know these are not caused due to changes in sche...more >>

I need to create a query that displays a string in a select query. For example, the following works in Access, but fails in SQL. SELECT PrimaryClientNames.ClientID AS A, "THE SAME VALUE" AS B FROM PrimaryClientNames; Where column B will always return "THE SAME VALUE" Any help is appreci...more >>

I am modifying a SQL Job script. Initially , the @command parameter is : @command = N'c:\sql-utilities\utility1.exe c:\mssql\data\db1.mdf' I need to modify this to : @command = N'c:\sql-utilities\utility1.exe c:\program files\microsoft sql server\mssql\data\db1.mdf' Problem is the ...more >>

Is it possible to have a sequentially increasing identity column that only calculates the next number based on some other field or value? For example, I have a table with the following fields: Company (PK, smallint, not null) ID (PK, smallint, not null) SomeValue (varchar(10), null) I w...more >>

Hi all, I imported a text file into a table, and. I know that I will not be able to create a primary key on my search field because there are duplicate values. However, I would like to sort the table so that when there is a search at least the values are ordered. Is there any way to ma...more >>

I am using this to send mail on a server that has to email out some reports. We changed, because MAPI on that box would hang at times for no particular reason. It is the program/stored procedure located here: http://www.sqldev.net/xp/xpsmtp.htm All of a sudden, today, it just stopped w...more >>

When using sp_detach_db on SQL 2005, the widows file persmission (the groups that have persmission on the mdf and ldf) changes. To the point where sqlcmd no longer has enough permission to do a file rename. I've seen the same problem posted by others in various groups, but no solution. CREA...more >>

I've two tables: a) id, header 1, col_a;col_b;col_c;col_d;.... b) id, a_id, data 1,1,water;bread;tee;coffee;... 2,1,toast;coffee;salad;egg;... The columns a.header and b.data are varchar(max).The query (with union all ?) should return the following table: col_a col_b col_...more >>

I have a database called "test_db" that has been backed up with the command: BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak' I want to restore it to another Sql Server and call it 'Test_Restored_DB', as well as save it as a different physical file name. So I copy the file over to th...more >>

I have a table which stored employee statuses. CREATE TABLE emp_status ( emp_status_id int not null Primary Key, emp_status_name varchar(50) not null ) emp_status_id emp_status_name ------------------------------------ 1 Active 2 Terminated ...more >>

I'm accessing a SS2K instance using SSMS05 ... I can't any of the jobs I know are on this instance. ...more >>

I have a table with three integer fields: an id, a profile_id and an attribute_id it looks like this: id profile_id attribute_id 1 3 74 2 3 116 3 3 57 4 3 ...more >>

In SQL 2005, what's the best method to import a file (via an ssis package that would have a schedule) if that file: 1) Exists 2) Is not empty 3) Is New (and by new that would be newer than 3 days old) ...more >>

Hello everyone, I'm having this problem, I'm trying to run a query that consists of six tables and one view (all joined together) When I run it without anything on the where (or just one criteria in the where clause) works just fine, but if I run it with two criteria in the where clause ...more >>

I'm writing script to properly case names that we import in all caps. The challenge is dealing with names with a capital third letter like McKinley. Is anyone willing to share some script examples or suggestions for this. I know all the syntax and what not, I'm looking for ideas for when to ...more >>

Hi, I'm new to MS SQL server databases. My experience is in MySql/Perl/Linux. In MySQL loading data into the database from a file is done by using the LOAD DATA INFILE command from the mysql command line client. How do I go about loading data into a MS SQL Server express 2005 database v...more >>

Helllo I am a novice SQL Server programmer in need of some serious help. I want to write a store proc that returns the following data about a particular DB: Status Owner Date created Size* Space available* Number of users (* = required) I was looking through the SQL Server Books O...more >>

Hi, I have a table, tb_invoicecheck, with the following sample data: Check_Num Invoice 0000000107 901 0000000107 902 0000000107 903 0000000233 805 0000000233 806 ........ How do I get SQL to display the result like this: Check_Num Invoice...more >>

I did a generate script for a job that has a TSQL job step. The TSQL is more than one line. When I run the script it errors on the line that adds the job step with all of its multiline text. What should I do to get this script to work? How can I put crlfs in the input so the text looks right i...more >>

Hi, I am trying to get a result set from multiple tables using joins and then using that result set to create another result set joining multiple tables based on the values from the first result set. Any guidance on how to do this would be appreciated. Hope I'm being clear here. Thanks, ...more >>

I solved the following problem posted a while back, but I now have big questions in my mind about indexes . . . In a nutshell: I am in the process of converting a database from 7 to 2000. Here's the steps I followed: 1) detached the db from the SQL 7 instance *with* update statistics 2) made...more >>

When building a BOM using the nested sets model do you need to include the childrens children in the definition of the assembly like the example below? SERVER_RACK SERVER_1 HDD MEM VID CARD SERVER_2 HDD MEM VID CARD ...more >>

I have a server that I set up a link to. I can query it successfully like this: <code> select * From VnetSQL.messagemanager.dbo.jobschedule </code> When I try to update one of the tables, like this: <code> Update VnetSqL.MessageManager.dbo.jobschedule set StartTime = l.StartTime From...more >>

SQL2K. Here is the table: M Q1 Q2 1/06 10 100 2/06 -5 150 3/06 15 50 4/06 20 10 5/06 -3 500 6/06 -10 24 ..... if Q1 of last month < 0, then (Q1 + Q2) else (Q2) end i used a self join to compare t...more >>

I have a select statement that gets and formats data from 2 tables. The fields for each table are concatenated and output to a text file. All the data should be listed from table 1, followed by the data from table 2. The problem is, the first item listed in the concatenated string isn't from a f...more >>

I have an intersection table named Documents. In it are three fields, DocID, SignatureDate and TaskTypeID. The data looks something like this... DocID SignatureDate TaskTypeID 1 some date 7 1 some date 17 1 some date ...more >>

I read about ObjectSpaces in a SQL 2005 preview book, I havn't heard about it much since. Is ObjectSpaces discontinued? Is Linq and Dlinq its replacement?...more >>

I have a table created from a number of other tables that has a system name, a time stamp, and the average CPU utilisation since the last time stamp. The table is ordered by time stamp, but has an unknown number of system names. I am trying to set a start date, an end date, and an interval wit...more >>

Hi all Bit of a poser for you, I have a single-row csv that I would like to store into a table in my database, just a really simple set of data: 1,2,3,4,5,10,100 Something like the above. In C# I could parse the string and loop around it executing a simple insert statement X amount of ...more >>

I have 1 table 'Data' and i need to select the max date. The table contains multiple rows per URN so I need all the fields where the date is most recent. URN =A3 Date Gift Type Frequency 164287 =A35.00 19/05/1988 Recurring Gift Monthly 164287 =A35.00 02/10/1992 Recurring Gift Monthly 164287 ...more >>

Hi All Some help on this would be appreciated before I spend way too long on it.... I have a start date and an end date. I need to determine how many working days (monday to friday) fall in this range. So if the start date is 19 July 2006 (Wednesday) and end date is 1 August 2...more >>

I am trying to follow earlier examples I have found that allow me to pass a table name to a stored procedure as a variable. I have created the following stored procedure: ALTER PROCEDURE DateCompare @DataTable SYSNAME, @StartDate DATETIME, @EndDate DATETIME AS DECLARE @sql_statement NVARCHAR...more >>

I need a good way to estimate a row size. You would think that there would be a utiltiy for this. any good ideas? -- moondaddy@noemail.noemail ...more >>

Hello, I am searching 70-431 exam test questions. Have you anyone known about some free exam questions which very like as real 70-431 exam? I heard that there was only 40 questions and time about 135 minutes, thats right? Regards ...more >>

I've a project that requires a billing system that has different requirements for each payment recipient and I was wondering how I'm to design an efficient DB. Each payment requirement is stipulated in the contracts w/ each payment recipient. Some are very easy, just a price per item per m...more >>

Hi, I have an application, which opens many connection and after completing work never closes connection. This is becoming a problem on sql server side. I am thinking to write a tool/Proc which will close all the sleeping connection at one run. Is this possible ?. how will I get a info ...more >>

I need help with this? Hopefully some one can give ne the correct sql for this update statement. The goal is to update totalweight in table tOrderHeader based on weight of each item in table torderDetail. The weight of the items are stored in UMWT in table inv_itemMstr which happens to be in a ...more >>

Hi, I write a sp to delete some tables at the same time. I want them in a transaction.But, when I tested it, I meet a problem. If the table doesn't exist in database, an error happened. And the transaction can't rollback correctly. The table will be locked. Why? begin tran DELETE F...more >>

Hi everybody I'm trying to get this to work but I don't even know if this is possible with an SQL-Query. My TableStructure look like this: tbl_employees ------------- id_employee int firstname varchar(200) lastname varchar(200) tbl_employeesoccupations -------------------------...more >>

In my stored procedure I will insert a record into a table. How do I get the id of the identity field value just inserted ? ...more >>

i want export data from sqlserver2000 to *.dbf file. but,error code:0x80004005 Microsoft OLE DB Provider for ODBC Drivers show code: declare @obj int, @constr nvarchar(4000), @sql varchar(100) declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int set @obj = 1 set @sql = 'c:\test...more >>

Hi, What is a simple way to calculate a record's length (in order to calculate how many records fit in a page)? Thanks, Yan ...more >>