Reading a directory

I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.

The pivotal step in this is that i run a command that looks like:

CREATE TABLE #DIR (FileName varchar(100))

DECLARE @.Cmd varchar(1050)
SET@.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'

INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!

...Until I try to put that information into a table while calling that
stored procedure:

CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'

When I run this I get:

Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.

Because I use an INSERT EXEC to with the results from the @.Cmd.

Anybody have any ideas how I can get that information into a table?

I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.

So, I would appreciate anybody who can help.

Thanks!

-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||Maybe I'm bot understanding your problem correctly ,, but if you did
CREATE TABLE #DIR (FileName varchar(100))

Quote:

Originally Posted by

>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

INSERT INTO myTABLE
SELECT filename FROM #DIR

would that not do the job?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||utah,

You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.

-- Bill

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'

Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.

Quote:

Originally Posted by

When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?

I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Read/Write Performance

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

Hi Avinash,

Could you provide a bit more information about how you determined the time it took to run the queries? Understanding your testing methodology will help determine if it is contributing or not.

Additionally, you mention you're using the auto attach feature, are you using User Instances as well? When using the VS UI to create connection to a database, the connection string specifies User Instance = True. This should be fine, but it's important to know.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hello Mike Wachal,

Yes we use User Instance and Auto Attach in the connection string to express.

At this time, I dont have a very 'scientific' or for that matter a very solid way to test out the performance. My question came from a general observation and thought I'd bounce it off the expert community to see if there was some caveats built into the use of express particularly with the auto attach mode.

Like I've already mentioned - our general observation is that queries 'Seem' to take longer on express both read and write when compared to 2005 server. Again, this may be a configuration thing aswell. But we are running the default configuration of express as done from within VS.Net 2005 setup and have made no changes what so ever.

On some counts we've put the start time and end time of execution in trace messages and have found the difference in execution times.

Thats all I have at this time,

Thanks and Regards,

Avinash

|||

Thanks Avinash,

In general, SQL Express should perform similarly to other Editions of SQL Server, but we do have limitation that might affect performance. For one thing, SQL Express will only use a single CPU and will only address 1 GB of RAM. If your hardware has more than this, then you could see a performance difference because the non-Express edition would be able to use the extra hardware components to increass performance over Express Edition.

If you're hardware is the same and within the limitations of SQL Express, I'm not sure what could be the issue without knowing more about the specific queries and data that is being queried.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hello Mike,

Many thanks for your response. In our case both the server and the client run the exact same hardware. They are (both) running on 1GB RAM.

I'll watch out for any further issues I may run into - and then bring them to your notice with all the data that I can provide.

Thanks,

Avinash

|||

OK Avinash,

Good luck with this.

As you find specific queries that perform differently you might want to bring those specific queries up in the SQL Database Engine forum. The folks in that forum will likely have some additional ideas both on tuning your queries and why they may perform differently on different Editions.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

sql

Read UNIX file from SQL Server

Is it possibel to run a DTS job to read a file from unix server?It sure is, if the file is on NFS partition.|||And you should install the UNIX drive on Windows machine.|||How do I get the unix drivers for Windows?|||Check the website of UNIX operating system to download these drives. Or, call UNIX operating system maker to ask them. Maybe this paper is useful for you. http://www.databasejournal.com/features/mssql/article.php/1756161

READ UNCOMMITTED - SNAPSHOT

Hi All,
I am having to run a "batch update" on a LARGE table that is used by a repor
t.
i.e.
BEGIN TRANSACTION
EXEC 5_minute_Procedure_On_Large_Table
COMMIT TRANSACTION
This batch update takes 5 minutes to run and during this time the LARGE
table is locked and prevents anyone from accessing the report since it uses
the LARGE table that is being updated in 5_minute_Procedure_On_Large_Table.
- this is not good
My "Sorta" Solution is:
READ UNCOMMITTED is "almost" the right solution - however it does not return
a SNAPSHOT of the table data as it was before the begining of the
5_minute_Procedure_On_Large_Table. Instead it shows the records as they are
being updated. - This gives distorted results
My question is - is there something like READ ONLYCOMMITED records?
If not - any suggestions?
Best Regards,
Mekim
P.S. For those interested in SQL 2005 - this would seem to resolve my issue
- but that's obviously not an option
http://msdn.microsoft.com/sql/defau...hotisola_topic6Mekim
In SQL 2000, there is no way to get a shapshot of the data before your proc
began. It doesn't exist anymore. So you have 3 choices:
1. Wait for the transaction modifying the data to finish. This is the
default behavior.
2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
new values of data that has changed. As you noticed, it does not guarantee
transactional consistency.
3. Use the READPAST hint. This will only show you data that is not locked.
It causes SQL Server to READ PAST locked rows. So it will show you only
COMMITTED data, as you asked for, but it won't show you ALL the data.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mekim" <mekim@.discussions.microsoft.com> wrote in message
news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
> Hi All,
> I am having to run a "batch update" on a LARGE table that is used by a
> report.
> i.e.
> BEGIN TRANSACTION
> EXEC 5_minute_Procedure_On_Large_Table
> COMMIT TRANSACTION
> This batch update takes 5 minutes to run and during this time the LARGE
> table is locked and prevents anyone from accessing the report since it
> uses
> the LARGE table that is being updated in
> 5_minute_Procedure_On_Large_Table.
> - this is not good
> My "Sorta" Solution is:
> READ UNCOMMITTED is "almost" the right solution - however it does not
> return
> a SNAPSHOT of the table data as it was before the begining of the
> 5_minute_Procedure_On_Large_Table. Instead it shows the records as they
> are
> being updated. - This gives distorted results
> My question is - is there something like READ ONLYCOMMITED records?
> If not - any suggestions?
> Best Regards,
> Mekim
> P.S. For those interested in SQL 2005 - this would seem to resolve my
> issue
> - but that's obviously not an option
> http://msdn.microsoft.com/sql/defau...hotisola_topic6
>|||Hi Kalen,
Thank you for your suggestions and it therefore looks like ReadUncommited
(or Read Past) are the best (and only) options
Best Regards,
Mekim
"Kalen Delaney" wrote:

> Mekim
> In SQL 2000, there is no way to get a shapshot of the data before your pro
c
> began. It doesn't exist anymore. So you have 3 choices:
> 1. Wait for the transaction modifying the data to finish. This is the
> default behavior.
> 2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
> new values of data that has changed. As you noticed, it does not guarantee
> transactional consistency.
> 3. Use the READPAST hint. This will only show you data that is not locked.
> It causes SQL Server to READ PAST locked rows. So it will show you only
> COMMITTED data, as you asked for, but it won't show you ALL the data.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "mekim" <mekim@.discussions.microsoft.com> wrote in message
> news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
>
>

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
>> Reads is generally a good thing to focus on, but it really depends on what
>> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
>> I/O-bound, focus on reads.
> My indicators for the performance monitor are showing huge lock counts, and
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess when
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Read source data without waiting for possible locks

hi!

I wonder if anyone can tell me how we can run select queries in an OLE DB data flow task and tell the target SQL 2000 server it should allow reads at all time. Currently when a lock is on the source table our SSIS package will sit and wait untill the lock on the source table is gone.

Thanks.

Marc

Try issueing the READUNCOMMITTED or NOLOCK switch.

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

|||

Hi,

When I simulate a lock on a table with:

begin transaction
update table set column = 'test' where columnkey = 1

and I execute the package reading from this table it will wait untill I commit the transaction. If I change the select statement in the package in select ....... with (nolock) and try running it again it still keeps waiting. However, when I execute the same SQL statement in management studio I will get the results.

Can you explain this?

Thanks

|||

Use NOLOCK on your SSIS query.

Management Studio may be issuing that behind the scenes. (You're deep into Transact-SQL territory here, and there is a better forum for this discussion.)

RE: where and how to run sp_updatestats after upgrading

Hi,
I have a question on where and how to run sp_updatestats after upgrading databases from SQL Server 7.0.
Thanks for your reply.After the successful upgrade from QUery analayzer use the database and run SP_UPDATESTATS to update stats on all the tables or you can run DBCC DBREINDEX for optimum performance.|||Hi Satya,

Thanks for your reply.

Do I need to run sp_updatestats for each database in order to updae stats for all the tables in the Query Analyzer? How does DBCC DBREINDEX works?

Thanks!
Alice

RDLC and Excel - No Grid Lines

Using ASP.NET 2, C#, Web application, we have an rdlc report which will be primarily used to export to an Excel spreadsheet. When we run thw report, then export it, we wind up with a spreadsheet with no gridlines, eg a spreadsheet with invisible cell borders.

Is the a setting or property somewhere which can be changed so that the spreadsheet which opens after the export looks like a standard spreadsheet, that is, with visible cell borders?

Many thanks
Mike Thomas

In the test I just did, setting the BorderStyle to Single on the text boxes I wanted to have borders worked. You should also take a look at http://msdn2.microsoft.com/en-us/library/aa178951(SQL.80).aspx to see how cells are converted. Short and sweet: best, use a table; next best, make sure to align everything.

Larry

|||

Sorry, it's late. I should have also mentioned that every cell in a table and a matrix are textboxes. The BorderStyle is 'none' by default and the BorderColor is 'Black' by default. The properties I mean are those in Visual Studio, not those found through the context menu.

Larry

RDL generation problem

Hi,

I am working with SQL Server 2005 Reporting Service from few days, though I am

not expert, for some reason I have to run on field without having sound

knowledge of RDL, but need your help, gys. I am using SQL Server Business

Intelligence Development Studio to design report.

Here is the procedure of my work to populate a RDL report

I used a stored procedure for the DataSet of my RDL

Then I drag and drop necessary field to my report layout.

Put required parameters to preview tab and then run report.

This is quite simple, I didn't face any problem with this process, even though

the process may not correct, but working perfect. My problem is

when the stored procedure returns multiple result set. The data tab only

shows the first result set though the SP returns multiple result set, I have

run the SP in the Query Analyzer. I don't want change my stored procedure.

And please give me some suggestions about the best procedure

to develop RDL report in real life.

Please reply me ASAP, it’s very urgent.

Thank youTareqe

Hi there,

SQL RS doesn't support multiple resultsets. You will need to either write a wrapper stored procedure to retrieve the dataset you want or modify your stored procedure.

regards,

Andrew

rationale to upgrade from SQL Server 6.5

While providing more functional assistance to a SME, I came accross an application still run with a SQL Server 6.5...Although the database is small, I would imagine that they could benefit from upgrading to 2000 (or 2005). Especially for security reasons. I would appreciate if you could point to some docs pros/cons of upgrading such old databases.

g a b r i e l. metz at club-internet.fr

I think the pros are clear, using new features and getting support from microsoft for the product.

The cons will be that you will have to test you application on the new platform. Old commands might be no longer supported on the new version (running out of the compatibilty horizon)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de