I am running a 'simple' stored procedure from the Query Analyser (command - FindCustomer L18239, [ ]) which should return a name from a customer table from the input id. It does return the name but I keep getting the message
'Server: Msg 245, Level 16, State 1, Procedure FindCustomer, Line 9
Syntax error converting the varchar value 'Kevin ' to a column of data type int.' - Any ideas what I've done wrong?, thanks.
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine 'machine information' Error: 2147749896 (0x80041008).
I've researched the error and found out some people were running batch scripts to reinstall wmi, but none of them worked for me. One of them I gave up on after thirty minutes and it wasn't doing anything.
Please give some suggestions on how I can resolve this issue.
I recently migrated from 2000 to 2005 database. On the new server, I kept getting this type of error message when there is some sort of join involved and they are not complex join either.
Error - "A severe error occurred on the current command. The results, if any, should be discarded."
ie
select A.Cookie1, A.Cookie2 into BookersLookers_Cookie from BookersLookers_DataSet A LEFT JOIN ActionSetup B on A.ActionID = B.ActionID group by A.Cookie1, A.Cookie2
Is there something in the code where I could prevent this error from happening?
Intra-query parallelism caused your server command (process ID #57) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
I've seen MS KnowledgeBase for that (http://support.microsoft.com/default.aspx?scid=kb;EN-US;837983)
But I have some probs with it: 1. I don't have permissions for this kind of queries "USE master EXEC sp_configure 'show advanced option', '1' RECONFIGURE WITH OVERRIDE GO" 2. I don't know what is an "intra-query parallelism"...
I'm attaching the query I use. The query runs OK for some parameters but gets this error msg on other parameters.
I'm using a single CPU on 2003 STD Edition.
Query:
create table #mytemp_table (client_id int not null, commission_value int null) insert into #mytemp_table (client_id, commission_value) select client_id, sum(transfer_value) from users_transfers where transfer_type in (1,3) and is_paid = 1 group by client_id
select u.name [Vendor], u.email, sum(cost) as Payment, cmpd.vendor_id, ua.name [Agent], vd.join_date, commission_value [paid], allow_sign from reports ra left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id left outer join userssb u on cmpd.vendor_id = u.client_id left outer join userssb ua on cmpd.agent_id = ua.client_id left outer join users_details vd on u.client_id = vd.client_id left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id left outer join allow_db asd on asd.client_id = u.client_id where [conditions] group by
[all the group fields] having sum(cost) > 2999 order by u.name
Hey guys, I bet you didn't guess it but I'm posting here because i need help :P
What I'm trying to do is create a procdure that will run a task on the first Working day of the week. Normally monday but then theres public holidays etc etc.
on a normal week the task runs monday, on public holiday weeks it is tuesday, and christmas even pushs it back to wednesday.
so far im using the dts designer to do this
What I have is: a table with the last run date(not sure I even need this now) a table with a list of public holidays(date) along with a year column
a sql statment getting the last ran date and storing it into a global variable glastrundate
a activex vbscript getting the current year, which is then stored into a globalvariable(gcurrentyear)
then gcurrentyear is then used in a select statement to get the public holidays that are in that year, which are then stored to a rowset (gpubdays) (select pubdays from tbl_pubdays where pubyear = ?)
Now the problem, I am trying to use another axtivex script to check the current date against each row in the global gpubdays. well really I just can't get the for each loop to work, was trying to use it like: for each row(or pubday) in gpubdays if day = today then task fails and will not run the rest of the procedure end if next
sorry if it isnt very clear, still kinda new to SQL server
thanks if you help ^_^
ps. please dont go writing all the code for me, just a nudge in the general right direction
I know I shouldn't rely on sysdepends because it's got all sorts of problems with broken dependency chains, etc. But it's better than nothing for finding dependencies, short of rolling your own t-sql parser. So I use sysdepends anyway, with all its faults. I just don't rely on it. anyway, to the point, just an fyi really:
Here's something about sysdepends discovered today that annoys me. If you introduce a dependency of a proc on a table by selecting from the table in the proc, the sql parser discovers this and dutifully adds a row to sysdepends. Very well. But if you do the same select into a temp table, sysdepends doesn't pick up the dependency! Here's what I mean:
use master go
drop database test_sysdepends go
create database test_sysdepends go
use test_sysdepends go
create table MyTable01 (id int) go
create proc MyProc01 as select id from MyTable01 go
create proc MyProc02 as create table #t (id int)
-- comment this next line out and the dependency is picked up. -- but as long as we insert into the temp table, we don't pick it up... :( insert into #t select id from MyTable01
select id from #t go
select so2.name as parent ,so1.name as dependent from sysdepends d join sysobjects so1 on d.id=so1.id join sysobjects so2 on d.depid=so2.id go
Well all bugs are annoying, but this one is particularly annoying because it kills the BIDS session with consequent loss of all changes made since the last save.
Click on a control flow task. Position the cursor so that it changes to the 4-way pointer. Drag the task a little bit. The UI locks up, and then BIDS disappears.
I can't repro this on demand, but it has happened about 6 times in the past month. Anyone else seen this one?
i have a weird situation here, i tried to load a unicode file with a flat file source component, one of file lines has data like any other line but also contains the character "ÿ" which i can't see or find it and replace it with empty string, the source component parses the line correctly but if there is a data type error in this line, the error output for that line gives me this character "ÿ" instead of the original line.
simply, the error output of flat file source component fail to get the original line when the line contains hidden "ÿ".
here is my code the falue of pr_h_reqby is "Test" Dim strconn As New SqlConnection(connstring) Dim myReqdata As New DataSet Dim mycommand As SqlDataAdapter Dim sqlstr As String = "select pr_H_reqby from tbl_pr_header where pr_h_recid = " & recid & "" mycommand = New SqlDataAdapter(sqlstr, strconn) mycommand.Fill(myReqdata, "mydata") If myReqdata.Tables(0).Rows.Count > 0 Then 'lblReqID.Text = myReqdata.Tables(0).Rows("reqid").ToString lblNameVal.Text = myReqdata.Tables("mydata").Rows("pr_H_reqby").ToString() lblEmailVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqemail").ToString() lblReqDateVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqdate").ToString() lblneedval.Text = myReqdata.Tables("mydata").Rows("pr_h_needdt").ToString() lblDeptval.Text = myReqdata.Tables("mydata").Rows("pr_h_dept").ToString() txtbxReqDesc.Text = myReqdata.Tables("mydata").Rows("pr_h_projdesc").ToString() End If
hi, i'm working with LINQ to SQL and i have a table called Account, in that is Type, which is an int in the database but i map it to a AccountType enum, this works fine, but if i go into the dbml designer and change something later on, it gets errors, so i have to change all int->enum mappings back to int, recompile and then set them again! has anyone else had this? kindest regards
I have the following problem in SSIS and it's driving me nuts!!!!!
My situation is as follows :
Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.
The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!
I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.
I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4
The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.
What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!
I have SQL Server Manager 8.00.2039 installed because I have one program(app) installed that requires it be installed. For the first littlewhile when I had SQL server installed it would only show up in thetaskmanager tray, now MOST of the time, it shows up as a popup on mydesktop and also in my task tray when I first start XP (SP2). It staysthere until I close it. Is there a way to prevent the SQL from poppingup?Thanks
I have a report that has rolled-up groups with the "expand row" + sign on each row. Everything works great except when you click the + the report scrolls everything above the + off the screen which completely re-shifts the users context... which nobody likes... how can we turn this "feature" off please?
I am having a problem connecting to my SQL Server 2005 database at the same time with SQL Management Studio Express and from the website at the same time. Everytime I want to view any pages in my site that access the database, I have to close management studio and restart the server for some reason or i get a failed login error message. Is it NOT possible to work on the database at the same time as viewing pages in the website that access the database? This freaking error is realy starting to bug me. I sure hope that there is a work around or something for this.
Hello, I am having problems inserting information into my DB.
First is the code for the insert
Sub AddCollector(Sender As Object, E As EventArgs) Message.InnerHtml = ""
If (Page.IsValid)
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database='MyCollection'" Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As SqlCommand Dim InsertCmd As String = "insert into Collectors (CollectorID, Name, EmailAddress, Password, Information) values (@CollectorID, @Name, @Email, @Password, @Information)"
myCommand = New SqlCommand(InsertCmd, myConnection)
Try myCommand.ExecuteNonQuery() Message.InnerHtml = "Record Added<br>" Catch Exp As SQLException If Exp.Number = 2627 Message.InnerHtml = "ERROR: A record already exists with the same primary key" Else Message.InnerHtml = "ERROR: Could not add record" End If Message.Style("color") = "red" End Try
myCommand.Connection.Close()
End If
End Sub
No matter what I get a "Could not add record" message
Even substituting the insert command string with my stored procedure I would get the same thing
Stored Procedure:
CREATE Procedure CollectorAdd ( @Name nvarchar(50), @Email nvarchar(50), @Password nvarchar(50), @Information nvarchar(3000), @CustomerID int OUTPUT ) AS
INSERT Collectors ( Name, EMailAddress, Password, Information )
VALUES ( @Name, @Email, @Password, @Information ) GO
Can anyone see any problems with this code? It looks good to me but I get the same message always.
Originally I started using SQL Server Management Studio on the server directly. When I would right click on a view->edit I would essentially get a Alter Script of the view. Clicking on view-> design would open the query editor.
However I decided to connect to the server from my desktop instead using a local installation of SQL Server Management Studio. Now when I right click on a view I only get the modify option which opens the Query Editor and not the actual code that can be scripted. I know I can just right click->script view as->alter to to get similar results. I just can't seem to find any options to change this functionality. Does anybody know how to change this or is this a limitation of a desktop installaion?
So on my local server I have a username CWI. I have my main DB: CW.
CWI is the owner of 5 schemas on CW, and everything works great. --- I now go and create a new DataBase called CWTest. I want to now add the user CWI to the security section of CWTest (The same way I did it in 2000). However, now I get the error message: "The login already has an account under a different user name."
When I created my DataBase, IT had the default user, but now I want to add another user so I can create my schemas. --- On our live servers, we will have 100-300 Databases all using the same useraccount as the "God Mode" user.
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables. When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3 FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
Hello, does anyone hate the new interface where you manage the Table Relationships, Indexes, and etc?
I hate it a lot for these reasons. 1) Dialog window cannot be resized (really really annoying) 2) The Table-Relationship configuration dialog window is not as convenient to use as SQL Server 2000 Enterprise Manager.
I hope this is the correct place to provide feedback and I hope this will get modified a bit in the next service pack (or) update....
Hi,I keep getting the error:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@qty' to data type int. When I initiate the insert and update.I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work.. I also tried fiddling with the update code, but I think it is to do with the insert bool as the update works at the moment.. Could someone help?My code:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@qty' WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@User,@productID,@Itemsname,@date,@qty)"; command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", Convert.ToInt32(TextBox1.Text)); command.Parameters.Add("@date", DateTime.Now.ToString()); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteInsert(TextBox1.Text); } } Thanks so much if someone can!Jon
Please help.I have an aspx page with a drop down list(ddlCategories), and a datalist(dlLinks). The drop down lists data property is a uniqueidentifier from a table.When an item in the list is selected it fires the following:SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValuedlLinks.DataBind()The sqldatasource for the datalist runs a stored procedure (below)sp_GetLinks (@CategoryID ?) ASselect * from links where category = @categoryMy question is, what should @Category be declared as if the category column in the table is a uniqueidentifier? And what conversion do I need to do I just can't work it out, as I keep getting the following error:Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.Source Error:
Line 5: Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCategories.SelectedIndexChangedLine 6: SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValueLine 7: dlLinks.DataBind()Line 8: End SubLine 9: End ClassSource File: C:Documents and SettingsKarl WallsMy DocumentsMy WebsAFRAlinks.aspx.vb Line: 7 Stack Trace:
This is the error message I get: :( Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This is the query: Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code, dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee, Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend, GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B, Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F, Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J, Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N, Plan_Mas.CR_O) AS PremiumRate FROM Qual_ins, Plan_Mas WHERE Qual_Ins.CompanyCode = 'ACME' AND Qual_ins.ParticipantCode = 4 AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code Order BY Qual_ins.Ins_Code
Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message. Any help is appreciate! I'm new to this.... "Hello" to all!
I have a dts package that imports data from a comma delimited .csv file.
I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).
So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.
How can I make the conversion correctly during the dts execution.
Using SQL 2005. Getting the following error. Using a cursor to update a table. I need to pass through the table two different times. The fields in the table update fine on the first time through, but on the 2nd time through I get the following error: Conversion failed when converting datetime from character string.
Thank you for your help. David
FETCH NEXT FROM DEQ INTO @GRGR_ID, @SGSG_ID, @SBSB_ID, @PASS1_GENERATION_DATE, @PASS1_TOTAL_AMOUNT_DUE, @STATUS_FLAG, @STATUS_FLAG_INSERT_DT
WHILE @@FETCH_STATUS = 0
BEGIN UPDATE dbo.RPT_DELINQUENCY_TEST SET PASS1_GENERATION_DATE = GETDATE() WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID
UPDATE dbo.RPT_DELINQUENCY_TEST SET STATUS_FLAG = 'B' WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID
UPDATE dbo.RPT_DELINQUENCY_TEST SET STATUS_FLAG_INSERT_DT = GETDATE() WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID
FETCH NEXT FROM DEQ INTO @GRGR_ID, @SGSG_ID, @SBSB_ID, @PASS1_GENERATION_DATE, @PASS1_TOTAL_AMOUNT_DUE, @STATUS_FLAG, @STATUS_FLAG_INSERT_DT END
Hi, can anyone please shed some light on this error:
[OLE DB Destination [466]] Error: There was an error with input column "Price" (518) on input "OLE DB Destination Input" (479). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The column "price" is a numeric (9)
In the flat file connection manager, the datatype for the price column is a float [dt r4]. I've also tried numeric, etc.
Basically I am trying to create a package that will
(A) Create a table with specified datatypes
(B) Use a text Source file for the data
(C) on Success Completion of the "Execute SQL" transform the data from the text into the table.
Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)
It all seems to work now but when I run the package I get the following error
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).
But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.
It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.
Any idea's or suggestions I could try ?
Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)
I have a function that retrieves a data set from a passed SQL string. I'm getting this weird error.
Input string was not in a correct format
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 205: sSQL = "Select * From LedgerDetails Where LedgerID = " & _LedgerID.ToString() Line 206: msgbox(sSQL) Line 207: TransactionDetails = GetDataSet(sSQL) Line 208: Line 209: _TransactionsIndex = CShort(Val(GetDataValue("Select Count() From LedgerDetails Where LedgerID = " & CStr(_LedgerID)))) -1
Source File: C:Documents and SettingsOwnerMy DocumentsUniversityFall 2004DSSASP WorkAlgorithmTest.aspx Line: 207
Stack Trace:
[FormatException: Input string was not in a correct format.] Microsoft.VisualBasic.CompilerServices.DoubleType.Parse(String Value, NumberFormatInfo NumberFormat) +184 Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +96
[InvalidCastException: Cast from string "Select * From LedgerDetails Wher" to type 'Integer' is not valid.] Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value) +211
I have no idea why, but it seems to trying to convert the string into an integer. Both the argument and the parameter in the function are strings. I checked on the internet and the usual response is this is because the SQL is incomplete, but I have it showing me the compiled SQL string, and it is not imcomplete. I've tried hotwiring the query to match something I know will work, and I still get the same error. I've also tried compile the string using .ToString() on my number portion, storing the converted number into a string and only combining strings on the call, using another string variable as a temporary holder, using the String.Concat function, and even CStr. There is no way possible that this thing is an integer when the call is sent...
I'm trying to create a jbo to run in SQL that will update my table DeviationMaster in crcwebauth table, with the value from qvqote in table invoice_tbl in database crcbrio...I get an error in the job that says...SQLSTATE 42000 Error 8114 Error converting data type varchar to numericThe field DNumber in the DeviationMaster table is numeric 9, and qvqote is char 6.I know about the cast/convert, but I havent been able to successfully do this. I was hoping someone could show me how to get around this problem.Here is my current SQL statement that triggers the above error:update crcwebauth.dbo.deviationmaster set ldate = (select max(qvdate) from crcbrio.dbo.invoice_tbl where DNumber = qvqote)
I am using DTS to import a DB2 table from the mainframe and export the table in text format to a shared folder. I want to convert two fields to a date format yyyy-mm-dd. RELSE_Date is in this format and Updtts is a timestamp coming from the mainframe. The text file is all vchar. In dts here is my query pulling from the mainframe SELECT A.PROD_ORDER_NUMBER, A.DYE_SEQUENCE, A.STYLE, A.COLOR, A.SIZE, A.STATUS_IND, A.STATUS_CODE, A.QUANTITY_REC_DC, B.SHIP_OTFQ_QTY, A.MRP_PACK_CODE, A.LABELS_PRINTED, date(A.RELSE_DATE) as RELSE_DATE, date(A.UPDTTS) as UPDtts, A.LOCATION FROM DB2.WP1_PO_CUST_REQ A, DB2.WP1_DYE_LOT_REQ B WHERE A.PROD_ORDER_NUMBER = B.PROD_ORDER_NUMBER AND A.DYE_SEQUENCE = B.DYE_SEQUENCE AND A.STYLE = B.STYLE AND A.COLOR = B.COLOR AND A.SIZE = B.SIZE AND (A.PROD_ORDER_NUMBER LIKE '__K____') When I parse the query it accepts it. When I run the DTS I get an error stating (SQL STATE 220077 SQLCODE -180) The sting representation of a datetime value has invalid syntax. Does anyone have a suggestion on how to convert these fields before the text file is exported or another output format that is similar to .dat in comma delimited format? Thank you.
New to the forum here, it seems like this is a good SQL Server resource...
I'm still somewhat in the learning phases of T-SQL coding, and so far what I've learned has been pretty beneficial...a problem i'm having at the moment though, is with some conversion. I created a stored procedure that basically takes all the records from one table to another. I'll paste the code onto here and show you what's flagging on me (comment about the error within code). It probably seems a bit convoluted, but the UserProfileValue table is something that I TOTALLY wish Microsoft had second-guessed (it's from SharePoint Portal Server 2003)
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER procedure profile_ImportUserProfileData
as
declare profile_curs cursor for select UserProfileValue.RecordID, UserProfileValue.PropertyID, UserProfileValue.PropertyVal, PropertyList.PropertyName, PropertyList.DataType from PropertyList inner join UserProfileValue on PropertyList.PropertyID = UserProfileValue.PropertyID order by UserProfileValue.RecordId, UserProfileValue.PropertyId
open profile_curs fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
if (@@fetch_status = -1) begin print 'End of table' close profile_curs deallocate profile_curs return end
while (@@fetch_status = 0) begin SET IDENTITY_INSERT UserProfileReportTable ON insert UserProfileReportTable(RecordId) values(@RecordId) SET IDENTITY_INSERT UserProfileReportTable OFF
set @OldRecordId = @RecordId
fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
if @DataType like 'nvarchar' set @DataType = @DataType + '(20)'
while (@RecordId = @OldRecordId) begin declare @sql nvarchar(1000) set @sql = 'update ' + @table -- this next line is the one that's crapping out, saying "Incorrect syntax near @DataType" set @sql = @sql + ' set ' + @PropertyName + ' = ' + convert(@DataType, @PropertyVal) set @sql = @sql + ' where RecordId = ' + @RecordId execute sp_executesql @sql fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType end end
close profile_curs deallocate profile_curs
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I also tried cast(@PropertyVal as @DataType) but got the same results. I know it probably isn't the best idea to use a cursor, considering there are 3000+ records in UserProfileValue, but more than likely this procedure will only be run once, and I couldn't think of any better way to do it. Any help would be appreciated.
I'm importing data from a text table, into a temp table, and then on to a final working table. I'm running into difficulty converting data from text into a 'datetime' format. I'm getting the following error:
"Arithmetic overflow error" when trying to convert into a column with the data type "DateTime"
I half expected it to reject all conversions into a Date format because of the source file being in text format, but it allows this conversion in other columns.
If I switch the Data type to 'nvarchar' instead of 'datetime' it converts and pops up with a date format.
My question is: Will this nvarchar that looks like a date behave like a date? For example, if someone tries to perform a calculation with a date format, will the nvarchar suffice, or would it cause problems?
