Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)
Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
QM_UID count(*)
25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3
...
--- Script to replicate problem
-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant
-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error
-- at 100000 rows but does at 1000000 rows.
-- Create a table
CREATE TABLE tmp (
[QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
[QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
[QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
[QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
[QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
[QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
[QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
[QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
[QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
[QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
[QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
[QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
[QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
[QM_HELD] [smallint] DEFAULT 0 NOT NULL,
[QM_PROOF] [char](12) DEFAULT '' NOT NULL,
[QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
[QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
[QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_TITLE] [char](72) DEFAULT '' NOT NULL,
[QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
[QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
[QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
[QM_PHONE] [char](22) DEFAULT '' NOT NULL,
[QM_FAX] [char](22) DEFAULT '' NOT NULL,
[QM_ORDER] [char](20) DEFAULT '' NOT NULL,
[QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
[QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
[QM_REP] [char](12) DEFAULT '' NOT NULL,
[QM_REP_1] [char](12) DEFAULT '' NOT NULL,
[QM_REP_2] [char](12) DEFAULT '' NOT NULL,
[QM_REP_3] [char](12) DEFAULT '' NOT NULL,
[QM_REP_4] [char](12) DEFAULT '' NOT NULL,
[QM_REP_5] [char](12) DEFAULT '' NOT NULL,
[QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
[QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
[QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
[QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
[QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
[QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
[QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
[QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
[QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
[QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
[QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
[QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
[QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
[QM_CURR] [char](6) DEFAULT '' NOT NULL,
[QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
[QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
[QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
[QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_CODE] [char](36) DEFAULT '' NOT NULL,
[QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
[QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
[QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
[QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
[QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_UID] [int] DEFAULT 0 NOT NULL,
[QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]
GO
-- Create an index on the table
CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]
GO
-- Populate the table
declare @Counter as int
SET NOCOUNT ON
set @Counter = 1
while @Counter < 100000
begin
insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);
set @Counter = @Counter + 1
end
GO
-- Update QM_UID to a sequential value
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
-- Find rows with a duplicate QM_UID (there should be no duplicate)
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
--drop table tmp
-- output from select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
View Complete Forum Thread with Replies
Related Forum Messages:
Exporting To EXCEL Produces Unexpected Results.
We are having a problem that we can not explain. We have reports that look fine when viewing them in the report viewer however after exporting they don't not look correct. First of all we are getting column that span more then one column on the spread sheet. Example column N and O get merge to hold the data from one column in the original report. Sometime the text in one column overwrites the text from other column. when using multiple matrix cotrols on a report the report when exported seems to misalgn the matrix on the report so that column no longer match correctly. Also I am have a problem with the total row on a matrix wich use and avg agregrate. the column should add up to 100% an what is displayed is 5.88%.. and tips or suggestions would be appreacaited
View Replies !
Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names- declared. Variable and after KILL and use the same declared. Variable like DECLARE @StartDate datetime KILL @StartDate datetime (remove from memory) use after with the same name i have 2 big stored PROCEDURE i need to put one after one and psss only 1 Variable name to the second stored PROCEDURE like this i don't get this error The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. Msg 134, Level 15, State 1, Line 146 The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. i use like KILL @endDate ?? KILL @StartDate ?? TNX
View Replies !
SQL 2005 SP2 - Unexpected Results With Snapshot Replication - Should Get An Error
I am using snapshot replication to copy data from one database to another. I truncate the destination table first. The publisher and the subscriber are different databases on the same server. The data turns into 1 unrecognizable row. Here is what the source table looks like: displayTypeID, displayType 1, Title List Formats 2, Title Details 3, Title List Export 4, Pricing Notes 5, clone Mask 001 6, clone Mask 002 7, clone Mask 004 8, clone Mask 008 9, hide From Admin 10, not Common Attr 11, Insert Title 12, title Types The resulting data looks like this: DisplayType, displayTypeId , 1660953600 I see no errors in replication and nothing in the source table looks like bad data that could cause such a malady. I see the problem on two different servers now and identified the source of the problem, the column order is reversed in the source and destination tables. The data types do not even match so I would at the very least expect an error from bcp.
View Replies !
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task: DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1; I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this: DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1; I have almost 50 references to these parameters in the query so a substitution would be helpful. Dan
View Replies !
SP Produces Intermingled Results
I have a custom SP running from a PB application. When I am retrieving information for a certain client, a co-worker decides to run the same custom SP for another client before my results are displayed. When both results display, I end up with certain pieces of data from his client and he ends up with certain pieces of data from my client. What code in my SP do I need to prevent this from happening?
View Replies !
Storing Results Of Select Statement In @variable
I'm new to sql stored procedures but I would like to store the results of an sql statement in a variable such as: SET @value = select max(price) from product but this does not work, can someone tell me how I would go about in storing the results in a variable. @value is declared as int Thanks in advance, Sharp_At_C
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Basic UPDATE Statement Results Question
Hi, I have written a basic UPDATE statement to update two fields in one table using a where clause to identify the record i wish to update. i.e. UPDATE TableName SET field1=1234, field2='' WHERE primary_key=N'1234'; When i run the statement, in the results window i get a value returned that is equal to the total number of records in that table!! i.e. (No column name) -------------------------- 588061 but when i check the table, indeed only one record has been updated as expected (and i have confirmed this by using SELECT @@ROWCOUNT straight after) My question is, why does it do this?? i paniced at first as i thought i had updated ALL rows, but luckily this seems not to be the case. And can i turn this off?? Many thanks, Martin
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks Code: Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as begin if (@userName = '') raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1) else begin Declare @leagueId int Set @leagueId = -1 --Check if the username belong to a player Select @leagueId = leagueId From Users u inner join players p on p.userId = u.userId inner join teams t on p.teamId = t.teamId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --Check if the username belong to a teamUser Select @leagueId = leagueId From Users u inner join teamUsers tu on tu.userId = u.userId inner join teams t on tu.teamId = t.teamId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --Check if the username belong to a leagueUser Select @leagueId = leagueId From Users u inner join leagueUsers lu on lu.userId = u.userId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --username is not in db or is an admin user return -1 end end end end end return -- when I run this I get no results returned LookupLeagueIdByUserName 'chris'
View Replies !
Can Variable Be Used In SQL UPDATE Statement In VB.NET
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() When I don't use a variable after SET statement, everything work fine. This code works fine: Dim variable As Integer Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() Please, if someone can help me in this...thanks..
View Replies !
Using A Variable In An Update Statement
I am having difficulties with some sql syntax with sql server 2000. I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String. Ex. Declare @varA varchar(12) select @varA = (select top 1 Value from #temp) Update TableX set @varA = y.ColTest from TableX x, TableY y where x.Colid = y.Colid It sets the variable = to the last value from TableX.ColTest I want the Update statement to update the value for the Variable which represents the correct column to update. Any ideas? Thanks, Daniel
View Replies !
Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length. I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB' this needs to be four characters long so want to pass it into a function and return 'AB 'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom
View Replies !
Select Declared Variable With Case Statements
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email. I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email. I have declared all variables appropriately but I will leave that section of the code out. Select @ifiledate = iFileDate from tblTelemark where iFileDate = CASE WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN Select @countfri1 = Count(*) from tbl1 Select @countfri2 = Count(*) from tbl2 Select @countfri3 = Count(*) from tbl3 Select @countfri4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN Select @countsat1 = Count(*) from tbl1 Select @countsat2 = Count(*) from tbl2 Select @countsat3 = Count(*) from tbl3 Select @countsat4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN Select @countsun1 = Count(*) from tbl1 Select @countsun2 = Count(*) from tbl2 Select @countsun3 = Count(*) from tbl3 Select @countsun4 = Count(*) from tbl4 END Is there a way to do what this that works???
View Replies !
Using Declared Variable As Passphrase Slows Query
I have two tables - gift_cards and history - each related by a field called "card_number". This field is encrypted in the history table but not in the gift_cards table. Let's say the passphrase is 'mypassphrase'. The following query takes about 1 second to execute with a fairly large amount of data in both tables: SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number When I use a declared variable to contain the passphrase, the same query takes over 40 seconds. For example, declare @vchPassphrase as nvarchar(20) select @vchPassphrase = 'mypassphrase' SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it. Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster? Thanks, SJonesy
View Replies !
Unexpected Results In Query
Hi, I'm trying to run what I thought was a relatively straightforward query to find all entries from one table that don't appear in another table: select * from Search_Suggestion where Suggestion not in (select distinct C106 from Search_Log) The Search_Suggestion table contains 4060 entries and the Search_Log table contains 142,000+ distinct entries. From running a similar query using 'in' instead of 'not in' I find that there are 3778 matches between the two tables would logic which suggest leave 282 that don't exist in the Search_Log table. However....when I run the above query it returns no records. I've tried changing it around and have also tried using temp tables but each time I still get no records. Any help wouldbe greatly appreciated. Kind regards, Steve
View Replies !
Using Declared Variables In SQL INSERT Statement.
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express. I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL. If I hard code the values in the SQL statement it works fine. Can someone explain what I'm doing wrong accomplish this? My code is below. Thanks in advance. file.aspx <asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT * FROM [Table]" InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" > </asp:SqlDataSource> file.aspx.vb Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click Dim variable1 As String = FileUpload.FileName Dim variable2 As String = Date.Now Dim path As String = Server.MapPath("~/directory/) If FileUpload.HasFile = True Then Try SqlDataSource.Insert() FileUpload.PostedFile.SaveAs(path & _ FileUpload.FileName) End Try End If End Sub
View Replies !
DBCC DBREINDEX - Unexpected Results
I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented. First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases? Clive
View Replies !
DBCC DBREINDEX Unexpected Results
Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it? Clive
View Replies !
Unexpected Results Of Stored Procedure
hi, hvae a little trouble with thos procedure. When executed it always adds 2 to the value of fields Telefon and Mobil. Can anyone help me, please Kurt ALTER Procedure opdaterbruger ( @Initialer nvarchar(100), @Mailadr nvarchar(100), @Telefon float(8), @PlusNet float(8), @Mobil float(8), @pnmobil float(8) ) AS UPDATE Brugere SET Mailadresse = @Mailadr WHERE Initialer = @Initialer IF (@Initialer= '0') OR NOT EXISTS ( SELECT * FROM PNetlokal WHERE Bruger = @Initialer) INSERT INTO pnetlokal ( Bruger, Kortnr, Telnr ) VALUES ( @initialer, @Plusnet, @Telefon) ELSE UPDATE PNetLokal SET TelNr = @Telefon, KortNr = @PlusNet WHERE Bruger = @Initialer IF (@Initialer= '0') OR NOT EXISTS (SELECT * FROM PlusNet WHERE Bruger = @Initialer ) INSERT INTO plusnet (Bruger, Kortnr, Mobilnr) VALUES (@initialer, @pnMobil, @mobil) ELSE UPDATE PlusNet SET Mobilnr = @Mobil, KortNr = @pnmobil WHERE Bruger = @Initialer
View Replies !
Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello, I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below: I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom ) ------------------------------------------------------------------------------------ I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure. I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above. ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( 'Insert Into ' + @NewTableNameOut + ' ' + 'Select * From tableToSelectFrom' ) ------------------------------------------------------------------------------------ It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. Is this possible at all? If you need more info please let me know.
View Replies !
Combining Two Queries Producing Unexpected Results
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table. The following query returns, for each store, the total number of records in the ProductInventory table: SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID GROUP BY Stores.Name The following query returns, for each store, the total number of records in the Customers table: SELECT Stores.Name, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID GROUP BY Stores.Name I combined the two queries: SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID LEFT JOIN Customers ON Stores.ID = Customers.StoreID GROUP BY Stores.Name When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
View Replies !
Merge Join Gives Unexpected Results When The Input Has Speacial Characters
Hi I have a strange issue here. Both the input datasets of the merge join are sorted . Everything works fine if the dataset doesn't contain speacial characters like - (hipen). This is the sorted dataset.ASI311_3ASI311_3ASI312ASI311ASIKit1ASIKit1 Merge Joiner has to look for ASI311 and finds it in the list and everything works as expected. But if the dataset is changed to ASI311_3ASI311_3ASI-312ASI311ASIKit1ASIKit1 Then it doesn't find the ASI311 in the above dataset ? Only change between the two datasets is the - (hipen) which is added in one of the field. Does SSIS has a differenent precedence of the special characters than that of the Database. Both the datasets are on SQLServer. Can anyone point out the issue here? Thankyou Vidya
View Replies !
DTS In SQL Server 2005 Unexpected Error
I have a problem with using DTS (imported from SQL Server 2000). 1. I opened my DTS package in Legacy folder 2. I opened "Data driven task" 3. I entered "Lookups" tab And when i pressed the Query button there - the error occured quote: SQLWB - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience. Does anybody know how to solve it?
View Replies !
Unable To Install SQl Server 2005 SP2 - Unexpected Error
I am trying to install SQl Server 2005 SP2 but it was throwing an unexpected error when trying to install "setup support files". O/s: Windows Server 2003 SP1 Product: SQL Server 2005 Standard Edition Log file shows: +++++++++++++++++++ === Verbose logging started: 2/20/2007 16:05:42 Build type: SHIP UNICODE 3.01.4000.2435 Calling process: F:SQL2005sp2hotfix.exe === MSI (c) (74:68) [16:05:42:635]: Resetting cached policy values MSI (c) (74:68) [16:05:42:635]: Machine policy value 'Debug' is 0 MSI (c) (74:68) [16:05:42:635]: ******* RunEngine: ******* Product: C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi ******* Action: ******* CommandLine: ********** MSI (c) (74:68) [16:05:42:635]: Client-side and UI is none or basic: Running entire install on the server. MSI (c) (74:68) [16:05:42:635]: Grabbed execution mutex. MSI (c) (74:68) [16:05:42:729]: Cloaking enabled. MSI (c) (74:68) [16:05:42:729]: Attempting to enable all disabled priveleges before calling Install on Server MSI (c) (74:68) [16:05:42:745]: Incrementing counter to disable shutdown. Counter after increment: 0 MSI (s) (20:70) [16:05:42:792]: Grabbed execution mutex. MSI (s) (20:68) [16:05:42:792]: Resetting cached policy values MSI (s) (20:68) [16:05:42:792]: Machine policy value 'Debug' is 0 MSI (s) (20:68) [16:05:42:792]: ******* RunEngine: ******* Product: C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi ******* Action: ******* CommandLine: ********** MSI (s) (20:68) [16:05:42:792]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (20:68) [16:05:42:823]: File will have security applied from OpCode. MSI (s) (20:68) [16:05:42:901]: SOFTWARE RESTRICTION POLICY: Verifying package --> 'C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi' against software restriction policy MSI (s) (20:68) [16:05:42:901]: SOFTWARE RESTRICTION POLICY: C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi has a digital signature MSI (s) (20:68) [16:05:43:151]: SOFTWARE RESTRICTION POLICY: C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi is permitted to run at the 'unrestricted' authorization level. MSI (s) (20:68) [16:05:43:151]: End dialog not enabled MSI (s) (20:68) [16:05:43:151]: Original package ==> C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi MSI (s) (20:68) [16:05:43:151]: Package we're running from ==> C:WINDOWSInstaller1348fe1.msi MSI (s) (20:68) [16:05:43:151]: APPCOMPAT: looking for appcompat database entry with ProductCode '{53F5C3EE-05ED-4830-994B-50B2F0D50FCE}'. MSI (s) (20:68) [16:05:43:151]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (20:68) [16:05:43:167]: MSCOREE not loaded loading copy from system32 MSI (s) (20:68) [16:05:43:182]: Opening existing patch 'C:WINDOWSInstaller1553df86.msp'. MSI (s) (20:68) [16:05:43:182]: Note: 1: 2203 2: C:WINDOWSInstaller1553df86.msp 3: -2147287038 MSI (s) (20:68) [16:05:43:182]: Couldn't find local patch 'C:WINDOWSInstaller1553df86.msp'. Looking for it at its source. MSI (s) (20:68) [16:05:43:182]: Resolving Patch source. MSI (s) (20:68) [16:05:43:182]: User policy value 'SearchOrder' is 'nmu' MSI (s) (20:68) [16:05:43:182]: User policy value 'DisableMedia' is 0 MSI (s) (20:68) [16:05:43:182]: Machine policy value 'AllowLockdownMedia' is 0 MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Media enabled only if package is safe. MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Looking for sourcelist for product {EE92F683-5F5C-4970-BB0B-9AC591B60268} MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Adding {EE92F683-5F5C-4970-BB0B-9AC591B60268}; to potential sourcelist list (pcode;disk;relpath). MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Now checking product {EE92F683-5F5C-4970-BB0B-9AC591B60268} MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Media is enabled for product. MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Attempting to use LastUsedSource from source list. MSI (s) (20:68) [16:05:43:182]: SOURCEMGMT: Trying source \TESTf$cff417a74df5a718124f2a95bdecdc32HotFixSqlSupportFiles. MSI (s) (20:68) [16:05:43:213]: Note: 1: 2203 2: \TESTf$cff417a74df5a718124f2a95bdecdc32HotFixSqlSupportFilesSqlSupport.msp 3: -2147287037 MSI (s) (20:68) [16:05:43:213]: SOURCEMGMT: Source is invalid due to missing/inaccessible package. MSI (s) (20:68) [16:05:43:213]: Note: 1: 1706 2: -2147483647 3: SqlSupport.msp MSI (s) (20:68) [16:05:43:213]: SOURCEMGMT: Processing net source list. MSI (s) (20:68) [16:05:43:213]: SOURCEMGMT: Trying source \TESTf$a81c0730a34f964148HotFixSqlSupportFiles. MSI (s) (20:68) [16:05:43:213]: Note: 1: 2203 2: \TESTf$a81c0730a34f964148HotFixSqlSupportFilesSqlSupport.msp 3: -2147287037 MSI (s) (20:68) [16:05:43:213]: SOURCEMGMT: Source is invalid due to missing/inaccessible package. MSI (s) (20:68) [16:05:43:213]: Note: 1: 1706 2: -2147483647 3: SqlSupport.msp MSI (s) (20:68) [16:05:43:213]: SOURCEMGMT: Processing media source list. MSI (s) (20:68) [16:05:44:245]: SOURCEMGMT: Resolved source to: 'SqlSupport.msp' MSI (s) (20:68) [16:06:14:619]: Note: 1: 1314 2: SqlSupport.msp MSI (s) (20:68) [16:06:14:619]: Unable to create a temp copy of patch 'SqlSupport.msp'. MSI (s) (20:68) [16:06:14:619]: Note: 1: 1708 MSI (s) (20:68) [16:06:14:619]: Note: 1: 2729 MSI (s) (20:68) [16:06:14:619]: Note: 1: 2729 MSI (s) (20:68) [16:06:14:619]: Product: Microsoft SQL Server Setup Support Files (English) -- Installation failed. MSI (s) (20:68) [16:06:14:619]: MainEngineThread is returning 1635 This patch package could not be opened. Verify that the patch package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer patch package. C:Program FilesMicrosoft SQL Server90Setup BootstrapCacheSQLSupportx861033SqlSupport.msi MSI (c) (74:68) [16:06:14:729]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (74:68) [16:06:14:729]: MainEngineThread is returning 1635 === Verbose logging stopped: 2/20/2007 16:06:14 === +++++++++++++++++++++++
View Replies !
JDBC And SQL 2005 With XACT_ABORT UPDATE Statement Bug ?
While troubleshooting a problem at one of our customers, we noticed the following consistent erratic behaviour of our product with the 1.1.105 JDBC driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on the SQL server: Set the user option XACT_ABORT on a database server with the following SQL query: exec sp_configure 'user options', '16384' reconfigure Now run the following Code Snippet import java.sql.*; import java.util.*; public class Test { public static void main(String[] args) { try { // Load the JDBC driver. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // Establish the connection to the database. String url = "jdbc:sqlserver://server:1433"; Properties props = new Properties() ; props.setProperty("user","user"); props.setProperty("password","password"); props.setProperty("DatabaseName","databasename"); props.setProperty("selectMethod","cursor"); Connection conn = DriverManager.getConnection(url, props); // Set up simple prepared Update statement PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); Boolean isresultset = pstmt.execute(); System.out.println("IsUpdatecount? " + !(isresultset)); Integer updatedrecords=pstmt.getUpdateCount(); /*updatedrecords should be 0 or higher if isresultset is false if updatedrecords=-1, the result should be a resultset, which it cannot be, but seems to be*/ System.out.println(updatedrecords+ " row(s) affected"); } catch (Exception e) { System.err.println("Got an exception! "); System.err.println(e.getMessage()); } } } You can revert the database server back to the correct behavior bij executing the following SQL query exec sp_configure 'user options', '0' reconfigure As you can see, when the SQL server user option XACT_ABORT is set, prepared UPDATE statements through the SQL JDBC driver will fail consistently when selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an UpdateCount is returned, but the UpdateCount itself claiming a resultset has been returned. On checking that, there is no resultset. Somehow the option, which should do the following: xact abort Rolls back a transaction if a Transact-SQL statement raises a run-time error. seems to roll back the results of an update statement in place ? Is this a bug or something I seem to be doing wrong ? Kind regards, Niels PS: With the 1.0 JDBC driver with this setting another problem occured, which pointed to a rollback done within a transaction without any exception being raised through JDBC, consistent with Angel Saenz-Badillos post on http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html We had hoped the 1.1 driver would at least raise the exception through JDBC, but alas, the result mentioned above happened, which in the end we could track back to the server setting. --Update-- When an SQL Trace is active on the server the following two error messages appear in the log, none of which are visible through the JDBC driver error handler: Exception Error: 156, Severity: 15, State: 1 Exception Error: 16954, Severity: 10, State: 1
View Replies !
UPDATE Statement Differences Between 2000 And 2005
I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005. Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it. IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL DROP TABLE #UpdateTestA IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL DROP TABLE #UpdateTestB CREATE TABLE #UpdateTestA( UpdateTestA int identity(1, 1), FullName varchar(20), UpdateData varchar(10)) CREATE TABLE #UpdateTestB( UpdateTestB int identity(1, 1), FullName varchar(20), UpdateData varchar(10)) INSERT INTO #UpdateTestA( FullName) VALUES ('Barney Rubble') INSERT INTO #UpdateTestB( FullName, UpdateData) VALUES ('Barney Rubble', 'First') INSERT INTO #UpdateTestB( FullName, UpdateData) VALUES ('Barney Rubble', 'Second') SELECT * FROM #UpdateTestA UPDATE a SET a.UpdateData = b.UpdateData FROM #UpdateTestA a INNER JOIN #UpdateTestB b on b.FullName = a.FullName SELECT * FROM #UpdateTestA DROP TABLE #UpdateTestA DROP TABLE #UpdateTestB Hope this solves a problem that you were having too.
View Replies !
Filtering SQL Statement By Server Variable
Hi Hope somebody can help me with a problem that i am having. I have the following piece of code <% Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim sqlReader As SqlDataReader sqlConn = New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password") sqlCmd = New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") + ")", sqlConn) sqlConn.Open() sqlReader = sqlCmd.ExecuteReader() %> <% Dim strPCNAme As String sqlReader.Read() strPCNAme = sqlReader("UserLoggedIn") Response.Write(strPCNAme) %> The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in. Can anybody help please? Thanks Marcus
View Replies !
Sql Server 2005 Dev Edition Error - Unexpected Error While Updating Installed Files
I am attempting to install SQL Server 2005 Developer Edition onto a Windows XP Pro SP2 machine, but unfortunately each time that I attempt to install I receive an error message in the summary log as follows: Product : Microsoft SQL Server 2005 Product Version: 9.00.1399.06 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup Last Action : InstallFinalize Error String : The setup has encountered an unexpected error while Updating Installed Files. The error is : Fatal error during installation. Error Number : 29528 There is probably a simple solution for the issue but unfortunately I am unaware of what it is? I can€™t tell if the issue is specific to registry settings, security, file types, etc€¦ The information below surrounds the error w/in the log, any suggestions would be greatly appreciated? Thanks, Jennifer MSI (s) (6C:94) [16:02:23:713]: Executing op: SetTargetFolder(Folder=C:WINNTsystem32) MSI (s) (6C:94) [16:02:23:713]: Executing op: SetSourceFolder(Folder=1System) MSI (s) (6C:94) [16:02:23:713]: Executing op: FileCopy(SourceName=sqlctr90.dll,SourceCabKey=sqlctr90.dll.7188DA12_A95E_46B7_8623_9D93B5260E2A,DestName=sqlctr90.dll,Attributes=16384,FileSize=66264,PerTick=32768,,VerifyMedia=1,,,,,CheckCRC=0,Version=2005.90.1399.0,Language=1033,InstallMode=58982400,,,,,,,) MSI (s) (6C:94) [16:02:23:713]: File: C:WINNTsystem32sqlctr90.dll; To be installed; Won't patch; No existing file MSI (s) (6C:94) [16:02:23:713]: Source for file 'sqlctr90.dll.7188DA12_A95E_46B7_8623_9D93B5260E2A' is compressed MSI (s) (6C:94) [16:02:23:713]: Note: 1: 2318 2: C:WINNTsystem32sqlctr90.dll MSI (s) (6C:94) [16:02:23:713]: Note: 1: 2360 MSI (s) (6C:94) [16:02:23:713]: Note: 1: 2360 MSI (s) (6C:94) [16:02:23:723]: Executing op: CacheSizeFlush(,) MSI (s) (6C:94) [16:02:23:723]: Executing op: InstallProtectedFiles(AllowUI=0) MSI (s) (6C:94) [16:02:23:723]: Executing op: ActionStart(Name=CAFTEInstallFTERef.68C6D15C_77E0_11D5_8528_00C04F68155C,,) MSI (s) (6C:94) [16:02:23:733]: Executing op: CustomActionSchedule(Action=CAFTEInstallFTERef.68C6D15C_77E0_11D5_8528_00C04F68155C,ActionType=1025,Source=BinaryData,Target=InstallFTERef,CustomActionData=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnFTERef|C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData) MSI (s) (6C:2C) [16:02:23:743]: Invoking remote custom action. DLL: C:WINNTInstallerMSI64E.tmp, Entrypoint: InstallFTERef FTECa.DLL: INFO: FTE: InstallFTERef(), Entering... FTECa.DLL: INFO: FTE: GetFTERefInstallParams: FTERef : C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDataoiseCHS.txt Size=1000 IDR=1696 . . . . . . FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData sSVE.xml Size=1035 IDR=1142 FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDataoiseTHA.txt Size=1036 IDR=697 FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData sTHA.xml Size=1037 IDR=1142 FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDataoiseTRK.txt Size=1038 IDR=2224 MSI (s) (6C:94) [16:02:24:454]: Executing op: ActionStart(Name=Rollback_UpdateETWMOFWithGUID.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Removing registry updates,) FTECa.DLL: INFO: FTE: InstallFTERef: File created: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData sTRK.xml Size=1039 IDR=1142 MSI (s) (6C:94) [16:02:24:454]: Executing op: CustomActionSchedule(Action=Rollback_UpdateETWMOFWithGUID.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1345,Source=BinaryData,Target=Rollback_UpdateETWMOFWithGUID,CustomActionData=100Removing registry updates100000{2373A92B-1C1C-4E71-B494-5CA97F96AA19}MSSQLSERVERC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn) MSI (s) (6C:94) [16:02:24:454]: Executing op: ActionStart(Name=Do_UpdateETWMOFWithGUID.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Updating Installed Files,) MSI (s) (6C:94) [16:02:24:454]: Executing op: CustomActionSchedule(Action=Do_UpdateETWMOFWithGUID.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1025,Source=BinaryData,Target=Do_UpdateETWMOFWithGUID,CustomActionData=010Updating Installed Files100000{2373A92B-1C1C-4E71-B494-5CA97F96AA19}MSSQLSERVERC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn) MSI (s) (6C:10) [16:02:24:464]: Invoking remote custom action. DLL: C:WINNTInstallerMSI64F.tmp, Entrypoint: Do_UpdateETWMOFWithGUID <Func Name='LaunchFunction'> Function=Do_UpdateETWMOFWithGUID <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='203'> Doing Action: Do_UpdateETWMOFWithGUID PerfTime Start: Do_UpdateETWMOFWithGUID : Tue Feb 13 16:02:24 2007 <Func Name='Do_UpdateETWMOFWithGUID'> Calling installSqlEvent Trance The arguments that are passed to InstallSqlEventTrace are InstanceName: MSSQLSERVER , ProductCodeL: {2373A92B-1C1C-4E71-B494-5CA97F96AA19} , BinPath: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn INFO: Checking installed version INFO: Writing MOF file ERROR: Unable to open registry key: HRESULT = 0x80070005, Key = SYSTEMCurrentControlSetControlWMISecurity The value returned is -2147024891 <EndFunc Name='Do_UpdateETWMOFWithGUID' Return='1603' GetLastError='0'> PerfTime Stop: Do_UpdateETWMOFWithGUID : Tue Feb 13 16:02:24 2007 Gathering darwin properties for failure handling. Error Code: 1603 MSI (s) (6C!D0) [16:02:37:273]: Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Updating Installed Files. The error is: Fatal error during installation. Error 29528. The setup has encountered an unexpected error while Updating Installed Files. The error is: Fatal error during installation. <EndFunc Name='LaunchFunction' Return='1603' GetLastError='203'> MSI (s) (6C:94) [16:02:37:283]: User policy value 'DisableRollback' is 0 MSI (s) (6C:94) [16:02:37:283]: Machine policy value 'DisableRollback' is 0 Action ended 16:02:37: InstallFinalize. Return value 3.
View Replies !
Unexpected Exceptions In SQL/COM+ Since Converting To VS 2005
We are running a COM+ DLL that handles transactions and security with a SQL Server back-end. This project was started in VS 2003, but we just converted it to VS 2005. Since the conversion, creating a number of transactions in a row generates errors at consistent, but moving, locations in the code (i.e. the location where the failure occurs changes when I do things such as add additional try/catch statements, so it is not random but also clearly not associated with any particular line of code).
View Replies !
SQL Server 2005 Install Error (Error 29528. Unexpected Error While Installing Performance Counters. )
I'm currently receiving the following error message whilst attempting to install SQL Server 2005 Standard Edition on Windows Server 2003 (32 Bit): Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified. This server already has an install of SQL Server 2000 as the default instance. I'm attempting to install a new named instance of SQL Server 2005. Extract from log: <Func Name='LaunchFunction'> Function=Do_sqlPerfmon2 <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='0'> Doing Action: Do_sqlPerfmon2 PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 <Func Name='Do_sqlPerfmon2'> <EndFunc Name='Do_sqlPerfmon2' Return='0' GetLastError='2'> PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 MSI (s) (4C:FC) [10:20:02:833]: Executing op: ActionStart(Name=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Removing performance counters,) <EndFunc Name='LaunchFunction' Return='0' GetLastError='0'> MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1281,Source=BinaryData,Target=Rollback_Do_sqlPerfmon2,CustomActionData=100Removing performance counters200000DTSPipelineC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INI) MSI (s) (4C:FC) [10:20:02:849]: Executing op: ActionStart(Name=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Installing performance counters,) MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1025,Source=BinaryData,Target=Do_sqlPerfmon2,CustomActionData=100Installing performance counters200000C:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INIC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.HC:Program FilesMicrosoft SQL Server90DTSBinnDTSPipelinePerf.dllDTSPipeline0DTSPipelinePrfData_OpenPrfData_CollectPrfData_Close) MSI (s) (4C:94) [10:20:02:864]: Invoking remote custom action. DLL: C:WINDOWSInstallerMSI1683.tmp, Entrypoint: Do_sqlPerfmon2 <Func Name='LaunchFunction'> Function=Do_sqlPerfmon2 <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='0'> Doing Action: Do_sqlPerfmon2 PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 <Func Name='Do_sqlPerfmon2'> <EndFunc Name='Do_sqlPerfmon2' Return='2' GetLastError='2'> PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007 Gathering darwin properties for failure handling. Error Code: 2 MSI (s) (4C!F0) [10:23:46:381]: Product: Microsoft SQL Server 2005 Integration Services -- Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified. You can ignore this and it will complete the installation, but subsequently trying to patch with SP2 will fail on the same sections - Hotfix.exe crashes whilst attempting to patch Database Services, Integration Services and Client Components (3 separate crashes). I've removed SQL Server 2005 elements and tried to re-install, but it's not improved the situation. Any ideas?
View Replies !
[SQL 2005] Msg 4832 Level 16 : Unexpected EOF Character
I have the above message while trying to use bulk insert to load a flat file into SQL 2005. After some investigation, I realized there is an additional EOF character at the end of file. May I know how can I resolve this problem without manually "cleaning" the file by removing the EOF character? Here is my Bulk Insert syntax: BULK INSERT PHARMSRAW FROM ABC.TXT WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = ' ' ) It has a funny row terminator. With the additional EOF character at the end of file, it really made the whole thing went crazy. Any idea how to get it resolved? Thanks in advance!
View Replies !
XML Source Produces DT_UI8 Key Columns - Which SQL Server Data Type To Store?
I'm using the XML Source to process a hierarchical set of XML. As such, the XML Source creates keys to maintain the hierarchy. This is very convenient, and keeps me from having to invent my own keys. The problem is that the datatype of these keys defaults to DT_UI8. Which SQL Server 2005 datatype should I use to store these values in my staging tables? BIGINT corresponds to DT_I8, which can't accept DT_UI8 values.
View Replies !
Select And UPDATE Statement Help! (Using SQL Server)
I have a table which I need to obtain data from but am having a problem with select statement. Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's"). I need to display only the long names (NOT THE SHORT NAME records with the similar hostName). Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName Which returns data Name Length ATVDDR 6 ATVDDR1 7 This is a s far as I can get but, Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble. The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement Name IP Addr Location ATVDDR1 1.1.1.1 2ndFloor I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN Please advise any assistance.
View Replies !
Advance Update Statement Sql Server
I am trying to speed up my update statements by removing inner selectstatements.Example:update orders set shipname = (select contactName fromcustomers where customerid = orders.customerID)I read some articles which said that I should be able to use an innerjoin on the update statement like the following:update orders set shipname = (select contactName from customers wherecustomerid = orders.customerID)But every time that I run this statement I get the follwing error:Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'inner'.Any Help will be greatly appreciated.Thank you.
View Replies !
Linked Server Update Statement
I've got the output from a cursor that generates the following sql statement. Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255 The statement generates the following error if the statement is run remotely Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'update'. But the statement executes without error as formatted if run directly on the linked server. Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.
View Replies !
Unexpected Error While Removing Backup Files In Sql 2005 Setup
I was trying an edition upgrade from SQL 2005 standard x64 to SQL 2005 enterprise x64 using the "setup SKUUPGRADE=1" from the command line. The SO is W2003 server R2 enterprise and I have a SQL clustered environment with 2 nodes (active/passive). I select to upgrade the SQL module and I mark the checkbox for the cluster installation. The installation seems to work properly untill setup is doing the task "removing backup files". Then 2 "unexpected error" message appears as follows. The 2 above windows appear when you click on the help icon. Link to screen capture The following is the end of the SQL setup log file: ________________________________________________________________________ Property(S): OutOfDiskSpace = 0 Property(S): OutOfNoRbDiskSpace = 0 Property(S): PrimaryVolumeSpaceAvailable = 0 Property(S): PrimaryVolumeSpaceRequired = 0 Property(S): PrimaryVolumeSpaceRemaining = 0 Property(S): RSVirtualDirectoryManager = Reports Property(S): RSVirtualDirectoryServer = ReportServer Property(S): SqlActionManaged = 3 Property(S): SqlStateManaged = 2 Property(S): Sql_sqlSqlUpgradeSequence = 387709900 Property(S): SOURCEDIR = I:Setup Property(S): SourcedirProduct = {79F1B65E-8FC0-4D03-954D-F9E71C85AEC7} Property(S): HotfixScript = 1 Property(S): DebugClsid.B2156BC9_6CE8_422F_A957_E99A65256B1D = {A42D1020-AA4C-401D-B7BD-26C333C0D627} Property(S): SQLBROWSERACCOUNT = HEREISTHEDOMAINheretheclusteraccount Property(S): ProductToBeRegistered = 1 MSI (s) (84:F8) [00:32:10:598]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Hotfix 3024 for SQL Server Database Services 2005 (64-bit) ENU (KB000000)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0024_SV00010-2_SQL.log. MSI (s) (84:F8) [00:32:10:613]: Note: 1: 1708 MSI (s) (84:F8) [00:32:10:613]: Product: Microsoft SQL Server 2005 (64-bit) -- Installation failed. MSI (s) (84:F8) [00:32:10:613]: Attempting to delete file C:WINDOWSInstaller75d5c.msp MSI (s) (84:F8) [00:32:10:613]: Unable to delete the file. LastError = 32 MSI (s) (84:F8) [00:32:10:645]: Cleaning up uninstalled install packages, if any exist MSI (s) (84:F8) [00:32:10:645]: MainEngineThread is returning 1603 MSI (s) (84:CC) [00:32:10:754]: Destroying RemoteAPI object. MSI (s) (84:74) [00:32:10:754]: Custom Action Manager thread ending. === Logging stopped: 18/12/2007 0:32:10 === MSI (c) (34:B4) [00:32:10:832]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (34:B4) [00:32:10:832]: MainEngineThread is returning 1603 === Verbose logging stopped: 18/12/2007 0:32:10 === _______________________________________________________________________ Does anyone have any clue or idea about the possible problem?
View Replies !
Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement DECLARE @VALUE_KEEP NVARCHAR(120), @COLUMN_NAME NVARCHAR(120) SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME') SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3) PRINT @VALUE_KEEP PRINT @COLUMN_NAME RESULTS ------------------------------------------------------------------------------------------- FirstName <-----------@VALUE_KEEP FirstName <-----------@COLUMN_NAME SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName SELECT FirstName from Contacts returns: Brent How do I make this select statement work using the @COLUMN_NAME variable? Any help greatly appreciated!
View Replies !
Forwarding Variable Number Of Parameters From VB.2005 To Sql Server 2005 Stored Procedure
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
View Replies !
Best Way To Send DB Results To Variable
Hello all I have a database query which will return a single value SELECT COUNT(DISTINCT(Level_id)) FROM RoleSkill WHERE Route_ID = 1 obviously this will return only a single value and not an array of data like most database queries, therefore i would like it to be sent directly to an integer variable. I tried doing this directly with a sqlcommand but get error that it cannot be converted to integer. Is there a good way of solving this problem? thanks for your help Tom
View Replies !
How To Store The Results Of A Query In A Variable.
Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.
View Replies !
|
TRACKER
