I can't get sp_executesql to accept more than 10 converted parameters in one execution. A stored procedure loops through a table variable that presents up to 100 parameters and their named data types. I've tried selecting into sql_variant and nvarchar variables and converting them to the named data type in the loop.
sp_executesql is outside the loop.
For the first 10 parameters sp_executesql recognizes the data type conversions of the variables that hold the parameter values, but then it fails to accept the conversions of the 11th and subsequent variables. Is there a limit to the number of conversions that sp_executesql can cope with?
What are the pros/cons of datatype conversions using the advanced properties of the source adapters or transformation components themselves vs. using the data conversion transformation? Sometimes I am able to do all the conversions I need using the advanced properties rather than use a data conversion component, but didn't know if this was considered bad practice, or has some other impact that isn't immediately apparent.
Hi all,I have a DataRow that I'm attempting to insert into SQL. The row has an item I"ll refer to as row["AbsorbtionDeficiency"]. I'm filling this data row from a ODBC connection to a propriatary database. Now I want to insert this data into SQL where the same row data type is "money".I'm not to familiar with the different data types, but the long and short of it is this. When I build my SQL Insert statement, sometimes this value is blank, sometimes it's 0, and sometimes it will have a dollar amount(no formating just the numeric). How to I get this data out of the row in a format that SQL understands... Since I'm creating a SQL string the datatype of the row I assume doesn't matter, so I"m doing a .ToString() when I build the statement.Do I need to do a CAST('0' AS MONEY) to each line that is money type? Or is there an easier way?I would insert the code, but it's a pretty masive statement and I don't think it's really needed yet.ThanksJosh
I'm trying to write some code that will alter column to change its type, in this case from Nvarchar to Int, but I'm not sure if there will be any columns that are uncovertable (say, there's a string that does not have an Int value). In this case, I'd like it to default to 0. Is there a way to write a T-SQL statement to do this, or will I have to write an UPDATE stamement first to clear out any problem cases?
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :
lookup_table1 table1_id description
lookup_table2 table2_id description
This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.
Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.
-- -- Check if the given lookup value exists in the give table / column -- set @sql ='SELECT ' + @lookup_id_name + ' FROM ' + @lookup_table + ' WHERE ' + @lookup_description_col + ' = ''' + @lookup_value + '''' EXECUTE sp_executesql @sql if (@@rowcount = 0) goto new_value if (@@error <> 0) goto on_error return(1)
-- -- Insert new lookup value into the given table / column -- new_value: -- NEED TO CLEAR PREVIOUS NULL SELECT begin transaction set @sql = 'INSERT INTO ' + @lookup_table + ' (' + @lookup_description_col + ') VALUES (''' + @lookup_value + '''); SELECT SCOPE_IDENTITY()' execute sp_executesql @sql if (@@error <> 0) goto on_error commit transaction return(1)
The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.
My attempted solution was to declare a @temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.
declare @temp int ... SET @temp = EXECUTE sp_executesql @SQL if (@@temp = null) goto new_value SELECT @temp return(1)
Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciated
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration
Partial Class Admin_emag_insert Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click Me.VersionTextBox.Text = "" End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click Dim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim ConnectionString As String Dim record_date As Date Dim version As String Dim emagSQL As String
'save form values in variables record_date = Record_DateTextBox.Text version = VersionTextBox.Text
'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open() emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _ "values('" & record_date & "','" & version & "')"
'Create and execute the command objDataCommand = New SqlCommand(emagSQL, objConnection) objDataCommand.ExecuteNonQuery() objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible? I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.
SELECT rsbirthday CAST(rsbirthday AS smalldatetime)
FROM panelists
the error returned is "incorrect syntax near 'rsbirthday'.
I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is
Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc;
try {
dc = dt.Columns.Add("Availability", typeof(decimal)); €¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) 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.
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT (
SELECT MT.MessageId 'MessageId', MT.MessageType 'MessageType', FROM MessageTable MT ORDER BY MT.messageid desc FOR XML PATH('MessageStatus'), TYPE
) FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
Hi All, I struck by a issue, In a table(tblMemberPayments) i had two columns one is to hold (month) as CHAR and other one is to hold (year) as INT. Now i need to implement search for this table based on the dates given , I mean i need to get the records from this table in between any two dates given by the user (Eg : feb 2007 to feb 2008). While retriving i am unable to type cast the things as required for the above case. I am getting an error unable to convert varchar to datetime while using CONVERT FUNCTION the same thing a raises while using DATEDIFF FUNCTION .help me if any one knows the solution . here are the couple of ways i tried 1) 'CONVERT(varchar(10),(tblMemberPayments.ExpirationMonth + tblMemberPayments.Expirationyear),1) between CONVERT(varchar(10),''feb 2007'',1) AND CONVERT(varchar(10),''feb 2008'',1)' Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'Apr' to data type int. 2)'datediff(m,Cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2008'') >= 0 and datediff(m, cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2007'') <= 0' Thanks & Regards, Sudheer.Y
In my DB I have strings which are all 8 characters long and all pass the isDate == 1 thing so everything row can be converted into a date.
When I run a select convert(datetime, value) it gives me an error saying that it's unable to convert datetime from string. However when I insert everything into a temp table and run each row individually, it converts fine.
Any ideas as to what I'm doing wrong? I've checked the date settings on SQL 2005 SP2 and the host itself and everything seems correct. I've been stuck on this all day and any help is very much appreciated.
I have this query where I do certain conversions and on top of this how can I sum the column values.
'$ '+ Replace(CONVERT(varchar,CAST(Total_Amount AS money),1) ,'.00','') as Total_Amount, '$ '+ Replace(CONVERT(varchar,CAST(Monthly_Amount As money),1),'.00','') as Monthly_Amount,
Hi,I'm trying to load date fields into SQLServer using DTS, but theformat of the raw data is the number of days since 1 Jan 1900. How doI convert this to a useful format, is there a standard conversionroutine?ThanksTim
Having some trouble converting strings to dates. I am using a .dtsx package and have figured out how to use a derived column to convert the string to a new string which is a valid date. The problem is now converting the column data type to date. I insert a new data conversion step to convert the vchar field to date, then when it runs it fails on this step. Error is
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."
If I leave out the data conversion step and run the package, then manually change the data type to date it warns with the same message, but if I accept it converts correctly.
I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure....... The value pstrLoginName is a public string defined in a code module The stored procedure is NVARCHAR Datatypes.....
@DeleteScan=UserP_DeleteScan from Clinic_Users where UserName = @LoginName and UserPassword = @Password
GO
Here is my error
Specified cast is not valid Line 194: cmdsql.CommandType = CommandType.StoredProcedure Line 195: Line 196: parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName Line 197: parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword Line 198: parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit)
my vb .net code to execute this
Public Sub ObtainPermission()
'get the needed data Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest") Dim cmdsql As New SqlCommand Dim parmLogin As SqlParameter Dim parmPassword As SqlParameter Dim parmDelete As SqlParameter
cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql) cmdsql.CommandType = CommandType.StoredProcedure
parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <----error occurs here
parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <---I'm sure it will happen here then parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit) parmDelete.Direction = ParameterDirection.Output
It was my understanding earlier that -- it is mandatory to have a Derived Column for a DATE String in a Flat File to be loaded properly into a table with "datetime" column type
However, i tried running my package with the Input Column as "DT_STR" and the Destination on the table is DATETIME.
Looks like the process went on fine.. Am i missing something here?
My Date Format from the source looks like -- "DD-MON-YY"
I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?
I have multiple columns in a table that I only want to convert if they are not null or of the proper type. I don't really want to redirect the whole row if one of the conversions fails, rather I would just not want to do the cast or set it to another value. In this scenario is it better to use a custom script or multiple tasks? The multiple tasks seems like a lot of overhead for processing the same data. Just curious how others handle this scenario as it seems as though it has come up quite often on our current project? Thanks for the assistance.
-Krusty
PS: Is it possible to embed evaluation statements along with conversion statements in a task expression? e.g. (len(trim([Column1])) > 0) substring([Column1], 1, 4)
I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.
I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.
