Database in recovery

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

I don't know it can be checked programatically but you can sql error log where you recovery progress will be recorded.

Looks like you guys shutdown the servers in the middle of big transaction....

There will no other option than waiting to recover...

|||

There is a way, via T-SQL to view process progress but apparently not via GUI. The database did eventually recover and we have submitted for a new server as it ran out of disk space during a stored procedure run.

Thanks!

Database Import/Export Question

I tried to export a database which includes tables and stored procedures from development environment to a production environment. For unknown reasons only the tables were copied whereas stored procedures were not copied and no error message shown what happened. Does anybody have a clue? I did the same export to an XP machine and it went through properly.How did you export the data? Did you use DTS?
Maybe you will find my previous post listing the steps I use to export objects to be helpful:http://forums.asp.net/1037418/showpost.aspx
|||Thanks. I got it. Don't know why the import/export said 100% complete but nothing showed up even I refreshed the stroed procedures in Enterprise Manager. And after a while (actually I went to pull someone to see the situation) the SP shown. Weird?|||

vljw8202 wrote:

Thanks. I got it. Don't know why theimport/export said 100% complete but nothing showed up even I refreshedthe stroed procedures in Enterprise Manager. And after a while(actually I went to pull someone to see the situation) the SPshown. Weird?

Enterprise Manager is often annoyingly unaware of schema changes. What I typically do is right-click on the server name and chooseRefresh, do the same for the Database node, the specific database, andthen the Tables node and any other node which should berefreshed. The new objects will then show up.
|||I see. Thanks for your advice.

Database image retrieval causes datasource credential prompt

I have an image stored in a database that I use on a report with other non image data. When I run the report, the non image data is displayed, followed by a prompt for me to enter datasource credentials. None of the images are downloaded until I enter the credentials. If I don't enter the creds, the images aren't downloaded and appear only as the red x, file not found icon. I don't understand why I'm getting the prompt because I've already passed the exact set of credentials that the dialog expects to the web service with my call to the render method. THIS IS SO FRUSTRATING! I am experiencing this on both staging and production servers, but not on my development machine. I'm using reporting services service pack 1 on staging and production.

Thanks in advance for the help,
Kim

I guess the answer depends on what export format you're obtaining from the report server. If you're asking for HTML output, then the Images are not included in the HTML output. When the browser displays the HTML output, it will follow the links to each image and after a successful authentication, it will retrieve the images.

If you use the report viewer controls in your application rather than the SOAP api directly, it will probably alleviate this problem.

-Lukasz

sql

Database image retrieval causes datasource credential prompt

I have an image stored in a database that I use on a report with other non image data. When I run the report, the non image data is displayed, followed by a prompt for me to enter datasource credentials. None of the images are downloaded until I enter the credentials. If I don't enter the creds, the images aren't downloaded and appear only as the red x, file not found icon. I don't understand why I'm getting the prompt because I've already passed the exact set of credentials that the dialog expects to the web service with my call to the render method. THIS IS SO FRUSTRATING! I am experiencing this on both staging and production servers, but not on my development machine. I'm using reporting services service pack 1 on staging and production.

Thanks in advance for the help,
Kim

I guess the answer depends on what export format you're obtaining from the report server. If you're asking for HTML output, then the Images are not included in the HTML output. When the browser displays the HTML output, it will follow the links to each image and after a successful authentication, it will retrieve the images.

If you use the report viewer controls in your application rather than the SOAP api directly, it will probably alleviate this problem.

-Lukasz

Database image on report

Hi,

How to display image stored in the database into sql server report?

Please guide.

Thank you,

Regards,

Prashant

Please read this RS BOL topic: http://msdn2.microsoft.com/en-us/library/ms156342(SQL.90).aspx

Particularly read the comment near the top if the images came originally from an Access database, because in that case the actual image data is an OLE image and you have to write an expression to remove the OLE image header from the data.

-- Robert

Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped
to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/pr...oads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodin...ions/books.mspx
========================================
==========
"Prasad" <ekke_nikhil@.yahoo.co.uk> bl
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by
connecting through the
dedicated administrator connection (DAC), looking at the source code from th
e view sys.databases and
then do a select directly against the physical system table that the view sy
s.databases uses.
I don't know what you want to achieve in the end, but best bet is probably t
o disregard objects that
lives in the database with id 32767.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005
, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of
the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea
to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databas
es ?
> I also wanted information as to how do I query the system tables, it giv
es me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Some tables are flagged as user tables even though they are shipped with the
product. The backup
history tables are such examples.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.g
bl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing a
s told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a
System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>sql

Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>

Database Help

Hi,

I have a question about setting up a couple of database tables.

I'll try to explain using an example:

We have a customer (stored in a customer table)

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Who was sent an invoice for an order he placed. That invoice was sent to his current address at the time (123 Cherry Lane).

Let’s say he moved to a new address. We would update his customer record accordingly.

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 987 Apple Road

City: Dubbo

State: NSW

PostalCode: 2830

Now, let’s say that we want to be able to look back at JDOE’s invoices and see where they were shipped too. This means we have to store that address somewhere.

Now if we said just store it on the invoice table like:

InvoiceNo: 0001 (Primary Key)

CustomerCode: JDOE (Foreign Key)

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Amount: $300.00

That would break 3^rd normal form as the address doesn’t depend on the primary key (InvoiceNo [0001]).

We want to be able to see the address from the invoice table via a relationship/lookup/computational column/whatever.

How would we go about doing this in SQL Server version 8?

Cheers,

Pete

I think it is pretty common to have a set up like that. So you might have have a table(s) that lists default values for customers and an actual invoice table that includes actual ship address. You can divide it up for storage and performance reasons as necessary but most companies need to have that historical info available.

My two cents :-)

|||

Hi,

You can have an additional column say address2 for the current address. I know this sounds naive, but sometimes this is the best way to get things done. Now you may ask, what if I have more than 2 addresses, then boy ur going to have a tough time coming up with a schema, which stores historical information.

Good Luck and let me know, how u went about it.

PP

|||

G'day,

Not be blunt, but the above suggested example is very poor design. One particular (and there are several) solutions to this is to first separate the address details from the customer details:

Customer Table

CustomerCode, CustomerName,

Address Table

CustomerCode, AddressID, Addresstype, line1, Line2, PostCode,State

This will then allow a customer to first of all have more then 1 address recorded (physical, postal, work, home etc) as well as almost conforming to 3NF (the State column would in fact need to become a "StateID" column lookup into a "State' table...).

Or, if you really didn't want to store multiple addresses, just keep your same design and create a trigger on the customer table to record any address details that change into a seperate table (example only):

CREATE TRIGGER CustomerAddress_upd

on CustomerTable AFTER UPDATE

AS

BEGIN

IF ( UPDATE(Line1) OR UPDATE(PostCode) ) --list all required cols here

BEGIN

INSERT INTO AddressAudit (CustomerCode, Line1, PostCode, DateChanged)

SELECT CustomerCode, Line1, PostCode, getDate()

FROM CustomerTable c

JOIN Inserted i ON c.CustomerCode = i.CustomerCode

END

END

Then, if required, just query the audit table.

Cheers,

Rob

sql

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
Simon
It would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon
sql

Database Engine Tuning Advisor and Small Workload Files

Hi all,
If I'm interested in tuning a single stored procedure, does the number
of executions of the Sproc in the workload make any difference to the
quality of the DETA?
For example, if I setup a trace in SQL Profiler and executed the sproc
10 times, would it make a difference as compared to a trace where the
procedure were executed only once?
Many thanks
SimonIt would if the stored procedure has parameters and you ran it with a
representative mix of parameter values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:OIHTCWOJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> If I'm interested in tuning a single stored procedure, does the number of
> executions of the Sproc in the workload make any difference to the quality
> of the DETA?
> For example, if I setup a trace in SQL Profiler and executed the sproc 10
> times, would it make a difference as compared to a trace where the
> procedure were executed only once?
> Many thanks
> Simon

Database Diagram + Reporting Services/Report Builder

If I make a new database, why is it that VS 2005 server explorer can see it, access the tables and stored procs, etc ... but when using Reporting Services/Report Builder after I make the data source and generate the model NOTHING APPEARS in Report Builder (message about "there is no content in this data source")..

now this has been driving me nuts for a few days and the solution was to click on "DataBase Diagram" in Sql Managment studio for that DB and say "Yes" to the dialog that appears?

My question is why is that the case?

Carl

Ping Microsoft.

Is this a bug?

Carl

database design:dnamically build where clause

Hi
I am creating a table to store metadata information for certain technical
parameters which are stored in various tables.
These parameters have various criteria to determine if they are compliant to
certain predefined conditions or not.I will explain with an example:
For ex,
paramA is compliant if its value is 50
paramB is compliant if its value is between 0 and 2
paramC is compliant if its value is 35% of another column
paramD is complaint depending on colA,ColB,ColC
If colA = 5 then paramD is compliant if paramD=2.
If colB = 10 then paramD is compliant if paramD=3
If colC =50 then paramD is complaint if paramD=70
paramE is compliant if is 1 or 0
So my table has fields such as
DDId,
ParamName
ParamDBname
ParamTableName
Compliancecondition(varchar 200)
etc.
I need to store the information regarding these compliant conditions so that
I can build my queries like
select <paramName>,case when<compliance condition is true> then 1 else 0,
from <paramDB>.<paramTable>
where<compliancecondition>
but this will result in dynamic SQL.is there a better way to store the
information for these varying compliance conditions?
thanks for your help.
etcLooks like you need something like a rules engine to satisfy your dynamic
needs. Check this nice article on this subject to see if its useful for your
scenario:
http://msdn.microsoft.com/sql/defau.../>
eengine.asp
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"tech77" <tech77@.discussions.microsoft.com> wrote in message
news:7E249C36-147E-4A0A-9B77-42F9F4CA6E6E@.microsoft.com...
> Hi
> I am creating a table to store metadata information for certain technical
> parameters which are stored in various tables.
> These parameters have various criteria to determine if they are compliant
> to
> certain predefined conditions or not.I will explain with an example:
> For ex,
> paramA is compliant if its value is 50
> paramB is compliant if its value is between 0 and 2
> paramC is compliant if its value is 35% of another column
> paramD is complaint depending on colA,ColB,ColC
> If colA = 5 then paramD is compliant if
> paramD=2.
> If colB = 10 then paramD is compliant if
> paramD=3
> If colC =50 then paramD is complaint if
> paramD=70
> paramE is compliant if is 1 or 0
> So my table has fields such as
> DDId,
> ParamName
> ParamDBname
> ParamTableName
> Compliancecondition(varchar 200)
> etc.
> I need to store the information regarding these compliant conditions so
> that
> I can build my queries like
> select <paramName>,case when<compliance condition is true> then 1 else 0,
> from <paramDB>.<paramTable>
> where<compliancecondition>
> but this will result in dynamic SQL.is there a better way to store the
> information for these varying compliance conditions?
> thanks for your help.
>
>
>
> etc

Database Design setup

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
--
David Portas
SQL Server MVP
--|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
--
David Portas
SQL Server MVP
--|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?

Database design question

I working on a project that will let users essentially create design and requirement documents online and keep the information stored in a database.
I was thinking that the model would be similar to that of a book online.
I was thinking the basic schema would be something like
Table - Project
Table - Author.
Table - Topic
Table - Sub Topic
A project can have many topics, A project can have many authors etc.
A topic can have many sub topics etc.
So I was wondering if anyone knew of some sample schemas that may support that functionality.

Try this link and download the PPT slide to get started. You may not need four tables because it is files and association. Hope this helps.
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

|||Thanks. I was actually looking for a sample schema to see what other tables etc were included as opposed to design concepts.
The examples in the link you provided are good but I was looking for something more specific. Such as if I was going to build an online
store, I would want to see a sample database from another online store to see how/what data is stored.
Thanks,
|||

SQL Server 2000 beginner's Guide by Dusan Petkovic has a project database and it is one of the better SQL Server books for Developers. But I would look at Pubs database to get started it is only nine tables so it will be easy to modify the create table statement. Hope this helps.