Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
How To Troubleshoot/correct An Excel Destination - Opening A Rowset For &&<sheetname) Failed Error?
I have 2 Excel sheets ( Sheet1 and Summary)in an excel output file.
Sheet1 is created and loaded with data fine.
Summary sheet is getting the following error:
Error: 0xC0202009 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error: 0xC02020E8 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: Opening a rowset for "Summary" failed. Check that the object exists in the database.

I do have an execute SQL task to create the summary sheet before the data flow task.
The execute SQL task has
CREATE TABLE `Summary` (
`Counts_and_Percentages` LongText

Please advise on what I can do to troubleshoot/correct the error. Thanks

More details on the error
DTS.Pipeline] Error: "component "Excel Destination" (337)" failed validation and returned validation status "VS_ISBROKEN".
My Excel file name is an expression

@[User::FullFilePath] + (DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + " " +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + " CLIENT=" +
@[User::ACCOUNT_NAME] + "output.xls"

View Replies !   View Related
Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputson getting this addressedwill be much appreciated.



View Replies !   View Related
Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.


View Replies !   View Related
Excel Destination Appends The Excel File Everytime A Package Is Executed
i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file?I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated

View Replies !   View Related
Simple Data Dump Export To Excel?
Any ideas on how to export from excel and simply get rs to do a data dump, not pull over any of the formatting, just simple columns and rows of data?

View Replies !   View Related
DTS From Excel File (excel Filename Is Different Everyday)
Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.

View Replies !   View Related
Meta Data Synchronization With Excel File Source Once We Update The File

hi all;

1. Excel file Source--> monthly Revenue details
2. Derived Colum Transoformations
3. Oledb Destination

its my flow in one of my packates (ETL job)
Excel file contains monthly revenue details, i wannaimport the excel data to my databasestaging table, so i've created the package.
its working fine...

if we change the new data for the next month and running the package its not running;
the same file, same format, only we delete the contents, of the file except first row of the excel sheet,
and pasting the new data;
new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us)

i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized
Do you want to Fix this issue automatically with the available external column's meta data

Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to.

now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the packageExecution wizard,

ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the webpage, that is executing the package in an On_click event)

The package is not running from the server, its due to the meta data change in the Excel file( i guess)

please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta datashould not change when wehave new updates in it;

otherwise suggest me somesolutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kindof Data Profiling activity;

i know its some what crazy, but i need to maintainthe systemwith permanent solution,instead of facing this meta data mismatch issue!!!

some what lenthy explanation--> its neededformy dearpowerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries,i'll try my level best.

View Replies !   View Related
Deploy Excel File That Already Exists On Server - File Isn't Replaced
Dear all,

I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file withthe same name already exists, that file isn't replaced. I would like the opposite to happen. I'm usingthe following code:


set svr=http://w3sdwsqld1/reportserver
set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\"
set dest_fld="Associados"
set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld%

--rss Code

' Script Variables
' Variables that are passed on the command line with the -v switch:
' (a) parentFolder - corresponds to the folder that the script creates and uses
' to contain your published reports

' (b) reportP - corresponds to the report to publish

Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = ROOT + "/"+ parentFolder
Dim filePath As String = path
Dim report As String = reportP

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Create the parent folder
rs.CreateFolder(parentFolder, ROOT,Nothing)
Console.WriteLine("Parent folder {0} created successfully", parentFolder)
Catch e As Exception


End Try

'Create shared data source
'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")

'Publish the sample reports

End Sub

Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)

Catch e As Exception
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx")

definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))

Catch e As IOException
End Try

rs.CreateResource(reportName + ".xlsx", parentPath, True, definition, "application/x-excel", Nothing)

Catch e As Exception
Console.WriteLine("Failed to publish report")
End Try
End Sub

Any thoughts? Many thanks,

Pedro Martins


View Replies !   View Related
DSO Rowset Provider
I have a question about the DSO Rowset Provider option in DTS packages. All of the information I've read suggests that with this option selected, you can extract info from the DTS package as easily as using a 'Select *' statement.

What I'd like to do is this. I have a DTS package that imports from a text file into a SQL 7.0 table. I have to fill in an additional field, so I'd like to import this text file to a recordset (via VB), and then insert it into the SQL table. Is this possible? Thanks for your help.

View Replies !   View Related
CSV File Saved As An Excel File - Error Message
I hope someone can help me with this - I started receiving this error message in the past month or so when I open a csv report and save it as an Excel file in a folder I use on my VPN and in My Documents. It does not show up when I save it to my Desk Top.

I have Microsoft Office Student and Teacher and Office XP Professional installed on my notebook. I tried to uninstall Office XP and it would not let me. Something about a "patch could not be opened......"

The error message is as follows:

Header: .NET-BroadcastEventWindow. Error

Excel error message The instruction at 0x0beab865 referenced memory at "0x00000008"

The memory could not be "read".
Click ok to terminate the program.

I hope someone could please help me with this I received 60 - 80 csv files a week and everythime I save on I get this pop up message!

Thank you!


View Replies !   View Related
Openxml Rowset Problem
hi all,

I'm trying to get some xml data into sql server but i ran into this problem: openxml seems to repeat results ...

I simplified my example to this:

Declare @rDoc int,
@sDoc varchar(4000)

Set @sDoc = '

EXEC sp_xml_preparedocument @rDoc OUTPUT, @sDoc

FROM OPENXML (@rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '../Rider')

it returns 3 records for the 'rider' elements, _but_ all the records contain the text 'aaa' :/

does anybody know the solution to this?



View Replies !   View Related
Open RowSet Syntax Please?
Hi..I am trying to read text file using OpenRowSet function for this my DBA was created linked Server with Name "DBLServerText", my text file path c:/txtcount.txt. Please guide me syntax any one have idea.

I don't have access on XP_CMDSHELL & BULK Insert commands.

DTS package is also not allowing.

as per my knowledge, I have only one option that is OpenRowset.

Please help me the syntax using with Linked Server. with Linked it is showing the error. " You don't have OLEDB access use with Linked server"

Pls guide me

Thanks in Advance..

View Replies !   View Related
'SELECT Clause Does Not Return Rowset From Sp'
I have sp20, simplified, as:

ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS

SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID

SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate

SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID

This works fine in EM.

When I try to execute it from MS Access ADP Project I get

'Stored Procedure excuted succesfully, but did not return any records'

Although, in EM it returns the right number of records.

Thank you in advance - Rehman

View Replies !   View Related
Rowset Values In A Single String
I am not close to an sql server today and this question was posed to me. can someone hook me up?

" I want to query a column of values and place them into a single string seperated by commas" (as a function)

Table a


thanks in advance

View Replies !   View Related
Failed To Open A Fastload Rowset

In OLEDB Destination, AccessMode as OpenRowset Using FastLoad raises the following error:

"Failed to open a fastload rowset db_object. Check that the object exists in the database"

db_object exists in the database. Is there any key point for the table or view used for fastload?


View Replies !   View Related
Enumerating Foreach Loop From Rowset
Please let me know if I am on the right track here.

I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.

I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.

Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection >Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).

Thanks - Dana Reed

View Replies !   View Related
Getting The Schema Rowset And Subrows Of Node_distribution

Well, i need a piece of code thatreturn methe schema rowset of a desicion tree and let me iterate me on it. I need some columns like node_type, node_unique_name.... etc.. And another question is that the node_distribution has multiple rows of information. How can i access them. I think there must be a adomddatareader object to iterate on them..

Well thanks a lot for your responses...

View Replies !   View Related
Rowset Does Not Support Fetching Backward.

I get the following error.
Err.Description - "Rowset does not support fetching backward."
Err. Number - -2147217884

Can somebody tell me the reason behind this error.

My Reasearch: I have got to know thatI get this error in the line of code recordset.MoveLast in My code.

My Code environment: I have built an SQL Query string which calls the Strored procedure with one parameter (parameter tyoe is string type). I have attached the SP code below.

and SP contains the call for querying other database in the same server using OPENQUERY command.

and the VB code contains code as Recordset.MoveLast. (Error is generated here and Recordset.RecordCount is also -1)

SP code is:

Code Block
@sRMS_Status CHAR(12)

WHERE JOKE_ID = ''' + '''' + @sRMS_Status + '''' +
EXEC ( 'SELECT * FROM OPENQUERY(lnk_joke__cat,' + @sSQL + ')' )

My Questions:
1). Is there any problem in SP as I am using OpenQuery? I queries the SP and concluded that it generates result properly.

2). Is there any problem with the Recordset.MoveLast line? I am using adOpenKeyset, adLockReadOnly as parameters to my Recordset.Open command along with other parameters.

Letme know the corrective steps to be taken to get rid of this error.

Thanks in Advance for your valuable time
Ranjan Jain

View Replies !   View Related
Can A DELETE's OUTPUT Rowset Be Ordered?
I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur inthe same order as the clustering key of the target table. Is this possible?

View Replies !   View Related
Collations And Simple Import From A Csv-file
Hello.Here a twodifferent problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database
This csv-file is defined as a variable length file
Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe?
I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?

I appreciate all help.


Thomas Ivarsson

View Replies !   View Related
Simple Flat File Import
Boy, do I need HELP! Have a simple csv file that I need to import. Worked fine in sql2000; I put it into dts to execute on a monthly basis. Makes connection, db connection, table creation fine, but stops at validation of flat file?

Basically, I want to go out and get a flat file, drop the existing table, and create the table, and import the information from the flat file. Not a complicated table of about 30,000 records.

Create table [db].[dbo].[tblPatient] (
[patientID] into not null, [chartID] varChar(15) null, [doctorID] int null, [birthdate] datetime null, [sex] varchar(1) null, [raceID] int null, [city] varchar(100) null, [state] varchar(2) null, [zip9] varchar(9) null, [patientTypeID] int null, [patName] varchar(100) null)

Below is the error report that tells me NOTHING!

Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Source - pmPatientInfo_csv" (1).
(SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Data Conversion 1" (71).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [fhc].[dbo].[tblpatient3] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)

View Replies !   View Related
Simple File And Printer Sharing?
I have problems with Windows Authentication and would like to understand better the following phrase:

Jens K. Suessmeyer wrote:

If you want to use Windows Authentication you have to disable the "Simple File and printer sharing" at the "server" (even if its only WIndows XP). With this option enabled, it normally simplifies the logon process as the requestor does not have to give a user name and a password, because he will be logged on with the Guest user.

HTH, Jens Suessmeyer.

from the post "Error: 18456, Severity: 14, State: 11 Valid login but server access failure "

I have in my LAN the following computers with SS2005SP2 Developer Ed at 2) and SS2005SP2 Enterprise Ed. at 1):


MS Windows Server 2003 Enterprise Ed SP1 (Russian), NTFS


MS Windows XP Pro SP2 (Russian)

on which I run MS SQL Server 2005 SP2 (with postSP2 hotfixes), NTFS


MS Windows 2000 SP4 (Russian), FAT32

I have "Simple File and Printer Sharing" (SFPS) or really

"И?пользовать про?той общий до?туп к файлам (рекомендует??)" unchecked in 2).
This option is simply NOT available on 1) or 3)

Do I understand correctly that absence of such option at server OS, i.e. at 1), is equivalent to having there always SFPS unchecked, like in 2)?

What does such absence mean for 3)?

I did not quite clearlyunderstand the consequences of having SFPS or not, enablingOR disabling itfor Windows Integrated securityof MS SQL Server.

View Replies !   View Related
Oledb Stored Procedure Rowset Binding
I have a strange problem with an OleDB call to a stored procedure thatreturns a rowset.Only the first time I execute the query, after I restart SqlServer, myprogram crashes becausethe rowset is empty. All next calls (after restarting my program, ofcourse) run successfully.The context is:1) if I do not bind the output rowset, my program doesn't crash2) If I run the call without the binding and then I run the call withthe binding, it doesn't crash3) It is not the first query I do in my session4) the call crashes only if in the SP there are some inserts: it is awell known problem with OleDB, but in manyother cases I have fixed it setting NOCOUNT to ON

View Replies !   View Related
Execute SQL Task Error: No Result Rowset Associated...
I am getting the following error when I execute my sql task:

An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query."

I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:( to get it to work but keep getting the above error. I have the following variables:

sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @[User::SnapShotDate] + "', NULL"
NullVar (string)

In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.

So I am not sure what I am missing here. Anyone have any suggestions?


View Replies !   View Related
Retrieving A Rowset From A SSAS Drillthrough Action
I've posted this in the SSAS forum, but I'm not getting any takers. Perhaps someone here can help.

I've been investigating "drillthrough actions" in SSAS, with the hope of passing the resulting rowset into an SSIS package. A drillthrough action has an "invocation" parameter whose value can be set to "batch". Searching the MS site for drillthrough/action/batch, this description is given for batch invocations:

The action
should run as part of a batch operation or an Integration Services task.
Does anyone have any experience with this? I've only found examples of actions that are basic drillthroughs and report server actions, none of which show any usage of the batch invocation. Thanks.

View Replies !   View Related
Database In Simple Mode Yet Txn Log File Can Be Out Of Diskspace ?
Hi ,I have set up my database to be using "Simple" mode. This will not log anytxns ?I have got the err message saying the log file is full and i need to do atxn log backupi do not understand why , could anyone kindly advise ?tks & rdgs--Message posted via SQLMonster.com

View Replies !   View Related
Backup Of Ldf File In Simple Recovery Model


I have a question regarding the backup for the database in Simple Recovery Model.

In this Model, I know we can restore only to the last full backup or can use differential

Backup, if implemented as a part of backup.

But my point of confusion is about the backup of '.ldf' file, should those file shouldbe backed up in the

Maintenance Plan, if yes does it help in reducing the size of Log file?

Do we need the backup of '.ldf' in phase of Restoring?

As I mention my database has Simple Recovery Model, but the sizeof log file is around 20GB,

Could not understand why as in this Model, normally it automatically truncate the Log file?

Help me to clear my these doubts,


View Replies !   View Related
Error: A Rowset Based On The SQL Command Was Not Returned By The OLE DB Provider.
I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max).

Anyway, it give me an error

[OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

What am I doing wrong?

Can I not have a stroed proc that returns a result set as my data source?

View Replies !   View Related
Saving Data To A Database File Via Simple Form
I am designing a site using Visual Web Developer, CSharp and Sql server Express.
One the contact page I want to put a form that allows users to enter details about
themselves. On clicking the button this will be stored in the database under a table
called subscribers. The form will have, name, address, telephone, email fields etc.
With the email addresses from the visitors I want to be able to keep them in
a newsletter section or similar which is automated so they recevie emails from time to time
Could somebody suggest a tutorial which shows how to complete this process
using c sharp and sql.
Thanks for you time

View Replies !   View Related
I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt)that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.

10001,2005/01/01,some more data

Can somebody place give me some guidance on how to do this in SSIS?

View Replies !   View Related
Simple SSIS Package, Problems With Flat File Output

I want to make a very simple package: Export all rows in a table to a flat file.
This package I cancreate pretty much by only using the wizards.
Now to my problems:

1) I need the output to have this format:

DS3 Plastpall trippelkrage 40 1

H is a header post, in this case with date and time following.
D is a details post, that is all the rows that was exported.
E is and end post, containing only the number of rows in the file, including H and E posts.

2) I need to set the file name dynamically, preferably using date and time to name the file.

Ive done this very same thing in T-SQL, like so:

Code Snippet
FROM tempProducts
SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1
FROM tempProducts
DECLARE @date char(8)
DECLARE @time char(8)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')

DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql

--WAITFOR DELAY '0:00:10';
FROM tempProducts


But Im sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell.
Pointers please

View Replies !   View Related
Dumbest Question Of The Day: Run A Simple Query And Save The Results Into A Text File
Can someone demonstrate a SIMPLE way to do this that does not requireadditional functions or stored procedures to be created? Lets say Iwant to execute the following simple query - "select * from clients" -and save the results to a text file, we will assume c:
esults.txtHow can I do this in one step?

View Replies !   View Related
Dts To Excel File
I tried to dts a table to an excel file. The data types of all the columns in the table are varchar. When it dts to excel file, excel force every column to be text. So every value of every cell of the excel has a single quote before it (not in the cell itself, but in the value shown space right below the toolbar). I also tried to dts the table into a excel template which I predefined all cells to be text type. The strange thing is some of the columns do not have the single quote anymore, but others still keep them. Since the other system that going to process the excel file does not expect the single quote, the process always fails.

Would anybody tell me how to get rid of the single quote?
Thanks a lot.


View Replies !   View Related
Excel File From Xls To Csv
Hi guys,
I am converting a xls file to csv file. When I am converting, the values in the columns 00 and 05 are converted into 0 and 5 respectivly. I tried changing column type from general to text format and tried converting it with no luck.
It is an urgent need and if anyone have any solution, can u please post it here. Thanks

View Replies !   View Related
DTS Excel File

I have to DTS a excel file to sql server 2000. The DTS package works fine. I have issue with one column that has value










If given the cell format general/text only that have alphanumeric characters are upload and not others. What should be done to upload all the values for this column.

thanks in advance.

View Replies !   View Related
Get Excel File When Run Sqlquery
Hi All
im using sqlserver2005 and i have sqlquery to get some fileds
now  i need to get out put in excel file when i run sqlquery
can anyone explain me how can i do it...this is my query
select ta_targetpath from tblattributes where ta_pagelevel = 2 and ta_description like '%Do you want to watch this configuration in action%'
and ta_targetpath is not null
when i execute this i need to get result set in excel file...
thnx in advance

View Replies !   View Related
Importing And Excel File
How do i import a Excel file into a table i have created in my database in SQL server 2005??? 

View Replies !   View Related
Importing Excel File
Hi friends i need to import data from Excel file to sql server database. i got code for importing data from Excel to datagrid now i need to push the data to server database.
The code i got is..
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("../ExcelData.xls") + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM MyRange1 ", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
OleDbCommandBuilder custCB = new OleDbCommandBuilder(objAdapter1);
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.AcceptChangesDuringFill =false;
objAdapter1.Fill(objDataset1, "Sample1");
DataGrid2.DataSource = objDataset1.Tables[0].DefaultView;
pl help me to push data from datagrid2 to server table called "Projects".

View Replies !   View Related
From Excel File Into MS SQL Server
I need to find a way to upload an Excel file into an MS SQL databaseusing a web control front end. I have my ASP.Net control (using C#)uploading a file to a directory, but the server people now tell me thatI cannot have a writeable area for the web and have a DTS see it as thisis too much of a security risk. So, I need a way to read the filedirectly into the database. I've no idea how to do this. Does anyonehave ideas? I know loading MS Office into the web server is out of thequestion. The webserver and database server are not the same physicalmachine.Thanks.

View Replies !   View Related
Importing An Excel File

I am trying to import data from an Excel 2000 file into a SQL Server 2000 table. I am doing this through DTS.

However, when I transfer it in, the integer numbers are imported in as <<NULL>>. I have tried cast/convert but they have been rejected.

Convert - Undefined function 'convert' in expression
Cast - Unexpected error occured.

Can anyone be of help ?



View Replies !   View Related
How To Execute An Excel File Within DTS?
Hello All.

I need to execute an excel file (built with macro) whenever a date change is detected between 2 fields in my sql table.

if @Date1<>@Date2
DECLARE @command varchar(1000)
SET @command='d:Refresh_ABC.bat'
exec xp_cmdshell @command

In my Refresh_ABC.bat, I have the following line
Start Excel.exe "D:ABC.xls"

When I tested the above script (from Declare... to @command) in query analyzer, nothing seems to happen. It shows The command(s) completed successfully. But my excel file was not refreshed at all. When I ran the batch file Refresh_ABC.bat on it's own, ABC.xls was refreshed so the batch file is working.

Next test, I placed the line Start Excel.exe "D:ABC.xls" on SET @command = ......... line. Same problem, nothing happened.

Any idea what went wrong. Please advise. Thank you.

Best regards
Teck Boon

View Replies !   View Related
Open Excel File
I am just trying a simple query of an Excel file.

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

and i am getting this error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I know for a fact that the path is correct and that it is the path of the server. So what is the problem with this??


View Replies !   View Related
Export To Excel File
I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported.

First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I created the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. I haven't been able to correct this.

Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.

How can I do this?


View Replies !   View Related
Updating From Excel File
Hi, I m trying to update a table using an Excel file using the following code:
UPDATE cargo
SET cargo.slot = (SELECT two
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)

select container,slot from cargo
Now, I have two similar databases, and both contain all the fields necessary for this operation and these fields are identical.
However on one table it works perfectly, on the other it gives an error:

MSDTC on server 'SCRBSQLITGTO002' is unavailable.

The server I am running is the only sql 2000 server we have, so it is cannot be in cluster mode.
Can anyone help?

View Replies !   View Related
Exporting Blg File To Excel
I have a few blg files created by performance monitor on a SQL2000 box on Win2000. Is there a way to export these to excel or something, I could not find a way

View Replies !   View Related
Can You Create An Excel File Using T-SQL?
I have a stored procedure. This proc returns some data that needs to be put in an excel format. Is it possible to create a excel file to have the result of a select statement from the database?


View Replies !   View Related

Copyright 2005-08, All rights reserved