Understanding Variable Update
May 16, 2008
Yesterday I was working to try to resolve a issue with not Using a cursor for a unique type of update.
TG was able to resolve my issue using this query
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY Clustered, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage(StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai )
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
Union all
select '2009-12-01 00:00:00.000',5,0.00,0.00,0.0900,0
Union all
select '2010-12-01 00:00:00.000',6,0.00,0.00,0.0200,0
declare @ai decimal(10,4)
,@aiTot decimal(10,4)
,@a money
,@ia money
update s set
@ai = ai = s.interest * (isNull(@aiTot,0) + @a + @ia)
,@a = isNull(@a, 0) + s.amount
,@ia = isNull(@ia, 0) + s.interestamount
,@aiTot = isNull(@aiTot, 0) + @ai
from @stage s
TG pointed out that although this query should always work, there is a underlying issue in this type of update method, due to it is dependent on the order in which sql updates (Currently the clustered index is the only way I know of to secure the order is in tact).
Although it is unlikely, TG pointed out that do to this inherent flaw in the methodology, if future releases of SQL were to be modified to improve performance in updates, this method COULD possibly not work, and there is no 100% guarentee that the index would be used.
My questions are
1. Is there any way to Ensure that the style of update I am using uses the clustered index (i.e. "from @stage s WITH (INDEX = ...)") but I do not know the ID of the index since it is a TMP table?
2. Force the order of the update to be the order of the index (Just because there is a index, that doesn't ensure that a future release of sql will reference the index from top to bottom (I know this is unlikely, but I still would like to know if there was a way to ensure the update did always occur from top to bottom of the index).
View 11 Replies
ADVERTISEMENT
Oct 25, 2007
I've been curious why some variables don't need to be declared with a value type in a stored procedure. Please see the example below in the bolded area. The stored procedure works fine - nothing is wrong with it, but I just wanted an explanation on why and when is a value type not needed for a variable.
CREATE PROCEDURE [DBO].[EmailRpt]
(
@TagDest char(2) = NULL,
@DateWanted smalldatetime = NULL,
@CustName varchar(64) = NULL,
@AcctID AcctId = NULL,
@ContactPhn phone = NULL,
@CustAddr address = NULL,
@City city,
@CrossSt varchar(50) ,
@Access varchar(50) = NULL,
@Activity tinyint,
)
AS
DECLARE @String as varchar(2000),
@Header as varchar(200),
@MailBox as varchar(50),
@ActivityName as Name,
@Return as int,
@UserName as Name
View 5 Replies
View Related
Dec 16, 2007
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 2 Replies
View Related
Jan 2, 2001
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 1 Replies
View Related
Oct 26, 2007
I wonder why this script IS NOT UPDATING the user-def variable varFileName (package-scoped) and I do not get any errors when running the package at all.
Dim strRow As String = Row.Column0.Trim()
Dim strConn As String = ""
Dim strFolder As String = "D:data"
Dim strFileName As String = ""
If Mid(strRow.ToLower(), 1, 2) = "d0" Then
' We have a file name.
strFileName = strRow
Row.Column0 = ""
strConn = strFolder + strFileName + ".csv"
Dim variables As IDTSVariables90 = Nothing
' Lock the var that stores the connection
' string for write
VariableDispenser.LockOneForWrite("User::varFileName", variables)
' Write to the var. This is where the connection should change.
variables(0).Value = strConn
' Determine whether the variable
' collection is locked before unlocking.
Dim isLocked As Boolean = variables.Locked
' Verify the value of variables.Locked.
' If the lock failed, call Reset.
If isLocked Then
variables.Unlock()
Else
VariableDispenser.Reset()
End If
Else
Dim charSep() As Char = {CChar(" ")}
Row.Column0 = String.Join(",", strRow.Split(charSep, System.StringSplitOptions.RemoveEmptyEntries))
End If
View 9 Replies
View Related
Apr 6, 2008
Hi all,
Hopefully an easy question to answer.
I'm looking to update a field in a table variable with the results from a SQL query. The SQL query howver needs to use the ID filed of the table variable which has already beeen populated.
Effectively, for each ID in the table variable run the query using that ID field and then pass the scalar result into another column.
I'm having a bit of trouble doing this. Can anyone help?
Thanks in advance,
G
View 5 Replies
View Related
May 7, 2008
Hello All
I m trying to update a table whose col name will be read from another table.
For e.g. Table1 gives the result:
'emp1', 1, 'John'
'emp2', 2, 'Mike'
Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2'
I need to write one Update Statement which will handle all the cases. I tried
Update Table2 set @VariableName = .......
but didnt work...
How can i do that ?
View 7 Replies
View Related
Sep 27, 2005
Sorry to bother you.
I am trying to create an Update Form. There is only one Field that can be edited on this form – agencydesc (Agency Description). I’m trying to perform the update to the Database the .Net way and I am definitely having some trouble. The form is loaded from the file AgencyEdit.aspx. An Image Button does a Server.Transfer to AgencyUPD.aspx where the update is completed. When AgencyEdit.aspx is loaded the Agency Description is pre-populated in TextBox1. When the value of TextBox1 is changed, the change does not show when the variable is passed to AgencyUPD.aspx, the value remains the initial populated value from the Database. Any help would greatly be appreciated.
Relevant CS Code from AgencyEdit.aspx
public string myBase;
public int eSernum;
public string eAgency;
public string eDesc;
protected System.Web.UI.WebControls.ImageButton ImageButton3;
protected System.Web.UI.WebControls.TextBox TextBox1;
public string myTest
{
get {return this.TextBox1.Text;}
}
private void Page_Load(object sender, System.EventArgs e)
{
string myUrl = Application.Contents["Base"] + Common/Login.aspx";
if (Session["isLogged"] == null) Response.Redirect(myUrl);
myBase = Application.Contents["Base"].ToString();
int mySernum = Int32.Parse(Request.QueryString["sernum"]);
SqlConnection con1 = new SqlConnection(Application.Contents["EwsConnectionString"].ToString());
SqlDataReader drsLog1;
SqlCommand cmd1 = new SqlCommand();
con1.Open();
cmd1.CommandText = "SELECT * FROM ewsagencies WHERE sernum = " + mySernum;
cmd1.Connection = con1;
drsLog1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
while (drsLog1.Read())
{
eSernum = drsLog1.GetInt32(0);
eAgency = drsLog1.GetString(1);
if (!drsLog1.IsDBNull(2)) eDesc = drsLog1.GetString(2);
}
con1.Close();
this.TextBox1.Text = eDesc;
}
private void ImageButton3_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
Server.Transfer("AgencyUPD.aspx");
}
Relevant CS Code from AgencyUPD.aspx
namespace VWeb.vAdmin.Agencies
{
public class AgencyUPD : System.Web.UI.Page
{
public AgencyEdit SourcePage;
private void Page_Load(object sender, System.EventArgs e)
{
AgencyEdit SourcePage = (AgencyEdit) Context.Handler;
int mySernum = SourcePage.eSernum;
string myDesc = SourcePage.myTest;
SqlConnection conUpdate = new SqlConnection(Application.Contents["EwsConnectionString"].ToString());
SqlCommand cmd1 = new SqlCommand();
conUpdate.Open();
cmd1.CommandText = "UPDATE ewsagencies SET agencydesc = @desc WHERE sernum = " + mySernum;
cmd1.Parameters.Add("@desc", SqlDbType.VarChar).Value = myDesc;
cmd1.Connection = conUpdate;
cmd1.ExecuteNonQuery();
conUpdate.Close();
string myUrl = Application.Contents["Base"] + "vAdmin/Agencies/AgencyList.aspx";
Response.Redirect(myUrl);
}
#region Web Form Designer generated code .. etc.
}
}
View 1 Replies
View Related
Dec 10, 2005
I'm trying something like:
UPDATE tbl SET @varFieldName = @varValue
The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.
I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.
To get the field name, I :
SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'
Thanks,
Carl
View 2 Replies
View Related
Oct 13, 2006
I have a Variable called - UpdateIDs.
I would like to create a conditional split on id's that have no responses and id's that have responses.
The idea is. There is a whole bunch of tables that can be updated if the foreign key is in the no responses id's.
So I have created a data flow. The conditional split is there, but I do not have a "Update Global Variable" Destination source.
Is there away to achieve this.
Basically this is the logic that I am trying to use
Dataflow --> Store Id's into Global Variable --> Data Flow update/insert Tables and Rows where Id in Gloabl Variable.
Is there another way this can be done?
Sorry I am very new to this, and would appreciate any help
Thanks
Andrew
View 2 Replies
View Related
Feb 6, 2008
Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.
I can get a table of which fields I need to update using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and I can do the update with UPDATE tblxxx SET column = LOWER(column)
But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?
Thanks,
Dave Stephens
View 3 Replies
View Related
Apr 20, 2015
We are using SSIS 2008 . A variable abc has been defined with sql script Select firstName,LastName from Employee.I need to edit the variable and add a where condition like SELECT firstName,lastName FROM employee where Name like '%Fr%' I tried to replace it with new script and saved also but when i ran the package it was still using the old sql script .
View 4 Replies
View Related
Feb 6, 2008
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
View 1 Replies
View Related
Feb 6, 2008
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
View 1 Replies
View Related
May 22, 2007
Dear friends,
Someone told me that I couldnt update the global variavel inside a dataflow... but i need to have some solution to do that... :-(
I crate my identity column manually based on a global variable... and it works perfect, but in one dataflow I have to insert 2 times in the some table... so... in the second time my identity will be GlobalVariavel + increment of the first time rows inserted...
how can I save this increment in the dataflow? How can I count the rows inserted in the first time do use it in the second time i save Data to the some table??
Thanks
View 9 Replies
View Related
May 30, 2007
Hi
I have an update query that joins with 2 table variables. this update updates about 50,000 rows in a table.
update table1
from @table2 t2
inner join @table3 t3
on t2.id = t3.id
where table1.id = t2.id
the problem is that sometimes this update takes forever. If I replace the table variable with regular tables, it only takes about 11 seconds.
I am not sure what the problem is. Could this be a problem with the tempdb. What should I be looking for in the tempdb that might cause a problem.
View 3 Replies
View Related
Jun 12, 2007
Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.
code snippet: <UpdateParameters><asp:Parameter Name="hrs_credited" />
<asp:Parameter Name="updater_id" DefaultValue="<%$ Session("User_ID")%>" Type="Int32"/>
<asp:Parameter Name="activity_id" />
<asp:Parameter Name="attendee_id" /></UpdateParameters>
The error message that I receive is:
Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:DevelopmentCMEdataentryattendance.aspx 29
Does anyone have an idea how to assign the session var value to the parameter?
Thanks!
View 1 Replies
View Related
Jul 23, 2005
Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end
View 6 Replies
View Related
May 25, 2007
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID". Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Jul 11, 2007
I am trying to update a field in a pre-existing record in my database. This update is supposed to happen when the protected sub entitled "PictureUpload" is called by button click. I listed the code immediately below and then I listed the error that I am getting further below that.
Does anybody know what I am doing wrong and why I am getting this error?
Thanks in advance and forgive my newbie ignorance!
Here is a portion of the Protected Sub that I am using in an attempt to update a field in a pre-existing record...
Protected Sub PictureUpload(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ImageUploaded As Integer = 1
srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded
srcprofiles_BasicProperties.Update()
End Sub
Here is the SqlDataSource control I included on the page with (what I hope is) appropriate formatting...
<asp:SqlDataSource ID="srcprofiles_BasicProperties" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|UserProfilesDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [profiles_BasicProperties] WHERE ([UserName] = @UserName)" UpdateCommand="UPDATE [profiles_BasicProperties] SET [ImageUploaded] = @ImageUploaded WHERE ([UserName] = @UserName)"> <SelectParameters> <asp:Parameter DefaultValue="imageuploaded01" Name="UserName" Type="String" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ImageUploaded" Type="Int32" /> </UpdateParameters></asp:SqlDataSource>
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^...and now the error...^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Server Error in '/PC_Dev' Application.
Must declare the scalar variable "@UserName".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserName".Source Error:
Line 164: Dim ImageUploaded As Integer = 1
Line 165: srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded
Line 166: srcprofiles_BasicProperties.Update()
Line 167:
Line 168: Source File: C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx Line: 166 Stack Trace:
[SqlException (0x80131904): Must declare the scalar variable "@UserName".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721
System.Web.UI.WebControls.SqlDataSource.Update() +17
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
View 3 Replies
View Related
Apr 3, 2014
I want to update one table with the value from variable using a set based approach.
so the line
>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
works fine but I want to take @list and use it to update my customers table. here is the full code.
create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history
[code]....
View 5 Replies
View Related
May 29, 2014
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
View 9 Replies
View Related
Jan 22, 2007
Hello,
I am having issues and can't see any errors in my code! When attempting to delete a table entry from my SQL database, I get the error "Must declare the scalar variable "@ID1"." even though I declare it as a parameter in my code! Can anyone see an issue with my code below?1 <asp:GridView ID="GridView1" runat="server" AllowSorting="True"
2 DataSourceID="SqlDataSource1" AutoGenerateColumns="False" OnSorted="GridView1_Sorted">
3 <Columns>
4 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
5 <asp:BoundField DataField="column1" HeaderText="CO" SortExpression="column1" />
6 <asp:BoundField DataField="column2" HeaderText="Network" SortExpression="column2" />
7 <asp:BoundField DataField="column3" HeaderText="C/S/T" SortExpression="column3" />
8 <asp:BoundField DataField="column4" HeaderText="Date Received" SortExpression="column4" DataFormatString="{0:d}" HtmlEncode="False" />
9 <asp:BoundField DataField="DESCRIPTION" HeaderText="Description" SortExpression="DESCRIPTION" />
10 <asp:BoundField DataField="column5" HeaderText="In Service Date" SortExpression="column5" DataFormatString="{0:d}" HtmlEncode="False" />
11 <asp:BoundField DataField="REMARKS" HeaderText="Remarks" SortExpression="REMARKS" />
12 <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
13 SortExpression="ID" />
14 </Columns>
15 </asp:GridView>
16 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConduitConnectionString %>"
17 SelectCommand="SELECT [ID], [CO/Area] AS column1, [NW #] AS column2, [C/S/T] AS column3, [DATE REC'D] AS column4, [DESCRIPTION], [I/S DATE] AS column5, [REMARKS] FROM [Inspector Workload] WHERE ([Inspector Name] = @Inspector_Name) ORDER BY [CO/Area], [DATE REC'D]" DeleteCommand="DELETE FROM [Inspector Workload] WHERE (ID = @ID1)" UpdateCommand="UPDATE [Inspector Workload] SET [CO/Area] = @column1, [NW #] = @column2, [C/S/T] = @column3, [DATE REC'D] = @column4, [DESCRIPTION] = @DESCRIPTION, [I/S DATE] = @column5, [REMARKS] = @REMARKS WHERE ([ID] = @ID1)">
18 <SelectParameters>
19 <asp:QueryStringParameter Name="Inspector_Name" QueryStringField="Name" Type="String" />
20 </SelectParameters>
21 <DeleteParameters>
22 <asp:Parameter Name="ID" Type="Int32" />
23 </DeleteParameters>
24 <UpdateParameters>
25 <asp:Parameter Name="column1" Type="String" />
26 <asp:Parameter Name="column2" Type="Double" />
27 <asp:Parameter Name="column3" Type="String" />
28 <asp:Parameter Name="column4" Type="DateTime" />
29 <asp:Parameter Name="DESCRIPTION" Type="String" />
30 <asp:Parameter Name="column5" Type="DateTime" />
31 <asp:Parameter Name="REMARKS" Type="String" />
32 <asp:Parameter Name="ID" Type="Int32" />
33 </UpdateParameters>
34 </asp:SqlDataSource>
Thanks!Rob.
View 8 Replies
View Related
Jul 30, 2007
Hello,
I have a SSIS package that has a SQL task which gets a result set and stores it in an SSIS object variable. The package then iterates through that result set in a foreach Loop Container. In the loop container I have a script task and a Send Mail Task. The script task sets the contents of the email message (through an SSIS String variable).
In the dataset I have an EmployeeCode column. I need to select a single EmployeeCode and grab all the records associated with that code to create a single EmailMessage for that Employee that includes all their records. I've got that done, however the way I'm implementing it I then have to update the SSIS Object variable that holds the result set to remove those records I've already processed. Doing this causes an error at the next ForEach loop iteration stating it can't find a source.
Result Set from SQL Query (16 columns, 674 rows, 145 distinct Employeecodes)
Employee Code EmailAddress Other columns
1 email1 set1
1 email1 set2
2 email2 set3
2 emial2 set4
2 email2 set5
etc etc etc
The first iteration should be sent to email1 where the body includes set1 and set2, the second iteration sends to email2 where the body includes set3, set4, and set5 and so forth.
The SSIS ForEachLoop Container is looping through the entire Result Set and passing into the .NET Script task the EmployeeCode (readonly) and the entire Result Set (readwrite) and setting the value of EmailMessage. So I create the value of EmailMessage based on the EmployeeCode and then use the value of Email1 and EmailMessage in the SendMail Task. I was trying to delete the rows I processed so I don€™t process them the next time the ForEachLoop Executes (otherwise I'll get duplicate emails).
Or if there's a different way to do this, I'm open to that as well.
See below for the code I'm using (colEmployeeCode is a ReadOnlyVariable and rsRecipients and EmailMessage are ReadWriteVariables in the Script Task).
Public Sub Main()
Dim Header As String
Dim Body As String
Dim Footer As String
Header = "blah"
Footer = "blah"
Try
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
olead.Fill(dt, Dts.Variables("rsRecipients").Value)
For Each row As Data.DataRow In dt.Rows
If UCase(Trim(row("EmployeeCode").ToString())) = UCase(Trim(Dts.Variables("colEmployeeCode").Value.ToString())) Then
Body = Body + "Label: " + Trim(row("colum").ToString()) + System.Environment.NewLine
row.Delete()
End If
Next
Dts.Variables("rsRecipients").Value = dt
Dts.Variables("EmailMessage").Value = Header + Body + Footer
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
Thank you very much,
Aaron
View 3 Replies
View Related
Oct 23, 2014
I'm using a DateTime variable in SSIS 2008 that is used to set the SQLStatement property of an Execute SQL Task.
"DELETE FROM Labor WHERE Week = '" + (DT_WSTR, 100) @[User::Week] + "'"
Week is the next Sunday:
DATEADD( "day", @[User::DaysTillSunday] , @[User::TheDayThatIsTwentyMinutesPrior] )
DaysTillSunday:
DATEPART( "dw", @[User::TheDayThatIsTwentyMinutesPrior] ) == 1 ? 0 : 8 - DATEPART( "dw", @[User::TheDayThatIsTwentyMinutesPrior] )
TheDayThatIsTwentyMinutesPrior:
(DT_DATE)(DT_DBDATE)DATEADD("minute",-20,GETDATE())
The SSIS Package deletes the current week's data, reloads it with fresh data, then calculates the difference between the current week and last week.
The problem is that randomly, instead of deleting the current week, it will delete the previous week. This happens maybe 5-10% of the time. At least it does until I rebuild the package and import it into SQL Server again.
I'm guessing that the Execute SQL Task is not updating the value of the Week variable before it executes. I started with the source type being a variable. Then I decided to try Direct input and pass in the Week as a parameter (OLE DB Connection Type). That didn't work either.
Most recently I tried writing the Week variable to a table first, then having a sequence container with all the tasks second. Slightly better but I still saw the date was wrong 2 times in about 90 executions. I was hoping that writing the Week variable out to the database would force an update of any associated connections to it, but that didn't seem to work.
View 18 Replies
View Related
Aug 7, 2007
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 4 Replies
View Related
Jan 19, 2005
Hi,
The following SQL is lifted from one of the Reporting Services / Adventureworks2000 sample reports. I'm a little slow / baffled on how the inner joins are working? Specifically the Inner Join Locale and Inner Join ProductModel. I'm used to seeing Inner Join SomTable On Something = Somthing but how these joins are working is lost on me. Can someone give a quick overview (or point me to a reference) so I can better understand.
Thanks!
SELECT ProductSubCategory.Name AS ProdSubCat, ProductModel.Name AS ProdModel, ProductCategory.Name AS ProdCat, ProductDescription.Description,
ProductPhoto.LargePhoto, Product.Name AS ProdName, Product.ProductNumber, Product.Color, Product.Size, Product.Weight, Product.DealerPrice,
Product.Style, Product.Class, Product.ListPrice
FROM ProductSubCategory
INNER JOIN Locale
INNER JOIN ProductDescriptionXLocale ON Locale.LocaleID = ProductDescriptionXLocale.LocaleID
INNER JOIN ProductDescription ON ProductDescriptionXLocale.ProductDescriptionID = ProductDescription.ProductDescriptionID
INNER JOIN ProductModel
INNER JOIN Product ON ProductModel.ProductModelID = Product.ProductModelID
INNER JOIN ProductModelXProductDescriptionXLocale ON ProductModel.ProductModelID = ProductModelXProductDescriptionXLocale.ProductModelID
ON ProductDescriptionXLocale.LocaleID = ProductModelXProductDescriptionXLocale.LocaleID AND
ProductDescriptionXLocale.ProductDescriptionID = ProductModelXProductDescriptionXLocale.ProductDescriptionID
ON ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID
INNER JOIN ProductCategory ON ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
LEFT OUTER JOIN ProductPhoto ON Product.ProductPhotoID = ProductPhoto.ProductPhotoID
WHERE (Locale.LocaleID = 'EN')
Shawn
View 3 Replies
View Related
May 17, 2007
I'm trying to get the following poll working:http://www.codeproject.com/useritems/Site_Poll_Control.aspIt looks like it's exactly what I was looking for, but it doesn't come with much in the way of instructions. I have the following function: Public Function CastVote(ByVal PollId As Integer, ByVal Answer As Integer, ByVal MemberId As Integer) As Boolean Dim cmd As New SqlCommand("InsertPollResult", New SqlConnection(Connection)) With cmd.Parameters .AddWithValue("@PollId", PollId) .AddWithValue("@PollChoice", Answer) .AddWithValue("@MemberId", MemberId) End With Return (SqlExecuteInsertSp(cmd) > 0) End Function This calls SqlExecuteInsertSp(cmd) which is:Public Function SqlExecuteInsertSp(ByVal cmd As SqlCommand) As Integer Dim i As Integer cmd.CommandType = CommandType.StoredProcedure Try cmd.Connection.Open() i = cmd.ExecuteNonQuery() Catch ex As Exception ErrorMessage = "ProDBObject.SqlExecuteInsertSp(SqlCommand): " & ex.Message.ToString Finally cmd.Connection.Close() End Try Return i End Function I can't figure out what this is doing. The best I can figure is it determines if we have a good connection. Is this right? In my code CastVote keeps returning false, and I don't know why. The answer seems to be in the i = cmd.ExecuteNonQuery() line, but I can't figure out what that line is supposed to be doing.Diane
View 3 Replies
View Related
Sep 4, 2006
Hi Guys,
I have written quite a big stored procedure which creates a temporary table (multi-session) and updates it. All the statements are encapsulated in a single transaction which is explicitly declared in the code. What happens is that a lock is being put by the server on that table (of type Sch-M) in order thus preventing any type of operations on it (including simple select)
Now, I want to be able read that table from within another transaction. Why is that I cannot use a table hint NOLOCK in the select statement?
Here is some code which reproduces my problem.
Query A:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN TR_DEMO;
CREATE TABLE ##TBL1(
Oidx int not null primary key identity(1,1),
Name nvarchar(30) not null,
Type char(1) not null
);
INSERT ##TBL1 (Name,Type) VALUES ('Car','M');
WAITFOR DELAY '00:00:10';
INSERT ##TBL1 (Name,Type) VALUES ('Plane','M');
WAITFOR DELAY '00:00:10';
INSERT ##TBL1 (Name,Type) VALUES('Submarine','M');
WAITFOR DELAY '00:00:10';
DELETE FROM ##TBL1;
DROP TABLE ##TBL1;
COMMIT TRAN TR_DEMO;
Query B:
SELECT TOP 1 * FROM ##TBL1 (NOLOCK) ORDER BY oidx DESC;
Launch query A and then execute query B.
Thanks a lot for your help.
View 2 Replies
View Related
Nov 3, 2004
My question is in what situations @@ERROR will be set...
I like to do some logic when some error is occured in a particular statement....
the doc. says the @@ERROR value will be set if an error occurs in a statement, and the control will move to the next statement without exiting(???) the procedure and @@ERROR value can be used in that statement.
but when i execute the below procedure, the execution is terminated ( when the error occurs) without moving to the next statement. please help me to understand the SQL Server's @@ERROR and the situations when it will be set....
-----------------------------------------------------------------------
CREATE PROCEDURE VALUE_ERROR_TEST
AS
BEGIN
DECLARE @adv_error INT
DECLARE @errno INT
DECLARE @var int
SELECT @var = '101 a'
SELECT @errno = @@ERROR
print @errno
END
go
-----------------------------------------------------------------------
procedure get successfully compiled. when executed it says,
Server: Msg 245, Level 16, State 1, Procedure VALUE_ERROR_TEST, Line 10
Syntax error converting the varchar value '101 a' to a column of data type int.
Jake
View 1 Replies
View Related
Jun 1, 2007
I am a new learner of SQL, Please can someone give me a very brief overview of what SSIS and what it can do?
thanks
View 2 Replies
View Related
Jan 10, 2008
What's the best resource (book or web article) that explains the joins between database tables?
Thank you!
View 3 Replies
View Related
Aug 7, 2006
Please consider the following example.CREATE TABLE test (an_ndx int NOT NULL primary key identity(1,1),a_var varchar(48) NOT NULL,last_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP);CREATE TABLE test_history (an_ndx int NOT NULL,a_var varchar(48) NOT NULL,last_edit_timestamp datetime NOT NULL,current_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP);GOCREATE TRIGGER update_history ON test FOR UPDATEASBEGININSERT INTO test_history (an_ndx, a_var, last_edit_timestamp)SELECT * FROM deleted;UPDATE inserted SET last_edit_timestamp = CURRENT_TIMESTAMP;END;The question is, does this do what I think it should do? What Iintended: An insert into test results in default values for an_ndx andlast_edit_timestamp. An update to test results in the original row(s)being copied to test_history, with a default value forcurrent_edit_timestamp, and the value of last_edit_timestamp beingupdated to the current timestamp. Each record in test_history shouldhave the valid time interval (last_edit_timestamp tocurrent_edit_timestamp) for each value a_var has had for the "object"or "record" identified by an_ndx.If not, what change(s) are needed to make it do what I want it to do?Will the trigger I defined above behave properly (i.e. as I intended)if more than one record needs to be updated?ThanksTed
View 3 Replies
View Related
