Trouble Converting Nvarchar(10) To Smalldate Time Where ISDATE()=1
Nov 17, 2007
I have a column which is current stored as nvarchar(10), and all of the enteries are either NULL or mm/dd/yyyy.
I am trying to convert the column to smalldatetime using CONVERT or CAST and each time I get an arithmetic overflow error message.
I also tried selecting just the enteries with ISDATE()=1 and converting those to smalldatetime, and still got an arithmetic overflow message.
(I've run ISDATE() several times on the column, and the only rows without ISDATE()=1 are those which have NULL values).
help!
View 3 Replies
ADVERTISEMENT
Oct 28, 2004
Hello all,
I'm trying to convert a nvarchar datatype to int
(ie:1234-56-78 to 12345678) . These values are primary keys in two tables. Both these tables have 3500 rows of this key type. I want to convert this to a int so I can make it a AutoNumber primary key so I can increment it. Is this possible? If so, how do I do it. Do I need to delete the dashes first some how? I fairly new to database adminstration, so any guidance will be greatly appreciated.
Thank You
View 2 Replies
View Related
Jul 21, 2006
Columns from database have values like 1,5 etc and I'm getting the error: Syntax error converting the nvarchar value '1,5' to a column of data type int. when trying to convert from nvarchar to int:
SELECT CONVERT(int, MyNVarChar_column) FROM MyTable
What I'm doing wrong? Thanks a lot
View 1 Replies
View Related
Jul 20, 2005
For a SQL statement in an Alias column I am am combing severalcolumns.But I am having problems with one column as it is a smallint.I get this errorSyntax error converting the nvarchar value to a column of data typesmallintMy Sql statement "Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColNamefrom Table1I was wondering is there anyway to format/convert the smallint tonvarchar, without changing the database.
View 3 Replies
View Related
Jan 17, 2008
i have some fields in SQL Server table as nvarchar(50) and the user actually enters date (example : 02/05/07) now they want those fields to be converted to datetime or small datetime field.
How do i achieve it without losing data?
Thanks
bhu
View 1 Replies
View Related
Oct 18, 2006
I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error : The average aggregate operation cannot take a nvarchar data type as an argument.Help!!!! :(
View 1 Replies
View Related
May 15, 2005
Hi there,I have a table named Action. This table has a column InPrice with datatypenvarchar(12). I want to change its datatype from nvarchar(12) to money. Ibrowsed through the values and removed any dots. Th column now has onlynumeric values (and commas for decimal values such as 105,8). When I try tochange the datatype from nvarchar to money, following mesage is displayed:ADO error: Cannot convert a char value to money. The char value hasincorrect syntax.How can I solve this problem? I cannot figure out which values are causingthis error.Thanks in advance,Burak
View 4 Replies
View Related
May 9, 2001
I need help converting an nvarchar value to int. I receive a SQL error when running the following query...
"SELECT DISTINCT [Time Cards].TimeCardID, [Time Cards].Status, [Time Cards].Verification, [Time Card Hours].[Date Worked], [Time Card Hours].[Billable Hours], [Time Card Hours].[Billing Rate], [Time Card Hours].[Overtime Hours], [Time Card Hours].[Overtime Rate], Employees.FirstName, Employees.LastName, [Function].[Function], [Time Card Hours].[Invoiced Hours], [Time Card Hours].[Invoiced Overtime], [Time Cards].[30 Day Grace]
FROM [Function] INNER JOIN (Employees INNER JOIN ([Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].TimeCardID = [Time Card Hours].[Time Card ID]) ON Employees.EmployeeID = [Time Cards].EmployeeID) ON [Function].FunctionID = Employees.FunctionID
WHERE ((([Time Cards].[30 Day Grace])=-1));"
[Time Card Hours - nvarchar, Time Cards - int]
View 2 Replies
View Related
Mar 21, 2006
I have a problem converting a float filed into nvarchar.
select cast(sold as varchar(50))
where sold=431597.15 results in 431597 and is ignoring always my decimals.
Do you have any idea how to fix this?
ty
View 1 Replies
View Related
Jun 23, 2008
Hi all
I want to pass a list of nvarchar characters to my procedure, then import them into a temporary table as numeric(5,2) numbers.
The code below does this but the values are corrupted (e.g. 76.25 becomes 7 in the table).
Can anyone please tell me what I'm doing wrong? Thank you!
declare @separator_position int
declare @array_value nvarchar
declare @separator char
DECLARE @numberList NVARCHAR(MAX)
SET @numberList = '23|76.25|100.36|45.54|87.14|74.25|8|98|'
SET @separator = '|'
SET @separator_position = NULL
SET @array_value = NULL
DECLARE @myTable TABLE(ID INT IDENTITY(1,1), compLevel NUMERIC(5,2))
WHILE patindex('%' + @separator + '%', @numberList) <> 0
BEGIN
SELECT @separator_position = patindex('%' + @separator + '%' , @numberList)
SELECT @array_value = LEFT(@numberList, @separator_position - 1)
INSERT INTO @myTable (compLevel) VALUES (CONVERT(NUMERIC(5,2), @array_value))
SELECT @numberList = stuff(@numberList, 1, @separator_position, '')
END
SELECT * FROM @myTable
View 4 Replies
View Related
Aug 13, 2014
i have the following code:
SELECT Quantity
FROM dbo.BNYForwardsTaxLotsForwards
WHERE investmentType ='Forward Cash'
and Quantity < 0
And I get the following error Msg 245, Level 16, State 1, Line 1..Conversion failed when converting the nvarchar value '-12213949.9' to data type int.
View 2 Replies
View Related
Mar 10, 2008
Hi,
I used to work with Sql 2000 and I could easily import a text file into my database and then convert the fields (all nvarchar) to appropriate data types. However, this procedure does not work with Sql 2005.
After I import a text file to Sql 2005 and when I convert nvarchar to numeric, I get "Error converting data type nvarchar to numeric".
Could you please let me know how can I fix this issue. Thanks in advance for your reply.
View 12 Replies
View Related
Mar 11, 2008
One of my clients decided to put letters into their customers' account numbers. They have a numbering scheme where all temporary accounts have a letter in the account OR are numbered greater than 33000, and all permanent accounts are all digits and less than or equal to 33000. all primary accounts have a NumberSuffix of 000.
Now i am tasked with retrieving all primary, non-temp accounts. I cannot simply do WHERE Number <= 33000 because when it gets to an account containing a letter like "00A01", it craps out and says "Conversion failed when converting the nvarchar value '00A01' to data type int."
So decided to run a filtering query first to filter out all accounts with letters, and then from that dataset select all accounts <=33000.
WITH members (FirstName, LastName, Number, NumberSuffix) AS
(
SELECT
dbo.Entity.FirstName,
dbo.Entity.LastName,
dbo.Entity.Number,
dbo.Entity.NumberSuffix
FROM
dbo.Entity
WHERE
NumberSuffix = 000 AND
Number NOT LIKE '%A%' AND
Number NOT LIKE '%B%' AND
Number NOT LIKE '%C%' AND
Number NOT LIKE '%D%' AND
Number NOT LIKE '%E%' AND
Number NOT LIKE '%F%' AND
Number NOT LIKE '%G%' AND
Number NOT LIKE '%H%' AND
Number NOT LIKE '%I%' AND
Number NOT LIKE '%J%'
)
SELECT *
FROM
members
WHERE
Number <= 33000
ORDER BY
Number
when i do this, i get the same error for some reason. Yet when i execute this at the end instead:
SELECT *
FROM
members
WHERE
Number LIKE '%A%'
ORDER BY
Number
i get an empty set (meaning it actually does get filtered).
but somehow it still able to participate in a range comparison?
WHY??
thanks,
Leon
View 10 Replies
View Related
Jan 14, 2008
Okay, I have the following store procedure and I kept getting an error.
Code Block
ALTER PROCEDURE [dbo].[Search]
-- Add the parameters for the stored procedure here
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL,
@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @SQL as Varchar(4000);
Declare @Params as Varchar(3000);
Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = ' + @schoolID;
Set @Params = N'@scholarship VARCHAR(250),@major VARCHAR(250),@requirement VARCHAR(250)'
If @scholarship IS NOT NULL
Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%'''
If @major IS NOT NULL
Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @last + ''%'''
If @requirement IS NOT NULL
Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%'''
If @requirement IS NOT NULL
Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%'''
If @requirement IS NOT NULL
Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%'''
If @requirement IS NOT NULL
Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%'''
If @requirement IS NOT NULL
Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')'
If @debug = 1
PRINT @SQL
Exec sp_executesql @SQL, @Params, @scholarship, @major, @requirement
END
And I kept getting this error:
Msg 245, Level 16, State 1, Procedure Search, Line 28
Conversion failed when converting the nvarchar value 'SELECT * FROM [scholarship] WHERE [sectionID] = ' to data type int.
Any help is much appreciated.
View 7 Replies
View Related
Apr 24, 2008
Hi, I'm working on a DB and I'm trying to write some select statements that will display an employee's pay using the CAST and CONVERT functions. However, I can never seem to get commas inserted into the results please help. Here's the SELECT statements, where am I going wrong?
SELECT
'$' + CAST(CONVERT(MONEY, SUM(PROJECT_BUDGET/1.00), 1) AS VARCHAR) AS 'Total Budget'
FROM
PROJECT;
View 5 Replies
View Related
Mar 27, 2008
Hi i keep getting this error when i search based on Team name (dropdownlist) or coach name(textbox). However it works when i make the search based on the region id(dropdownlist) here is my stored procedure;ALTER PROCEDURE [dbo].[stream_FindTeam]
-- Add the parameters for the stored procedure here
@coachName varchar(100),
@TeamName varchar(100),@regionID INT
AS
SELECT TeamID, coachName FROM Teams WHERE coachName LIKE @coachName OR TeamName= @TeamNameOR regionID = @regionID;
Here is my code;SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
conn.Open();SqlCommand command = new SqlCommand("stream_FindTeam", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.AddWithValue("@coachName", coachName.Text);
command.Parameters.AddWithValue("@TeamName", TeamList.SelectedValue);command.Parameters.AddWithValue("@regionID", Region.SelectedValue);SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
View 13 Replies
View Related
May 11, 2000
I am trying to do some amount calculations.
The amount was declared as nvarchar data type.
I did the following coding. I want to get 2 places right of decimal
but am getting only one place right of decimal.
SELECT
(CAST(EQ_TotQuoteAmnt AS Float(7,2)) -
CAST(EQ_InsFee AS Float(7,2)) - 150) as Inforce_Premium
FROM Quoted
Can someone please help me with the syntax?
Thanks in advance.
M. Khan
View 1 Replies
View Related
Apr 8, 2004
HI,
I HAVE BEEN TRYING TO TRANSFORM AN OLD TABLE TO A NEW FORMAT AND CHANGE SOME OF THE DATATYPE FORMATS USED IN THE OLD ONE.
OUT OF WHICH ONE IS A COLUMN CALLED AS FORM_RECEIVE_DATE WHICH HAS NVARCHAR(41) AS DATATYPE IN THE OLD TABLE CREATED BY A PREVIOUS DBA (DON'T KNOW wHY?)
wHILE CONVERTING IT INTO DATATYPE DATETIME , I AM GETTING THIS ERROR :- "Arithmetic overflow error converting expression to data type datetime." i DON'T KNOW WHY
hERE ARE FEW EXAMPLES OF THE DATA CONTAINED IN THE PREVIOUS TABLE :-
05082003
05062003
05142003
COULD YOU PLS TELL ME A WAY TO SOLVE THIS ?
View 4 Replies
View Related
Apr 13, 2004
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!
View 10 Replies
View Related
Jul 24, 2015
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013'
"05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
View 9 Replies
View Related
Feb 28, 2008
Hi,
I get "Error converting data type nvarchar to int." It is some kind of SQL exception. This is the code in my application.
SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
Object start;
string activityID = Session["activitygroupID"].ToString();
cmd.Parameters.AddWithValue("@ActivityGroupID", activityID);
cmd.CommandText = "get_startdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
SqlDataReader reader = cmd.ExecuteReader(); //The code stops here!!!
start = reader["StartDate"];
sqlConnection1.Close();
The stored procedure on the SQL-server looks like so. StartDate is of DateTime type.
ALTER PROCEDURE [dbo].[get_startdate]
-- Add the parameters for the stored procedure here
@ActivityGroupID int
AS
BEGIN
SELECT StartDate
FROM dbo.CurrentGroup
WHERE ActivityGroupID = @ActivityGroupID
END
I appreciate any help!
View 4 Replies
View Related
Oct 7, 2015
I am new to SQL and trying my hand at what I thought would be a simple query to bring back the loan products that are not HMDA reportable - hence the NOT IN and I get an error message after adding the NOT IN...I have read a lot of responses to the issue of an error message of 'Conversion failed when converting the nvarchar value' to try and solve the error - and have had no luck. Before I throw my laptop out the window..Here is the error message along with what I thought was a simple query; Conversion failed when converting the nvarchar value '80/10/10' to data type int.Here is the query;
SELECT
P.ProductID,
P.Name,
PC.Name AS [Product Group],
LP.Name AS [Loan Purpose],
CASE WHEN LP.IsHMDAReportableFL = 0 THEN 'No' ELSE 'Yes' END AS [HMDA Reportable],
[code]...
View 5 Replies
View Related
Apr 1, 2008
Exception in one of the stored procs:
Conversion failed when converting the nvarchar value to datatype int. After moving database from 2000 to 2005.
Using backup and restore.
Note I had the same issue after restoring the production database on my local server SQL2000->SQL2000.
The problem was solved by restoring master and msdb from the production. But I can't do this on the SQL2005.
This is not a collation issue as I have ensured collation is the same on all databases including the system ones.
The database in question makes extensive use of user defined data types (Which I have recreated on the destination server).
Can anyone please help?
Raf
View 9 Replies
View Related
Jan 13, 2006
Our database gets updated each week with text files spat out by a mainframe. Previously, the database was in Access; we copied and pasted the text files into Excel, ran macros on them to convert the data, then pasted the results directly into Access and this worked fine for us.
Now that we've moved the tables to SQL Server 2000, we're having problems with the data. We wanted to set up DTS packages for each file to just put them directly into SQL Server. This works for pretty much everything except for the dates.
The way the files are set up, they're comma delimited files with quotes around the text and nothing around the dates. The dates don't have any delimiters; they're just listed like 13012006. Every time we try to import these files into SQL, it gripes about the datatypes; we're trying to put the dates into datetime fields but SQL thinks they're strings. Eek! If we put date delimiters (like 13/01/2006) SQL pulls them in fine, but apparently the mainframe lacks the ability to put these delimiters in by itself and still run everything else OK. The person who writes the extracts has to do it in a language called 'Focus' which I've never heard of and don't know anything about, and he says what I'm asking for can't be done. OK...so now what?
I've tried and tried to convert these strings into dates using both CAST() and CONVERT() and just can't manage to do it. I know I'm missing something really obvious here; does anyone have any tips or advice? Thanks in advance.
View 3 Replies
View Related
Aug 21, 2007
I have a data field that's datatype int and I need to convert it to smalldatetime, however, I get one row erroring out due to a typo in the data that has a date of 21190101. What's wrong with my case statement to set that one typo to 20790101?
'SD' = Case When pro_date > 20790000 Then
pro_date = '20790101'
Else CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) End,
View 4 Replies
View Related
Feb 25, 2008
I getting the above error can someone please help me solve it, here is the code:
public void InsertHost() { // TODO // - Call stored procedure to write to a log file writeToLog using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { SqlCommand cmd = new SqlCommand("writeToLog", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pAction", "action"); cmd.Parameters.AddWithValue("@pHostName", "Hostname"); cmd.Parameters.AddWithValue("@pUserNUm", "requestorID"); cn.Open(); cmd.ExecuteNonQuery(); } }
Here is the storedprocedure:
ALTER PROCEDURE dbo.writeToLog(@pAction varchar(10), @pUserNUm bigint, @pHostName varchar(25))AS INSERT INTO dbo.hostNameLog (action, requestorID, HostName)VALUES (@pAction, @pUserNUm, @pHostName)
Here is the table:
HostName - varchar, action - varchar, requestorID - bigint
I can't seem to find the error.
View 7 Replies
View Related
Jun 1, 2008
i am getting this error when passing multiple checkbox values to next page..
Error is Conversion failed when converting the nvarchar value '3,4' to data type int.
my code is below
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mobiletimesConnectionString %>"
SelectCommand = "SELECT * FROM [device] WHERE [dev_id] IN (@chk1)">
<SelectParameters>
<asp:FormParameter FormField="chk1" Name="chk1" />
</SelectParameters>
</asp:SqlDataSource><asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"
BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"
CellPadding="4" GridLines="Both">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<ItemStyle BackColor="White" ForeColor="#330099" />
<SelectedItemStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<ItemTemplate><div class="overviewpdamain" >
<div class="overviewpdadevice">
<center>
<b><%#Eval("dev_name")%></b><br />
<b>
</b>
</center></div>
</div></ItemTemplate>
</asp:DataList>
View 6 Replies
View Related
Mar 23, 2006
Hi
I have an insert stored procedure with a parameter @eventDate date type date time. I have written a web form that takes the textbox.text value and assigns to the @eventDate parameter using a querystring. Howevr when I run the application I get a SQL Exception error converting data type nvarchar to datetime.
I have tried dssqlInsertEvent.InsertParameters.add("EventDate", convert.todatetime(txtEventDate.text)); wbut received another error.
Any assistance would be greatly appreciated.
View 4 Replies
View Related
Apr 9, 2015
Declare @specificDB nvarchar(max) = 'AdventureWorksDW2012'
,@tablename nvarchar(max) = 'DimAccount'
declare @localtab INT
SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename)))
Print @localtab
Print @localtab
----
Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.
View 2 Replies
View Related
Dec 12, 2014
I am using MS SQL Server 2008R2 along with VB 2010.The first question is: why is it even trying to convert anything to numeric? I have NO numeric data types. And I don't have any nvarchar data types either. I'm very confused.Doesn't nchar include any and all characters, in any combination? Should I change everything to text data type? Maybe something else? Some values are going to be blank. The Lab/Source Lots have numbers, letters and dashes.
My stored procedure:
ALTER PROCEDURE dbo.MChemsInsert
(
@LabLot nchar(10),
@Chem nchar(50),
@Source nchar(50),
@SourceLot nchar(10),
[code]....
View 2 Replies
View Related
Apr 17, 2015
This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]
If I run,
SELECT *
FROM dbo.tblIDNumber
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
AND CAST(IDN_Number AS BIGINT) = 1
SQL Server give me the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.
I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!
UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
To workaround, I ran the query,
UPDATE dbo.tblIDNumber
SET IDN_Number = '123'
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
and then I ran the first query, and SQL Server does NOT give me the same error - Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????
finally, I found the root cause to be an index that the first query uses :
CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
If I remove the index, the first query works without the error.
View 10 Replies
View Related
Aug 27, 2015
I am using rowVersion to detect rows that need to be re-extracted for my ETL. The first step in the ETL is to get MIN_ACTIVE_ROWVERSION() from the database, as well as the rowVersion from the last successful ETL run. Both of these values are saved in variables in SSIS. Since SSIS doesn't have an associated data type, these values are converted to nvarchar(512) before getting sent to SSIS.
I'm now trying to create a stored procedure to grab the data out of the source system. The sproc takes RowVersionMin and RowVersionMax as input parameters. Those parameters are then converted to varbinary(max), and I use them in a query to select the appropriate rows.
When I run the sproc in SSMS, it runs without a problem. However, when I try to use the sproc as a data source in SSIS, I get the following error: "Error converting data type nvarchar to varbinary". Why SSIS has a problem with this, but SSMS does not? The sproc code looks like this:
ALTER PROCEDURE [dbo].[dw_DimComment_DataLoad]
@RunType varchar(3),
@RowVersionMin nvarchar(512),
@RowVersionMax nvarchar(512)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
View 2 Replies
View Related
Jun 2, 2008
Hi,
I'm pretty desperat after trying solving the above problem in several days. I hope somone here can help me :-)
I have this stored procedure. When I try to Execute the storedprocedure where I have a date as parameter I get the above error.
I checked (many times), that the input parameter is a datetime format (30-05-2008) and also I checked the tabel and it is datetime format. I checked the table to see if it was english datetime format - but all the datetimes that are listet are listed in danish datetime format DD-MM-YYYY.
In the stored procedure I try this:
fProjectSkuTimeStamp >= @ReducedTime
I can't figure out where it gets that nvarchar from?
My stored procedure look like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set NOCOUNT ON
go
ALTER PROCEDURE [dbo].[GetProjectSkuList]
@fProjectFId AS INTEGER = null, --ProjektId
@ReducedTime AS DATETIME --Tidsrum fra
AS
BEGIN
SELECT tProjectSku.fProjectSkuPId, tProjectSku.fCompanyFId, tProjectSku.fProjectFId,
tProjectSku.fProjectSkuDate,
tProjectSku.fOurContactFId, tProjectSku.fOurContactF2Id, tProjectSku.fOrderFId,
tProjectSku.fProjectSkuTimeStamp,
tProjectSku.fProjectSkuNote1, tProjectSku.fProjectSkuNote2, tProjectSku.fContactFId,
tProjectSku.fStoreTransactionsFId,
tProjectSku.fProjectSkuSalesPrice, tCompany.fCompanyName, tProject.fProjectName,
tOurContact.fOurContactFirstName + ' ' + tOurContact.fOurContactLastName AS CreatedBy,
tOurContact_1.fOurContactFirstName + ' ' + tOurContact_1.fOurContactLastName AS Responsible,
tContact.fContactFirstName + ' ' + tContact.fContactLastName AS ContactFullName,
tStoreTransactions.fStoreTransactionsCostPrice,
tStoreTransactions.fStoreTransactionsSerialNo
FROM tOurContact AS tOurContact_1 RIGHT OUTER JOIN
tContact RIGHT OUTER JOIN
tProjectSku LEFT OUTER JOIN
tStoreTransactions ON tProjectSku.fStoreTransactionsFId = tStoreTransactions.fStoreTransactionsPId ON
tContact.fContactPId = tProjectSku.fContactFId LEFT OUTER JOIN
tOrder ON tProjectSku.fOrderFId = tOrder.fOrderPId ON tOurContact_1.fOurContactPId = tProjectSku.fOurContactF2Id LEFT OUTER JOIN
tOurContact ON tProjectSku.fOurContactFId = tOurContact.fOurContactPId LEFT OUTER JOIN
tProject ON tProjectSku.fProjectFId = tProject.fProjectPId LEFT OUTER JOIN
tCompany ON tProjectSku.fCompanyFId = tCompany.fCompanyPId
WHERE
(tProjectSku.fProjectFId = @fProjectFId) AND
--fProjectSkuTimeStamp >= CONVERT(datetime, @ReducedTime, 120)
fProjectSkuTimeStamp >= @ReducedTime
ORDER BY tProjectSku.fProjectSkuPId DESC
END
Kind regards,
simsen :-)
View 7 Replies
View Related
