extract database content

My server had the following main tool before the harddiak gone dead
MS Windows server 2003
MS Sharepoint Portal Server 2003
MS SQL Server 2000 Enterprise
I already have a new harddisk and the above key software installed. Now
is to get the data back in place. The main data i had backup separetely
and wanted to restore is a .SPB file created using Sharepoint Backup
tool. I have since manage to use SQL Enterprise Manager to restore the
content of the .SPB file into a new database.
I have been trying to restore the data using Sharepoint Restore tool
but have yet to see any result. I am exploring other alternatives. One
of the alternative in my mind is to extract the content of this
database using any tools suitable. Most of the data in this database
are MS office document, PDF file and images. I am not familiar with SQL
database and hence not sure if there are tools like that readily
available.
Thank you very much in advance for any advice to get the file extracted
such that I can rebuild my system.
Best Regards
ChanHi,
just to close the loop,
I found a tool that works very well so far in extracting the files in
my databases. It is capable of viewing all the version history but not
able to retrieved them, anyway who cares this since I can extract the
latest files :)
http://www.sharepoint-france.com/Pr...ery/Release.rar
Best Regards
Chan

Extract a program from SQL 2000?

Hello,
We have a web based program that is using SQL 2000 as the db. Sql 2000 is running on a Windows 2003 server.

Is it possible to extract the SQL 2000 program(web based) with a clean db onto a blanket CD so another person can install it?

Any help would greatly be appreciated.

Mike

If you mean, is it possible to create an Installation disk for SQL Server, the answer is -No.

The 'another person' will have to purchase, or otherwise, legitimately obtain and license SQL Server.

Very, very Silly Question.

External Stored Procedure in SQL Server 2005(x64)

I have generated a DLL file in VC++ 2005 by a 'C' file. It works fine when I put in a 32bits machine(32bits Windows Server 2003 + 32 bits SQL Server 2005).

However, when I build it into 64 bits, it doesn't work in a 64 bits machine. I have checked by Dependenct Walker, the DLL generated is linked with KERNEL32.DLL / OPENDS60.DLL / MSVCR80D.DLL, all of these DLL files are on the 64 bits machines and linked correctly.

I used the command

sp_addextendedproc 'abc', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll'

to create a ext. stored procedure. When I run it, the error message shows that

Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll, or one of the DLLs it references. Reason: 126(error not found).

I would like to ask what is cause of the problem? Do I need to use CLR instead?

Thank you very much!!~

BTW, it is because of your thread I posted the "32Bit Vs 64Bit" SQLCLR platform differences thread. If there are any definite differences this would be a great addition to my upcoming book. Have you experienced any other problems on 64bit platform for sqlclr?

thanks,

derek

extended stored procedures

Hello!
I have a difficult situation with a clustered server. It consists of two
parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003. Cluster_1
and Cluster_2 are with 6 GB RAM.
The whole clustered server is named Cluster.
SQL Server 2000 is installed on Cluster.
Service Pack 4 for SQL Server 2000 is installed and latest hot-fix KB899761
is installed too.
I have some extended stored procedures implemented in xp_parser.dll.
One of those is - lets say xp_MyProc.
The problem is - when I call xp_MyProc, the returned result is correct, but
the execution time is extremely long.
xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
Cluster_2.
The shared hard disk between Cluster_1 and Cluster_2 is F:
A copy of xp_parser.dll is not located on F:
What is the cause of the extremely long execution time of xp_MyProc?
When I call xp_MyProc for second and third time - the situation is not
better.
Thank you!
What other environment did this xp run quick? Did it ever? What does it
do?
Here's my car and it runs slow Mr. Mechanic, but I'm not going to tell you
what type of car it is. Good luck.
Sincerely,
Anthony Thomas

"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>
|||XP's should be installed in the BINN directory, which is on the shared
resource disk, same as all the other SQL Server XP's
In that case you do not have to register the XP with an absolute PATH so it
simply works.
With regards to your performance, does it perform when not running on a
cluster?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
> Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
> KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
> but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>

Extended Stored Procedure problem

Hi,
I made some extended strored procedures
and they goes well when they are placed at
c:\windows\system32 or the same derectory to
sqlservr.exe which is usualy in C:\Program Files\
Microsoft SQL Server\MSSQL\Binn.

I thought it's OK wherever they are placed
as long as I put them to the system path.

But actually they didn't work at all
when they were placed to the other path
that I had newly added as a system path.

Here is the contents of 'path' valiable:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\Sy stem32\Wbem;C:\WINDOWS\System32\AmiClick;C:\Progra m Files\Microsoft SQL Server\80\Tools\BINN:C:\Program Files/INC

I just added only "C:\Program Files/INC",
placed the extended stored procedures there,
then called them through a trigger.

Can Somebody tell me what was wrong?
Please help me if you have any informations about this issue.

regards,
Takanobu MaekawaKeep in mind that the forward slash causes all kinds of problems in the NT environment. I would very strongly recommend that you change it to a backslash, which is the NT default path element separator.

-PatP|||Thanks.

I've fixed it now as you said and tried again,
but the problem remains. mm..why??|||Have you enabled the path on SQL server and from where you;re calling this XP.|||Thanks for reply, but I couldn't understand what you meant ...

I just want to put the Extended SPs into the place except system32
or bin directory of SQL Server or the other system directory,
and then run them from the remote hosts.

well,So far,I wonder the problem might be that
the SQL Server uses local system account.(I don't want to change it)

I guess every thing would be OK if I can put the path which I want to add to the local system account's path...but how??
Isn't the system path shared with the local system acount's one?
I'm very confused.

Is there some one who had a problem like me and solved it?
If you know any informations about this issue,please tell me too.

regards.|||ENsure the path is set on the server not on the local client machine.|||Originally posted by Satya
ENsure the path is set on the server not on the local client machine.

Yes, I'm sure that the path is set on the server's system path, not the client's one ...|||Can you post the error generated while working with this XP.|||well, here is the result of running one of XPs
through the Query Analyzer.

----------------------
<R_DB_T_KANJA>Jn 03 17 2004 10:36AM
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:390
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:390
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
<R_DB_T_KANJA>I 03 17 2004 10:36AM
<R_DB_T_KANJA2>I 03 17 2004 10:36AM
R_MQ_T_KANJA<UPDATE> Jn 17 03 2004 10:36:27:967
ODBC: bZ[W 0Ax 16A 1
DLL INC_MNSQL.dll [h??AQ?? DLL 1 ?[h??BR : 126(w肳?W[??B)B

R_MQ_T_KANJA<UPDATE> @.lSeqNum=1 @.nByotoCode=21
R_MQ_T_KANJA<UPDATE> (?ID 1005)XV?B @.Old_bytSex=1 @.New_bytSex =0 (NULL -> 0)

(1 ?)
R_MQ_T_KANJA<UPDATE> I 17 03 2004 10:36:28:060
------------------------

As you see, the localization of SQL Server is set to Japanese.
Should I translate the messages into English?

'R_DB_ ...' are output of print function in triggers.
XP is called from them.
The DLL the XP is written is INC_MNSQL.dll.
The error said the DLL can not be loaded.|||Based upon the last link of your reply, I feel this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;Q151596 may be suitable to resolve.

HTH|||Can you put the XP DLL into the SQL binary directory (by default, C:\Program Files\Microsoft SQL Server\Mssql\Binn although that can be changed at installation time) and have them work correctly? Did you by any chance specify a pathname when you called sp_addextendedproc (which is a bad thing)?

-PatP|||Finally, I've solved this problem.
I don't know this is the essential solution for this problem,
but today, once I rebooted the Server machine
(it's been long time --at least 2 months since I rebooted the server machine last time),then the system path which I added began to work well !!

Extended Stored Procedure Permissions Problem

I have devloped a .NET windows service to manage
server-side traces for an ongoing database auditing effort
here at work. The service creates traces, rolls them over
periodically, and loads the trace file contents into a
reporting instance. This service logs into each audited
instance using its a domain user account. The service must
call the sp_trace_create, sp_setstatus, sp_setevent, and
sp_setfilter extented stored procedures. Addtionally, it
must access the fn_trace_getinfo and fn_trace_gettable
functions.
The service works very well when the domain account is a
member of the sysadmin role in an audited instances. The
service fails to work when it is just a regular db user
with EXECUTE permissions on the above stored procedures.
The service also does not work when configured as a dbo in
the master database of the audited instance.
I am guessing that this issue arises from the trace
procedures being extended stored procedures and perhaps
there is some underlying OS permission issue. Can anyone
offer some guidance that can help me avoid running this
service as a sysadmin?
Thanks in advance,
TimIn SQL2000 you have to be a sysadmin to run trace procs. This is a change
from SQL7 where you could grant exec on the extended stored procedures
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
> I have devloped a .NET windows service to manage
> server-side traces for an ongoing database auditing effort
> here at work. The service creates traces, rolls them over
> periodically, and loads the trace file contents into a
> reporting instance. This service logs into each audited
> instance using its a domain user account. The service must
> call the sp_trace_create, sp_setstatus, sp_setevent, and
> sp_setfilter extented stored procedures. Addtionally, it
> must access the fn_trace_getinfo and fn_trace_gettable
> functions.
> The service works very well when the domain account is a
> member of the sysadmin role in an audited instances. The
> service fails to work when it is just a regular db user
> with EXECUTE permissions on the above stored procedures.
> The service also does not work when configured as a dbo in
> the master database of the audited instance.
> I am guessing that this issue arises from the trace
> procedures being extended stored procedures and perhaps
> there is some underlying OS permission issue. Can anyone
> offer some guidance that can help me avoid running this
> service as a sysadmin?
> Thanks in advance,
> Tim|||Thanks, Jasper.

>--Original Message--
>In SQL2000 you have to be a sysadmin to run trace procs.
This is a change
>from SQL7 where you could grant exec on the extended
stored procedures
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Tim Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
>
>.
>

Express Edition SP2 - How long to wait for complete installation?

Just curious if there's a reason why everytime I try to apply the Microsoft SQL Server 2005 Express Edition Service Pack 2 from Windows Update that it will sit at "Installing" and appear to do nothing (no hard drive activity, but the MSI and setup processes are chewing a little bit of CPU time). How long should I give this update to complete? I am running a 2-CPU dual-core Xeon 3.0GHz w/ 2GB of RAM setup, so I would have thought it would be completed very quickly. :(

Edit:

Disregard; I had to wait for a full 15 minutes! :OFifteen minutes isn't a 'unusual' amount of time. Depends upon what activity is occuring on the computer.|||I was surprised because nothing else was chewing CPU and Firefox was the only other application open besides IE7 running the Microsoft Update.

Expose RS to Internet?

Hello I have just created some reports and I want to expose them to the
internet without windows authentication. I mean that every user outside can
see my reports without being part of the domain.
Thanks for your help.From everything that I have read, I don't think that this is possible. You
need to authenticate against RS using Windows Authentication or some other
type of Forms Authentication (which is only supported in Enterprise Edition).
If you figure out a way, please do share, but I don't think that it is
possible.
I have the same issue, but have SQL Server Standard Edition and can't figure
out away to make this happen.
"Luis Esteban Valencia" wrote:
> Hello I have just created some reports and I want to expose them to the
> internet without windows authentication. I mean that every user outside can
> see my reports without being part of the domain.
> Thanks for your help.
>
>

Exporting to Excel thru an SP

Hi all,
I know how to use DTS create a package and use the cammand shell to call the
DTS package.
Is there a way to BCP a table into Excel using windows authentication
without creating the DTS package and calling it?
TIA,
Joe1.Use BULK INSERT
2 Opendatasource
INSERT INTO
OpenDataSource('Microsoft.Jet.OLEDB.4.0' ,
'Data Source="D:\testsource\testsource.xls";Extended properties=Excel
5.0')...sheet1$
SELECT col1,col2 from table_name
But, the excel should have the columns and should be on the server.
Hope this helps
--
"jaylou" wrote:

> Hi all,
> I know how to use DTS create a package and use the cammand shell to call t
he
> DTS package.
> Is there a way to BCP a table into Excel using windows authentication
> without creating the DTS package and calling it?
> TIA,
> Joe
>|||1. DO a BCP out to Text File
2. Convert the Text File to Excel
I would recommend this.
"Omnibuzz" wrote:
> 1.Use BULK INSERT
> 2 Opendatasource
> INSERT INTO
> OpenDataSource('Microsoft.Jet.OLEDB.4.0' ,
> 'Data Source="D:\testsource\testsource.xls";Extended properties=Excel
> 5.0')...sheet1$
> SELECT col1,col2 from table_name
> But, the excel should have the columns and should be on the server.
> Hope this helps
> --
>
>
> "jaylou" wrote:
>

exporting sql table into excel.

Hi,
I have a windows form in visual studio and am trying to export a table from the local sql database into a excel worlbook. I followed this example: (http://support.microsoft.com/kb/307029/en-us) that uses the example northwind mdb database and it worked ,but when I try to change the connection string I get an error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Could anyone let me know the correct way to go about this. My ultimate aim is to export a report.

How did you change the connection string?

This link may be useful:

www.connectionsstrings.com

|||

Hey thanks,

That links not working for me for some reason.

This is my connection string at the moment

private string strConn = "Provider= SQLOLEDB;Data Source="

+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\My Documents\\Visual Studio 2005 \\Projects\\CallManSys19Jul\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";

and my code for exporting to database

private void AnalyseButton_Click(object sender, EventArgs e)

{

OleDbConnection objConn = new OleDbConnection(strConn);

try

{

//Fill a dataset with records from the Customers table.

OleDbCommand objCmd = new OleDbCommand(

"Select Call_id, name, dept, "

+ "location, Resolved_by, Problem_Type from Call Reports", objConn);

//"Select CustomerID, CompanyName, ContactName, "

//+ "Country, Phone from Customers", objConn);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();

objAdapter.SelectCommand = objCmd;

DataSet objDataset = new DataSet();

objAdapter.Fill(objDataset);

//Create the FileStream to write with.

System.IO.FileStream fs = new System.IO.FileStream(

"C:\\Call Reports.xml", System.IO.FileMode.Create);

//Create an XmlTextWriter for the FileStream.

System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(

fs, System.Text.Encoding.Unicode);

//Add processing instructions to the beginning of the XML file, one

//of which indicates a style sheet.

// xtw.WriteProcessingInstruction("xml", "version='1.0'");

// xtw.WriteProcessingInstruction("xml-stylesheet",

// "type='text/xsl' href='http://links.10026.com/?link=Call Reports.xsl'");

//Write the XML from the dataset to the file.

objDataset.WriteXml(xtw);

xtw.Close();

//Close the database connection.

objConn.Close();

}

catch (System.Exception ex)

{

MessageBox.Show(ex.Message);

} /**/

now I am getting this error after changing the provider in the connection string from

Provider=Microsoft.Jet.OLEDB.4.0; to Provider= SQLOLEDB

ex.Message = "No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)."

|||

To verify that you are using SQL Server, in a database query window, execute this string: "SELECT @.@.Version".

Please post the results.

|||

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This was my result from executing the string
|||I don′t know which part of the connection string you changed, but if you want to connect to the user instance of SQL Server the datafile will have to exist before connecting. Did you create it before ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||How do I go about creating the datafile first
I changed the connection string from this
private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

to the above Connectionstrings.
|||

The lifecycle of the applications I did was.

-I created a database using any version of SQL Server and used a server instance.
-Created my objects and the database logic within the database.
-Dettached the database
-Used it for user istances without using the ldf file.

You can also right click on the project > Add > New item > SQL Database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = CMSDatabaseDataSet1.Tables[0];
int ColumnIndex=0;
foreach(System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}

I've tried to use the above code but I recieve this error

Error 1 An object reference is required for the nonstatic field, method, or property 'CallManagementSystem.CMSDatabaseDataSet1.Tables.get' 407 C:\Documents and Settings\bentc2\My Documents\Visual Studio 2005\Projects\CallManagementSystem\CallManagementSystem\Workstation.cs 31 CallManagementSystem

|||

Die to the error message you are trying to use the instance of a class variable in a static method, this is not possible. Please post the whole code to investigate this issue and make a suggestion to improve your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey I now have the the code creating an excel sheet with the column headers but none of the rows

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using RKLib.ExportData;
using Excel;

namespace CallManagementSystem
{
public partial class Workstation : Form
{
LogCall logCall = new LogCall();
int tempid;
string desc;
String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
private DataSet CallReports = new DataSet();
private System.Windows.Forms.Label lblMessage;

private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");

public Workstation()
{
InitializeComponent();
}

private void populateWorkLoadList()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
wLoadListView.Columns.Clear();
wLoadListView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'True'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
wLoadListView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
wLoadListView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void populateIncomingCallReports()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
incomingCallReportslistView.Columns.Clear();
incomingCallReportslistView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'False'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
incomingCallReportslistView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
incomingCallReportslistView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void Workstation_Load(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_Panel2_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_SplitterMoved(object sender, SplitterEventArgs e)
{

}

private void CreateButton_Click(object sender, EventArgs e)
{
logCall.Show();
}

private void closeButton_Click(object sender, EventArgs e)
{
System.Windows.Forms.Application.Exit();
}

private void button1_Click(object sender, EventArgs e)
{
simple_search searchFunction = new simple_search();
searchFunction.Show();
}

private void timer_Tick(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void textBox1_TextChanged(object sender, EventArgs e)
{

}

private void listBox1_SelectedIndexChanged_2(object sender, EventArgs e)
{

}

private void wListGroupBox_Enter(object sender, EventArgs e)
{

}

private void wLoadListView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems = wLoadListView.SelectedItems;

string selectedTitle2 = selectedItems[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle2);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void wLoadListView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems3 = wLoadListView.SelectedItems;

string selectedTitle4 = selectedItems3[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle4);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void incomingCallReportslistView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems5 = incomingCallReportslistView.SelectedItems;

string selectedTitle6 = selectedItems5[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle6);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void incomingCallReportslistView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems7 = incomingCallReportslistView.SelectedItems;

string selectedTitle8 = selectedItems7[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle8);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void Analyse_Click(object sender, EventArgs e)
{

SearchFunction analysis = new SearchFunction();
analysis.Show();

// DataSet DS = (DataSet)Session["MyDS"];

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = this.CMSDatabaseDataSet.Tables[0];
int ColumnIndex = 0;
foreach (System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();

exporting sql table into excel.

Hi,
I have a windows form in visual studio and am trying to export a table from the local sql database into a excel worlbook. I followed this example: (http://support.microsoft.com/kb/307029/en-us) that uses the example northwind mdb database and it worked ,but when I try to change the connection string I get an error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Could anyone let me know the correct way to go about this. My ultimate aim is to export a report.

How did you change the connection string?

This link may be useful:

www.connectionsstrings.com

|||

Hey thanks,

That links not working for me for some reason.

This is my connection string at the moment

private string strConn = "Provider= SQLOLEDB;Data Source="

+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\My Documents\\Visual Studio 2005 \\Projects\\CallManSys19Jul\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";

and my code for exporting to database

private void AnalyseButton_Click(object sender, EventArgs e)

{

OleDbConnection objConn = new OleDbConnection(strConn);

try

{

//Fill a dataset with records from the Customers table.

OleDbCommand objCmd = new OleDbCommand(

"Select Call_id, name, dept, "

+ "location, Resolved_by, Problem_Type from Call Reports", objConn);

//"Select CustomerID, CompanyName, ContactName, "

//+ "Country, Phone from Customers", objConn);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();

objAdapter.SelectCommand = objCmd;

DataSet objDataset = new DataSet();

objAdapter.Fill(objDataset);

//Create the FileStream to write with.

System.IO.FileStream fs = new System.IO.FileStream(

"C:\\Call Reports.xml", System.IO.FileMode.Create);

//Create an XmlTextWriter for the FileStream.

System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(

fs, System.Text.Encoding.Unicode);

//Add processing instructions to the beginning of the XML file, one

//of which indicates a style sheet.

// xtw.WriteProcessingInstruction("xml", "version='1.0'");

// xtw.WriteProcessingInstruction("xml-stylesheet",

// "type='text/xsl' href='http://links.10026.com/?link=Call Reports.xsl'");

//Write the XML from the dataset to the file.

objDataset.WriteXml(xtw);

xtw.Close();

//Close the database connection.

objConn.Close();

}

catch (System.Exception ex)

{

MessageBox.Show(ex.Message);

} /**/

now I am getting this error after changing the provider in the connection string from

Provider=Microsoft.Jet.OLEDB.4.0; to Provider= SQLOLEDB

ex.Message = "No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)."

|||

To verify that you are using SQL Server, in a database query window, execute this string: "SELECT @.@.Version".

Please post the results.

|||

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This was my result from executing the string
|||I don′t know which part of the connection string you changed, but if you want to connect to the user instance of SQL Server the datafile will have to exist before connecting. Did you create it before ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||How do I go about creating the datafile first
I changed the connection string from this
private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

to the above Connectionstrings.
|||

The lifecycle of the applications I did was.

-I created a database using any version of SQL Server and used a server instance.
-Created my objects and the database logic within the database.
-Dettached the database
-Used it for user istances without using the ldf file.

You can also right click on the project > Add > New item > SQL Database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = CMSDatabaseDataSet1.Tables[0];
int ColumnIndex=0;
foreach(System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}

I've tried to use the above code but I recieve this error

Error 1 An object reference is required for the nonstatic field, method, or property 'CallManagementSystem.CMSDatabaseDataSet1.Tables.get' 407 C:\Documents and Settings\bentc2\My Documents\Visual Studio 2005\Projects\CallManagementSystem\CallManagementSystem\Workstation.cs 31 CallManagementSystem

|||

Die to the error message you are trying to use the instance of a class variable in a static method, this is not possible. Please post the whole code to investigate this issue and make a suggestion to improve your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey I now have the the code creating an excel sheet with the column headers but none of the rows

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using RKLib.ExportData;
using Excel;

namespace CallManagementSystem
{
public partial class Workstation : Form
{
LogCall logCall = new LogCall();
int tempid;
string desc;
String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
private DataSet CallReports = new DataSet();
private System.Windows.Forms.Label lblMessage;

private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");

public Workstation()
{
InitializeComponent();
}

private void populateWorkLoadList()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
wLoadListView.Columns.Clear();
wLoadListView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'True'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
wLoadListView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
wLoadListView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void populateIncomingCallReports()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
incomingCallReportslistView.Columns.Clear();
incomingCallReportslistView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'False'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
incomingCallReportslistView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
incomingCallReportslistView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void Workstation_Load(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_Panel2_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_SplitterMoved(object sender, SplitterEventArgs e)
{

}

private void CreateButton_Click(object sender, EventArgs e)
{
logCall.Show();
}

private void closeButton_Click(object sender, EventArgs e)
{
System.Windows.Forms.Application.Exit();
}

private void button1_Click(object sender, EventArgs e)
{
simple_search searchFunction = new simple_search();
searchFunction.Show();
}

private void timer_Tick(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void textBox1_TextChanged(object sender, EventArgs e)
{

}

private void listBox1_SelectedIndexChanged_2(object sender, EventArgs e)
{

}

private void wListGroupBox_Enter(object sender, EventArgs e)
{

}

private void wLoadListView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems = wLoadListView.SelectedItems;

string selectedTitle2 = selectedItems[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle2);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void wLoadListView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems3 = wLoadListView.SelectedItems;

string selectedTitle4 = selectedItems3[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle4);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void incomingCallReportslistView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems5 = incomingCallReportslistView.SelectedItems;

string selectedTitle6 = selectedItems5[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle6);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void incomingCallReportslistView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems7 = incomingCallReportslistView.SelectedItems;

string selectedTitle8 = selectedItems7[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle8);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void Analyse_Click(object sender, EventArgs e)
{

SearchFunction analysis = new SearchFunction();
analysis.Show();

// DataSet DS = (DataSet)Session["MyDS"];

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = this.CMSDatabaseDataSet.Tables[0];
int ColumnIndex = 0;
foreach (System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();