Re: How to export Dataset to excel file using OLEDB connection? - Database Discussion Boards

Eammonn Murray's article helped me to find the existing sql servers, 
but it didnt deducted the existing databases in the network,

Databases exist within a SQL Server instance. You ought to enumerate the Servers first, and query the master DB in each server to find the list of databases available in that particular server. Ofcourse, you need valid credentials to log in into each server.

SG
Cause is effect concealed. Effect is cause revealed.

Hi friends,

In my application (developed in asp.net 1.1), I am going to implement the Cache dependency mechanism. I want to automatically invalidate the Cache object, once any manipulations done on a particular table in SQL Server.

My application and DB is running in different system. I found in one of the site, to create a trigger on the table which in tern calls the sp_makewebtask System stored procedure and write output of the query into a file.

EXEC sp_makewebtask @outputfile = 'c:\testcache.txt'
@query = 'select top 1 Problem_Category_Code from SMD_Problem_Category'

But the 'C:\testcache.txt' is pointing the local system in which SQL is running. I want to write a file which is available in remote system. I shared a folder from the system and made a mapped drive in the SQL system. (as z:\ drive)

I used the statements as :
@outputfile = 'Z:\testcache.txt' also as @outputfile = '\\10.160.1.4\wc\testcache.txt'

But in both the cases I got the error as:

Msg 16821, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not open the output file.

But in one site it is mentioned that, we can use the UNC name if we want to write file in remote system. How can I run the SP?

Can anyone please help me to solve this problem?

I need some swift reply on this issue, as it is very urgent for me.

Thankfully,
jm

This means that SQL Server does not have the required permission to write into the shared folder.
You ought to give write permission to the account under which SQL Server is running to the shared folder. Make sure you give permission under the "Sharing" tab and "Security" tab.

SG
Cause is effect concealed. Effect is cause revealed.

Hi ,

In my code, if I assign a dataset to the combobox datasource property,it is not getting assigned. The combobox datasource remains null. Because of this the combobox is not getting populated with the data.Can Some one tell me what could be the problem.

B.RAMYA

You are definitly not trying to do that in SQL / ADO / ADO.NET, so better try the related forum!

Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.

Hi all,

I am developing a window application which can import EXCEL file to dataset/datatabel and display it in datagrid view. I am success to import it and edit or update the data into EXCEL. But i can't delete data in EXCEL(i know this is the limitation of excel if we do excel as a database - error ISAM). So i would like to delete data in dataset/datatable ,and then save entire dataset/datatable into EXCEL file.So, Can anyone tell me how to export entire dataset to excel by using OLEDB?:confused:

Any tips or related resources are welcome.Thanks Wink | ;)

regards
cocoonwls

make a google search...[^]

I Love SQL

I can't remember anymore where I found the original for this, but i've enhanced it a bit though.
Read the warning comments about double vs. decimal. (If someone has a solution I'd be happy to know about it!)

        Private Function OS(ByVal Word As String) As String<br />
            Dim i As Integer = Word.IndexOf(".")<br />
            While i > -1<br />
                Word = Word.Remove(i, 1)<br />
                i = Word.IndexOf(".")<br />
            End While<br />
            Return Word<br />
        End Function<br />
<br />
        Public Sub ExportDatasetToExcel(ByVal Location As String, ByVal DS As DataSet)<br />
<br />
            If My.Computer.FileSystem.FileExists(Location) Then<br />
                Try<br />
                    My.Computer.FileSystem.DeleteFile(Location)<br />
                Catch ex As System.IO.IOException<br />
                    Dim MsgStr As String = "The file is in use by another process. Close all programs that might use the file and try again."<br />
                    MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
                    Exit Sub<br />
                Catch ex As Exception<br />
                    Dim MsgStr As String = String.Format("This shouldn't happen, call support{0}Error message:{0}{1}{0}{0}Stacktrace:{0}{2}", Environment.NewLine, ex.Message, ex.StackTrace)<br />
                    MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
                    Exit Sub<br />
                End Try<br />
            End If<br />
            Dim CreateString As New StringBuilder<br />
            Dim Columns As New StringBuilder<br />
            Dim Mark As New StringBuilder<br />
            Dim ConnectionStr As String<br />
            ConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Location & ";Extended Properties=""Excel 8.0;HDR=YES"""<br />
            Using Connection As New OleDb.OleDbConnection(ConnectionStr)<br />
                Connection.Open()<br />
                For Each table As DataTable In DS.Tables<br />
                    CreateString.AppendFormat("CREATE TABLE [{0}] (", table.TableName)<br />
                    Columns.Append("(")<br />
                    Mark.Append("(")<br />
                    For Each Column As DataColumn In table.Columns<br />
                        CreateString.Append(OS(Column.ColumnName))<br />
                        Select Case Column.DataType.Name<br />
                            Case "SByte", "Byte", "Int16", "Int32", "Int64", "Decimal", "Double", "Single"<br />
                                CreateString.Append(" Number, ")<br />
                            Case "Boolean"<br />
                                CreateString.Append(" Bit, ")<br />
                            Case "Char", "String"<br />
                                CreateString.Append(" Memo, ")<br />
                            Case "DateTime"<br />
                                CreateString.Append(" DateTime, ")<br />
                            Case Else<br />
                                CreateString.Append(" Text, ")<br />
                        End Select<br />
                        Columns.AppendFormat("{0}, ", OS(Column.ColumnName))<br />
                        Mark.Append("?,")<br />
                    Next<br />
                    CreateString = CreateString.Remove(CreateString.Length - 2, 2)<br />
                    CreateString.Append(")")<br />
                    Columns = Columns.Remove(Columns.Length - 2, 2)<br />
                    Columns.Append(")")<br />
                    Mark = Mark.Remove(Mark.Length - 1, 1)<br />
                    Mark.Append(")")<br />
                    Using Command As New OleDb.OleDbCommand(CreateString.ToString, Connection)<br />
                        Command.ExecuteNonQuery()<br />
                    End Using<br />
                    Using Adapter As New OleDb.OleDbDataAdapter("SELECT * FROM [" & table.TableName & "$]", Connection)<br />
                        Using ExcelDataset As New DataSet<br />
                            Debug.WriteLine("ExcelDataset.Locale.Name = " & ExcelDataset.Locale.Name)<br />
                            Adapter.Fill(ExcelDataset, table.TableName)<br />
                            Adapter.InsertCommand = New OleDb.OleDbCommand("INSERT INTO [" & table.TableName & "] " & Columns.ToString & " VALUES " & Mark.ToString, Connection)<br />
                            For Each Column As DataColumn In table.Columns<br />
                                Select Case Column.DataType.Name<br />
                                    Case "SByte", "Byte", "Int16", "Int32", "Int64", "Double", "Single" ', "Decimal"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Numeric, 100, OS(Column.ColumnName))<br />
                                    Case "Decimal"<br />
                                        'BIG, BIG Warning about this one, Inserting a a double intead of a Decimal is done to fix a bug(?) in OleDB <br />
                                        'when using another Language setting than en-US on the computer (decimal comma vs. decimal dot)<br />
                                        'Changing Cultureinfo on the CurrentThread doesn't help<br />
<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Double, 100, OS(Column.ColumnName))<br />
<br />
                                        'Here's my original to use with en-US if anyone is interested:<br />
                                        'Dim myParameter As New OleDb.OleDbParameter("@" & OS(Column.ColumnName), OleDb.OleDbType.Decimal, 100, OS(Column.ColumnName))<br />
                                        'myParameter.Precision = 8<br />
                                        'myParameter.Scale = 4<br />
                                        'Adapter.InsertCommand.Parameters.Add(myParameter)<br />
<br />
                                    Case "Boolean"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Boolean, 100, OS(Column.ColumnName))<br />
                                    Case "Char", "String"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
                                    Case "DateTime"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.DBTimeStamp, 100, OS(Column.ColumnName))<br />
                                    Case Else<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
                                End Select<br />
                            Next<br />
                            For Each Row As DataRow In table.Rows<br />
                                If Row.RowState <> DataRowState.Deleted Then<br />
                                    Dim ExcelRow As DataRow = ExcelDataset.Tables(table.TableName).NewRow<br />
                                    For i As Integer = 0 To table.Columns.Count - 1<br />
                                        ExcelRow.Item(i) = Row.Item(i)<br />
                                    Next<br />
                                    ExcelDataset.Tables(table.TableName).Rows.Add(ExcelRow)<br />
                                End If<br />
                            Next<br />
                            Adapter.Update(ExcelDataset, table.TableName)<br />
                        End Using<br />
                    End Using<br />
                Next<br />
            End Using<br />
            System.GC.Collect()<br />
        End Sub

[Message Deleted]

Don't double post. It's rude and against forum rules.

You've just asked this question. Why have you asked it again?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

I am sorry,

Hi
can u please help me to run following code.
___________________________________

declare @tablename varchar(20);
SET @tablename = 'table1';
select * from @tablename;

error: must declare @tablename
Thanks

Jayesh

Why do you want to do that in the first place?

If your table name is known only at runtime, you've to use dynamic sql

<br />
declare @tablename varchar(20), @sql nvarchar(256);<br />
SET @tablename = 'table1';<br />
set @sql = 'select * from ' + @tablename<br />
exec sp_executesql @sql<br />

SG
Cause is effect concealed. Effect is cause revealed.

actually i need query with dynamic table name for cursor where ur this code will not help me dear.

my problem is here..

declare @TABLENAME as varchar(50);
SET @TableAuto = 'temp_' + @LINE; (where @LINE is parameter)

DECLARE C1 CURSOR READ_ONLY
FOR SELECT *
FROM @TABLENAME

modified on Wednesday, April 9, 2008 4:18 PM

Is there anyone who knows about how to connect PROGRESS databse? How do I make connection string with ADO.NET? Please let me know.

Dong Kim wrote:
Is there anyone who knows about how to connect PROGRESS databse?

Nope. I certainly don't.

Dong Kim wrote:
How do I make connection string with ADO.NET?

See this page[^]

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Mixins in C#3.0

My website | Blog

try this site[^]. It's a good one to reference for all types of connection strings.

Hi guys,

how do I insert into an oracle database table with a select statement from within mssql server2005 or 2000?

e.g
Oracle db: Table A id,msisdn,message

Msql server:Table A id, msisdn, message.
1 +476334566 Happy birhday

So I want to select this result from sql server database and insert into oracle Table A.

Thanks for you solutions.

Nasty
You need to set up a linked server from SQL to Oracle, this will give you access to the table.

This is a particularly nasty thing to have to do, the server name will then be hard coded into your procedure and will require modification when you promote the proc from dev/uat to production. (and I hate linked servers and I hate setting up Oracle and....)

Good Luck.

Never underestimate the power of human stupidity
RAH

I have 2 columns with data type "Datetime"
i want to write a query which will return max of these 2 datetimes and also i want to consider the time part of the fields i.e hours and minutes, but all this without using stored procedures or function.
Can any one help please.

<br />
DECLARE @Dates TABLE (date1 DATETIME, date2 DATETIME)<br />
<br />
INSERT INTO @Dates (date1, date2) <br />
	VALUES (DATEADD(d, -1, GETDATE()), DATEADD(m, -1, GETDATE()))<br />
INSERT INTO @Dates (date1, date2) <br />
	VALUES (DATEADD(hh, -1, GETDATE()), DATEADD(hh, -2, GETDATE()))<br />
<br />
SELECT * FROM @Dates<br />
<br />
SELECT CASE WHEN date1 > date2 THEN date1 ELSE date2 END AS Date<br />
FROM @Dates<br />

Mark's blog: developMENTALmadness.blogspot.com

thank you Very Much Mark for the solution

I have data of a start date, which I imported into SQL from an excell spreadsheet in which the date was an nvarchar type. So I imported it as smalldatetime, with the hopes that when I used a calender on my webpage it would use the same format and work beautifully! but it doesn't! for some reason, the calender uses the format: yyyy/mm/dd and the format I have it in my database is yyyy/dd/mm (and that's how it came with the import with the smalldatetime type). And I have fiddled with it all day and can't figure out how to get them to the same format as I can't get my SQL database to change my data to that format.

Am I doing something wrong or is this a silly problem that someone can help me with?

Thanks!

DATETIME and SMALLDATETIME fields will always have the same format. You need to convert it to a string and specify the output format you would like to use. The method for doing this will depend on the language you are using but any language should have a method for formatting date/time strings.

If you are using .NET for your web pages the DateTime class has overloaded ToString() methods for formatting the string. If you want the output of your SQL query to be formatted then use the CONVERT function:

.NET (c#)

<br />
myDateObject.ToString("yyyy/dd/MM");<br />

T-SQL

<br />
SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR, GETDATE(), 120), 1, 10), '-', '/') AS Date<br />

T-SQL is less flexible, so you either need to concatenate calls to DATEPART (or YEAR, MONTH, DAY) or do something like the example above (SUBSTRING and REPLACE along with the 'style' argument of the CONVERT function).

Mark's blog: developMENTALmadness.blogspot.com

Thanks, I will try that tomorrow morning. Will try the T-SQL script as I will only be using sql to do my coding.

appreciate the help Smile | :)