Hi i have a csv file where the last column usually happens to be null. So my file is like this:
1,2,3,,{LF}
4,5,6,,{LF}
7,8,9,,{LF}
{CR}
I run the dts export from csv file to db and it doesnt save the last row (column5) in the table us null. instead it stores the {LF} character. Ascii =10. How can i resove this problem.
the csv comes from a third party application. the DTS needs to un automatically and somehow resolve that last problem on its own. but how??? is there a code that can trim the last character in the last line to delete the last {LF} or some query in DTS that can achive this.....
Please let me know within Reporting server database which table stores the actual report name,keywords and author of the report.
I want to search the report based on the report name using my own custom code.It will be helpfull if i get the table name so that i can fire select query.
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
I read in a technical journal (somewhere) that it is possible to create a ActiveX DLL in Visual Basic and use the DLL in a stored procedure. There were some limitations (because of stored procedures) but this is still a very powerful feature. Has anyone ever heard of this or read about this? Is there s source for explanation and examples?
I have a Stored Procedure that I am calling from ADO 2.7 in Visual Basic. The process works fine when the stored procedure has records to return. However , when the recordset is return emtpy ADO does not recognize the recordset as being open and empty. For example: VB Code to open recordset: Set rstClass1 = New ADODB.Recordset With rstClass1 .CursorLocation = adUseServer Set .ActiveConnection = DBConn .Source = strRecordSource .LockType = adLockOptimistic .CursorType = adOpenKeyset .Open If Not .EOF Or Not .BOF Then
The program bombs on the If Not .EOF statement with Error 3704 - "Operation is not allowed when the object is closed.
When I run the Stored Procedure In QA using an ID that I know will NOT return any records the result window returns nothing. I noticed that on some of my other SP's the result window will at least return the column titles. These SP's also do not produce the 3704 error in ADO.
Is there a command or a certain method to construct my SP so that is will return enough for ADO to know that the recordset did open its just empty?
The SP is ugly looking but I will post if need be.
I used Datagrid to show "Title", "Location" and "Date", It works very well. I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date", my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.
Here is my stores procedure:
CREATE Procedure JobSearch ( @Search varchar(150), @Sort varchar(50) ) AS
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
Hi,We have a lame app that uses 2 datetime(8) fields, 1 stores the date, theother the time.example query:select aud_dt, aud_tmfrom ordersresults:aud_dt aud_tm2006-06-08 00:00:00.000 1900-01-01 12:32:26.287I'm trying to create a query that give me records from the current date inthe past hour.Here's a script that gives me todays date but I cannot figure out the time:select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,getdate())from orderswhere (datediff(d,aud_dt,getdate()) = 0)results:aud_dt aud_tmdatediff(0=today) timediff (since 1900-01-01)2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 055978689I added this next part to the above query but it does not work since thedate/time is from 1900-01-01and (datediff(mi, aud_tm, getdate()) <= 60)Thanks for any help.
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
how to measure a change in inventory over various stores. My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. When the new stock arrives it is added to the "OnHand" count. I want to measure the delta change per day,per store. I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
I have an Execute SQL Task that may return a result set. If it returns a result set, I'd like to log a failure in my package with the results visible.
I have logging turned on and that's working great. I've read about assigning results to a user variable of type Object and that's great. I can shred my results, thanks Jamie, with a Foreach loop no problem. Within that loop, I've got some VB that manipulates the values and will call Dts.Events.FireError as appropriate. However, VB is frowned upon here so my boss has asked that I push the VB logic into a Control Flow item.
I've built custom components already so I've got some familiarity with the process. Where I'm stuck at is figuring out _what_ the actual object type is in my code. The Connection manager is Native OLE DBSQL Native client. My Execute SQL Task uses a connection type of OLE DB with a Full result set. Results are stored in a variable named ErrorResultSet. Within the Execute method, I currently have this code set up in an attempt to pick apart the object and discover the available methods.
// Iterate through the variables that we were // able to lock. Assigning values to entities as // available. foreach (Variable _en in _variableCollection) { switch (_en.Name) { case "ErrorResultSet": Object _rs = _en.Value; System.Type _type; _type = _rs.GetType(); System.Data.DataSet _realResults; _realResults = _rs as System.Data.DataSet; // My expectation is that the cast of _realResults would // not fail. break; } } // unlock before we go _variableCollection.Unlock(); return DTSExecResult.Success;
At this point, my assumption is that the unboxed type of the recordset is not in the System.Data.DataSet inheritance chain as the cast failed. Anyone have insight into what it is? I can't seem to get any hits on google for what it's using behind the scenes in the Foreach ADO Enumerator.
Beyond the immediate question, anyone have thoughts on how else I can solve the problem? I had thought perhaps the task could raise an event if it returned rows but it didn't seem to have that functionality. Even if that had worked, telling the logging provider to capture the result set into the log might have been too much for native functionality. Another option I was thinking about would be to continue using the Enumerator and my custom component is a pure rewrite of the current Script task with the obvious downside being that I'd lose the generic-ness I was hoping to get with being able to hit my dataset.
i have one col in a nonclusted index which is bigint 8 bytes follwoing result shows min_record_size_in_bytes and max_record_size_in_bytes is 20 that is 12+8 page size is 8 kB. does that mean a record will use 20 bytes in a page or it will only use the space equalent to data stored in the col.what i have seen is page count is same weather i put some data in the col or null
1) atmWonOpportunities_Users 2) atmWonOpportunities_Data (startdate and enddate parameters)
In (1) I created a filtered list of all user names associated with a certain business unit and saved it to a temp table. in (2) I create a filtered query listing all won opportunities during a period of time (date start and date end paramters)
In the report, I have 3 parameters, startdate, enddate and username (dropdown list).
When I run the report I select a startdate, enddate and username. I click the View Report button and get the same results for any one of the usernames I pick which is wrong.
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
im trying to import data using a query...how ever i can only insert data into one table, and i need to insert data into many...this is at the part where you map the fields...
HelloI have created a table in mssql.2000 which holds details of names etc. I have also included categories of interest. However the table is growing very big and unmanageable as the list of interest expand.Instead I would like to create seperate tables for each category of interest within the same database and populate the table with names taken from the Names_Table I could then indicated yes or no if any name is interested in this category.for example: Art_category. However, I am unsure how I can import the column of names from the Names_Table to polulate the NameID column in the Art_category table.I would appreciate advice and possibly a link to step by step tutorial.Thanks.Lynn
hi! my problem both concerns vb and sql. the problem is I have a text file delimited with semicolon. I have to enter this data into Sql table. for example; item description ;code1;code2;code3; there are too many lines like this. The code should enter the table these values.Table name is products, column names are desc,code1,code2,code3 I am using vb.net and sql server thanks all!
I have a table that contains about 50 millions of records and i would like to know which is the fastest method to export this table - bcp, DTS/SSIS, impot and exports wizard? What is the best way to import text file to SQL Server?
I want to import an Access table into a SQL table. Some of the integer Access fields have a null value, but when they are imported I want them to be 0 or zero in my SQL table. How can I do this please ?
I have a large excel file that contains some contact information and includes the following columns: Company Name, Company Fax, Company Address, Contact Person
In the db I have 3 tables that I need to insert into: Company table id ([uniqueidentifier] NOT NULL DEFAULT (newid()) Company Name nvarchar (200) Company Fax nvarchar (200) Company Address nvarchar (200)
Contact table id ([uniqueidentifier] NOT NULL DEFAULT (newid()) Contact Name nvarchar (200)
Contact_Company table id ([uniqueidentifier] NOT NULL DEFAULT (newid()) contact_id [uniqueidentifier] NOT NULL, company_id [uniqueidentifier] NOT NULL,
In addition, the excel file will contain the company name more than once (for every contact person in company new row with company name). I need to insert into table Company the company only once. I then need to insert the Contact details into the table Contact Finally, i need to insert into Contact_Company table both the company_id and contact_id
Problems: -How do i insert into Company table the company only once from excel? -How do i insert into Contact_Company the correct contact_id and company_id so that the right contact person will be assigned to the company?
Thanks for the help
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
and the other statment is: CREATE TABLE [TMsalg].[dbo].[Order_Ernering] ( [OrderID] int NOT NULL, [Customer_id] int NULL, [Name] nvarchar (100) NULL, [Adress] varchar (100) NULL, [ZipCode] varchar (10) NULL, [City] varchar (100) NULL, [Phone] varchar (10) NULL, )
Problems: -How do i insert into tbl_Customer table the custom only once from excel? -How do i insert into Contact_Company the correct customer_id and company_id?
