DBTYPE_DBTIMESTAMP Error Converting Legacy .dbf
Apr 28, 2008
I am trying to import a legacy dBase III file (.dbf format) into SQL server. The file contains timestamp fields which, as implemented in the dBase data file format, are actually eight-byte character strings. I am using this command:
SELECT * INTO LegacyData FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:Files','SELECT * FROM data.dbf')
The command fails with this error:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
This is happening because some of the datetime fields contain strings that can't be parsed by SQL as valid dates and times. The legacy application which created the data file apparently indicated a missing timestamp by storing "- - " as the character string.
If I change the select statement to say "select top 2 *" to only import the first two records (neither of which happen to have any invalid datetime values), the records are imported successfully. What I would like to do is to import all records and either skip those records that have a bad datetime value or, better yet, import all records converting invalid dates to null values.
I tried changing the select statement to include various types of casts but it seems that because the .dbf file indicates that the data field is of time timestamp, SQL will always try to read it as a datetime field regardless of how the select statement is written. I don't currently have any way of modifying the dBase III file or I would attempt to search for and remove the offending records.
Does anyone know of a workaround for such a situation? Is there a way I can import the data using SQL server or will I need to find a dabasebase conversion utility that can handle unparseable date strings?
Any suggestions would be appreciated. Thanks
View 12 Replies
ADVERTISEMENT
Jul 7, 2004
I'm getting the error Error converting data type DBTYPE_DBTIMESTAMP to datetime when I try to import a date field from Oracle to SQL Server.
SELECT CAST(cancel_dt as datetime) FROM OPENQUERY(orcldb, 'SELECT cancel_dt FROM tablename WHERE id= 12345')
The date in Oracle is 19-JUN-99, but it's coming over as 06/19/0999 and it throws the error because the year 999 is invalid in SQL Server. Any ideas on why Oracle or SQL server would convert the year to 0999 instead of 1999?
This is also happening on a value 24-SEP-07 coming over as 09/24/0207
Most other date values work, so I'd really like to get in depth into the process that occurs when converting to a SQL Server datetime.
View 7 Replies
View Related
May 4, 2006
Hi
I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.
[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".
Please help.
Thanks in advance,
Ramzee
View 12 Replies
View Related
Apr 22, 2007
Hi
I am trying to migrate data from oracle to sql server 2005 in sql server management studio express, most of my data in oracle have been successfully moved into sql server 2005 by using storage procedure. But some of data can't move across becasue i got this error:
Error converting data type DBTYPE_DBTIMESTAMP to datetime
Does anyone know why? Please help?
Thanks
Li
View 1 Replies
View Related
May 18, 2008
Hi
I have a linked server to MSAccess DB and, when I try to select record from a table that have a column
with a bad date formatted (with year less than 1753) I receive the message:
.... conversion error from DBTYPE_DBTIMESTAMP to datetime....
My scope is set to NULL this bad values from Sql Server ...
I first try with something like
UPDATE LNK_SVR...TABLE SET FLD=NULL WHERE ISDATE(FLD)=0
But I receive the same error... perheaps the provider generate the error before an Sql Server evaluation ...
So I think to a passthrought query:
exec sp_serveroption @server='LNK_SVR',@optname='rpc out',@optvalue='true'
exec LNK_SVR...sp_executesql 'update table set FLD=NULL WHERE YEAR(FLD)<1753'
But I receive the folowing message ....
Messaggio 7213, livello 16, stato 1, riga 1
Il provider non รจ riuscito a passare parametri di stored procedure remota al server remoto 'LNK_SVR. Verificare che il numero, l'ordine e il valore dei parametri passati siano corretti.
Any suggestion is appreciate.
Thanks in advance
Matteo
Mds
View 10 Replies
View Related
Jun 1, 2007
Hi all,
the SQL Management Studio keeps crashing out on me everytime i try to open a Lookup in one of my DTS packages i am using in the Legacy section.
I am copying the DTS package across and need to change the server connections (which i do), but then i was getting a permissions based error when the package ran when it tries to access the Lookup.
I tried to open the Lookup and the SQL Management Studio hanged when it tried to display the details for the lookup. It's done this many times and i have tried different files incase one was corrupted to no avail.
Anyone have any ideas of what i can try?
Thanks
Jordan
View 2 Replies
View Related
Mar 22, 2007
$exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
View 3 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Apr 20, 2007
Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07
View 2 Replies
View Related
Feb 12, 2005
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---
Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN
--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--
SET NOCOUNT ON
DECLARE @error int, @rowcount int
--
-- Make sure that there isn't already a user with this username
--
SELECT userID
FROM users
WHERE username=@username
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END
--
-- Set expiration date
--
DECLARE @expDate AS smalldatetime
IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END
IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END
DECLARE @paidCopies AS decimal
SET @paidCopies = 5
--
-- Add this user to the database
--
IF @acct_type <> "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
IF @acct_type = "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
COMMIT TRAN
RETURN 1
GO
---
Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)
Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand
db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()
cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
cmd.ExecuteNonQuery()
(...)
---
View 1 Replies
View Related
Jan 7, 2004
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO
Thanx in front
Cya
View 14 Replies
View Related
Mar 16, 2007
Hi,
I have imported 3 DTS from SQL 2000 to the SQL 2005 server. The wizard went fine, everything is ok. When I close the wizard window, I cannot see any of them. If I reimport it, it asks confirmation to override it. Where are the 3 DTS in the management Studio if they are not under Legacy DTS?
Thanks
View 4 Replies
View Related
May 30, 2006
I am in the process of migrating to a new SQL 2005 server. I have a number of DTS packages on my SQL2000 server, approximately 200, that are used on a daily basis. I used the migration wizard to migrate the packages from the 2000 server to the new 2005 server however there are issues with the way some were brought over. I would like to have all of the packages moved from the 2000 to the 2005 server and appear under Legacy DTS so that I can run them as 2000 DTS packages unitl I have a chance to correct the issues.
Here is where my question lies. The migration wizard migrates upgrades all of the packages. How do I move them from the one server to the other and perserve their 2000 DTS format? The servers are on 2 separate boxes with different instance names. Everything I've read tells you how to run the legacy packages but nothing seems to explain on to move them.
Any help would be appreciated
Pete
View 5 Replies
View Related
Oct 4, 2006
How can I scheudule a legacy DTS 2000 package if it stored in SQL Server itself?
I assume the package 'lives' in the msdb database.
For what bizarre reason is there no option to schedule legacy packages anyway, why provide the DTS legacy/designer components if the ability to schedule them isn't possible?
Is this microsoft's subtle way of telling me that I should convert them to SSIS packages ...I just don't have the time to do that...help
Thanks
View 5 Replies
View Related
Mar 8, 2007
Hi everyone,
I am trying to migrate DTS packages from sql2000 To SQL2005 server. I am running the migration wizard from Data Transformation services under the ManagementLegacy Node on SQL 2005 server. I get to choose the packages to be migrated, but each of the selected pkg ends with a progress of "STOPPED" in the wizard and the outcome of the wizard shows as Successfull with a chaek mark on Top Left corner.
But No packageg appear under the location reff. above. I like to know if anoyone has a solution for this issue.
Thanks
sekhar
View 1 Replies
View Related
Apr 20, 2007
I have changed a legacy dts 2000 package. It resides on a sql 2005 server. How do I schedule it? That option doesn't look like it is available for legacy 2000 dts packages.
View 4 Replies
View Related
Mar 24, 2006
I have a legacy DTS package on my test SQL Server 2005 in the ManagementLegacyData Transformation Services folder. I can run the package, but how can I schedule it?? this doesn't appear to be an option anymore like it was in 2000.
Thanks
View 4 Replies
View Related
Mar 6, 2007
Hi,
I am converting several DTS pkgs to SSIS. Several of the pkgs contain complicated "Active X script" transformations on text files. That is, it would take me a long time to have to re-write this!
In the meanwhile, do you think it's just best to use the EXECUTE DTS 2000 task until I have a better grip on SSIS??
Also, what is the equivalent of "Active X script" validation in SSIS?
For example, I have an Active X script that checks the values of a particular column in a text file. If the column contains a datefield, then load into the database, if not, then discard... what task in SSIS would replace this logic? (not now, but for later reference)
Thanks so much
View 1 Replies
View Related
Sep 7, 2007
Hi Guys
There are a few dts packages in our sql server 2000 database which we schedule daily for at night for business purposes.
how can i schedule the same DTS packages in Legacy SQL 2000 DTS when we migrate the Databases across to Sql 2005.
Can that be done or we need to migrate the DTS Package to SSIS
Vic
View 1 Replies
View Related
May 22, 2006
My manager wants me to produce a legacy dts-style display of an executing package in an asp.net grid view. It would be color-coded the same way: red, green, black showing the status of each step with start and finish time. Any ideas on how to do this?
View 3 Replies
View Related
Dec 29, 2005
My packages are migrated over our new 2005 server. How do I schedule/run them? BOL seems to suggest that we replace the dtsrun commands with the dtexec one. Am I on the right track?
View 6 Replies
View Related
May 1, 2008
Hi,
We are in the process of converting legacy DTS packages to SSIS. we have some script like this :
If trim(DTSSource(col1)) = "" then
DTSDestination(DestCol) = null
DTSDestination(errorcode) = "1"
else
DTSDestination(DestCol) = trim(DTSDestination(DestCol))
I am re-writing like this:
trim(Row.col1) = "" then
Row.DestCol_isnull = true
Row.errorcode = "1"
else
Row.col1 = trim(Row.col1)
But, this is not writing NULL to the column, writing just nothing to the column. I want to see NULL in my columns. How do I achive this using script ?
Thank for the help!!
-V
View 1 Replies
View Related
Apr 9, 2008
if we see provider=sqloledb in the connection string of a legacy sql server 2000/2005 system we're trying to understand better, can we conclude that ODBC is definitely not in the client picture, that possibly OLEDB is and that SQL Native isnt? Can we conclude for sure what middleware must be in use?
My limited understanding of this "middleware" space is that ODBC is old and restrictive, OLEDB is a lot more state of the art and general, and that SQL Native is more proprietary than OLEDB.
Even if we can conclude what middleware must be in use, is it generally as simple as changing a connection string and ensuring installation of the preferred middleware in upgrading to better performing middleware?
View 6 Replies
View Related
May 29, 2007
Hi,
I am not able to delete the Legacy maintenance plans created:
for example I created a maintenance plan: "MyPlan" with:
master.dbo.xp_sqlmaint
and the plan created at:
ManagementLegacyDatabase Maintenance PlansMyPlan
After this I tryed to delete this using SQL Server Management Studio, it is
deleted (right click on the plan and select delete menu item)
But when I refresh the server, the "MyPlan" is back again.
Any idea how to delete Legacy Maintenance plans?
Thanks,
Venkat
View 4 Replies
View Related
Sep 13, 1999
Hi..,
There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome...
Thanks in advance..
View 2 Replies
View Related
Apr 11, 2008
I am unable to acces Oracle from legacy DTS designer, here is what I have done,
1. I have kept my old DTS packages in SQL2K5 legacy DTS container
2. Now I am unable to access oracle; it gives provider error
3. I am able to connect oracle from SQLPLUS
How can I troubleshoot this problem?
------------------------
I think, therefore I am - Rene Descartes
View 7 Replies
View Related
Sep 12, 2007
Hi Experts
I am trying to schedule a legacy DTS 2000 package in 64 bit Sql Server 2005.
I cannot use the DTSRun tool on the Command prompt of the Sql 2005. What about dtexec ?Can i run Sql Server 2000 DTS packages from the Command Prompt.
Is DTS 2000 the only way to do that?
Any Advise is greatly appreciated
Vic.
View 5 Replies
View Related
May 13, 2008
Hi All,
One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server..
At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
View 2 Replies
View Related
Jan 4, 2008
Declare nvarchar(1000)
Select @Number = 'Select Convert(varChar, Data_Machine_Id) From dbo.ADMN_System'
Exec(@Number)
Set @idMachine = Replace(Str(@Number, 10, 0),' ', '0')
I keep getting this error and the Data_Machine_Id is a Numeric field?
Error converting data type nvarchar to float.
View 9 Replies
View Related
Nov 17, 2005
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
View 3 Replies
View Related
Oct 4, 2007
We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
Thanks
View 6 Replies
View Related
Jul 16, 2007
Hello,
Firstly, i need to work out why I cannot change my datatypes(please see query)
Code Snippet
SELECT * FROM (
SELECT top 10
ref,
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM (
SELECT 1 seq,
'ref' ref,
'RecordDate' RecordDate,
'TransactionID' TransactionID,
'TransactionStatus' TransactionStatus,
'StatusChangedTimeStamp' StatusChangedTimeStamp,
'PartyTransactionStatus' PartyTransactionStatus,
'BadDeliveryReason' BadDeliveryReason,
'TradingDaysRef' TradingDaysRef
UNION ALL
SELECT 2 seq,
cast(ref as bigint),
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM dbo.ParticipantTradeStatusChange
) x
order by seq, RecordDate
) y
The error returned is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
The reason for me doing this, is exporting both column names & data to a xls file.
Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do
SELECT top 10 * FROM (
SELECT *
It falls over and dies
Help much appreciated
thanks,
i
View 3 Replies
View Related
Jan 31, 2008
Good Morning Forum
First time poster here, but I have gleaned much needed support using the forum in the past, so
many thanks to all.
Well, on to business.
I have a strange problem with a T-SQL stored procedure my company uses for reporting.
Around the Christmas holiday period, it just stopped working.
I managed to track the error down to the code below:
(Just the code to recreate the error.)
declare
@g datetime,
@g2 datetime
select @g2=getdate()
select @g=convert(datetime,convert(varchar(10),@g2,101))
select @g,@g2 , SERVERPROPERTY('PRODUCTVERSION'), SERVERPROPERTY ('PRODUCTLEVEL'), SERVERPROPERTY ('EDITION')
When this is run, I get this error message:
Msg 242, Level 16, State 3, Line 6
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the About information from .
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.1.2600
I know this is not the best way to use datetime or conversions, but I did not write it, I am just supporting it.
The strange thing is, the above code has worked every week since January 2006?
The even stranger thing is, it runs on perfectly well on Server 2000 and on the version below that has not had
any recent service packs installed.
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
Is this a bug, or have I lost the plot?
Thanks in advance for any feedback.
P.S: The style code 101, if changed to 103 allows the code to work. But, when apllied to the pre-RTM server, is
returns the same error message.
View 7 Replies
View Related
