Transact SQL :: Determining Procedure Variable Type?
May 12, 2015
I am building as Search page whereby a user passes in a variable and depending on the variable type, different results will come back ...if the search criteria is '123 somewhere' it would be a string and we would search the address field. If the search criteria is '123' i want to search the address field as well as the id field.that being said, in TSQL is there a way to determine if the variable coming in is a string or an int?
View 3 Replies
ADVERTISEMENT
Feb 26, 2002
Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!
View 1 Replies
View Related
May 12, 2008
I currently have a stored procedure that is defined as follows:
CREATE PROCEDURE UpdateSyncLog
@TableName char(100),
@LastSyncDateTime datetime,
@ErrorState int OUTPUT
I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:
exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState
Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).
I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32
The parameter mappings are as follows:
Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1
Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1
The error I am getting when running this execute sql task is as follows:
Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.
What gives?
View 2 Replies
View Related
Jun 11, 2007
Is there a way to determine if a local variable exists or not?
There's a parameter I often use in code called @guid_batch that is usually declared in the parameter of a stored proc, but when in debugging it would be nice to have it available without having to change code.
Is there something that I could do similar to the following
Code:
IF VARIABLE_ID('@guid_batch') IS NULL
BEGIN
DECLARE @guid_batch UNIQUEIDENTIFIER
SELECT @guid_batch = NEWID()
END
Thanks in advance,
-MBirchmeier
View 8 Replies
View Related
Mar 7, 1999
Hi,
Is it possible to determine when last a DBCC statement was run on a database & what that DBCC was.
TIA
Shaun Tinline
Database Administrator
Alcatel Altech Telecoms
View 1 Replies
View Related
Jul 20, 2005
Hey,I'm writing some automated unit tests for a database app. The appcreates a database via script. I'm developing the system in atest-driven manner and need a way to determine if a parameter existswithin a stored procedure using SQL. Any ideas?Thanks,WillJoin Bytes!
View 3 Replies
View Related
May 27, 2015
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
View 8 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
View 6 Replies
View Related
Dec 14, 2007
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
View 1 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Sep 25, 2013
I have a situation where I need to determine which type this record is. If type ID is 20, 23, or 25, then select F otherwise it is P.
Select FirstName
,Lastname
,address
, (If typeid = 20, 23, or 25 then F else P) as TypeDesc
From sometable
The number(20,23,25) are hardcoded in the stored procedure and I have them in a variable because there are over 30 different numbers.
View 3 Replies
View Related
Jul 20, 2005
Is there a way to check the type of a variable in SQL? I want to knowif a variable is a float or a string.Thanks,Ramzi
View 1 Replies
View Related
Apr 10, 2006
Hi all,
I am trying to extract certain data from MySQL
(example sql statement is SELECT COLUMN1, COUNT(COLUMN2) FROM TABLE
GROUP BY COLUMN1) and stored COLUMN1 and COUNT(COLUMN2) values into
variables and then copy the values from the variables and insert them
into a new table in sql server 2005 and has two columns in it (COLUMN1
of type nvarchar and COLUMN2 of type int). I can map the first output
(COLUMN1) correctly to variable type String and store them in the new
table in sql server 2005 (store the value in a variable type String and
then use insert and store it into sql server using parameter in Execute
Sql Task set the data type to NVARCHAR, but I cannot map the second
output (COUNT(COLUMN2)) using the same method, I could not even get it
to store into a variable of any type. Anyone have any idea how to go
about it? Thanks in advance.
The output of the sql statement for MySQL should look like:
COLUMN1 COUNT(COLUMN2)
----------------------------------------
aaa 101
bbb 232
ccc 542
Daren
View 2 Replies
View Related
Feb 13, 2007
How can I return a datetime type variable from a stored procedure in SQL Server to C# code?
View 4 Replies
View Related
Dec 19, 2007
Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen
View 1 Replies
View Related
Apr 28, 1999
I'm new to SQL Server. I installed a copy of 6.5 on my server and set it up today. I received a db from a colleague and have been unable to find out what the variable types and lengths are. This may be very easy but I need to know.
Thanks,
David A. Fordinal
fordinal@uta.edu
View 1 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
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 29, 2007
I€™ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. . The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TESTE]
ON [dbo].[TABTESTE]
FOR INSERT
AS
DECLARE
@YearTable nvarchar(4),
@IdClient INT,
@MyTable TABLE
(
IdClient INT,
Situ NVARCHAR(50)
)
BEGIN
SET NOCOUNT ON;
SELECT @YearTable = SITUACAO, @IdClient = IdClient FROM INSERTED
SET @MyTable = 'TABLE' & @YearTable
UPDATE @MyTable
SET
Situ = 'X'
WHERE IdClient = @IdClient
END
GO
Erros:
Msg 156, Level 15, State 1, Procedure TESTE, Line 9
Incorrect syntax near the keyword 'TABLE'.
Msg 137, Level 15, State 1, Procedure TESTE, Line 17
Must declare the scalar variable "@MyTable".
Msg 1087, Level 15, State 2, Procedure TESTE, Line 18
Must declare the table variable "@MyTable".
View 8 Replies
View Related
Oct 3, 2007
Is there anyway to change the data type of a variable while in an expression?
My problem is I am trying to compare a variable w/ a string data type to a variable w/ an object data type. I would change the data type of the variable from object to string but if I do that my sql task fails when it tries to write a value to that variable. The variable w/ the object data type is the result of an openquery sql stmnt. So I guess there are two ways around my problem.
1. Change data type of variable while in an expression..ie flow constriant
or
2. Change data type of vraiable from object to string and still get the openquery result to work.
Any help???
Thanks !!
View 4 Replies
View Related
Jan 25, 2006
Hi
I have some SSIS variables of type System.Object (they have to be this
type because they are used to hold the results of a single row result
set in an Execute SQL task which is querying an Oracle database.
Although I know the Oracle table columns are Numeric, this was the only
SSIS type that worked).
My problem is that I want to use these variables in expressions, but
can't - I get the error "The data type of variable "User::varObjectVar"
is not supported in an expression".
The only workaround I can think of is to use a script to assign
the numeric values (integers, in fact) that these variables hold to
other variables of type Int32.
Is that my only option, or am I missing something?
thanks
- Jerzy
View 6 Replies
View Related
Jun 29, 2006
Hi,
I am using the following code in Script Task and it is giving me the error as shown below
FileCount is a variable defined as Int16 (with initial value = 0 ) and it is part of Read/Write variables portion of Script
Dim FileCount As Int16
FileCount = CType(Dts.Variables("FileCount").Value, Int16)
Dts.Variables("FileCount").Value = FileCount + 1 //Error on this Line
The type of value being assigned to @User::FileCount differs from the current variable type. Variables may not change during execution.
Please Guide what is wrong with this.
View 1 Replies
View Related
Dec 14, 2007
HI Experts,
I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.
for that i have written query :
DECLARE @rowcount_mastertable FLOAT
SET @rowcount_mastertable = (select count(*) from master_table)
DECLARE @TABLE_ID float,
@TABLE_NAME varchar (100),
@primary_key varchar (100),
@Primarykey_DATATYPE varchar (50),
DECLARE @COUNTER FLOAT
SET @COUNTER = 1
WHILE (@Counter <= @rowcount_mastertable)
Begin
SET @TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @COUNTER)
SET @primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @COUNTER)
SET @Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @COUNTER)
--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @MAX_primary_key to fetch --max of primary key from table name where table id is 1
DECLARE @MAX_primary_key @Primarykey_DATATYPE
SELECT @MAX_primary_key = MAX(@primary_key) FROM @TABLE_NAME
WHERE TABLE_ID = @COUNTER
---But by running it i am getting error that "Incorrect syntax near '@Primarykey_DATATYPE'. and "Must declare the variable '@MAX_primary_key'.
Please suggest
Thanks in Advance
View 3 Replies
View Related
Jun 7, 2006
Greetings my SSIS friends
I am attempting to create an expression as follows:
"Select * from someTable where someColumn >= " + (dt_str, 25, 1252) @[User::DateTimeVariable]
The problem is that my variable is a Datetime field and when I convert it to string, the string will not execute correctly.
How to solve this problem?
View 3 Replies
View Related
May 23, 2001
In a stored proc, can you declare a local variable that is an existing column in a table & then based on other criteria, do an order by using the local variable?
View 1 Replies
View Related
Nov 19, 2004
I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .
:confused: Please help me out .
Thanx
Arnie .
View 1 Replies
View Related
Dec 5, 2014
Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END
View 3 Replies
View Related
Feb 19, 2008
Hello All -
Have you ever seen the error message below?
Description: The package path referenced an object that cannot be found: "Package.Variables[User::<variable_name>].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning Could not load package "<package_name>" because of error 0xC0010014.
Basically, I create a package variable under my User Namespace and this variable will tell what server the SSIS is running at. We first create a system variable locally and the SQL Server will have a variable with exactly the same name so that the server name will be evaluated through the package variable/package configurations when the SSIS is executing from a SQL Server job.
This way we do not hard code the server name... We always succeeded on doing that with DTS as well as SSIS packages but just now my package is running into this issue...
Since I did not change ANYTHING in the package, I am guessing this is not programming related and that something was changed in the server. However, the DBA was helpless over here and I have no clue of what this error means.
Any help would be appreciated.
Thanks, Gabriel.
View 14 Replies
View Related
Aug 19, 2015
I am trying to find what datatype I can use for variable values like below in a column
E.g. column which we get
10000.10
100
180.34
98203710231.34
From the above example, you can see some of the values contains no decimal and with decimal
Also we cant say whether the decimal comes after the 5th number or 10th number. Any other datatype to capture this values. If not last option is to give varchar2.
View 3 Replies
View Related
Sep 4, 2007
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char
View 3 Replies
View Related
Nov 4, 2015
My data is in 4 columns and multiple rows, like this
PartNo  Quantity TransactionType        TransactionDate
aaa      25        Incoming                   2015-03-01
aaa       25        Incoming                   2015-03-01
aaa      50        Transfer                    2015-03-02
bbb      30        Incoming                  2015-03-03
bbb      30        Transfer                    2015-03-03
ccc       50        Incoming                  2015-05-15
ccc       75        Incoming                  2015-05-20
ccc       50         Transfer                   2015-05-18
ccc       75        Transfer                   2015-05-21
What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.
PartNo  Quantity  IncomingDate  TransferDate
aaa       50          2015-03-01     2015-03-02
bbb       30           2015-03-03     2015-03-03
ccc        125        2015-05-15      2015-05-21
How to achieve this goal.
View 5 Replies
View Related
Dec 3, 2015
Is it possible to group the below code into an Island type scenario? The data represents people in a location. If they are in the same place one after another, to group and provide the min/max scenarios.
DECLARE @Table TABLE
(
PersonVARCHAR(10)
,LocationCHAR(1)
,Order_WhenINT
[code]....
View 6 Replies
View Related
Sep 28, 2006
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View 1 Replies
View Related
