How can I define the default value for a text field to be a zero length string ...more >>

Hello, For anyone that's a VB programmer out there, I'm looking for the evivalent of a Choose statemetn in SQL Server. For those that aren't VB guys, a choose statement will return a value, based on the numeric value of the input. for instance; Choose(InputValue,[Return if InputValue=1...more >>

I have a stored procedure that returns outage totals by month for the past 7 years. The problem is that if there is no outage for a given month it doesn't return all of the previous years. For example, I have 4 outages in Jan 07, 0 in Jan 06, 1 in Jan 05; My resultset returns 4 Jan 07 and 1 ...more >>

Is it possible to run queries against ##temp tables created by another process ? Any help is appreciated. Sid. ...more >>

Hi, SELECT COL1, COL2 COL3 FROM TABLE1 In the above query, there is no comma between COL2 and COL3, why wouldn't it flag an error, but instead result in a resultset that shows only COL1 and COL3? thanks Eugene...more >>

So here's the question: Is there a best practice for using Count to determine a rowcount for a result set? For instance if I was using the northwind database is there a substantial difference between select count(*) from categories and select count(categoryid) from categories Thanks in...more >>

I have noticed a couple of posts that have expressed dissatisfaction with cursors when compared to temp tables. Now I come from a VFP background where cursors are de rigueur so when they turned up in SQL2005 they seemed a great idea to me. Can anyone explain if there is a good reason for prefe...more >>

When running a SP from an ODBC connection from Visual FoxPro I would get a result set returned that I can subsequently process. Is there a way of doing this in T-SQL? I.e. SP1 executes SP2 the code in SP2 creates a result set. Can I pass this result set back to SP1 in some way. I know I can us...more >>

I am receiving the below error message when i am trying to assign the @counter to @total in the program below declare @counter bigint,@total bigint select @counter=count(*) from table1 where column1 between 0 and 10 set @total =@counter select @counter=count(*) from table1 where column1 be...more >>

Hi All. I am wondering if somebody could help me fine-tune my stored procedure? I am sort of new to this and could really use some help. The point of the stored procedure is to execute the query and then return the first N ROWS back, allowing the user to page through the results accordin...more >>

Detect datetime of last change to a database Is there a way I can detect the last time something changed in a database? Like maybe the date stamp from the log file? Will this work on all database logging methods (Simple, Bulk, Full)?...more >>

Thanks for any help anyone can provide as I am stuck with this and although i am learning every day by reading the many posts on here my various searches haven't provided a solution to date. I have two tables with the following varchar data in them Site ====== Bristol Swansea Blackpool ...more >>

Hi all again, I can I get the last execution time of a stored procedure? Is that even stored? thanks gv ...more >>

Hello, For some reasons, I need to change the server of Windows Server. On that server, there is a SQL 2005 installed. My question is after changing the name of Wins Server, do I need to change the SeverName in SQL 05? I know I can use the following statement to change the SQL Server Nam...more >>

CREATE TABLE #tmp1 (co varchar(10), rev int) INSERT INTO #tmp1 SELECT 'abc', 10 UNION all SELECT 'def', 13 UNION all SELECT 'ghi', 14 UNION all SELECT 'jkl', 10 UNION all SELECT 'abc', 10 UNION all SELECT 'def', 13 UNION all SELECT 'ghi', 14 UNION all SELECT 'jkl', 12 The list above c...more >>

What is the correct way to convert a varchar that contains... 27/03/2007 4:30:00 a.m. to a datetime?...more >>

I have poured over countless examples and post on this forum and I made my attempt at this query and I still can not get my desired results. I am trying to get the latest request from a user and what type of access he or she requested. I am also attempting to get the individual whom requested ...more >>

I'm working in SQL Server 2000, sp3. I am trying to parse a filename within a nvarchar variable using PATINDEX. Unfortunately the exact literal substring I need to match is '_db_'. Apparently I cannot use the ESCAPE word within a PATINDEX. Any ideas? ...more >>

Hi, using sql 2000 sp4 I'm sorry if I don't explain this correctly. In a NutShell: Trying to combine first column string that matches in the same stored procedure in a different row. Elemenate duplicates. I think the root of what I need would be in a case statment? The following query i...more >>

What does ON 'PRIMARY' mean in the below? Is it necessary? CREATE TABLE "LotSortEnd" ( "LotSortEndID" uniqueidentifier NOT NULL, "LotSortEndHead" smallint NOT NULL, "LotSortID" uniqueidentifier NOT NULL, "LotID" uniqueidentifier NOT NULL) ON 'PRIMARY' Thanks! ...more >>

Hi DBers, This same question comes up from time to time. I have two tables with a simple parent-child relationship: Customer -------- Id: number Name: varchar(50) Address: varchar(100) Orders ------ CustId: number Date: date Description...more >>

Since Strings in .NET translate to nvarchar in SQL, should I use nvarchar in SQL even if unicode is not needed and the app is just going to be used in the United States? Thanks, Ron ...more >>

Is it possible to create a dynamic view from a stored procedure? I would like to have a sp run when someone pulls from a view. Otherwise, I have to create a nightly job. If it matters, this is for an AdHoc report....more >>

I have a simple table that contains a primary key, two [irrelevant for the purpose of this question] foreign keys, a column identifier FK, and a value; the idea being that a user could dynamically create new "columns" though an application. This table now contains some 500m records. When the...more >>

I have an example dataset in SQL Sever 2000. Assume 4 columns exist: 1,1,1,1 1,1,1 1,1 1 I want to query and return a result that outputs the # of non-null values in that row into a new column [already created]. I'm working with a sample that mirrors this action using cursors, but I can't...more >>

Hello, Thanks for reviewing my question. I am new to SQL and new to using Cursors. I would like to know why @@CURSOR_ROWS is returning -1 when I open the cursor. My code is so basic so I am not sure what could be wrong. DECLARE PRODUCT_LIST CURSOR FOR SELECT * FROM ProductList SET @NU...more >>

I'm using sql server 2000 and I have some heavy tables that I have partitioned them into 2 federated servers. I folowed all of instructios to implement a federated servers. When I execute a query using partitioned view, I dont see any differences in waiting time with the conditions that I use ...more >>

If an auto update statistics setting is on for a database. Is there any instance where I need to create statistics for any table explicitly? ~Shiju ...more >>

I have a system where the users need to be able to copy records. A record comprises an number of related rows in various tables (Ive posted sample ddl below of a simplified version of what i have) I want to create a stored procedure that will perform the copy. This procedure will need to ...more >>

Is it possible to do a trigger after a rollback event? I've got an MS Access application with tables in sql server 2000 that occasionally causes a rollback after data is entered, so I want to monitor for rollback events and send myself an email when it happens. Can I do this with a trigger or ...more >>

Hi, I use SQL Server 2005. I have a table with around 7 million records in it. I am doing a count(*) on it to get the records based on some condition. ie., Select COUNT(*) from <table_name> where <condition>. It should return around 4000 records. It takes approximately 15 seconds to d...more >>

Hi there. This is my first post here so please excuse me if I am posting in the wrong place or missing an established post that solves my problem. I have looked for one to no avail. I am in the process of writing a rather rough and ready sequence of stored procedures to import large (some...more >>

In simple words, when to use clustered index? Thanks. ...more >>

Should i write the best match column in the beginning of the where query or in the end? for instance: SELECT * FROM users where Gender=M AND Fullname Like '%A%' or SELECT * FROM users where Fullname Like '%A%' AND Gender=M Thanks. ...more >>

10x ...more >>

For different database, i am using "USE <DATABASE NAME>" in the header of the script. what should i write there in order to work on different server? i know that i can run isqlw - S Servername but i want that everyone who is running the script in my office will not need to take care of the serve...more >>

Hi, I need a brief description about the columns of DBCC IND result. Thanks in advance, Leila ...more >>

Hi, I want to update a text field of Table-A with text field of Table-B Following is the sample query which gives an error. -- Query Starts here -- Update TableA Set txtField = (Select txtField From TableB Where TableB.Code=99) Where TableA.Code=99 -- Query Ends here -- Following is the ...more >>

Hi all, Using SQL 2000 sp4 I'm getting the following error when trying to insert into a table using results from a stored procedure? Server: Msg 8164, Level 16, State 1, Procedure sp_Gerry_Search, Line 85 An INSERT EXEC statement cannot be nested. ...... repeated many times Cam someon...more >>

Hi all, I have a very strange pb: I'm trying to script a sql view, programatically, in .NET 2.0 : SQLDMO.SQLServer _srv = new SQLDMO.SQLServer(); _srv.Connect(_serverName, _userID, _pwd); SQLDMO.Database _db = (SQLDMO.Database)_srv.Databases.Item(_dbName, "dbo"); SQLDMO.View _tbl = (...more >>

I have a stored proc (see below) that inserts a new record. When I run it from query analyzer it does not show the new inserted column ID. Is this just a problem in QA? The sp works great but I need to be sure it returns the new ID. Thanks. CREATE PROCEDURE [ms_insRepairOrderLinesDup]...more >>

Hi. I use DTS Wizard to craeat an export job. The data from the job is exportet into a .csv file. However I want the filename to be on the form MyExport_TIMESTAMP.csv. When using DTS Wizard, I end up with a package that only allows fixed file names. What would be the right PL/SQL code t...more >>

From MSDN docs: "DBCC DBREPAIR is included in Microsoft® SQL ServerT 2000 for backward compatibility only. It is recommended that DROP DATABASE be used to drop damaged databases." Really handy. Why don't they just say if your database is damaged then you are farked. ;-) ...more >>

Hi ppl, Is there an equivalent of the above function in MSSQL? I need a query to return IP address instead of the dotted-decimal-address from the database. Thanks in advance. SY Lim. ...more >>