how to make sure a upgrade from sql 2005 standard to enterprise edition?

Randy,

I did run upgrade advisior to check the existed sql 2005 standard edition to upgrade to enterprise editon. I got the following error message:

SQL Server version: 09.00.1399 is not supported by this release of Upgrade Advisor

Is it means the upgrade advisor can only work on from 7.0, 2000 to 2005? If I need check from standard to enterjprise in 2005, what kind of tool I can use?

One of the SQL Server forums would be a better place to ask this question:

http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

You'll have better luck finding an answer there.

-Tom

how to make report parameter value change dynamic when run time?

Dear all,

i have 2 parameter [startDate] [endDate].
startDate will basic on the day run report to get the start date from system(follow the store procedure) as below.

CREATE PROCEDURE [dbo].[CFSRep_spGetLastWeekMonDate]
AS
BEGIN
DECLARE @.dtStart DATETIME
-- SET @.dtStart = GetDate() --Set Date to Todays Date
--Set Start Date to 6 days before. If its a Sunday it will get this weeks Monday Date, else any day before will get the past weeks Monday Date
SELECT @.dtStart = dateadd(dd, -6, Getdate())
WHILE (datepart(dw, @.dtStart) != 2) --While its not Day 2 (Monday)
BEGIN
SELECT @.dtStart = dateadd(dd, -1, @.dtStart) --Set the Start Date to a day before
END

SET @.dtStart = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.dtStart))) --Set the time to 00:00:00 AM

SELECT @.dtStart as LastWeekFirstDay
--

END
GO


endDate will get the date after the startDate is process. let say the answer as below.
SELECT DATEADD(dd, 4, @.startDate) as endDate

when i get the startDate = 30th Jan 2006, then the endDate = 3rd Feb 2006

then when i preview report, and i change the parameter of the date on start date to 23rd Jan 2006 but the endDate still the same 3rd Feb 2006.

anyway for me to refesh the endDate to 27th Jan 2006?

Hi Terence,

first of all you can use visual basic functions like Today etc. to get the startdate. In case of that you must not go to the database.

then you can try the following steps:

1. define 2 parameters (startdate + enddate)

2. in the enddate-dataset define a parameter @.startDate and set it to =Parameters!StartDate.Value.

3. make your 'SELECT DATEADD(dd, 4, @.startDate) as endDate' statement.

Now the reportservice should set in the endate-parameter the startdate-param +4 days.

hope this will work for you

sql

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

How to make Application.LoadPackage() and Package.Execute() to run asynchroniously?

Hi,

I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.

The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.

Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):

"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."

So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....

Thanks for any help,

DrewThe calls are synchronous, but each package object is independent - so if you create a thread per incoming file, create a new package object and do LoadPackage/Execute you should get the behavior you need.

The random errors you see are probably caused by your code trying to load the DTSX file before it was fully copied. So SSIS tries to read partial file and throws the error reporting it is not a valid XML file. You need some way to ensure you only load a file when it is ready, e.g. try opening it exclusively until you succeed (it will fail if someone is still writing to the file).

How to make a SQL run longer?

Hell All,
To reproduce one of our cusotmer's probem, I need to make the SQL to
run for more than a minutes before it returns the result set. I do not
have large amount of data in the database to simulate the dealy.

Is there a way in SQL to cause the delay while returning the result
set

Thanks for the help.

Regards
RajWAITFOR will do that.

select getdate()
waitfor delay '00:01:00'
select getdate()

Roy Harvey
Beacon Falls, CT

On Wed, 27 Jun 2007 11:16:26 -0700, Raj <jkamaraj@.gmail.comwrote:

Quote:

Originally Posted by

>Hell All,
>To reproduce one of our cusotmer's probem, I need to make the SQL to
>run for more than a minutes before it returns the result set. I do not
>have large amount of data in the database to simulate the dealy.
>
>Is there a way in SQL to cause the delay while returning the result
>set
>
>Thanks for the help.
>
>Regards
>Raj

|||On Jun 27, 11:56 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

WAITFOR will do that.
>
select getdate()
waitfor delay '00:01:00'
select getdate()
>
Roy Harvey
Beacon Falls, CT
>
>
>
On Wed, 27 Jun 2007 11:16:26 -0700, Raj <jkama...@.gmail.comwrote:

Quote:

Originally Posted by

Hell All,
To reproduce one of our cusotmer's probem, I need to make the SQL to
run for more than a minutes before it returns the result set. I do not
have large amount of data in the database to simulate the dealy.

>

Quote:

Originally Posted by

Is there a way in SQL to cause the delay while returning the result
set

>

Quote:

Originally Posted by

Thanks for the help.

>

Quote:

Originally Posted by

Regards
Raj- Hide quoted text -

>
- Show quoted text -

Thanks for replying. My challange is that I can pass only one SQL
statement at at time. Is there a function that I use like this:

select a, b, c
from table_a a, table_b where a.cid = b.cid and delay(0:0:1)

Thanks.
Raj|||On Wed, 27 Jun 2007 12:47:14 -0700, Raj <jkamaraj@.gmail.comwrote:

Quote:

Originally Posted by

>Thanks for replying. My challange is that I can pass only one SQL
>statement at at time. Is there a function that I use like this:
>
>select a, b, c
>from table_a a, table_b where a.cid = b.cid and delay(0:0:1)

No, there is no such thing that I know of.

Roy Harvey
Beacon Falls, CT|||"Raj" <jkamaraj@.gmail.comschreef in bericht
news:1182968186.181731.177720@.k29g2000hsd.googlegr oups.com...

Quote:

Originally Posted by

Hell All,
To reproduce one of our cusotmer's probem, I need to make the SQL to
run for more than a minutes before it returns the result set. I do not
have large amount of data in the database to simulate the dealy.
>
Is there a way in SQL to cause the delay while returning the result
set
>
Thanks for the help.
>
Regards
Raj
>

maybe you should tell us WHY you need a delay, because most in the eyes of
most people a SQL-server must be fast, so no DELAY's...

I hink when you do give that information, there might be another way to
solve your problem.

regards,
Luuk|||Raj (jkamaraj@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for replying. My challange is that I can pass only one SQL
statement at at time.

Huh? What environment is this?

Quote:

Originally Posted by

Is there a function that I use like this:
>
select a, b, c
from table_a a, table_b where a.cid = b.cid and delay(0:0:1)

You could write one that calls xp_cmdshell and the uses a wait command
in the shell.

But hopefullly you can also access the database from a regular query
window. In such case you can start a transaction that locks one of the
tables in your query. After a minute you commit/rollback that transaction,
so that the other process is let go.

--
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|||On Jun 27, 2:33 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Raj (jkama...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for replying. My challange is that I can pass only one SQL
statement at at time.

>
Huh? What environment is this?
>

Quote:

Originally Posted by

Is there a function that I use like this:

>

Quote:

Originally Posted by

select a, b, c
from table_a a, table_b where a.cid = b.cid and delay(0:0:1)

>
You could write one that calls xp_cmdshell and the uses a wait command
in the shell.
>
But hopefullly you can also access the database from a regular query
window. In such case you can start a transaction that locks one of the
tables in your query. After a minute you commit/rollback that transaction,
so that the other process is let go.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you. I will try this and then update the thread.|||On Jun 27, 1:05 pm, "Luuk" <l...@.invalid.lanwrote:

Quote:

Originally Posted by

"Raj" <jkama...@.gmail.comschreef in berichtnews:1182968186.181731.177720@.k29g2000hsd.g ooglegroups.com...
>

Quote:

Originally Posted by

Hell All,
To reproduce one of our cusotmer's probem, I need to make the SQL to
run for more than a minutes before it returns the result set. I do not
have large amount of data in the database to simulate the dealy.

>

Quote:

Originally Posted by

Is there a way in SQL to cause the delay while returning the result
set

>

Quote:

Originally Posted by

Thanks for the help.

>

Quote:

Originally Posted by

Regards
Raj

>
maybe you should tell us WHY you need a delay, because most in the eyes of
most people a SQL-server must be fast, so no DELAY's...
>
I hink when you do give that information, there might be another way to
solve your problem.
>
regards,
Luuk

Our is an reporting application that can connect to either SQL server
or Oracle, retrieve the data and prsent the data over the web for the
end users. This application has the option to preview the data while
developing the report. Duing the preview, if the query takes more
than
one minute than our application hangs.

I need to reproduce this behaviour but unfortunately does not have
enough
data in my database to create the one minute.

Thanks.
Raj

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
Sr. System Engineer
Symantec
Hi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member property
of the server object.
John
"Kennet Johansen" wrote:

> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used t
o
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
Sr. System Engineer
SymantecHi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member propert
y
of the server object.
John
"Kennet Johansen" wrote:

> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used
to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to make a enquiry for all database instances?

In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
enquiry all instance names on a SQL server for backup reasons, after
upgrading to SQL2005 this enquiry doesn't work.
Is there a new way to enquiry for all instance names in SQL2005?
--
Sr. System Engineer
SymantecHi Kennet
How are you running this enquiry? With T-SQL I would expect that you would
look at the sys.databases view. In SMO you have the databases member property
of the server object.
John
"Kennet Johansen" wrote:
> In SQl2000 i were abe to run an enquiry called "DATABASE $ALL" it was used to
> enquiry all instance names on a SQL server for backup reasons, after
> upgrading to SQL2005 this enquiry doesn't work.
> Is there a new way to enquiry for all instance names in SQL2005?
> --
> Sr. System Engineer
> Symantec

How to login to SQL Server Web Data Administrator?

I have installed Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) and the SQL Server Web Data Administrator. After I run the SQL Server Web Data Administrator it
is asking me for login and password. How can I determine my login and password? Thank you.Try using "sa" for login (without the quotes) and a blank password. After you login, make sure you change the password to something non-blank!

How to Log Locks within a Database

I was wondering if there was a simple script that could be run or a way to h
ave an entry created in a log file whenever a SQL Lock occurs. Specifically
if it could also give the username of the user running the query that creat
ed the lock. Thank you.You could have a profiler trace running. But be aware that the locking
activity in SQL Server can be *very* high! Do a test first so you don't
overload the system with the logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.|||Tibor, Thanks for the reply. I tried profiling but noticed the performance
decrease. All I really would like is basically something that alerts me via
either a log file I can check daily or a netsend message that tells me when
the database gets blocked
by a SPID and who it is that is causing the block. I don't actually need to
monitor every lock as I found out with the profiler. Thank you.|||INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/defaul...kb;EN-US;271509
Also have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/defaul...kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/defaul...b;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.

how to lock the store procedure and allow one process to acces it at a time

Hello:

I run one process that calls the following the store procedure and
works fine.

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

END
GO

If I run two processes that call the above store procedure, I might
occasionally get the dirty data of int_parameter_dbt. I guess that is
caused by two processes accessing to the same resource simultaneously.
Is there any way to lock the store procedure call from MSSQL Server
and allow only one process to access it at a time?

Thanks for help.

Best Jin"Jin" <texlqj@.hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@.posting.google.c om...
> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

Here is one possible approach, using an UPDATE syntax specific to MSSQL:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

declare @.val int

UPDATE system_parameter_dbt
SET @.val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

select @.val

END
GO

Alternatively, you can use a locking hint:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

begin tran

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

commit

END
GO

Simon|||Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

How to load database name at runtime?

I m creating a report using data view.
its working fine. BUt i want to load db name at run time, I tried a sample and which does it very fine.

But when i use the same code in my project, its not loading the database name at runtime, old name is being used.

What i m doing is like this in visual basic.net

rptCustomersOrders.Load("..\CustomerOrders.rpt")

' Set the connection information for all the tables used in the report
' Leave UserID and Password blank for trusted connection
For Each tbCurrent In rptCustomersOrders.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

ReportViewer.ReportSource = rptCustomersOrders

Plz. tell me ; what property i have to set, to load the database name at runtime.

Thanks in adavancehttp://www.dev-archive.com/forum/archive/index.php/t-293276.html|||try adding

.location = .name

after

.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"

so that it will erase previous location details ....

if u have any other methords ... plzz let me know too dude .........|||Here's my code which you can use to change Database Name, User Name, Password and SQL Server Name at run time. This code is written in VB6 and works with Crystal Reports 10.

Copy this code in a Module in VB and I used frm Report where I have placed crystal report viewer control.

Public Sub DisplayReport(ReportFileName As String)
Dim app2 As CRAXDRT.Application
Dim rap As CRAXDRT.Report

Set app2 = New CRAXDRT.Application
Set rap = New CRAXDRT.Report
Set rap = app2.OpenReport(ReportFileName)

rap.EnableParameterPrompting = False

For Each CRXDatabaseTable In rap.Database.Tables
STORED_DATABASE_NAME = CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") ''JUST TO READ DATABASE NAME STORED IN CRYSTAL REPORT FILE
Exit For
Next

For Each CRXDatabaseTable In rap.Database.Tables
CRXDatabaseTable.ConnectionProperties("Data Source") = SQLServerName
CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") = DatabaseName
CRXDatabaseTable.ConnectionProperties("USER ID") = UserName
CRXDatabaseTable.ConnectionProperties("PASSWORD") = LoginPassword
If Not CRXDatabaseTable.TestConnectivity Then
MsgBox "Error connecting to database table." & vbCrLf & "Please contact Adminstrator to validate Report Database"
Exit Sub
End If
Next

rap.EnableParameterPrompting = True
On Error Resume Next
Ret = rap.SQLQueryString 'THIS WILL CALL USER TO INPUT PARAMETERS FOR REPORT (IF ANY)
On Error GoTo 0

If Err.Number = -2147206395 Then 'CHECK IF USER PRESSED CANCEL
Err.Clear
Exit Sub
ElseIf Err.Number <> 0 Then 'CHECK IF ANY OTHER ERROR OCCURED
MsgBox Err.Number & " : " & Err.Description
Err.Clear
Exit Sub
End If

If UCase(STORED_DATABASE_NAME) <> UCase(DatabaseName) Then
''IF DATABASE WHILE CREATING REPORT WAS DIFFERENT THEN THE CURRENT DATABASE.
''UPDATE DATABASE NAME IN CONNECTION STRING, ELSE CONNECTION STRING READS DATA FROM DATABASE USED WHILE CREATING REPORT
DoEvents
rap.EnableParameterPrompting = False
Ret = rap.SQLQueryString
Ret = Replace(Ret, STORED_DATABASE_NAME, DatabaseName)
rap.SQLQueryString = Ret
rap.SQLQueryString = Ret ''IT DOESNOT WORK IF I SET THIS ONCE (PARAMETERS ARE NOT SET). :-( CRYSTAL BUG
DoEvents
End If

frmReport.Show
frmReport.CrystalActiveXReportViewer1.ReportSource = rap
frmReport.CrystalActiveXReportViewer1.ViewReport
frmReport.CrystalActiveXReportViewer1.Refresh
End Sub|||Sorry forget to add this declaration in my above function:

Dim CRXDatabaseTable As CRAXDRT.DatabaseTable

How to list all history/snapshots of a given report

We are running SSRS2005 and produce multiple reports.

Our users need history report data so we set up snapshots executions to run weekly.

How do we display the list of these historical snapshots to the users for selection?

Currently we code:

http://myserver/Reports/Pages/Report.aspx?ItemPath=/myreports/Available+Floor+Space&rs:Command=Render&SelectedTabId=SnapshotsTab

That is not preferred as the user is presented the Report Manager interface and can go use other functionality such as subscriptions etc... that we do not support.

Is there (a) a way to suppress the Report Manager controls? or (b)another way to list the snapshots executed given a report name( maybe by querying the report server database?) or (c) code http://myserver/Reportserver/?/myreports/Available+Floor+Space&rs:Command=Render&? to get the history listing?

Thanks

Hi,

The best way is to use Reporting Services Web Services.

You need to add a Web Reference to your Reporting Server and name it <ReferenceName>. Once it is setup you can call the web services using the <ReferenceName>.

In your class first you need to add the "using" statement....

using <NameSpace>.<ReferenceName>.

then in the code you can use...

ReportingService rs = new ReportingService();

rs.Credentials = <your credentials>;

ReportHistorySnapshot[] historySnapshotList = rs.ListReportHistory(<report name>);

Hope this will help you.

Thanks.

Hammad

How to leverage custom security with ReportViewer and SSRS Web Service

My company is building a WindowsForms application that will use SSRS 2005 for it's reporting needs. The application will use ClickOnce and will run in an extranet type of environment against a centralized database designed in an "ASP" fashion with key, customer-specific tables marked with an "OrgId."

The authentication/authorization within the app will be with custom classes implementing IPrincipal and IIdentity and leveraging the built-in .NET security framework. Because of this, I have come to the conclusion that we will have to author a custom security extension for SSRS.

Now, none of this is rocket science, but we would like to use the Windows Forms ReportViewer control within our app, in conjunction with the Reporting Services Web Service and I have yet to find a decent example that illustrates how to utilize custom security extensions with the web service and the reportviewer.

Any suggestions, tips, tricks, pitfalls?

Thanks,
Matthew Belk

This stuff is in BOL, but it's not very discoverable.

Here's an example of using the SSRS Forms Auth security extension in conjunction with the ReportViewer:

http://blogs.msdn.com/bimusings/archive/2005/11/04/489100.aspx

Here's an example of using the Forms Auth Security extension with the SSRS webservice (basically, just calling LoginUser(): implemented in the security extension)

http://blogs.msdn.com/bimusings/archive/2005/08/04/447939.aspx

Hope this helps

|||Thanks for the blog pointers.

Now, the next logical question is how to leverage the custom security extensions to restrict access to various items within the SSRS space so that the custom "CheckAccess" routines from the sample code will work properly.

I'd love to let SSRS handle this, but if the answer is "You have to do that from your app," then that's OK, too.

Thanks,
Matthew Belk
|||

Have you explored the Forms Auth security extension sample yet? If not, I would -- You could pretty much use 60-70% of it for your purposes (authorization included)...The only changes you'd have to make is how LogonUser gets handled, etc.

How to launch them?

Hi everyone,

Primary platform is Framework 2.0

Our vb application throws .dtsx by means of the usual methods.

When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when

I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.

Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from

Database Engine->Management->Legacy->Data Transformation Services.

How do I launch this kind of SSIS packages?

I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?

Thanks in advance for your input,

If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.

Hope this helps,
Ovidiu Burlacu

|||

so you're talking about that they really are dts no ssis. ok

How to launch them?

Hi everyone,

Primary platform is Framework 2.0

Our vb application throws .dtsx by means of the usual methods.

When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when

I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.

Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from

Database Engine->Management->Legacy->Data Transformation Services.

How do I launch this kind of SSIS packages?

I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?

Thanks in advance for your input,

If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.

Hope this helps,
Ovidiu Burlacu

|||

so you're talking about that they really are dts no ssis. ok

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.You can get the information for the job (including the result of the last
run) with sp_help_job.
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.If you check the sp_help_job command you can configure an
NT event to see whether the job has succeded or failed.
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan
>--Original Message--
>How to know the sp_start_job job has failed?
>No matter the job run successfully or not, we can only
know that it started
>successfully.
>Job 'JobName' started successfully.
>Is there any way we can know the job run result?
>
>Thanks.
>
>.
>|||You can get the information for the job (including the result of the last
run) with sp_help_job.
--
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>

How to know the sp_start_job job has failed?

How to know the sp_start_job job has failed?
No matter the job run successfully or not, we can only know that it started
successfully.
Job 'JobName' started successfully.
Is there any way we can know the job run result?
Thanks.
You can get the information for the job (including the result of the last
run) with sp_help_job.
Jacco Schalkwijk
SQL Server MVP
"Tee" <thy@.streamyx.com> wrote in message
news:eUm5Jrr1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> How to know the sp_start_job job has failed?
> No matter the job run successfully or not, we can only know that it
> started
> successfully.
> Job 'JobName' started successfully.
> Is there any way we can know the job run result?
>
> Thanks.
>