Hi, We are storing some date stamp values for our application in GMT. Based on the user logged in, we need to convert these values to the local time of the time zone specified by the user. Is there any direct function or user defined function avialable for this Thanks and Regards, ...more >>

If you're able to shed light on the problem described below, you will be forever acknowledged as THE supreme being of all things SQL Server 2000. I have a stored proc (spGetMemberInfo) that accepts one int parameter, @AppID, and returns XML via FOR XML EXPLICIT. The output looks something like ...more >>

XML and SQL: Developing Web Applications http://www.ebook5.com/readingeBook5.aspx?productID=436&catid=1&subcatid=136&pageno=2 SQL Performance Tuning http://www.ebook5.com/ebook5details.aspx?productID=307&catid=1&subcatid=136 Microsoft SQL Server 2000: A Guide to Enhancements and New Feat...more >>

Hi, I'm about to start working on a new project and I would like to have some ideas from you guys. I already got an idea how to do it, but I'm not sure how good it is. This is the problem: We have about 20 tables with data. We have Persons, Addresses, Vehicles, Properties, Phones... ...more >>

How can I get (Programatically) the size of a row in a table in SQL stored procedure? Evan ...more >>

Hi All Can you please help me with a few queries on adding a header line and padding rows out. I apologise profusely for not providing the DDL for this, but I don't have it. All I have is stored procedure that I'm trying to edit to make the front-end app display the right data. The rele...more >>

I'm trying to understand some behavior I'm seeing running a stored procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or osql, it completes as expected, but in the process may generate a few 3604 errors (duplicate keys ignored on insert). If any errors other th...more >>

Any help on this problem would be greatly appreciated. I have a field in my database that contanis numbers (i.e. 1, 2, 6, 7 etc.) I want to create a stored procedure to return these numbers, but formatted in such a way that they always show a full 10 digits. For example 7 would be 000000000...more >>

I have a simple table the has 3 columns CDATE NCout RCount 11/26/05 423 3545 11/27/05 474 3597 11/28/05 636 3512 11/29/05 674 3587 11/30/05 692 3512 12/01/05 515 3577 I want to add another column that would identify the date as a weekday or a weekend....more >>

What is the difference between the two statements, besides the syntax? And in general what are the differences between the DISTINCT and the GROUP BY methods? -- Thanks select field from table group by field select distinct field from table ...more >>

I am trying to understand when to index a column and when not to. I have read several post and web sites on indexing, but I am still not clear on if I should index columns that only contain a few distinct values. (btw... I did not design this table, but I do have to live with it :) I have a ...more >>

I'm asking lots of design questions here.. but they are little ones, sometimes matter of taste, sometimes more than that. Imagine I have a table of "Keys". Some of these keys will be "active" and some will be "blocked". I'm trying to decide whether I should use a BIT column and call this ...more >>

Hello, How can i attach a db where there is a log deleted. When i want to attach the db, i cannot click the ok button because a second log-file is missing. Anyway to work around this?...more >>

Hi, Imagine I have a set of numbers: 1, 2, 5, 6, 9, ....., 10000 I wish to fetch one number that IS NOT already in the set. I can do some kind of FOR/NEXT loop, taking id 1, checking if it already exists, and keep incrementing this id until I find one that does't exist yet. This obvious...more >>

Hi, Does something similar to the view designer in enterprise manager exist that I can integrate in word? Something like Insert, Database Shema... that shema would be binded to a database so if something change (let's say a field name) the corresponding schema would be changed (or at lea...more >>

CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1)) INSERT INTO test values(111,'a12','a') INSERT INTO test values(111,'d33','a') INSERT INTO test values(222,'a25','a') INSERT INTO test values(222,'g21','e') INSERT INTO test values(333,'a65','a') INSERT INTO test values(333,'a...more >>

The first experiments we ran in the migration to SQL Server 2005 were quite successful. .NET written applications all worked pretty much flawlessly. However, when testing our most important internal application, we quickly discovered it did not do nearly so well. This is what we had done. ...more >>

Hi, A very basic question: Imagine I have a table with multiple fields, some strings, some integers etc. None of these fields are unique on their own, but all fields together is unique per record and thus can be used as the primary key. However, referring to a record based on all these c...more >>

This works: IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @qci_pk) But since I may need to build the Sql statement I tried something like this, which did NOT work, IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk = @qci_pk')) Is there a way to go around this?...more >>

How can I transfer DB diagrams and referential integrity relationships between databases with the same structure. I copied dtproperties table from one DB to another but it copies diagrams without foreign keys. ...more >>

I am trying to copy a file and giving it a name with a date behind it. Below is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File Not Found" message on Line 25 (I marked it below). Can someone see the problem with my code?? Dim NYear Dim NMonth Dim NDate NYear ...more >>

I'm just getting to grips with the new SQL Database file concept in VS 2005 and have a couple of questions in the hope that someone can clarify my understanding. I understand that I can now add both the <dbname>.mdf and <dbname>.ldf files traditionally associated with a SQL Server into my a...more >>

hello, i am working on a consumer-review website. in it, my users can write reviews for given products. in addition, other users can rate any given review as "Helpful" or "Not Helpful". how can i write a query to give me the "Most Helpful Review" ? meaning, on my product page, i want to hil...more >>

I have Visual Studio .Net 2003...I am assuming that since it can't find System.Data.SQLServer that I need Visual Studio 2005 to develop CLR apps for SQL? ...more >>

Environment: SQL 2000 SP3a I have tired various things but not sure which route to go from now. I have a SQL Job which should be owned by non SA account as some of the members in the other team want to look at this job and by making this job owned by SA, they lose this previlege. This...more >>

I just installed the new MS SQL2005. I have been trying to get used to the new Management Studio. So, far I'm not impressed, albeit, I will admit I have not spent that much time with it. One of the first tasks that I went to accomplish is to extract the DDL for some objects in one my databas...more >>

Hi: I have a .net web application and I am getting a lot of deadlocks. My system processes a lot of real-time transactions daily. In addition , there are like 3 DTS packages running. The problem is that I am getting a lot of deadlock and based on the history of the pakages I would ...more >>

We are in the process of migrating from a DOS based binary file storage format to a relational database structure. However, for migration reasons we need to be able to export the relational data to the binary file structure for processing. I have accomplished this with a not-so-lightweight pro...more >>

In SQL Server 2000 Online Help, the method ReCompileReferences (looks like C++ which I know nothing about) is mentioned. It causes recompilation "prior to the next execution" of any stored procedure on the referenced table. In a production system as part of the maintenance plan we reindex ni...more >>

SS2K: How to deal with IDENTITY property restore [from backup] situation? Specifically: table Foo has IDENTITY property which is also used as the primary key (yes, UNIQUE constraints are on the "real-world" key). Data entered on November 16 was deleted by user on November 20. It's now December...more >>

Hi, I had 3 tables Tets_06 Test_05 Test_04 I want to combine these 3 tables into one new table 'Test_combined' --I cant Use UNION ,bcoz the structure of the 3 tables is not same (Test_06 had two extra columns than test_04) here is the sample DDL: CREATE TABLE TEST_06 ( ID ...more >>

If I do this: select * from tblAmcareDetail I get my records back but as soon as try to specify column names I get: Server: Msg 207, Level 16, State 3, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'apexorderid'. I think it must be a connection problem beca...more >>

Is there an easier way to write the WHERE clause when the same column is compared over and over again? I need to pull by 360 zip codes. Instead of Where Zipcode = '12345' OR Zipcode = '54321' OR......and so on. Something like Where Zipcode = OR{12345,54321,12342...}would be what i'm looki...more >>

I have a query which returns the Top 100 selling products for each of the 14 categories that we sell. It takes 10 minutes to run, however. Does anyone have a suggestion on speeding this up. SELECT ProductID, CategoryID, Sales, Qty FROM Sales s1 WHERE productid in ...more >>

I have two tables - header, detail. HeaderID appears in both, is an auto-incrementing rowID in the header table, and required also in the detail table. It's a one-to-many relationship from the header table to the detail table. The code I have writing to these tables really needs to write ...more >>

When does a global temp table get dropped? i have a stored proc that creates a global temp table and inserts a record into it.say for eg. i run the stored proc first, the temp table gets created.if another user runs the stored proc now,the temp table is already there,hence the stored proc just...more >>

Can someone please tell me the best way to handle this problem... I have the following SERVICES Server Service ------- -------- S1 A,B,C,D,E,F S2 A,B,C,D,E,F S3 A,C,D,E,F I need to run a query that will select a Server and Services like A,B, or C. I...more >>

Is there a way to access 2 databases simultaneously using a single connection string? server: SQLSERVER databases: gasstations customers I'm working on a VB.NET application. Thanks Bill ...more >>

Hi, I have a old VC++ application using DB-Library for connection with sqlserver 6.5. Recently I have migrated the sqlserver from 6.5 to 2000. during recompilation of the vc++ code, i have a problem with ntwdblib.lib file. I think it does not match with the SQL 2000 version one. The SQL 200...more >>

Hi, Consider the following result set: PNID PN_NUMBER Date1 Date2 Status PN_Name ================================================================ 27 2051 08 Sep 1941 NULL Received NULL 28 2143 01 Jan 1945 NULL Accepted NULL 28 2143 01 Jan 1945 NULL Accepted R Anderson 29 2151 NUL...more >>

I have run in to the following problem on MS SQL Server 2005: When I attempt to execute the following query in SQL Server Management Studio or Visual Studio 2005 Database project an exception is thrown. SELECT CAST('2' AS INTEGER) AS Expr1 The error message says: ---------------------...more >>

I know I have done this before but for the life of me I can remember how' When I run the query below it loops and loops, how do I make it appear just once - missing something but brain freeze has taken over aaaggghhh.... select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO], [tra...more >>

Hello NG, we all know the problem of (date) equality checks on datetime fields preventing us from issuing a command like: SELECT * FROM [MyTable] WHERE [SomeDateField] = @SomeDateVariable As datetime contains time information down to the milliseconds this query will usually re...more >>

Hello NG, I need to start SQL Server Agent jobs frequently from my stored procedures by using the code fragment: EXECUTE msdb..sp_start_job 'MyJob' If the job is still running though, I get flodded with (completely harmless but disturbing) error messages that tell me, that the job is...more >>