I am importing via DTS a .csv file. I have 2 issues:
-One field from the source needs to be inserted into two existing fields.
-Two fields from the source, First_Name and Last_Name need to be
concatenated to a destination field, Full_Name.
I do not know VB but I do know SQL.
Can anyone help?
i don't know if this is the right forum for this question but here goes. I have created a gridview to display a list of records retrieved by a SQL query. Everytime I run it, I get the following error:
Object must implement IConvertible.
I thought it might have to do with the data I'm passing in the session string from the previous page. Here is that code:Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Session.Add("Shift", shShift.SelectedItem)Session.Add("Type", shType.SelectedItem)
Session.Add("SelDate", OccDate.SelectedDate)
Response.Redirect("SelectIncRep.aspx")
Here is the code that is supposed to execute and display the data on page_init:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="RID" DataSourceID="SqlDataSource1" Width="1215px" BorderStyle="Groove" BorderWidth="4px">
<Columns>
<asp:BoundField DataField="RID" HeaderText="Record ID:" InsertVisible="False" ReadOnly="True"
SortExpression="RID" />
<asp:BoundField DataField="Date" HeaderText="Date:" SortExpression="Date" />
<asp:BoundField DataField="Shift_ID" HeaderText="Shift:" SortExpression="Shift_ID" />
<asp:BoundField DataField="Xref_ID" HeaderText="Type Of Occurence:" SortExpression="Xref_ID" />
<asp:BoundField DataField="Notes" HeaderText="Notes:" SortExpression="Notes" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PerfDataConnectionString %>"
SelectCommand="SELECT [RID], [Notes], [Date], [Shift_ID], [Xref_ID] FROM [Safety_data] WHERE (([Shift_ID] = @Shift_ID) AND ([Xref_ID] = @Xref_ID) AND ([Date] = @Date))">
<SelectParameters>
<asp:SessionParameter Name="Shift_ID" SessionField="Shift_ID" Type="Int32" />
<asp:SessionParameter Name="Xref_ID" SessionField="Type" Type="Int32" />
<asp:SessionParameter Name="Date" SessionField="Date" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
What could be causing the issue?
Datediff won't give you an age.
For example:
SELECT (((DATEDIFF(DAY,BIRTHDATE,GETDATE())) / 30) / 12) as AGE
is CLOSE but not accurate. Is there another function I should consider in determining age?
Semper fi and Thanks!
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _
cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"
cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"
cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"
cstring = cstring + "service_request, store_number_senditem, register_number, street_address"
cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"
cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"
cstring = cstring + "notes, client_number)"
cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _
this is the error is get, but i did the same thing on a select statement and it works fine...do i need to add something to the string or what i am kinda confused and help would be great.....
Operator '+' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.
Hi!
While working for a client on a SQL Server 6.5 SP5a, I got the following error when running the code below in first SQL Enterprise Manager 6.5 and then SQL Query Analyzer 7.0:
IF @Departures = 1
DECLARE TableCursor CURSOR
FOR SELECT AcType,
BackPax = CASE BackPax
WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax
WHEN NULL THEN 0 ELSE BestPax END,
DepTime,
FlightNumber,
ArrStn
FROM #TimeCall
ORDER BY DepTime, FlightNumber
ELSE
DECLARE TableCursor CURSOR
FOR SELECT AcType,
BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END,
ArrTime,
FlightNumber,
DepStn
FROM #TimeCall
ORDER BY ArrTime, FlightNumber
And the error I get when the query is run for the first time after switching tool:
Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 178
Internal error -- Unable to open table at query execution time.
Server: Msg 202, Level 11, State 1, Procedure CreateFile, Line 188
Internal error -- Unable to open table at query execution time.
If I run the query again in one of the tools, it works. It also works if I use WITH RECOMPILE in the stored proc header.
If I use the code below, it also works, and without RECOMPILE:
DECLARE @SqlStr varchar( 255 )
IF @Departures = 1
SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' +
'FOR SELECT AcType, ' +
'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' +
'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' +
'DepTime, FlightNumber, ArrStn ' +
'FROM #TimeCall ORDER BY DepTime, FlightNumber'
ELSE
SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' +
'FOR SELECT AcType, ' +
'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' +
'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' +
'ArrTime, FlightNumber, DepStn ' +
'FROM #TimeCall ORDER BY ArrTime, FlightNumber'
EXECUTE( @SqlStr )
Trying to get around the problem with the following code did not do any good:
DECLARE TableCursor CURSOR FOR
SELECT AcType,
BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END,
CurTime = CASE @Departures
WHEN 1 THEN DepTime
ELSE ArrTime END,
FlightNumber,
OtherStation = CASE @Departures
WHEN 1 THEN ArrStn
ELSE DepStn END
FROM #TimeCall
ORDER BY CurTime, FlightNumber
Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 176
Internal error -- Unable to open table at query execution time.
Anyone have some good ideas on why this happens?
Brgds
Jonas Hilmersson
I installed RS2005(SP2) with a SQL2000(sp4) db. Both are on seperate Windoes 2003 servers. I configured the reports manager to connect to a different web site on the same server instead of default web site. There were no installation prohlems. The RS server is up and running , all the virtual directories setup etc. When I try to access the reports directory via: http://dev2/Reports or browse the reports virtual directory from IIS, I get a 401 access denied error. The RS log reads:
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.3042.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Daylight Time</TimeZone>
<Path>D:ReportingServices2005MSSQL.1Reporting ServicesLogFilesReportServerWebApp__07_10_2007_11_29_21.log</Path>
<SystemName>NWISTCST99</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
<OSVersion>5.2.3790.131072</OSVersion>
</Header>
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing ReportBuilderTrustLevel to '0' as specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing ProcessRecycleOptions to default value of '0' because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing WatsonFlags to default value of '1064' because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing SecureConnectionLevel to default value of '1' because it was not specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!1!7/10/2007-11:29:22:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False' because it was not specified in Configuration file.
w3wp!ui!5!7/10/2007-11:29:30:: e ERROR: The request failed with HTTP status 401: Unauthorized.
w3wp!ui!5!7/10/2007-11:29:31:: e ERROR: HTTP status code --> 500
-------Details--------
System.Net.WebException: The request failed with HTTP status 401: Unauthorized.
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!5!7/10/2007-11:29:32:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
Please advise.
The RSweApplication.config in the ReportManager folder has the fully qualified URL to ReportServer<ReportServerUrl> and I have blanked out the <ReportServerVirtualDirectory>
Also the <UrlRoot> in rsreportserver.config in ReportServer folder points to the fully qualified URL to ReportServer.
What am I missing here ..?
Hi,
I have a linked server on two of my other servers. On one server, I can run linked queries just fine. On the other, I get the following error:
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'LOCATOR_ID' with incorrect bookmark ordinal 0.
I have SQL 7.0 SP2 on both of the linking servers, and the linked server is SQL 6.5. Both querying servers are also using MDAC 2.1
Any insight anyone could provide would be appreicated.
Joe
I am trying to import a database into Sql 6.5 from Access 97`.
How shall I go about this ?
Thanks.
mark.
Hello everyone and thanks in advance.
Ok, here's the deal. I am 1 of 2 developers at my organization, the other is a COBOL programmer. Long story short, every night he sends me a 22MB XML file containing our company's inventory. This contains about 40,000 items. In C#, I'm basically looping through all the XML nodes and on each node I load up an object with the particular properties (manufacturer, SKU, serial number, etc.) and call a Stored Procedure to to save the item.
In saving the item, I'm checking to see if it was already in my inventory and I either perform an update or an insert on the table as approriate. The query does a couple of other things like retreiving the Purchase Order Number, Cubic Feet, etc. from other tables. All in all, the query takes about 1-2 seconds to complete. I've optimized the query as much as I know how (including the addition of indexes), but this still takes at least 6 hours to complete the 40,000 items. Is this the best approach? Any other suggestions?
So far the only things I've thought about changing is:
1. Break it out into multiple queries to avoid poor compilation of execution plans
2. Multithread the C# app so that I'm running like 10 queries at once.
3. Get the COBOL programmer to give me a delimited file and do a BULK INSERT on it into an empty HEAP, then run a single INSERT and a single UPDATE (using CASE statements)
Again, Thanks in advance!
Hi everybody:
I want to know how to import to SQL Server an XML File
Thanks for your help and quick answer.
Cristopher Serrato
Has anyone any idea what could be causing this problem.I've a large txt file, with each item on each row seperated withquaotaion marks and seperated from each other by a comma.I can import it no problem to Access, but when I try to import it toSQL server, SQL Server gives me that "cannot find.... within first 8KB"of data error.Any ideas?Ciarán
View 2 Replies View Relatedorange May 14, 5:16 pm show optionsNewsgroups: comp.databases.ms-accessFrom: "orange" <orange...@mail.ru> - Find messages by this authorDate: 14 May 2005 14:16:49 -0700Local: Sat,May 14 2005 5:16 pmSubject: import complex dataReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report AbuseI've got a database in .txt file similar to this:BookName;Author;Year;ReviewedBy;Rating;PagesNemesis;Isaac Asimov;1989;13,31,24;good;110Sense & Sensibility;Jane Austen;1970;45,32;great;120The Bicentennial Man;Isaac Asimov;1965;14;excellent;124...the data in field 'ReviewedBy' should be connected to names of peoplethat read the book. I'd like to have a separate table in Access likethis:Id;Reviewer13;Bob...24;Jim...31;TomHow do I import that data into database?
View 1 Replies View RelatedHi i'v e installed the file: SQLServer2005_DTS.msiBecause i've heard that i need that to import a csv file into a ms sql database. now i have no idea how to work it, like to even make it open.Where would i open it from? I reckon i'v eread enough literature about using it, to be able to have a bash... thanks in advance...
View 3 Replies View RelatedHi All,Here is my code Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader") con = New SqlConnection(strCon) con.Open() Dim strselect As String = "" Try strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)" strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')" cmd = New SqlCommand(strselect, con) cmd.ExecuteNonQuery() con.Close()i have defined connection string in web.config file and my csv file is inside C:UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."Regards
View 1 Replies View RelatedI have created a DTS package for SQL Server, saved this as a VB file and upgraded this to the .NET framework.
It is now saved in a file called Shortages.vb.
What I want to do now is add this to an existing ASP.NET project and be able to call this DTS function by the click of a button.
Is this possible and how can I go about it? Can I just add it to the Click event of a button?
Hallo every one,
I', working for a school project.
The setuation is an old database that has to be repaird.
All the data from each table of the old data base are stored in different tabdelimited text files.
I designed a new database, but the tables are a little different.
When I try to import the data I get some errors, because of the changed tables.
An example of my new setuation is a new table thas as colums from 2 different tabdelimited text files.
My question is, how do I solve thos problem!
Do I have to use DTS?
What do I have to do.
tnx
icheron.
Hi folks,
I am having importing problem, when i tried to import tables from sybase to Sql server 7.0, it is not importing the keys, it is creating only datas without keys. Do u know any way in solving this issue?? My next issues will be on Serial number generation. In sybase we have Number (*), if we do
select number(*) from a it will show the sequence numbers, but in Sql server what is the equivalent function to show the sequence number when i am using a select statement.
Can u folks solve both the issues...
thanks and appreiciating u folks...
rgds,
VJ
I have an excel file with 5 columns. 1st column values are -
^^234
^^456
678
123
456
SQL 2000 DTS, doing straight copy into an Access table takes only first two
rows from 1st column ( see top 2 rows with trailing white spaces). How can I
import all of rows ?
Thanks for your assistance !
_Ivan
Hi, Is there any way , we can bulk import sql server logons with password?
Thanks.
I have a project at work that requires me to transfer all the data, including the queries, forms, macros, and modules from Access 97 to SQL Server 7. I was able to import the tables only using DTS wizard. Is there a way to copy the queries, forms, macros, and modules?
View 2 Replies View RelatedHow can you can import results into table from system procedures.
For Ex. if you exec sp_spacesued, how do I import the results into the table.
Thank You
Hi, i've struggled in the past to get ms sql 7 db's from one server to another server and always end up getting frustrated. I usually just export and then import..... more work but it does the trick.
Now i've been sent one single db data file that i'm told is a backup copy of someones database. How do i get this loaded onto my server. I've tried restoring from this file but it complains that the backup file is not for the database i'm restoring to.... which is correct.... this has to be something that many people deal with? isn't it?
Can someone please help me out.... some pointers would be much appreciated.
Thanks in advance,
Mark.
Hi, this is my first time and was wondering what's the best way to do the following:
i've sql server 6.5. also have a sql database(.DATfile) in a folder.
i need to import this database to my server. should i just LOAD/import it? or do i need to first create a database device on my server(how much size should i allocate etc) and then load it??
thanks a lot
rohit
I use ms sql 2000 and i wanted to import a new backup database into a new ms sql 2000 on a different machine but it does not work so i wanted to know if it can be done or i have to do something else thank u.
View 3 Replies View RelatedHi guys,
I have to write a Store procedure which will pickup a txt file from a destination, read it and update some table.
How to pick and read a txt file in a Stored procedure.
Thanks in advance
Vineet
I need to setup a dts package to import all tables from an Access 97 database into a SQL 2000 database. Do I need to specify each table in the package or is there a better way to grab all the Access tables?
Thanks
I have a MS SQL table that I want to import into a test db in my SQL 2000. The table is filename.sql and has the script in it to set up the table, etc.
How do I import this table into the sql 2k db?
I tried the wizard and I cannot get a choice to locate the file. How does one go about importing a copy of a sql table? The help file wasn't much of a help, thanks for your input.
Is there any way at all to import from Excel to MS SQL without using the DTS package and without doing it manually? I really do not like the lack of flexability that DTS has and I need something else that will always work. Basically I am importing shipping documents that are in excel and putting them into a table. This has 2 issues, 1 I don't know the file name, just the folder it is in and 2 there is a large amount of header data in the file, some of which I need, some of which I don't. Is there any solutions out there? Thanks!
View 1 Replies View RelatedI have an identity question. I have an ID field with identity set to 'yes (Not for Replication)' with the increment of '1'. I have a range of IDs that skipped at one point so I have a range from 1-500, then the rest starting at 6000. I want to fill in some of the rest when I import new data.
Let's say I have 300 records. I want to start the ID at 501 and go to 801. How do I do that when I'm importing.
Thanks in advance.
~ Doug
Hi there, could let me know how to reference to an excel database in SQL code?
I think
Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:MyExcel1.xls',Sheet1$)
Would import everything from a spreadsheet but im not sure how you can reference individual columns?
I need to copy the data in two spreadsheets into two
tables in "SQL Server 2000". Below are the details:
One spreadsheet contains the data that needs to be added
to an existing table in SQL server. All field names in the
spreadsheet match the DB table column names.
Another spreadsheet contains the data that needs to be
copied to a new DB table. The table currently does NOT
exist in the DB.
I'm not sure how to accomplish this. ANy help would be
appreciated.
Hallo.
Is there a way to create a general dts to import tables from access mdb file?
so that i will not have to change the dts for every table i am adding to the access file?
something like foreachtable? or a way to read the tables list from the access file in the sql connection?
i am importing to sql server 2000.
thanks
Hi Gurus.
My client drops for me many files like this on a shared drive M: daily
1_Test.csv
2_Test.csv
3_Test.csv
I would like you to advice on how to write a SQL code (that can include DTS if possible) that will take this files, merge them into one (since they have same columns) and send them to another client as one file. But it must only take the files of the same date and must not resend files i have send already. I need this to be an automated process since the files are dumped into M:drive midnite and I need this code so that I can schedule it as a job and run around 4h00am.