Excel Files against SQL Server - validation

Hi
I'm rather new to ASP.NET (was working with win forms before) and having a few problems with my first app.
To the point - this is an internal system - every week about 50 branches send financial reports to the headquaters where those excel files are validated against data in sql server. after file is uploaded to the server I'm opening it using Excel object model and iterating through it line by line. Apart from format and datatype checking, for each line there are ~2 stored procedures invoked
Everything is working actually but my concern is performance.. After about 800 rows it is getting really slow.. Appreciate any comments on this.
thanks
AMWhen the Excel files are that many you have two options use DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. The solution with the XP_CMDSHELL can be scheduled to run for four hours a day five days a week. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

Excel destination fails

Hi All!

I am trying to create a table destination to excel which fails with the following error that " Table cannot be created " System Error in Field definition" where as the same works in the Falt File Destination. What could be the problem?

Thanks

jas

It maybe due to the fact that excel has in built data types and column sizes attached to the columns whereas a flat file does not have that.

Try creating a table first and then map the excel columns to it to extract the records from the excel spreadsheet. When you import into a staging table, it is best to just keep the columns as varchar(255). If you have fields like remarks field that could be even bigger, you might want to customize your staging table.sql

Excel as a Data Source

I am building a report that uses an Excel Doc as its data source. I used a
system ODBC as the data connector, and taht works fine per the test button,
but I am not sure what the query syntax would look like. Any help would be
greatly appreciated!!
Thanks!Never mind. After much trial and error, I got the syntax down:
select * from [<sheet name>$] where <column name>=<value>
"Carl Henthorn" wrote:
> I am building a report that uses an Excel Doc as its data source. I used a
> system ODBC as the data connector, and taht works fine per the test button,
> but I am not sure what the query syntax would look like. Any help would be
> greatly appreciated!!
> Thanks!

Excel and pdf not searcheable

I cannot find a simple string or number within ANY and EVERY Excel file and
the only pdf I have EVER exported. My colleague System Administrator has
tried DOS and Search... also to no avail...
I have tried DOS, Windows Explorer Search..., Copernic Desktop Search and
MSN Desktop Search (Beta - circa yesterday). NONE of these apps is able to
find a result.
Is this a known bug (couldn't find another topic with 'search' and Excel in
the subject)? A configuration problem? What?
(Many) sample files can be supplied...
RS SP1 on XP/Pro SP1 with all cumulative updates and Office 2003 updates.Hi,
From your descriptions, I understood that you would like to search the
context in export file Excel and PDF. Have I understood you? Correct me if
I was wrong.
Based on my test, I deploied sample report Product Line Sales.rdl (which
locates C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Reports) and I was able to use Search Funcation in Excel
(Edit -> Find) and in Acrobat Reader (Edit -> Search).
Would you please deploy these sample reports to your server and let me know
whether you could use search function (If you still cannot use search to
find the result, you are encourage to let me know the detail steps you do
with sample reports)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I can search the sample report that is exported to the same folder as the
"failed searches" xls files.
I can send you the rdl and Excel file for the file wherein I cannot
search...
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KQ9$Zw$DFHA.3900@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> From your descriptions, I understood that you would like to search the
> context in export file Excel and PDF. Have I understood you? Correct me if
> I was wrong.
> Based on my test, I deploied sample report Product Line Sales.rdl (which
> locates C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Reports) and I was able to use Search Funcation in Excel
> (Edit -> Find) and in Acrobat Reader (Edit -> Search).
> Would you please deploy these sample reports to your server and let me
know
> whether you could use search function (If you still cannot use search to
> find the result, you are encourage to let me know the detail steps you do
> with sample reports)
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi,
Thanks for your prompt updates!
First of all,please understand that newsgroups are staffed weekdays by
Microsoft Support professionals to answer your non-urgent, break/fix
systems and applications questions. Our goal is to provide 24 hour response
to all questions.
Without datasource, I am afraid it will be hard to reproduce it on my side,
anyway, you are welcome to send rdl and excel files to
v-mingqc@.microsoft.com. I will have a look into it.
However, To find out the root cause of this issue we may need to analyze
what Reporting Services do internal, this work has to be done by contacting
Microsoft Product Support Services. Therefore, we probably will not be able
to resolve the issue through the newsgroups. If the issue is urgent, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
Thank you for your patience and corporation.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If you could point me to the URL that will actually allow me to open an
incident using my MSDN Universal Subscription, I'd be MOST grateful... (The
MSDN Site hasn't exactly been helpful in this regard...!)
Thanks.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:3dRcZxmEFHA.4072@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Thanks for your prompt updates!
> First of all,please understand that newsgroups are staffed weekdays by
> Microsoft Support professionals to answer your non-urgent, break/fix
> systems and applications questions. Our goal is to provide 24 hour
response
> to all questions.
> Without datasource, I am afraid it will be hard to reproduce it on my
side,
> anyway, you are welcome to send rdl and excel files to
> v-mingqc@.microsoft.com. I will have a look into it.
> However, To find out the root cause of this issue we may need to analyze
> what Reporting Services do internal, this work has to be done by
contacting
> Microsoft Product Support Services. Therefore, we probably will not be
able
> to resolve the issue through the newsgroups. If the issue is urgent, I
> recommend that you open a Support incident with Microsoft Product Support
> Services so that a dedicated Support Professional can assist with this
> case. If you need any help in this regard, please let me know.
> Thank you for your patience and corporation.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi,
Worldwide technical support contact information can be found in the
following table. You may also contact your local MSDN Subscriptions
Services Center to inquire about technical support options available in
your country and/or region. Or, if you already have a Microsoft support
contract, you can submit an incident online.
Technical Support
http://msdn.microsoft.com/subscriptions/support/technical/
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Excel - Read-only

I created a system DSN for an Excel file and I have been using it for a
while. I can read from it as well as update it. (The Excel file
includes weekly updates to some records.)

Then I tried to create the same connection with an explicit connection
string. It opens the file fine, I can read from it and all. But when
I try to update it, it complains that the database is read-only. I am
using the connection string: "Driver=Microsoft Excel Driver
(*.xls);DBQ=excel-fle-path;Uid=;Pwd="

Is there a switch to include to say that I would like to open the Excel
file as a read/write database just like via the ODBC set up?No idea - you'll probably get a better response in an ODBC/OLEDB group,
or one for your programming language/environment - VB, C#, ASP etc. But
there is plenty of information available about connecting to Excel and
working with the data:

http://www.carlprothman.net/Default...roviderForExcel
http://support.microsoft.com/defaul...kb;en-us;278973

Simon|||what driver do you use for DSN?
A good check would be to open the DSN file with notepad and check that.

DM Unseen

Examples of OleDb with SQL Server Compact Edition

Hello,

My C# .NET application is currently using the System.Data.OleDb namespace to access SQL Server 2000 and SQL Server Express databases. I would like to begin using SQL Server Compact Edition to access a local database that has the same structure as the others. I'd like to use the same code regardless of the data source, so I would prefer to use System.Data.OleDb instead System.Data.SqlServerCe.

Where can I find examples of the use of System.Data.OleDb with SQL Server Compact Edition? Some of the specific questions I have are:

1.) How do I build the connection string for the OleDbConnection object?

2.) What are the limitations, if any, of using OleDb instead of SqlServerCe?

I will appreciate any help you can provide.

Thank you,

WTW

re 1: Use this provider name: PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

eventlog

my system W2000+ SQL7.4
why I am getting 3 messages on the eventlog ?
Source: SQLServer/Agent
Category: Alret Engine
Event ID : 319
Unable to read local Event Log ( reason: The event log
file changed between read operation
Source: SQLServer/Agent
Category: Alret Engine
Event ID : 311
Attempting to to re-open the local eventlog..
Source: SQLServer/Agent
Category: Alret Engine
Event ID : 312
Successfully re-opened the local eventlog - NOSomeone cleared the event log and sql agent lost it's original handle to
that file and had to grab a new one. It's normal and nothing to worry
about.
--
Andrew J. Kelly
SQL Server MVP
"glik" <agrolan@.agrolan.co.il> wrote in message
news:01b401c34b66$499c3ff0$a001280a@.phx.gbl...
> my system W2000+ SQL7.4
> why I am getting 3 messages on the eventlog ?
>
> Source: SQLServer/Agent
> Category: Alret Engine
> Event ID : 319
> Unable to read local Event Log ( reason: The event log
> file changed between read operation
> Source: SQLServer/Agent
> Category: Alret Engine
> Event ID : 311
> Attempting to to re-open the local eventlog..
> Source: SQLServer/Agent
> Category: Alret Engine
> Event ID : 312
> Successfully re-opened the local eventlog - NO

event viwer

Is there a way to pull the event viwer from a system sp or
xp with out writing a new xp?Jared,
"pull" means? you can use master..xp_logevent or RAISERROR (...) WITH LOG to
log a user-defined message to the Event Viewer. Does that help?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Jared Kirkpatrick" <jared@.hgpdev.com> wrote in message
news:83da01c35abd$5cc29280$a001280a@.phx.gbl...
> Is there a way to pull the event viwer from a system sp or
> xp with out writing a new xp?|||That works if I need to log an event, but I need to read
the events.
>--Original Message--
>Jared,
>"pull" means? you can use master..xp_logevent or
RAISERROR (...) WITH LOG to
>log a user-defined message to the Event Viewer. Does that
help?
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Jared Kirkpatrick" <jared@.hgpdev.com> wrote in message
>news:83da01c35abd$5cc29280$a001280a@.phx.gbl...
>> Is there a way to pull the event viwer from a system sp
or
>> xp with out writing a new xp?
>
>.
>

Event Log Error: Event 19011

Hi,
My system is running NT4 SP6 and SQL2000 Developer Edition SP3. I am getting Event 19011 error in the event log. The description is: "SuperSocket info: gethostbyname(MSAFD Tcpip [TCP/IP]) : Error 11001. "
Anyone can help me to understand or resolve this problem would be greatly appreciated.
Thanks,
MichaelHi Michael:
This KB article may be helpful:
http://support.microsoft.com/default.aspx?scid=kb;en-us;830215
Best Wishes
Wei Ci Zhou

Event Log Error: Event 19011

Hi,
My system is running NT4 SP6 and SQL2000 Developer Edition SP3. I am getting Event 1
9011 error in the event log. The description is: "SuperSocket info: gethostbyname(MS
AFD Tcpip [TCP/IP]) : Error 11001. "
Anyone can help me to understand or resolve this problem would be greatly ap
preciated.
Thanks,
MichaelHi Michael:
This KB article may be helpful:
http://support.microsoft.com/defaul...kb;en-us;830215
Best Wishes
Wei Ci Zhou

Event ID: 17053 - Operating system error 112(error not found) encountered

Hi there

I have recently installed a new server for a client running SBS2003 R2 with SQLServer 2005.

I noted that the backup failed the SQL Server database on the consistenty check. On checking the event log I see this message as per above.

It reads in the description

c:\data\law\sqldata\data41_Data.MDF:MSSQL_DBCC6:

Operating system error 112(error not found) encountered.

Looking in the error log I see :

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

Error: 17053, Severity: 16, State: 1.

C:\Data\LAW\sqldata\data41_Data.MDF:MSSQL_DBCC6: Operating system error 112(error not found) encountered.

DBCC CHECKDB (data41) WITH no_infomsgs executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 26 seconds.

This is a new server with about 90% of disk space NTFS still available

Any idea's thanks.

Error 112 means there is not enough space on disk. The system uses sparse file and alternate data stream for its dbcc operation. Look like your C:\ drive does not have enough disk space - you need at least the same amount as the original database.

|||

Hi Thanks for the reply.

The server is new with sbs2003 r2 with about 65Gb free space on C:\ so it can't be not enough free space. The database is not very big..

Any more idesa's

Thanks

Stephen

|||If your sql service is running under any service account then check necessary NTFS privilege for the folder you have mentioned above for that service account.|||

If the service account can't see/get to the disk (doesn't matter if it's empty or not), it will generate error.

Try setting the service account for sqlserver as LocalSystem and try creating the db again.

Event ID: 17053 - Operating system error 112(error not found) encountered

Hi there

I have recently installed a new server for a client running SBS2003 R2 with SQLServer 2005.

I noted that the backup failed the SQL Server database on the consistenty check. On checking the event log I see this message as per above.

It reads in the description

c:\data\law\sqldata\data41_Data.MDF:MSSQL_DBCC6:

Operating system error 112(error not found) encountered.

Looking in the error log I see :

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

Error: 17053, Severity: 16, State: 1.

C:\Data\LAW\sqldata\data41_Data.MDF:MSSQL_DBCC6: Operating system error 112(error not found) encountered.

DBCC CHECKDB (data41) WITH no_infomsgs executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 26 seconds.

This is a new server with about 90% of disk space NTFS still available

Any idea's thanks.

Error 112 means there is not enough space on disk. The system uses sparse file and alternate data stream for its dbcc operation. Look like your C:\ drive does not have enough disk space - you need at least the same amount as the original database.

|||

Hi Thanks for the reply.

The server is new with sbs2003 r2 with about 65Gb free space on C:\ so it can't be not enough free space. The database is not very big..

Any more idesa's

Thanks

Stephen

|||If your sql service is running under any service account then check necessary NTFS privilege for the folder you have mentioned above for that service account.|||

If the service account can't see/get to the disk (doesn't matter if it's empty or not), it will generate error.

Try setting the service account for sqlserver as LocalSystem and try creating the db again.

event id 17055

Hi,
I having a problem starting sql server in a cluster. The error has the
description;
17113:
initconfig: Error 2(the system cannot find the file specified.) opening
'master.mdf' for configuration information
did you check if the file master.mdf exist?
first check in cluster administrator if the configuration is ok and the
resources are available.
If the master.mdf is corrupt, you can't start sqlserver.
So you have to do a restore.
Start up SQL server in single user mode:
check this ARTICLE!
http://articles.techrepublic.com.com/5100-1035_11-5025441.html
GOOD LUCK!
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"snazzy" wrote:

> Hi,
> I having a problem starting sql server in a cluster. The error has the
> description;
> 17113:
> initconfig: Error 2(the system cannot find the file specified.) opening
> 'master.mdf' for configuration information
|||Hi,
I am able to start the SQL server manually through a command line. The
error occurs when I try to start SQL server through the Cluster
Administrator. Does this mean the master.mdf is still corrupt?
Thanks
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> did you check if the file master.mdf exist?
> first check in cluster administrator if the configuration is ok and the
> resources are available.
> If the master.mdf is corrupt, you can't start sqlserver.
> So you have to do a restore.
> Start up SQL server in single user mode:
> check this ARTICLE!
> http://articles.techrepublic.com.com/5100-1035_11-5025441.html
> GOOD LUCK!
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
>
>
> "snazzy" wrote:
|||More likely that the quorum registry entries that locate the master database
are corrupt or the installation is not properly configured for failover
clustering.
Can you provide some historical context?
If it is registry keys, then you will need to perform a cluster recovery
scenario:
http://support.microsoft.com/kb/258078
http://support.microsoft.com/kb/266274
Good luck.
Sincerely,
Anthony Thomas

"snazzy" <snazzy@.discussions.microsoft.com> wrote in message
news:9F443B16-693C-4F52-B204-4C7E96A539B7@.microsoft.com...[vbcol=seagreen]
> Hi,
> I am able to start the SQL server manually through a command line. The
> error occurs when I try to start SQL server through the Cluster
> Administrator. Does this mean the master.mdf is still corrupt?
> Thanks
> "Hate_orphaned_users" wrote:
the[vbcol=seagreen]
opening[vbcol=seagreen]

Event Error 108

I periodically get the following event error on the system running SSRS:
Event Type: Error
Event Source: Report Server (MSSQLSERVER)
Event Category: Extension
Event ID: 108
Date: 1/24/2008
Time: 10:04:32 AM
User: N/A
Computer: SERENITY
Description:
Report Server (MSSQLSERVER) cannot load the Report Server DocumentLibrary
extension.
Can anyone tell me what might be causing this?
ThanksHello Tom,
I would like to know more detailed information.
1. What's your SQL Server version? Have you applied latest services pack?
2. Are you using some custom data extension to access the data source?
3. Are you using some subscription to deliver the report?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||1. 9.0.3054
2. No
3. Some reports are delivered by subscription
Thanks
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I would like to know more detailed information.
> 1. What's your SQL Server version? Have you applied latest services pack?
> 2. Are you using some custom data extension to access the data source?
> 3. Are you using some subscription to deliver the report?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Tom,
If you try to run the report, did you find this event?
If you run the report subscription. Did you find the event?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Event 3034

Hi!
I get the following warning in the system event viewer:
Type: Warning
Error ID: 3034
Source: MRxSmb
Description: The redirector was unable to initialize security context or que
ry context attributes.
The last DWord is c0000064.
What does this mean and what can I do to fix it?
Thanks
AnnaHave a look a http://support.microsoft.com/defaul...kb;en-us;263142
Peter
"And if you gaze for long into an abyss, the abyss gazes also into you."
Friedrich Nietzsche
"Anna" wrote:

> Hi!
> I get the following warning in the system event viewer:
> Type: Warning
> Error ID: 3034
> Source: MRxSmb
> Description: The redirector was unable to initialize security context or q
uery context attributes.
> The last DWord is c0000064.
> What does this mean and what can I do to fix it?
> Thanks
> Anna
>

Event 3034

Hi!
I get the following warning in the system event viewer:
Type: Warning
Error ID: 3034
Source: MRxSmb
Description: The redirector was unable to initialize security context or query context attributes.
The last DWord is c0000064.
What does this mean and what can I do to fix it?
Thanks
Anna
Have a look a http://support.microsoft.com/default...b;en-us;263142
Peter
"And if you gaze for long into an abyss, the abyss gazes also into you."
Friedrich Nietzsche
"Anna" wrote:

> Hi!
> I get the following warning in the system event viewer:
> Type: Warning
> Error ID: 3034
> Source: MRxSmb
> Description: The redirector was unable to initialize security context or query context attributes.
> The last DWord is c0000064.
> What does this mean and what can I do to fix it?
> Thanks
> Anna
>

Evaluation period expired

Hi, I am runnig a system with SQL Server 2000, where I get the message (17502): SQL Server evaluation period has expired.

I have valid key for this product, can anybody tell me how to updgrade the SQL Server lisence without a new installation of SQL Server?

Cheers

Kim

Hi,

http://support.microsoft.com/kb/281574/en-us

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de