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 DB
Declare @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_OutBound
Once 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?
regards
Tom
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
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 Replies !
Variable Length CSV To Table
Hi, I want to export one CSV file to a SQL table. My problem is my Csv File is variable length file like : 000;1;amruth;20000 000;1;amruth;20000;praveen;shell;floor11 000;1;amruth;20000;praveen;shell;floor11;aaa;aa;aa;aa;aa;aa;aa Sql Table is like col1- - - - - - - Col 15 I want to make a package to import that variable length Csv to This Sql table like 000 amruth 20000 Null Null Null Null Null Null Null Null Null Null 000 amruth 20000 Praveen shell floor11 Null Null Null Null Null Null Null 000 amruth 20000 Praveen shell floor11 aaa aa aa aa aa aa aa I hope you got the problem. I tried with BulkInsert using ; as column Delimiter and as Row Delimiter and Flat File connection with Same Delimiters but its not working. its importing like : 000 1 amruth 20000 000 1 amruth 20000;praveen;shell;floor11 000 1 amruth 20000;praveen;shell;floor11;aaa;aa;aa;aa;aa;aa;aa its inserting like this in 4th column. If any body can help me on this please reply Asap. Thanks in advance
View Replies !
Variable-Length Arguments
Hi, we can use 'sp_executesql' to execute any statemens. I have made a search and people, seems, need the dynamic sql only to process some table/cloumn unknown in advance. My idea is that the dynamic SQL feature is ideal for passing blocks of code (aka delegates). Particularily, you may require to execute different procedures under some acquired locks. A procedure would acquire the locks, execute the code and release the locks. The problem is, however, that I cannot find the specification for the variable length parameters. It seems not feasible for SPs. Nevertheless, the 'sp_executesql itself does accept the variable number of parameters. How? Can we look at the defenition?
View Replies !
QUERY Exceeding Variable Max Length
Hi,A query is exceeding the length of varchar and nvarchar variable.Because I'm picking the data from each record from table and giving itto the query.suggest me some way to do it.sample query:SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotalFROM (SELECT Year,SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) ASQ1,SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) ASQ2,SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) ASQ3,SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4FROM Pivot1 AS PGROUP BY P.Year) AS P1GO---> even the P.QUARTER .... FIELD NAME IS BEING GENERATEDDYNAMICALLY.MY QUERY IS EXCEEDING VARCHAR AND NVARCHAR LIMIT.THANX IN ADV.
View Replies !
Finding Out Variable Type And Length
I'm new to SQL Server. I installed a copy of 6.5 on my server and set it up today. I received a db from a colleague and have been unable to find out what the variable types and lengths are. This may be very easy but I need to know. Thanks, David A. Fordinal fordinal@uta.edu
View Replies !
Variable Length Procedure Arguments
Is there any way to have variable length arguments for stored procedures - kinda like how there is in C/C++? For example, if I wanted to send 2 arguments at one time, and then at another time send 7 to the same procedure, is that possible?
View Replies !
Ambiguous Length Varbinary Variable
Hi, I'm trying simple code: declare @varbinary varbinary(8000) set @varbinary = 2592 print len( @varbinary ) number length 2590 4 bytes 2591 4 bytes 2592 3 bytes ???? 2593 4 bytes All number in range 1 - 9000 has length 4 bytes except these numbers: 32,288,544,800,1056,1312,1568,1824,2080,2336,2592,2848,3104,3360,3616,3872,4128,4384,4640,4896,5152,5408,5664,5920,6176,6432,6688,6944,7200,7456,7712,7968,8480,8736,8992 These numbers has length 3 bytes ! Why? I´m using varbinary variable to transport ID values and parsing in loop set @cnt = len( @binEmpIDList ) / 4 -- 4 length .... insert into @List( ID ) values( substring( @varbinary, @i * 4 + 1, 4 ) ) ... but this algorithm malfunctioning in enumerated numbers. Please help, how correct this. ID values in @varbinary too much. I cannot using convert varbinary to bigint and convert bigint to string. Dalibor
View Replies !
How Do I Calculate The Average Variable Length For A Varchar?
im trying to learn how to calculate table size. i understand some of it, but im stuck at calculating the varchars Ex. i have 2 varchar columns - varchar(50) - varchar(100) i'm suppose to find the average length for them? i'm suppose to use that to add up to my ROW SIZE and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array please help me asap before 2morrow night. Thanks! i have a test
View Replies !
Parsing Variable Length Delimited Records
I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript. The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions? How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there. Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it. Thanks for yuor help, --greg
View Replies !
Import Variable Fixed Length File
I have a requirement to import a file of rows containing fixed length data. The problem is that each row can be one of 5 different formats (i.e. different columns) -- where the "type" of row is indicated by the first two characters of the row. Each row gets inserted into its own table. Could I use a simple Conditional Split to route the rows? Or is the split for routing similiar rows? Anyways, problems are never this simple... In addition, each "grouping" of rows is related. The "first" row is considered the "primary" row (and gets a row id via IDENTITY, whereas the remaining rows in the group are "secondary" rows and have foreign key references back the the primary rows id. Given (using spaces to separate columns and CrLf to show "grouping"): 01 MSFT blah blah 02 blahblah blahblahblah 03 boring boringblah 01 AAPL blah blah 02 blahblah blahblahblah 03 boring boringblah 01 CSCO blah blah 02 blahblah blahblahblah 02 blahblah blahblahblah 03 boring boringblah So, the first 3 lines are all related to a MSFT record which needs to be spread across multiple tables. The next three lines are all related to AAPL, And the next FOUR lines (yes, each record can have zero, one, or more secondary rows) are related to CSCO. (If this is still not clear, all the "01" rows will be written to [Table1] with each row having an IDENTITY value. All the "02" rows will be written to [Table2] the a FK pointing to the correct [Table1] row. All the "03" rows will be written to... and so on. Any ideas would be appreciated.
View Replies !
DataType Storing Strings Of Variable Length ?
Hello guys! I am looking for the best DataType that makes it possible to store strings of variable length (from 15 to 300 caracters, rarely longer). Thanks a lot for any help ! Regards, Fabianus my favorit hoster is ASPnix : www.aspnix.com !
View Replies !
Urgent - Notes Import ( Variable Length Column )
I have two columns in source text file ( bodylength, body ). body is a text type column with notes data. What would be a script/DTS for import that source using bodylength to determine the size/how much to grab from body column into destination table body column. Any help is greatly appreciated.
View Replies !
Variable Length Column Import ( Good Exercise ! )
I have two columns in source text file ( bodylength, body ). body is a text type column with notes data. What would be a script for import that source using bodylength to determine the size/how much to grab from body column into destination table body column. Any help is greatly appreciated.
View Replies !
Using A Declare Variable To Represent A Data Type Length
I'm trying to use a variable set by DECLARE to represent a data type length with CAST. Don't ask why. Here is what I'm got: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(@n)) from tblOne This should make Col_2 the length of the longest entry in Col_1. It does not work. I must use a set number for the length after the data type like this: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(28)) -- (28 is longest current entry in Col_1) from tblOne Is there a way around this? I'm new to SQL.
View Replies !
BlobColumn.GetBlobData() With Data Length &&> Integer Length (32767)
For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component. The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "¶mname=value&parm2=value..." format. The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code: Dim paramBlob as Byte() paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length) The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters. THIS IS A PROBLEM!!! Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in! Thanks for your help in advance, LOSTlover
View Replies !
SQL Server 2005: Changing Latin1_General_BIN Collation To Latin1_General_CI_AS Collation
Hello, I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error: The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it? Thanks, Bruce
View Replies !
CommandParameters.length Don't Match Parametervalues.length
I am trying to narrow down this problem. Basically, I added 3 columns to my article table. It holds the article id, article text, author and so on. I tested my program before adding the additional field to the program. The program works fine and I can add an article, and edit the same article even though it skips over the 3 new fields in the database. It just puts nulls into those columns.So, now I have added one of the column names I added in the database to the code. I changed my businesslogic article.vb code and the addarticle.aspx, as well as the New article area in the addartivle.aspx.vb page. The form now has an additional textbox field for the ShortDesc which is a short description of the article. This is the problem now: The command parameters.length is 9 and there are 10 parameter values. Right in the middle of the 10 values is the #4 value which I inserted into the code. It says Nothing when I hover my mouse over the code after my program throws the exception in 17 below. Why is command parameters.length set to 9 instead of 10? Why isn't it reading the information for value 4 like all the other values and placing it's value there and calculating 10 instead of 9? Where are these set in the program? Sounds to me like they are hard coded in someplace and I need to change them to match everything else. 1 ' This method assigns an array of values to an array of SqlParameters.2 ' Parameters:3 ' -commandParameters - array of SqlParameters to be assigned values4 ' -array of objects holding the values to be assigned5 Private Overloads Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)6 7 Dim i As Integer8 Dim j As Integer9 10 If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then11 ' Do nothing if we get no data12 Return13 End If14 15 ' We must have the same number of values as we pave parameters to put them in16 If commandParameters.Length <> parameterValues.Length Then17 Throw New ArgumentException("Parameter count does not match Parameter Value count.") 18 End If19 20 ' Value array21 j = commandParameters.Length - 122 For i = 0 To j23 ' If the current array value derives from IDbDataParameter, then assign its Value property24 If TypeOf parameterValues(i) Is IDbDataParameter Then25 Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)26 If (paramInstance.Value Is Nothing) Then27 commandParameters(i).Value = DBNull.Value28 Else29 commandParameters(i).Value = paramInstance.Value30 End If31 ElseIf (parameterValues(i) Is Nothing) Then32 commandParameters(i).Value = DBNull.Value33 Else34 commandParameters(i).Value = parameterValues(i)35 End If36 Next37 End Sub ' AssignParameterValues38 39 40 41
View Replies !
Getting SqlDbType Not Declared
How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString) Dim cmd As New Data.SqlClient.SqlCommandWith cmd .Connection = conn .CommandType = Data.CommandType.StoredProcedure .CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int) conn.Open() For Each item As ListItem In CheckBoxList1.Items If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue cmd.ExecuteNonQuery() End If Next conn.Close() End WithEnd Sub End Class
View Replies !
SqlStatementSourceType Is Not Declared
I have simple SSIS package with only ScriptTask. Script was copied from MSDN: Code Snippet Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask ... Public Sub Main() Dim pkg As Package = New Package() Dim exec1 As Executable = pkg.Executables.Add("STOCK:SQLTask") Dim th As TaskHost th = CType(exec1, TaskHost) Dim myVar As Variable = pkg.Variables.Add("myVar", False, "User", 100) th.Properties("SqlStatementSourceType").SetValue(th, SqlStatementSourceType.Variable) ... Last line shows error - 'SqlStatementSourceType' is not declared. Well, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask is imported so SqlStatementSourceType should be visible. I don't see microsoft.sqlserver.sqltask.dll in task's references and can't add it because Add Reference doesn't show this DLL. Could you please forward me in right direction? Thank you, Alexander
View Replies !
Obtaining The Last Record
Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks! Kamen
View Replies !
Type 'adCmdStoredProc' Is Not Declared
I am trying to set up a "cmd.CommandType = adCmdStoredProc" but Ireceive the error "Type 'adCmdStoredProc' is not declared". What do Ineed to do to declare it? I am using MSDE with SQLDataAdapter. I amtrying to exceute a stored procedure from within my VB .NET 2003 code.Thanks.JH
View Replies !
Obtaining Timezone Offset In T-SQL.
In MS SQL 2000, I would like to obtain the timezone offset from UTC fora given date. For today's date, I can doDATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a futuredate may not be the same as today because some countries go in DaylightSaving mode. Can you suggest a way to obtain the timezone offset forany given date?ThanksYash
View Replies !
Obtaining Count For Identity
Is there a way to query a SQL server such that it will return thecurrent counter value of a field that increments automatically (anidentity field)? Using a MAX query might not necessarily give you thecorrect information because it is possible that the high value recordswere deleted. I checked the system tables and none of them appear tohave what I need. I suppose it would be possible to maintain a tablethat lists all the incrementing columns, but ideally there would be away already built into SQL Server.Thanks.
View Replies !
Obtaining Disk Space
Hi, I'm new to these forums (and to SQL Server), so please be gentle with me. I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database. I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics. I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary. I am having difficulty obtaining the free drive space details. I'm using :- 'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb) I wish to avoid creating any objects on the remote servers if at all possible. I really want to pass the remote server name into the procedure, and the output to be inserted into the table. Many thanks
View Replies !
Obtaining PATH To Master DB
I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key: HKLMSoftwareMicrosoftMSSQLServerSetup It seems that a SQL2005 installation does not have the same default directory. Is there a way to query this from the server itself so I don't have to read the registry?
View Replies !
Obtaining Schema For XML Export
I am trying to obtain the schema for the XML Export of a report generated from Report Server. I am using ReportViewer in an ASP.net web app to display the report. The problem with the XML is that it does not render fields that are null in the database, whether I have the output set to element or attribute. I need to provide a schema to other developers so as they can consume the xml output. I can't just give them a copy of the xml output, because alot of the fields are not displaying because of null values. Is it possible to obtain the schema? Thanks!
View Replies !
Obtaining Audit Information
Hi People We want one trigger which captures the data as mentioned below. Table Name Date and Time User type Mode of modification TABLE_ABC 03/07/2007 12:00:04 XCSRaoa Update Procedure TABLE_DEF 03/07/2007 12:00:34 XCSRaoa Insert Class Integration SSIS Package TABLE_GHI 03/07/2007 12:01:04 XCSRaoa Insert Procedure TABLE_GHI 03/07/2007 12:01:34 XCSRaoa Update XCSRaoa (Manual) I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually. Could someone help me achieving this? Regards Abhi
View Replies !
Obtaining Login ID From Page
Hello, By using DBCC Page I am able to exemine data entered to row and other changes related to the rows. What I want to know is; is it possible to find out (and how) which Login ID commited these change directly from pages or where do I need to look to find it out? Any guidance is appreciated....
View Replies !
Obtaining Management Studio?
I'll be working on a web project that utilizes sql server 2005. Our web host will be providing the server however I'll need a version of Sql Server 2005 management studio to work with it. I believe the studio software costs around $50 however the express edition software is free. So my question is, is the express edition software compatible with the full version of sql server 2005 or only sql server 2005 express edition? Thanks. -Goalie35
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 !
Script Component Name Ouput0Buffer Is Not Declared???
Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error. Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Output0Buffer.AddRow() End Sub There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.
View Replies !
Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor. The doctors table has the following columns with specialism allowing NULL values doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) ) Any help would be greatly appreciated.
View Replies !
Obtaining Data From Different Tables In The Same Database
Hi I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides. Thanks
View Replies !
Timeout When Obtaining A Connection From The Pool
Hi All,I'm having a bit of a problem with a c# program which creates and index file/table. The number of records I need to process is around 3500, the main loop. Each of thoes records I need to look at a 2 text fields and create an entry in an index table for each valid word found in thoes fields. I call a stored proc to save the key word. So this stored proc get called numerous time per record.What seems to be happening is proceese about 400 main rcords the errors out with the following error message: {"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." }Has anyone had a similar problem? if so any suggestions on how to fix, I've been looking at this problem and recoding for the last 4 days! I need some help! Using SQL Server 2000 & .net v1.1Thanks in advance Davej
View Replies !
Need Help Obtaining Reporting Services For SQL Developer
I'm hoping someone can shed some light on a reliable way to get Reporting Services if you don't have an MSDN subscription. I have registered copies of SQL Server 2000 Developer and SQL Server 2000 Standard that I bought late last year. I've had a backorder with MS Parts since January to get updated CDs with Reporting Services on them. Each month, the back order is pushed up another month. The disks are now due sometime in late May. I called the fulfillment center and the operator end didn't seem too enthusiastic about it coming in stock anytime soon. All 3 versions (Developer, Standard, and Enterprise) are on a long back-order. I thought I could get by temporarily by using the 90-day trial copy of Reporting Services, but I'm now concerned it might run out before I get my own non-trial version. Isn't there a way for legit users to get a hold of this product? I'd be happy to download it, but it's not available unless I want to pay add'l money for an MSDN subscription. _ANY_ suggestions or help appreciated. Thanks. dotnetFool
View Replies !
|
TRACKER
