Two problems / questions I could do with some help on please......
1). How do you clear a spreadsheet before loading it ? I am sending a spreadsheet by Email on a regual basis. Each time I run my SSIS package the data is appended to that of the previous run. I need just the contents of this data load to populate the spreadsheet each time. I have tried various methods / techniques for achieveing this so I just need to know the accepted method. Sorry if this is very basic ( as it should be ?!) but I seem to be missing the point somewhere.
2). I have tried to resolve issue 1 above by having an empty 'template' spreadsheet and copying that one over the top of the previously populated one. I get an error saying that the source spreadsheet ..'is in use by another processes.' Amusingly, as a test, I can delete it but I cannot copy it !
Are there any decent workthroughs on working with destination excel speadhseets ? Im struggling.
I need to move specific files from a server to another server on a monthly basis. Â There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server. Â I would like to easily add or delete the file list as needed. Â I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them. Â With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable. Â Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....
We're trying to set a new standard at my office, of using GUIDs as database ID fields - all new tables in all databases will have a GUID as the ID field, using the UniqueIdentifier data type.
The problem that we are running into, is that different applications interpret the UniqueIdentifier data type differently - ADO 2.x interprets it with the opening and closing { } braces. ADO.NET does not include the { and } in the GUID structure, SQL Server Query Analyzer does not include the { } braces, and SQL Server Enterprise Manage does include the { }
The problem with this is in two parts: 1) VB6 does not have a GUID structure or data type, so we are treating GUIDs as strings... but VB6 doesn't recognize "{012345678-abcd-ef01-2345-6789abcd}" and "012345678-abcd-ef01-2345-6789abcd" as the same string 2) when sending a URL link through an email, the { } braces break the link - in all email applications that we have tested. This include Groupwise 6, Groupwise 6.5, IPSwitch Web IMail, Outlook 2000, and Outlook Express (IE 6).
We're becoming very frustrated with the problems at hand, and need to know how others have worked around these problems. Please respond with any kind of advice or any real life situations where you have encountered this and found a solution, etc.
I am executing an update query in access as follows.... UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = IIf([DtePrice1]<=CDate(Date()),[AmtPrice1],IIf([DtePrice2]<=CDate(Date()),[AmtPrice2],0)) WHERE (((tPriceRaw.IdPriceType)=8));
Here's my stab at converting this to a stored proc.
UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = SELECT CASE WHEN DtePrice1 <= GetDate() THEN AmtPrice1 ELSE (CASE WHEN (CASE DtePrice2 WHEN '00/00/0000' THEN '1/1/1900' END) <=GetDate() THEN AmtPrice2 ELSE 0 END) END WHERE tPriceRaw.IdPriceType=8
I kknow the Join Statement works becasue it does work in the context of a select. I also know the case logic is working becasue I am using it in an Insert query elsewhere.
Can't quite pull it together though. I am getting an error at Line #1 "(". I gave it a good try though before I resorted to throwing it into the forum. I am sure it is a stupid syntax error. Any help would be appreciated.
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :o
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :eek:
Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.
I have two tables, 1) Ticket_Purchase, 2) Flight.
The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID
The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination
I am trying to create a query that will tell me: On which flight were the most first class tickets sold?
There are only two types of classes; 'E' for economy and 'F' for First Class.
So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?
The SQL I wrote for the above is:
Select Class_of_Ticket, Flight_Number From Ticket_Purchase Where Class_of_Ticket = ('F') Order By Flight_Number;
And it produces:
Class_of_Ticket Flight_Number --------------- ------------- F 1 F 1 F 1 F 2 F 2 F 3 F 3 F 3 F 3 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 5 F 5 F 6 F 6 F 6 F 7 F 7 F 8 F 8 F 8 F 9 F 9 F 9 F 9 F 9
(38 row(s) affected)
Rather I would like it to produce: First_Class_Seats Purchased Flight_Number --------------------------- ------------ 14 4
I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.
Thank you for any help you could offer. It would be much appreciated.
Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.
Hello! I have an SSIS package that basically collects Excel files from an FTP server, deposits them to a file share on a SQL server and then processes each Excel file into a SQL 2005 database using the ForEach enumerator.
The package runs flawlessly when executed directly on the SQL server. The problem starts when I set this package to run via the SQL Agent. I have looked through these forums and other places and have done what this article suggested http://support.microsoft.com/kb/918760; changing my package protection to "Dont Save Sensitive" and setup the package to store its configuration in a SQL table. Despite all this I still continue to get this error when the package is run via the Agent:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelSourceFile" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
The Excel files are not password protected, no funky settings, nothing complicated, its just a plain Excel file with a few rows of data on one worksheet. The package transfers the files from the FTP server to the share on the SQL server with no problem, I can see that the files exist on the share, so it isnt a case of the Excel connection manager not finding any files to process.
The Agent account is running under administrator privileges and has full access to the share, so it shouldnt be a permissions issue either.
I have one report (R1) which includes a list of ports ("=Fields!Port_Name.Value")
Another report (R2) supplies details for individual reports and has a parameter named PortDWIDPortName which gets port names from the same dimension as those in R1. So in the field properties of the Port field in R1, I selected Navigation > Jump To Report and entered PortDWIDPortName = Fields!Port_Name.Value (also tried changing .Value to .Key or .UniqueName) but although it jumps to report R2 it does not enter a value for the parameter (ie leaves it blank or displays <Select Value> depending on whether multivalue or not).
Does anyone know the right syntax to pass the parameter ? If not, does anyone know to view what Jump To Parameter is returning so I can figure how to doctor it!
I need to take my 'table stats'every week and put them into an excel spreadsheet so that I can track the changes of my table sizes over time (basically I am watching to see how many rows are in each table). What I was planning on doing was to create a view of my table stats that I could then use DTS to transfer on a weekly basis into my excel spreadsheet. I have only used DTS a couple of times, and I have not ever tried it with excel. Now the problem: Everytime my DTS job runs it appends the data to the end of the origional columns that were created. Since my database has over 5000 tables these columns grow quite quickly. What I would like to do is set it up so that everytime the job runs it puts the new data into new columns in the same worksheet of my excel file. Is this possible? Any suggestions?
I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. I’m using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named “Results”. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. I’ve Google’d this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.
Error I See:
Running DTS package with passed variables ... DTSRun: Loading...
Can somebody please tell me what I am doing wrong or need to do to resolve my issue. I having problems with one of the columns in an excel spreadsheet that I am trying to upload into the system. One of the columns contain values of both text and numbers such as 'ABC123', 'ABC124', '123456' etc. When I try uploading the sheet into SQL Server 2000 using DTS, the system removes all characters from the one column that I am having problems with. So entry 'ABC123' for example would be truncated to '123'. I tried formatting the column that I am having trouble with in excel to 'General' format as well as tried to transform the column to type varchar in SQL Server while using the DTS wizard but still had no luck. The problem is that SQL server thinks that the column is a float type column from the source and therefore truncates any characters.
Using an access project front-end to an MSDE database, I'm trying toimport a spreadsheet using File->Get External Data->Import.After I select the spreadsheet name, I tell it I want to import into anexisting table and select the table name from the list. The name inthe list is dbo.Cost_Data. When I tell it to finish, it says the tabledoes not exist.If I do the same steps as the database owner, the table name does notcontain the "dbo." on the front of it and it is successful.Thanks for any help.Jerry
Being new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?
I have some XML that I'm passing through a variable into the XML task in SSIS. I also have an xsd file that I'm using and I want to validate (I think) that XML and have the XML task output an excel document. I've got the xsd file all set up in the "second operand" part of the XML task and the XML I'm passing in as a variable and that's in the "input" part of the XML task. My question is are there any tricks to make an excel document with these two things? Is there something I need to do in the xsd file to tell it that I want excel? Below is my XML and xsd:
I am able to import an excel spreadsheet into a table in sql server 2005 using SqlBulkCopy. The only thing that bothers me here is how to check duplicate entries and throw an error to the user regarding the duplicate entries. In the table in sql, there is no primary keys. There are five columns and the way I will have to find the duplicates is to match all those 5 columns. Since the excel spreadsheet can have 40 to 500 entries, how can I check those dupes.
I've got an Excel Spreadsheet with 5 columns of data which I need to get into an SQL Table. There's 13,000 rows in this Spreadsheet so manually doing it is out of the question.
I'm exporting via dts to an excel spreadsheet. However my database has more than 65000 records. DTS croacks and shoots out that there are too many records in the spreadsheet.
What do I do? Any way to go around that? These are my daily hit logs that are recorded and they tend to get big.
I am new to SQL and can do queries OK on SQLTalk. I need to know if there is a script to retrieve data and then export to an Excel spreadsheet for internal company use. Is there such a beast and is this the right place to look???
Ok, so my boss handed me a spreadsheet that contains about 1,000 records as retrieved by a SQL query.
My boss now wants me to run a query on ONLY the records given to me in the spreadsheet. He wants me to grab two more fields of data for each of the records in the sheet.
Naturally, my first question to my boss is, "Why don't you just give me the SQL code that was used for that query, and I'll just add a string or two to grab those additional fields? ...And then we'll run the new SQL?"
My boss says, "Isn't there a way to run a query on just the records I've given you? Someone in another department has been able to do that before."
I've never heard of this. Can anyone provide insight into how I might accomplish what my boss wants before I tell him he's full of crap?
Hi, I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:
Function Main() on error resume next Set objxl = CreateObject("Excel.Application") objxl.Visible = False
Dim xlFile xlFile = "C:Datafile.xls" Set objWkb = objxl.Workbooks.Open(xlFile)
'' Connecting to SQL Server set cn = server.CreateObject("ADODB.Connection")
Dim serverName serverName = "myserver2"
strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial Catalog=mycat; Integrated Security=SSPI"
cn.ConnectionString = strCS On Error Resume Next cn.Open Set objsht = objWkb.Worksheets.Open("Sheet1") Dim client_name, rb, date_rvd, LOB Dim sql Dim row, sequence row = 2
client_name = Trim(objsht.Cells(row, 2).Value) Do While IsNull(client_name) = False And client_name <> "" 'client_name = Trim(objsht.Cells(row, 2)) rb = Trim(objsht.Cells(row, 4).value) date_rvd = Trim(objsht.Cells(row, 6).value) LOB = "WCS"
i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!
I'm trying to run this code, which worked on SQL 2000, on 2K8:
SELECT [Business Unit] ,[Department] ,[Ext :] ,[F9] ,[First Name :] ,[Last Name :] ,[Mobex :] ,[Mobile :] ,[Sub Department] FROM MyCompanyStaffPhoneList...Sheet1$
However it just returns this message..Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.
-I am selecting Jet 4.0 as the provider -Product name is Excel -Data Source is the path on our network to the excel file: N:Devon 5403 4.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls -Provider String is Excel 8.0 -Security | Login not defined is set to Be made using the login's current security context.
The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages
I have a query window open in SQL Server Management Studio and the following is my select statement:
SELECT * FROM DEVON_LINE_LIST...Pages$
I get the following error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".
I get similar error messages no matter which security settings I pick.
Any thought as to what I can try to get this to work?
I am looking for a way to import data from a CSV or Excel spread sheet and add the data directly into an Extended field instead of a regular field in the table. for example: let's say I have a comma delimited field with the following info:
NDC_M_FORMULARY,CUSTOM_EXTSIG,Custom EXT SIG NDC_M_FORMULARY,DRUG_CODE,Alternate key, user defined NDC_M_FORMULARY,CHARGE_CODE,From the Charge code table
The first column is the table name Second Column is the Column name in the table The third column contains the description that I would like to store in the Value in the Extended Property Name "MS_Description"
BTW,I did find the following T-SQL which returns the Extended description for a specific Extended Property
Here it is:
SELECT [Table Name] = i_s.TABLE_NAME, [Column Name] = i_s.COLUMN_NAME, [Description] = s.value FROM INFORMATION_SCHEMA.COLUMNS i_s LEFT OUTER JOIN
I am new to SQL program. I did little management for SQL 2000 before. I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?
hi guys,i've just started woth Excel component in .NET. i manged to importthe Excel Component on windows Forms but i'm not able to load Excelfile in that. if anyone has any idea how can this be done please do letme know. i'm in great need.thanks,Lucky
I am using SSIS to export data from a table to an Excel spreadsheet. This all seems to work put just fine. The user would like a data in column B1 to say when the spreadsheet was created. Is there a way within SSIS to do this. I was looking at using a .NET script but it accesses the spreadsheet as a table so I do not know how to insert data above the headings in row 1. I believe the OleDB provider using column 1 as it column names for the table. Maybe I am just going about the whole think wrong?
Is there a limit to the number of fields that can be set in an OleDB Update Statement?
This works with 6 fields: cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"
This Fails with 7 fields: cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"
The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.
The complete segment is:
Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & ExcelFileName & ";" & _ "Extended Properties=""Excel 8.0;HDR=NO""" Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection) Dim cmd As New System.Data.OleDb.OleDbCommand()
I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql. Does anyone have any suggestion what I did wrong or what is the right way of doing this? Thanks a lot. Mia
