Storing A Money Data Item (in SQL Server) In A Double SSIS Variable
Nov 5, 2007
Hi
I have a table in SQL Server with following spec
Table1(Grossamount(money))
I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS
Select Sum(Grossamount) from Table1
I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.
it gives me the error :
[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
I tried the following sql to no avail
Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1
Your help very much appreciated
View 7 Replies
ADVERTISEMENT
Aug 4, 2015
I have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
[Code] ....
View 6 Replies
View Related
Apr 28, 2015
I have a file with a header row which contains the date of the file and under that all the columns without a header.
In SSIS package I skip row and manually name the different columns.
However, I want to use the column in the header row to store the date value in an SSIS variable and use that variable to write it to our staging table. How can I do this when I skip the header row?
View 5 Replies
View Related
Aug 16, 2007
Hi All,
I need to select some collumn data , can i store it in a varable some how.
Select name from Names
how can i assign this value to a variable, I need to manipulate this column value based on few cases.
As always thanks and appreciate your help.
View 1 Replies
View Related
Feb 12, 2007
Does anyone have a helpful link for using the partition processing data
flow task in SSIS? I am trying to process a monthly partition
from within my package and am getting the following error:
Error: 0xC113000A Errors in the high-level relational engine. Pipeline
processing can only reference a single table in the data source view.
If anyone has used this before and could point me in the right direction, I would appreciate it.
Thanks,
Nick
View 3 Replies
View Related
Sep 13, 2004
Hi,
i am using command files(.cmd) and osql utility to conneft to a database.
following is the requirement.
I have a query "SELECT top 1 au_fname FROM pubs..authors".
Can i store the value of au_fname in the command file?
Is there any work around for this.
Any help is appreciated.
Thanks.... :)
View 1 Replies
View Related
May 19, 2011
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
View 2 Replies
View Related
Jul 19, 2007
Hi,I have a Price variable in a table and when the price = 999 999.99$ the database convert it to 1 000 000$.If price = 999 999.51$ convert it to 999 999.50$If the price is 999 998.99$ convert it to 999 999.00$How can i fix that? I always want that the price to be the same as the user enter it.I use :Dim Prix As Single
myCommand.Parameters.Add(New SqlParameter("@Price", SqlDbType.Money))myCommand.Parameters("@Price").Value = Prix
Thanks
View 7 Replies
View Related
Mar 2, 2006
Here's what I want to do: I've got a table with orders, each order hasa specific discountrate (an int, which represents a percentage). Eachorder consists of 1 or more items in another table, each item in thattable has a price. Now I want to return the full price and thediscounted price (or the discounted amount).Here's a relevant excerpt of the code:------------------------------------------------------------------CREATE TABLE #tmp (OrderID Integer,Price money,Discount money)DECLARE @Discount moneySELECT @Discount =(((SELECT SUM(OrderDetails.Price * OrderDetailsAmount)FROM OrderDetailsWHERE OrderID = @orderID AND CustomerID = @CustomerID)+(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *OrderDetailsAmount),0)FROM OrderDetailsSupplementINNER JOIN OrderDetails ONOrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsIDWHERE OrderID = @orderID AND CustomerID = @CustomerID))*( @DiscountRate / 100 ))SELECT CustomerFull,SUM(Price) As Price,SUM(Discount) As Discount,SUM (Products) As Products,COUNT(@orderID) As OrdersFROM #tmpGROUP BY CustomerFullORDER BY CustomerFull------------------------------------------------------------------The problem: instead of getting a low number (like 0.57 for instance),I get a 0. Right now I've "solved" this by replacing "( @DiscountRate /100 )" with just "@DiscountRate" and then dividing by 100 in my aspcode, but I'd really like to know what I'm doing wrong.--BVH
View 7 Replies
View Related
Aug 11, 2015
Example of data in CSV are as follows:
"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS
[Code] ....
View 5 Replies
View Related
Dec 29, 2006
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies
View Related
Oct 15, 2015
Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
View 7 Replies
View Related
Feb 9, 2014
I have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
I want to get only data in double codes in using sql query.
View 9 Replies
View Related
Aug 31, 2007
how to pass the numeric(12,0) data type to a variable in SSIS? what kind of variable data type should I choose?
I am trying to assign object_key column ( numeric(12,0)) to a variable in SSIS
If i select int32 , it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User::Object_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
View 3 Replies
View Related
Jul 11, 2007
Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?
Thanks,
View 17 Replies
View Related
Mar 28, 2001
Can someone tell me how to store the results of a multi-row query in a text or varchar variable so that I might do something like this:
declare @var1 text
select @var1 = {results of select date, count(*) from testtable2 group by date)
Thanks!
joe
View 2 Replies
View Related
Aug 3, 2007
I know this can be done, because I saw it and tried it once. But did not use it at the time and consequently forgot how.
I am storing an expression to a variable @Var Varchar(50)
@Var = ('aa','bb','cc')
I want to:
Select * from Table WHERE Field IN @Var
Substituting my variable for the " ('aa',bb','cc') which I can build dynamically...thus the use.
Purpose:
I have an aggregate query that currently has a CASE statement based on the contents of ('aa',bb','cc'). However, those contents can change. The aggregate will not allow the subquery (SELECT Field FROM Table).
What I remember looked something like:
SELECT * FROM Table WHERE field in ' & @Var &'.
View 1 Replies
View Related
Feb 1, 2007
Hello All,
A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.
Thank you in advance
View 1 Replies
View Related
Sep 7, 2007
hello
Does anybody know how to store the result of a select top 1 into a variable??
I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
But none of them work
Any ideas??
Thanks
View 1 Replies
View Related
Sep 25, 2006
Greetings,
I have written a SSIS package which does the following:
- An 'Execute SQL Task' uses an OLEDB connection to execute a Stored Procedure in SQL Server 2005
A SSIS user defined variable named @SourceSQLStatement is passed to the Stored procedure as an OUTPUT
This variable is a string Data Type
- The Stored Procedure generates an SQL command and saves it in the variable
This variable is defined as @SourceSQLStatement nvarchar(4000) OUTPUT
The varible is then returned to the SSIS package
When the package is executed the variable does not contain the entire SQL command.
When I check in the Locals window during Debug, the contents seem to have been truncated:
+ User::SourceSQLstatement {SELECT a.FailureID, a.ExceptionHandlerID, a.FailureTypeCd, a.AccountNbr, a.AccountNm, a.CaseNbr, a.CustNm, a.AssociateNm, a.TargetSysNm, a.FailureDt, a.FailurePointTxt, a.SubmittedByNm, a.ErrorMsgTxt, a.CreateId, a.CreateDt, a.UpdateId, a.UpdateDt FROM GSPIntegrationException a WHERE a.CreateDt >= (select max(ETLC.RunDt) from X141572_ETLSTAGING.dbo.ETL ETL inner join X141572_ETLSTAGING.dbo.ETLControl ETLC on ETL.ETLID = ETLC.ETLID inner join X141572_ETLSTAGING.dbo.ETLRun ETLR on ETLC.ETLID = ETLR.ETLID an} String
The fully generated SQL command does not exceed the 4000 character length provided in the stored procedure.
Has anyone encountered this issue before?
Any help would be greatly appreciated
Thank You
View 4 Replies
View Related
Aug 22, 2007
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 3 Replies
View Related
Feb 21, 2007
Hi below is the code I am using.------------------------------------SET NOCOUNT ONDECLARE @emailid varchar(50), @rastype varchar(50),@message varchar(80)declare @allrastypes varchar(200)DECLARE email_cursor CURSOR FORSELECT distinct EmailFROM dbo.tblMaintCustomerORDER BY EmailOPEN email_cursorFETCH NEXT FROM email_cursorINTO @emailidWHILE @@FETCH_STATUS = 0BEGINPRINT ' 'SELECT @message = 'Email Address ' +@emailidPRINT @message-- Declare an inner cursor based-- on vendor_id from the outer cursor.DECLARE rastype_cursor CURSOR FORSELECT distinct [RasType]FROM dbo.tblMaintCase x, dbo.tblMaintCustomer yWHERE x.caseid = y.caseid ANDy.Email = @emailidand RasType is not nullOPEN rastype_cursorFETCH NEXT FROM rastype_cursor INTO @rastypeselect @allrastypes = @allrastypes + ',' + @rastypeIF @@FETCH_STATUS <0PRINT ' <<None>>'WHILE @@FETCH_STATUS = 0BEGINSELECT @message = @rastypePRINT @messageselect @allrastypes = @allrastypes + ',' + @rastypeFETCH NEXT FROM rastype_cursor INTO @rastypeENDCLOSE rastype_cursorDEALLOCATE rastype_cursorinsert into dbo.tblTest values(@emailid,@allrastypes)select @allrastypes = ''FETCH NEXT FROM email_cursorINTO @emailidENDCLOSE email_cursorDEALLOCATE email_cursor--------------------------------------I basically want the value of @allrastypes to accumulate each time itloops through, which is is not doing.The result I get is :Email Address Join Bytes!G5R(for here i want @allrastypes to be 'G5R,')Email Address Join Bytes!G1G3G5O(for here i want @allrastypes to be 'G1,G3,G5O')Can someone helpThanksArchana
View 1 Replies
View Related
Feb 11, 2008
I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:
SET @value = select max(price) from product
but this does not work, can someone tell me how I would go
about in storing the results in a variable.
@value is declared as int
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
Feb 19, 2008
Hi
is it possible to store a SQL CE command object in SQL Server CE database.
If so then what would be the datatype of such column????
thanx in advance
View 1 Replies
View Related
Mar 25, 2002
Hi.
I am trying to store the column value to a variable from a distributed query.
The query is formed on the fly.
i need to accomplish something like this
declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname
-MAK
View 2 Replies
View Related
Oct 1, 2007
Hi,
How to insert or store arabic data into a table in sql server 2005, also i want to retrieve it as arabic format.But now it insert (?????) like this.Please help me to find a solution.
Regards
Sabna S
View 3 Replies
View Related
Jun 13, 2008
I have a function which is writing thousands of records coming from Access database and I was wondering if someone can suggest how can reconstruct my code for faster processing. Here my code: Public Sub MigrateNFSData(ByVal calcTbl As DataTable, ByVal strDBConnection As String) Dim sqlServerConn As New SqlConnection(strDBConnection) 'Define stored procedures Dim command As New SqlCommand Dim getAccID As New SqlCommand("GetAccountID", sqlServerConn) Dim getActionID As New SqlCommand("GetActionID", sqlServerConn) Dim getExchangeID As New SqlCommand("GetExchangeID", sqlServerConn) 'Dim getParrentAccID As New SqlCommand("GetParentAccID", sqlServerConn) Dim getStatusID As New SqlCommand("GetStatusID", sqlServerConn) Dim getTraderID As New SqlCommand("GetTraderID", sqlServerConn) Dim getGroupID As New SqlCommand("GetGroupID", sqlServerConn) Dim getGroupIDByIP As New SqlCommand("GetGroupIDByIP", sqlServerConn) Dim getTraderIDByIP As New SqlCommand("GetTraderIDByIP", sqlServerConn) 'Define insert records stored procedures Dim insertAcc As New SqlCommand("InsertAccount", sqlServerConn) insertAcc.CommandType = CommandType.StoredProcedure Dim insertAction As New SqlCommand("InsertAction", sqlServerConn) insertAction.CommandType = CommandType.StoredProcedure Dim insertExchange As New SqlCommand("InsertExchange", sqlServerConn) insertExchange.CommandType = CommandType.StoredProcedure Dim insertGroup As New SqlCommand("InsertGroup", sqlServerConn) insertGroup.CommandType = CommandType.StoredProcedure Dim insertStatus As New SqlCommand("InsertStatus", sqlServerConn) insertStatus.CommandType = CommandType.StoredProcedure Dim insertTrader As New SqlCommand("InsertTrader", sqlServerConn) insertTrader.CommandType = CommandType.StoredProcedure Try sqlServerConn.Open() Catch ex As Exception MessageBox.Show("Connection failed to open!") End Try 'Set parameters to helper Get Stored Procedures to retreive Id's getAccID.Parameters.Add("@AccName", SqlDbType.NVarChar) getAccID.CommandType = CommandType.StoredProcedure getActionID.Parameters.Add("@ActionName", SqlDbType.NVarChar) getActionID.CommandType = CommandType.StoredProcedure getExchangeID.Parameters.Add("@ExchName", SqlDbType.NVarChar) getExchangeID.CommandType = CommandType.StoredProcedure 'getParrentAccID.Parameters.Add("@ParentName", SqlDbType.NVarChar) 'getParrentAccID.CommandType = CommandType.StoredProcedure getStatusID.Parameters.Add("@StatusName", SqlDbType.NVarChar) getStatusID.CommandType = CommandType.StoredProcedure getTraderID.Parameters.Add("@TraderName", SqlDbType.NVarChar) getTraderID.CommandType = CommandType.StoredProcedure getGroupID.Parameters.Add("@GroupName", SqlDbType.NVarChar) getGroupID.CommandType = CommandType.StoredProcedure getGroupIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getGroupIDByIP.CommandType = CommandType.StoredProcedure getTraderIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getTraderIDByIP.CommandType = CommandType.StoredProcedure command = New SqlCommand("InsertTradeTransaction", sqlServerConn) command.CommandType = CommandType.StoredProcedure 'Set Parameters for Insert stored procedures insertAcc.Parameters.Add("@Account", SqlDbType.Text) insertAction.Parameters.Add("@ActionName", SqlDbType.Text) insertExchange.Parameters.Add("@Exchange", SqlDbType.Text) insertGroup.Parameters.Add("@Group", SqlDbType.Text) insertGroup.Parameters.Add("@ACCID", SqlDbType.Int) insertGroup.Parameters.Add("@GroupID", SqlDbType.UniqueIdentifier) insertStatus.Parameters.Add("@StatusName", SqlDbType.Text) insertTrader.Parameters.Add("@Group", SqlDbType.UniqueIdentifier) insertTrader.Parameters.Add("@IP", SqlDbType.Text) insertTrader.Parameters.Add("@TraderName", SqlDbType.Text) insertTrader.Parameters.Add("@TraderID", SqlDbType.UniqueIdentifier) 'Adding stored Get Stored Procedure's parameters----------------------- command.Parameters.Add("@OrderNum", SqlDbType.Text) command.Parameters.Add("@ACC_ID", SqlDbType.Int) command.Parameters.Add("@Group_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Trader_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Exch_ID", SqlDbType.Int) command.Parameters.Add("@Date", SqlDbType.DateTime) command.Parameters.Add("@Time", SqlDbType.DateTime) command.Parameters.Add("@ActionID", SqlDbType.Int) command.Parameters.Add("@StatusID", SqlDbType.Int) command.Parameters.Add("@TimeSent", SqlDbType.DateTime) command.Parameters.Add("@Qty", SqlDbType.Int) command.Parameters.Add("@Product", SqlDbType.Text) command.Parameters.Add("@MMYYY", SqlDbType.Text) command.Parameters.Add("@ExchOrderID", SqlDbType.Text) command.Parameters.Add("@TimeTicks", SqlDbType.Int) command.Parameters.Add("@W2G", SqlDbType.Int) command.Parameters.Add("@W2Exch", SqlDbType.Int) command.Parameters.Add("@G2ExchDerived", SqlDbType.Int) command.Parameters.Add("@Msg", SqlDbType.NVarChar) 'command.Parameters.Add("@ExchDate", SqlDbType.DateTime) 'command.Parameters.Add("@ParentID", SqlDbType.Int) 'Paremeters Defenition-------------------------------------- 'Parsing DateTime Objects Dim formaterA As IFormatProvider formaterA = New System.Globalization.CultureInfo("en-GB", True) Dim dateObj As Date 'DEBUG 'Dim rows = calcTbl.Rows.Count Dim colValues = GetColumnsValues(calcTbl) 'Write table with computed NFS data to sql server DB For Each dr As DataRow In calcTbl.Rows Dim orderNo = dr.Item("Order No").ToString() command.Parameters("@OrderNum").Value = dr.Item("Order No").ToString() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() If getAccID.ExecuteScalar() = Nothing Then insertAcc.Parameters("@Account").Value = dr.Item("Acct").ToString() insertAcc.ExecuteNonQuery() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() command.Parameters("@ACC_ID").Value = getAccID.ExecuteScalar() Else command.Parameters("@ACC_ID").Value = Int32.Parse(getAccID.ExecuteScalar()).ToString() End If getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If getTraderID.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() If getTraderID.ExecuteScalar() = Nothing Then getTraderIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getTraderIDByIP.ExecuteScalar() = Nothing Then insertTrader.Parameters("@Group").Value = getGroupID.ExecuteScalar() insertTrader.Parameters("@IP").Value = dr.Item("IP").ToString() insertTrader.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() insertTrader.Parameters("@TraderID").Value = Guid.NewGuid insertTrader.ExecuteNonQuery() command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() Else command.Parameters("@Trader_ID").Value = getTraderIDByIP.ExecuteScalar() End If Else command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() End If getExchangeID.Parameters("@ExchName").Value = dr.Item("Exch").ToString() If getExchangeID.ExecuteScalar() = Nothing Then insertExchange.Parameters("@Exchange").Value = dr.Item("Exch").ToString() insertExchange.ExecuteNonQuery() command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() Else command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() End If getActionID.Parameters("@ActionName").Value = dr.Item("Action").ToString() If getActionID.ExecuteScalar() = Nothing Then insertAction.Parameters("@ActionName").Value = dr.Item("Action").ToString() command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() Else command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() End If getStatusID.Parameters("@StatusName").Value = dr.Item("Status").ToString() If getStatusID.ExecuteScalar() = Nothing Then insertStatus.Parameters("@StatusName").Value = dr.Item("Status").ToString() insertStatus.ExecuteNonQuery() command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() Else command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() End If 'getParrentAccID.Parameters("@ParentName").Value = "" 'If getParrentAccID.ExecuteScalar() = 0 Then 'insert parent acc 'Else 'command.Parameters("@ParentID").Value = getParrentAccID.ExecuteScalar() dateObj = Date.Parse(dr.Item("Exch Date").ToString(), formaterA) command.Parameters("@Date").Value = dateObj command.Parameters("@Time").Value = DateTime.Parse(dr.Item("Time").ToString()) command.Parameters("@TimeSent").Value = DateTime.Parse(dr.Item("Time Sent").ToString()) If (dr.Item("Qty").Equals(System.DBNull.Value)) Then command.Parameters("@Qty").Value = System.DBNull.Value Else command.Parameters("@Qty").Value = Int32.Parse(dr.Item("Qty").ToString()) End If command.Parameters("@Product").Value = dr.Item("Product").ToString() command.Parameters("@MMYYY").Value = dr.Item("MMMYY").ToString() command.Parameters("@ExchOrderID").Value = dr.Item("Exchange Order ID").ToString() If (dr.Item("Time Ticks").Equals(System.DBNull.Value)) Then command.Parameters("@TimeTicks").Value = System.DBNull.Value Else command.Parameters("@TimeTicks").Value = Int32.Parse(dr.Item("Time Ticks").ToString()) End If 'command.Parameters("@ExchDate").Value = Date.Parse(dr.Item("Exch Date").ToString()) 'command.Parameters("@ExchDate").Value = Convert.ToDateTime(dr.Item("Exch Date").ToString()) 'DEBUG 'Dim strW2G = dr.Item("W2G").ToString() 'Dim strW2E = dr.Item("W2E").ToString() If (dr.Item("W2G").Equals(System.DBNull.Value)) Then command.Parameters("@W2G").Value = System.DBNull.Value Else command.Parameters("@W2G").Value = Int32.Parse(dr.Item("W2G").ToString()) End If If dr.Item("W2E").Equals(System.DBNull.Value) Then command.Parameters("@W2Exch").Value = System.DBNull.Value Else command.Parameters("@W2Exch").Value = Int32.Parse(dr.Item("W2E").ToString()) End If 'command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) If (dr.Item("Time Delta G2E").Equals(System.DBNull.Value)) Then command.Parameters("@G2ExchDerived").Value = System.DBNull.Value Else command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) End If command.Parameters("@Msg").Value = dr.Item("Msg").ToString() command.ExecuteNonQuery() Next sqlServerConn.Close() End Sub
View 3 Replies
View Related
Mar 22, 2007
Hello All,
We are developing customer support application. We will have so many customers after launching this product. But my problem is how will i store data of all these customers in SQL Server. Please suggest me.
View 1 Replies
View Related
Aug 28, 2014
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
View 5 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Oct 7, 2015
One of my current responsibilities is to export data to 3rd party vendors. Each export can contain many csv files. The exports are all different in terms of what data is being sent.
The way I have it currently setup is each file that needs to be created is a view. An SSIS package gets the data from the view, writes to CSV, and then sftp to 3rd party vendor. This seemed like a good idea at first because the columns are static but the calculations might change. So all I have to do is ALTER VIEW and I don't have to change anything in the package.
Is there a better way of doing this? I was curious to see what other people are doing. What makes it challenging is that all the exports are so different. If they were similar I could have created generic views that cover all the exports instead of each export having its own view. Eventually I'm going to have 100's of views.
View 9 Replies
View Related
May 1, 2007
Hi all,
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
Any suggestions?
View 1 Replies
View Related
Dec 12, 2014
We have an issue with importing a CSV file into SQL where using a double quote " text qualifier is failing. The data is correct but it fails on a particular line, complaining about the qualifier even though the qualifier is in place and previous lines have imported fine.
View 3 Replies
View Related
