Is it usual for a 'not in' statement to return no rows when the subquery has a null in it? Also, is this a distinction to the left outer join method? TIA ...more >>

Hi, We had a simple application which compared schemas on local servers. We are updating it to compare schemas across servers. Unfortunately the query which refers to the Information_Schema fails, and I cant find any syntax to make it work! Here is the simplified version of the query. SE...more >>

Hi, I am having issue with simple update statement: Statement is as follows: UPDATE tblProduct SET Quantity = Quantity - 1 WHERE ProductId= 'SG7271308' and Q_tag = 'YES'; tblproduct table has only 5000 rows and productid is PK. Above statement is run in loop. It times out in couple of it...more >>

I posted this here a few days ago, and someone said to ask on a delphi forum. Well, I did, and also have searched everywhere I can imagine, without luck. My stored proc when executed from delphi 7 produces a Bad variable type error. This happens on win2k and win98, but does not happen on ...more >>

I have just read in Chapter 12 of the Microsoft SQL Server 2000 Performance Tuning Technical Reference by Microsoft Press that.. "an update-in-place is actually a delete operation immediately followed by an insert operation". This one sentence concerns me because we have just changed most of...more >>

I have a set of tables that I have inherited that are fairly large and we are setting up some searches that entail pulling ID's out of a string. I know this is not the greatest set up (and not at all relational), but I do have to deal with this table. I have stripped down that table to jus...more >>

Hi, I am trying to use sp_executesql to check the existence of a user table at run time. I am using the sp_executesql stored procedure but getting the incorrect response. Can sp_executesql be used to change table names at run time. Do I have to use 'exec' to achieve my goal. If yes, how...more >>

Hi, I am trying to use sp_executesql to check the existence of a user table in run time. The t-sql code is as follows: *************************************************** DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @Exists int SET @SQLString...more >>

I have a bunch of rows similar to this ID, Name, eMail, SignupDate 1, John, john@nowhere.com, 2005-01-03 2, Mary, mary@nowhere.com, 2005-04-05 3, Mike, mike@nowhere.com, 2004-06-06 etc... Ok, now I have parameters called @StartMonth, @EndMonth, @YEAR I want to to select all the rows tha...more >>

Hi, I'm trying to create a store proc and use a WHERE clause only if a certain condition is met. When I use the WHERE after an if condition, the sql enterprise tool tells me it's invalid. "Invalid syntax near WHERE clause" Here is my proc: CREATE PROCEDURE [dbo].GetContractorsList ...more >>

I got data in multiple records with the same ID, and I would like to rearrange it into 1 record per id. Please help, Thanks, Culam Issues --------- id work home alternative emergency ------------------------------------------------------------------...more >>

Imagine a table that enumerates membership of items to some set: create table sets ( setId int not null, itemId int not null ); some data: set 1 = {1,3,5,7,9} set 2 = {1,2,3,4,5} set 3 = {4,5,6,7} translated to this model: setId, itemId 1,1 1,3 1,5 1,7 1,9 2,1 2,2 2,3 2...more >>

I have a code that I need to parse, there's got to be an easier way to approach this than the way I am... here's a sample pattern for the code.. V-930186-1-IRS1099 So I need the '930186' part.. The problem I'm having is I'd like to be able to reference the string part that I'm currently...more >>

Hi, I'd like to output values into my table as follows: If column A of my input table equals 1, then I want 4 values in my output table: x,y,z,0 If column A of my input table equals 2, then I want this instead: x,y,0,z x, y, and z are columns in my input table. I don't know how to...more >>

What are the consequences of changing the current database (using "use dbname") during a transaction? If I modify multiple databases (on the same Sql Server instance) during a transaction, will all changes be done in the same transaction (and commited/rollbacked atomically)? Does using X...more >>

I have fact tables in a data mart where it is possible for the user to have rated a particular item on date X and then rated it again on date Y and again on Date Z. This is OK, as we need to perform analyses on these behaviors. However, for the more important analyses it is necessary to use o...more >>

Hi. I need some help with my query. Below is a sample of a data from a table. What I need to accomplished is to get the TDates of the first occurence of 500 hours for each EmployeeID. So with the sample data below, I need to get in my query, Emp1, 2005-02-19 Emp2, 2005-02-25 TID...more >>

I have a table that is being dropped and then recreated by "SELECT INTO" through a scheduled stored procedure. So, therefore, all permissions are being dropped as well. I need to re-establish SELECT privileges for this table to a role containing a user, or if it can't, a user alone. I have ...more >>

I need to restore a database on my test server from that on my production server. Here's what i've done so far: 1) Created a new db on my testing server named 'main_test' 2) Restored 'main_test' using .bak file from my production db. Now, I have several transaction log files I wan to inc...more >>

I know how to retrieve the list of triggers in teh database. Select * from sysobjects where xtype = 'TR' What I want to know is how do i tell whether the trigger is disabled or enabled. I would appreciate if anybody can help me this. thanks MB ...more >>

Hi, I am passing datetime from my vb.net app for storage in sql server like this Dim DT As DateTime = DateTime.Now Dim str As String str = DT.ToString("MMddyyyyHHmmss") ....insert to database str (example datetime 10312005135802) The table column datatype is cha...more >>

How can I alter the system procedure in MSDB database. I am not getting the alter window to modify SQL system procedures...more >>

hello, I am working on a stored procedure to produce a report where the user will select categories to be placed on a report. Is it possible to create a storedprocedure that will create a user-specific table that will contain the categories and a column to indicate selected or not. I nee...more >>

Hi, if i call from query analizer this command EXEC XP_CMDSHELL 'C:\SHIPINFO 492796' c:\shipinfo.bat = echo off e: cd\ cd dbman~1\dbman dbman "shipdjp.run with %1" type c:\shipfile.txt .... The problem is something happens not sure what at the moment but the process will ...more >>

Folks Probably a complete newbie question... I'd like to use the result of a computed column of my select in the said select WHERE clause. Something like SELECT ColIdx, COL1, (SELECT SUM(Ammount) FROM OtherTable WHERE (MyTable.ColIdx = OtherTable.colIdx)) AS TotAmmount FROM myTable WHERE ...more >>

Hi all, I have one doubt, if anyone can helpe-me. I have one table that have one trigger (UPDATE), this trigger have one loop with 12 interactions, for each interaction I have one INSERT in other table. I did read that is not good put loop in trigger, this is true, if yes how I should do th...more >>

The table looks like this: PARTNUM, BREAKPOINT, PRICE The table might contain data that looks like this: ABC,3,10.00 ABC,5,8.00 ABC,10,7.00 XYZ,5,13.00 How do I make a query that can give me the price if I supply a part number and quantity? For instance, if someone is buying 4 ABCs, th...more >>

I need to update a very large fact summary table with the detail data from other fact tables - what suggestions are there for making this perform better on the server? UPDATE STAGE_PHX_FACT_POLICY set county = FACT_POLICY_HO.County,Sub_County = FACT_POLICY_HO.Sub_County, Zipcode = FACT_PO...more >>

Hi, I am doing an outer join in my SP and sometimes the results will have a null value. I have tried the code below but it is still returning a null. How do I test for a null to return a ' ' value? SELCT CASE b.fcsetupsh WHEN NULL THEN ' ' END ...more >>

Given 2 tables, Transactions and Instructions, my goal is to join a transaction to its appropriate instruction. The 3 fields I can possibly join on are: Exchange, SecType, and Country. All of these fields are nullable in the instruction table. Not my design, I can't change this. How can I jo...more >>

Ladies and Gentlemen Is their a way to force BCP to accept a Date Value of '000000' instead of giving an error? I am using BULK INSERT with a FORMAT FILE. Mark Moss ...more >>

I just had all data w/in about six tables deleted. I'm trying to figure out how this occured. Is there a way to view and make sense of the transaction log file(s)?...more >>

Hi guys, I have a sql query that returns some data in XML (for XML EXPLICIT), the query is encapsulated in to a stored procedure and runs in 2minutes (the sp has been freshly recompiled etc). If I however take the sql commands and run the query directly in query analyser (i.e. not in a spr...more >>

Is there a way to do a multi column subquery using a IN clause? Example of what works for Oracle and needs to get it to work for SQL Server 2000: SELECT * FROM Test T WHERE (T.Key1, T.Key2) IN (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS NULL) I would l...more >>

I would like to maybe wrap a case statement around the following so that I can set any results that come back as NULL to 0 (the performance of updating afterwards is horrible) update STAGE_PHX_FACT_POLICY set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee, STAGE_PHX_F...more >>

I have 2 tables (ta [parent], tb[child]) and need ref. "...on delete set null" (SQL server doesn't know this by native). Alternative solution (generated by CASE soft) uses BEFORE DELETE TRIGGER (on parent table) to set NULL to all depending child table's record (not usable even). Problem i...more >>

Hello, I am new to MS SQL Server and I am in the process of implementing a database system which introduces an interesting security issue that I was hoping some one could advise me on. BACKGROUND: I am developing a client / server application that which requires users to be able to downloa...more >>

hi... I am trying to run MS DTC sample application (dblib) equipped with MS SQL Server 2000.I am using following commnd line to link SQL Servers on two different systems "dblib -s1 abc -u1 administator -p1 password1 -s2 xyz -u1 sa -p1 password2 " Server 1: my PC (OS Xp) Server 2: R...more >>

I am now looking for a few days for the "Unique Value" indic of an index. I can't find it in sysindexes, other sys table or information_schema. So I am missing something. I need it to generate some code Can somebody help me? tnx, Pierre ...more >>

Hello Everyone i am new to SQL Server. i have a problem. i have to send a query string to a server using post method from a stored procedure. i got a stored procedure in some group for sending Http Post Request from Stored Procedure. but i dont know how to pass parameters and Http Header In...more >>

Hi I am receiving, on a regular basis, a massive xml files with invoices from our clients. The invoices have a unique InvoiceNumber. There is many types of invoices,(4 or 5), and each type will have a diferent invoiceNumber patern. Type 1 might be xxxxx Type 2 might be 1xxxxxxxx type 3 mi...more >>

Hi, The goal of this script is to update the CurrentBalance column so it reflects the true current balance for each record based on the data in the Amount column. I will appreciate a set based solution because even though this is a one time process it takes longer time than I have. Th...more >>

Hi, I have an issue where a query on SQL uses a different execution plan dependent on a datetime value passed to it. If I use: declare @SiteId int set @SiteID = 201 declare @EligibleRecipient table ( UserId int not null ) declare @Cutoff DateTime declare @vccutoff as varchar(...more >>

Hello All, Could someone help provide a query that I can run to determine the primary key and unique columns for any given table ? Thanks in Advance, Akintoye ...more >>

Hi, Is there a way to split names which are stored in one column to multiple columns (first name, prefix, lastname)?...more >>

I've written 2 stored procedures. Proc 1, cycles through the system table and pulls out the names of all DB's. Proc 2 is called by Proc 1, after it grabs 1 of the database names. It then performs a type of backup based on a flag: F = Full, D = Differential, and T = Transaction Logs. I...more >>

I have a table that contains fields such as: ---------------------------------------------------------------------------- --------------- CustomerID ProductName SmallDateTime AnotherID1 AnotherID2 Amount --------------------------------------------------------------------...more >>

My SQL server is set as case insensitive, 1252 character set. When trying to export some data (NVarchar) out from a table, I got some funny characters but viewing them from the Enterprise Manager or QA, they are perfectly normal. Can someone pls tell me what unicode type does MS SQL support?...more >>

Where can I find what version and SP my SQL Server has. TIRislaa ...more >>