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.
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 ??
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
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.
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
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
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
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'
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) )
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.
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
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
Hi,I have a problem:I am writing an update script for a database and want to check for theversion and Goto the wright update script.So I read the version from a table and if it match I want to "GotoVersionxxx"Where Versionxxx: is set in the script with the right update script.Whenever I have some script which need Go commands I get error in theoutput thatA GOTO statement references the label 'Versionxxx' but the label hasnot been declared.But the label is set in the script by 'Versionxxx:'Is there a way I can solve this easily?Thanks in advance
I have declared a variable XYZ in Parent package Similarly I have declared ABC in Child package and have done the configuration. I have assigned some value to XYZ How to get the value in Child Package.
I use BIDS 2008 R2 and I have a SQL script that works fine and gives me the desired output in SQL Management studio.
declare @dt datetime select @dt = '2015-09-10 08:23:28.000' select ref_id ,desn ,tran_date ,payment_due ,ref1 ,gross_val from acptran (nolock) where seq_id in (select seq_id from acptcash (nolock) where date_time = @dt) order by ref_id,ref1
However i need to create a report so that someone else can enter the date time as a parameter in a report to get the required results. Normally i would drop my SQL script into BIDS and it would create the dataset but as this has a declared value it gives an error "The Declare SQL construct or statement is not supported."
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?
I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily. For example, I ran the following...
declare @v1 varbinary(max)
set @v1 = 0x0100AB
select len(@v1)
set @v1 = @v1 | (len(@v1) - 2) * 256
select master.dbo.fn_varbintohexstr(@v1)
select len(@v1) ...and get 3 0x00000000000101ab 8
This messes up a plan I had for varbinary column use.
So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking....
declare @i int
set @i = 5
declare @v2 varbinary(@i)
...but got errors.
So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.
declare @i int,@output varchar(max) set @i = 1 while @i < 10 begin set @output = @output + convert(varchar(max),@i) + ',' print @output set @i = @i +1 end
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.
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.
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared:
Server Error in '/AverageTCE' Application.
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error:
Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server">
</div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
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
-- 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)
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.
Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
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.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet select @ReportingId = max(ReportingId) from [dbo].[GuidToReportingid] where Guid = @UniqueIdentifier and EffectiveEndDate is null
if @ReportingId is null BEGIN insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() END ELSE BEGIN select @rowcount = count(*) from [dbo].[someTable] where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet if @rowcount > 0 and @joinsMatch = 1 begin set @insertFlag = 0 end else begin update [dbo].[GuidToReportingId] set EffectiveEndDate = getdate() where ReportingId = @ReportingId insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() end
...and before the return it's value is inserted to different tables.
