"Illegal characters in path" while getting varaible values from stored proc

hi!

I am getting some junk characters while executing sql task(which is a stored proceedure) when i execute the same sql environment it is fine.

User::ArchiveDir {? )ArchiveVoucherLog_6-26-2006

What is problem in here?

Any help

Thanks,

Jasmine

} String

was an error message generated? if so, please post it.

<= vs between, Table scan and indexes

Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The CaptianOriginally posted by CaptainEstock
Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The Captian

Try clustered index on this field.|||Yes! Clustered index worked!

Runtime dropped from 18 minutes to less than 2
WOOHOO!!

Regards
The Captain|||...as long as you don't expect any duplicate datetime values.

blindman|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

What are you afraid of?|||Okay so I created clustered indexes on the createdate field

Then I ran SQL query analyzer and now I see clustered index scan...

Is that better or worse?

REgards
The Captain|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

You can have duplicate values in a clustered index. SQL Server adds a hidden key to each duplicate value.

#temp table vs. @temp table variable in stored proc?

I have a complex stored procedure (it's the strip-packing one, Dan) that
uses several #temp tables along the way. There are typically 1000 to 50000
rows inserted into them during use. At the end of the procedure the final
results end up inserted into permanent tables. Concurrency is not a factor
here because the procedure is typically run only once a month.
My question is, could any performance improvement be gained by changing
them from #temp tables to @.temp table variables?http://www.aspfaq.com/2475
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jy8ydt3440yb$.dlg@.rpresser.invalid...
> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to
50000
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a
factor
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?|||Ross,
It is hard to tell you without knowing what kind of operations are you doing
with the temporary tables. SQL Server does not create statistics for table
variables, so for small number of rows this is a good option. The best way o
f
knowing this, is giving it a try and comparing performance results.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"Ross Presser" wrote:

> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to 5000
0
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a facto
r
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?
>|||Thank you both (Aaron, Alejandro) for the links. Given the large amount of
data, I think I'll stick with my #temp tables in this instance.|||yes, My experience is that with Small rowsets, the Table Variables are the
way to go.
TempTables, you can add indexes to, etc.
For large Rowsets, Temp Tables performed better in our situations.
Greg Jackson
Portland, OR

##Temp tables

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.

CREATE PROCEDURE usp_CreateTakeoff
@.iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 .....
Field2 .....
)

-- Add Structural data
usp_AddStructural @.iEstimateID, 1, 'Structural'
usp_AddForming @.iEstimateID, 2, 'Forming'
...
...
...
GO

Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc..... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike BGlobal and local temp tables are created in tempdb.|||Global and local temp tables are created in tempdb.
Now, if I remember correctly, 1 (#) indicates global and 2 (#) indicates local.

So if multiple users executed the stored proc with ##Temp, then each connection would create a table unique to the connection? So multiple users could execute this proc without interfering with each other?

Mike B|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.
Not the first time, won't be the last I am afraid! :) Thanks for the correction.

Mike