Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

Question about Running Totals in a Matrix

I am having problems specifying a running total within a matrix. I have
monthly values in the columns of the matrix so that I am displaying Month To
Date (MTD) sales numbers in the data. I would like to use a running total
across months to get a YTD report. I am trying to add a row group to the
matrix and define an expression referencing the RunningValue of the
salesamout field.
I get errors about running values cannot be used in group expressions. How
would I solve this problem?You can use the RunningValue function in matrix headers and in matrix cells
(which seems what you want to achieve), but not in the matrix grouping
expression. Therefore, just use a grouping expression like
=Fields!Month.Value for the column grouping and use a function in the matrix
cell similar to: =RunningValue(Fields!Sales.Value, Sum,
"CategoryYearGrouping") - where CategoryYearGrouping would represent the
parent group of the month grouping. See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_5tt1.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:6B23ECD0-8C7F-4AF1-A414-1A712E686303@.microsoft.com...
> I am having problems specifying a running total within a matrix. I have
> monthly values in the columns of the matrix so that I am displaying Month
To
> Date (MTD) sales numbers in the data. I would like to use a running total
> across months to get a YTD report. I am trying to add a row group to the
> matrix and define an expression referencing the RunningValue of the
> salesamout field.
> I get errors about running values cannot be used in group expressions. How
> would I solve this problem?

Question about querying across servers

Hi,
We have 8 sites running each one a SQL Server.
Clients can connect to any of these sites to get information.
Now we want to have an 'extra site'. Clients that connect to this site
will be able to get information from the previous 8 sites. I mean, this
extra site, will contain all the data of the other 8 sites.
Ex.
Site 1 has a table called 'Persons' with a record called 'John'
Site 3 has a table called 'Persons' with a record 'Peter'
If I query for all persons on site 1, I get 'John'
However, If I query for all persons in that extra site, I get 'John' and
'Peter'
1. What is the best way to do this? I have been looking at linked
servers but it's not clear to me... I'm not sure if this is a solution.
2. Should I transfer all the data phisically from Site1->Extra Site,
Site2->Extra site... ?
3. On the other hand, how do I distinguish primary keys? Let's suppose that
primary key for 'John' on site 1 is 1000, and 'Peter' on site 3 is also
1000. Could this cause some problems?
Thanks a lot.Will the New sites will be added on a periodic basis , or the developer is
adding settings for new sites ?
U can use linked servers for this process than replication
first register the link server , then in queries use the
linkserver..dbname.table for querying
thanks
"Star" wrote:

> Hi,
> We have 8 sites running each one a SQL Server.
> Clients can connect to any of these sites to get information.
> Now we want to have an 'extra site'. Clients that connect to this site
> will be able to get information from the previous 8 sites. I mean, this
> extra site, will contain all the data of the other 8 sites.
> Ex.
> Site 1 has a table called 'Persons' with a record called 'John'
> Site 3 has a table called 'Persons' with a record 'Peter'
> If I query for all persons on site 1, I get 'John'
> However, If I query for all persons in that extra site, I get 'John' and
> 'Peter'
> 1. What is the best way to do this? I have been looking at linked
> servers but it's not clear to me... I'm not sure if this is a solution.
> 2. Should I transfer all the data phisically from Site1->Extra Site,
> Site2->Extra site... ?
> 3. On the other hand, how do I distinguish primary keys? Let's suppose tha
t
> primary key for 'John' on site 1 is 1000, and 'Peter' on site 3 is also
> 1000. Could this cause some problems?
> Thanks a lot.
>

Question about paging?

- where running a server 4GB of RAM

- set to use a min 2.5GB and max of 3.9GB

- AWE is not enable

-- the problem is that microsoft sql server 2000 is only using 1.6GB and is paging a lot

Also the server is dynamically managing memory the RAM at 2.5 GB to 3.9 GB|||

ensure your /PAE and /3gb switches are correct. in SQL 2000 you could use DBCC PinTable to "pin" an entire table's worth of records into memory, yet this has been deprecated in 2005 for good reason the database engine is efficient enough to decide when pages should enter/remain/and exit buffer pool.

Derek

|||

With 4GB of RAM, and without /3GB the largest buffer pool will grow to is ~1.6GB.

Paging is caused by external memory pressure - meaning SQL Server shares the box and other things need memory too. You should use perfmon to look at the Memory\available MB counter and ensure it's something reasonable, say somewhere larger than 20MB. Next thing is to look at Process\Private bytes counter for all processes on the machine. If SQL Server is the app you care most about then shutdown as many large memory consumers as you can.

Please don't use dbcc pintable. That will just guarantee that the same data get's paged out over and over again, and shrink the amount of buffer pool available for hotter pages. SQL 2000 did a fine job of determining what data pages belonged in memory, the decision to remove dbcc pintable rested more on the fact that it does more harm than good.

|||

The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

Use AWE or the /3gb switch to get the memory support you desire via your min/max buffer pool settings.

Good articles I always references for sql memory config issues:

http://www.sql-server-performance.com/awe_memory.asp

Wait a second here.. I just promoted a competitor's site lol. these guys know their stuff regardless

Derek

|||If this is SQL Server 2000 Standard Edition, it won't matter. You can only address 2Gb of RAM with that edition.|||Michael is correct regarding sql 200 std. I forgot that the old std does not support AWE...GLAD 2005 DOES!

Question about paging?

- where running a server 4GB of RAM

- set to use a min 2.5GB and max of 3.9GB

- AWE is not enable

-- the problem is that microsoft sql server 2000 is only using 1.6GB and is paging a lot

Also the server is dynamically managing memory the RAM at 2.5 GB to 3.9 GB|||

ensure your /PAE and /3gb switches are correct. in SQL 2000 you could use DBCC PinTable to "pin" an entire table's worth of records into memory, yet this has been deprecated in 2005 for good reason the database engine is efficient enough to decide when pages should enter/remain/and exit buffer pool.

Derek

|||

With 4GB of RAM, and without /3GB the largest buffer pool will grow to is ~1.6GB.

Paging is caused by external memory pressure - meaning SQL Server shares the box and other things need memory too. You should use perfmon to look at the Memory\available MB counter and ensure it's something reasonable, say somewhere larger than 20MB. Next thing is to look at Process\Private bytes counter for all processes on the machine. If SQL Server is the app you care most about then shutdown as many large memory consumers as you can.

Please don't use dbcc pintable. That will just guarantee that the same data get's paged out over and over again, and shrink the amount of buffer pool available for hotter pages. SQL 2000 did a fine job of determining what data pages belonged in memory, the decision to remove dbcc pintable rested more on the fact that it does more harm than good.

|||

The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

Use AWE or the /3gb switch to get the memory support you desire via your min/max buffer pool settings.

Good articles I always references for sql memory config issues:

http://www.sql-server-performance.com/awe_memory.asp

Wait a second here.. I just promoted a competitor's site lol. these guys know their stuff regardless

Derek

|||If this is SQL Server 2000 Standard Edition, it won't matter. You can only address 2Gb of RAM with that edition.|||Michael is correct regarding sql 200 std. I forgot that the old std does not support AWE...GLAD 2005 DOES!sql

Question about Paging?

- where running a server 4GB of RAM

- set to use a min 2.5GB and max of 3.9GB

-- the problem is that microsoft sql server 2000 is only using 1.6GB and is paging

a lot

Please post this question to the database engine forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

This thread might also have already answered your question:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=411462&SiteID=1

Question about locks

In short, we are sending several statements to the database from one source
(my application). When this application is running nobody else will be
accessing the database.
If I do a table lock isnâ't it still releasing that lock after each statement?
I ask because each update I send to the database will be effecting only one
row at a time so I donâ't think I would get performance gains because I lock
the table for one record update then lock it again etc etc. The manager
itself could most likely do that faster anyway.What do you mean by 'doing a table lock'? Do you mean using a lock hint?
Which hint? What is the statement that is affecting the data?
Have you started an explicit transaction with BEGIN TRAN?
We need to see exactly what you are doing to lock the table to be able to
answer your questions, but in general, it is better to let SQL Server's lock
manager handle all the necessary locking automatically.
Why are you updating only one row at a time? If you are interested in
performance, you might consider figuring out a way to process all the rows
at once. That's what a relational database is good at.
--
HTH
Kalen Delaney, SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:5C5D6009-F702-4100-BCE1-F194E3695DAC@.microsoft.com...
> In short, we are sending several statements to the database from one
> source
> (my application). When this application is running nobody else will be
> accessing the database.
> If I do a table lock isn't it still releasing that lock after each
> statement?
> I ask because each update I send to the database will be effecting only
> one
> row at a time so I don't think I would get performance gains because I
> lock
> the table for one record update then lock it again etc etc. The manager
> itself could most likely do that faster anyway.
>sql

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. Th
e
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP
4.
> I have to install a new application which will reside on this sql server.
The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:
> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>sql

question about instances (what is an instance)

I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
I have to install a new application which will reside on this sql server. The
installation tells me to "Stop" all SQl instances.
My question is this: Is the instance the "MSSQLServer" service running on
this server?
If I understand "instances" right, then it is the O/S and the SQL Server
itself which makes up the instance. Does this mean I currently have 1
instance of SQL running?
Thanks for any help and info on this matter.
Hi
IF you check out the topic "Working with Named and Multiple Instances of SQL
Server 2000" you will see you can have a default or a named instance of SQL
Server running on the same machine.
This default instance is identified by the network name of the computer on
which it is running. A named instance is identified by the network name of
the computer plus an instance name, in the format
<computername>\<instancename>.
For the service names a default instance will have the name MSSQLServer and
a named instance wll have a service name of MSSQL$InstanceName.
John
"carl" wrote:

> I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000 SP4.
> I have to install a new application which will reside on this sql server. The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>
|||An instance is one installation of the SQL Server program files onto an O/S.
You can have up to 16 instances of SQL 2000 per machine (1 default and 15
named instances, or 16 named instances).
Each has its own section of the registry and file system. There may be some
shared files but I can't swear to it.
Each will use up its own specified amount of memory as well, so multiple
instances per box may or may not be a good idea, deoending on your hardware.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"carl" <carl@.discussions.microsoft.com> wrote in message
news:6F1D3CE3-305B-4430-928E-327D8037C05D@.microsoft.com...
>I have 1 SQL 2000 Standard Edition (SP3) server running on Windows 2000
>SP4.
> I have to install a new application which will reside on this sql server.
> The
> installation tells me to "Stop" all SQl instances.
> My question is this: Is the instance the "MSSQLServer" service running on
> this server?
> If I understand "instances" right, then it is the O/S and the SQL Server
> itself which makes up the instance. Does this mean I currently have 1
> instance of SQL running?
> Thanks for any help and info on this matter.
>

question about failover

Hi,
In MS Clustering the failover happens only at hardware level or it can also
happen at application level? I mean If I am running two nodes, node1 & node
2. both these servers hardware has no issues but SQL server on node1 has some
problem, will it be able to use SQL server on node2?
Thanks
Reddi
Sure. If a resource stops responding either due to hardware or software
failure, it will fail over. You can test this by using the Move Group
command from the Cluster Administrator tool.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:F05B1776-F173-4795-A0ED-6751749AB867@.microsoft.com...
> Hi,
>
> In MS Clustering the failover happens only at hardware level or it can
> also
> happen at application level? I mean If I am running two nodes, node1 &
> node
> 2. both these servers hardware has no issues but SQL server on node1 has
> some
> problem, will it be able to use SQL server on node2?
> Thanks
> Reddi
>
|||Thanks for your time Rod!
Reddi
"Rodney R. Fournier [MVP]" wrote:

> Sure. If a resource stops responding either due to hardware or software
> failure, it will fail over. You can test this by using the Move Group
> command from the Cluster Administrator tool.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:F05B1776-F173-4795-A0ED-6751749AB867@.microsoft.com...
>
>

Question about Dynamic WHERE clause without Dynamic SQL statement

I'm running into an issue while using coalesce to create a dynamic where
clause. To give some background, here is the article that got me started on
this:
http://www.sqlteam.com/item.asp?ItemID=2077
What I am trying to accomplish is a series of user configurable filters that
will ignore a parameter if its not selected. So, let's presume there are 2
possible filters: "Location" and "Assigned To" for equipment that is "signed
out" to individuals. If the user selects a Location but not a person
(assigned to), then it should query only on LocationID. This should work
vice versa. And it should also work if they choose not select either.
Table EquipmentAssignments:
EquipmentID | LocationID | AssignedToID
--
1 1 2
1 1 1
2 2 1
2 NULL 2
2 2 NULL
So, based on the above mentioned article:
CREATE PROC ListEquipmentAssignments(
@.locationID int = NULL,
@.assignedToID int = NULL)
AS
-- note: in actual implementation all ID fields are actually
UNIQUEIDENTIFIERs
-- but it easier to read by using INTs
SELECT EquipmentID
FROM EquipmentAssignments
WHERE LocationID = COALESCE(@.locationID, LocationID) AND
AssignedToID = COALESCE(@.assignedToID, AssignedToID)
GO
This works great, except when dealing with the NULL column values. Because
of how COALESCE works, it returns NULL in the instances of those 2 records,
which won't evaluate to TRUE (and thus those records don't get returned).
I want to stay away from dynamically building SQL in the stored procedure.
Does anyone know of any other technique that could address this issue?Unfortunately, that method is absolutely terrible when it comes to
performance -- it forces a table scan. Why don't you want to use dynamic
SQL for this? You'll get MUCH better results.
There are other, non-dynamic methods with less performance problems, but
dynamic SQL really works best for these situations.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
> on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
> that
> will ignore a parameter if its not selected. So, let's presume there are
> 2
> possible filters: "Location" and "Assigned To" for equipment that is
> "signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
> Because
> of how COALESCE works, it returns NULL in the instances of those 2
> records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||http://www.sommarskog.se/dyn-search.html

> I'm running into an issue while using coalesce to create a dynamic where
> clause.|||IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
(location_id, 99))
Don't do that. You cannot validate or verify such non-relational
proprietary extensions.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
Joe,
Can other SQL engines (other than SQL Server, that is) use an index to
satisfy this kind of query?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||>> Can other SQL engines (other than SQL Server, that is) use an index to sa
tisfy this kind of query? <<
DB2 has some optimizer stuff now that looks at COALESCE() and builds
several execution plans (up to 16 or 32 of them as I recall) per
procedure, then it picks one based on the input parameters. It looks
for search conditions that can be reduced to (a = a) by a NULL and
drops them from consideration.
I am not sure about Ingres.|||see my Zero to N column
http://www.sqlservercentral.com/columnists/sholliday/
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
that
> will ignore a parameter if its not selected. So, let's presume there are
2
> possible filters: "Location" and "Assigned To" for equipment that is
"signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
Because
> of how COALESCE works, it returns NULL in the instances of those 2
records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||Give us a scalable solution that won't do a TABLE SCAN because this is a
MICROSOFT SQL SERVER forum.
Keep the DB2 solution on the DB2 forum, the Ingres solution on the Ingres
forum.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147830039.976788.281080@.j73g2000cwa.googlegroups.com...
> DB2 has some optimizer stuff now that looks at COALESCE() and builds
> several execution plans (up to 16 or 32 of them as I recall) per
> procedure, then it picks one based on the input parameters. It looks
> for search conditions that can be reduced to (a = a) by a NULL and
> drops them from consideration.
> I am not sure about Ingres.
>|||> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
table scan.
Can I have a list of clients you have recommended this too and a contact
name there so I can go and fix your lousy solution, this won't scale, it
will cause all sorts of blocking on a real system that has
update/insert/delete activity.
Tidy code does not mean efficient code.
You've been told several times this won't work well on SQL Server so why
continue to post this horrible, inefficient solution?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
>
> Don't do that. You cannot validate or verify such non-relational
> proprietary extensions.
>

Question about detaching databases.

I need to have someone clarify a couple things for me.
1. If a system running MSDE is rebooted in the middle of a transaction, I
am assuming at some point when the system is restarted, the transaction log
file is rolled back into the main database file. However, if a database is
detached before anyone connects to it, will the uncommitted transaction log
data be rolled into the main database file before the detach is performed?
2. When exactly does the uncommitted transaction log data get rolled back
into the database after a reboot in the middle of a transaction?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
hi Ken,
Ken Varn wrote:
> I need to have someone clarify a couple things for me.
> 1. If a system running MSDE is rebooted in the middle of a
> transaction, I am assuming at some point when the system is
> restarted, the transaction log file is rolled back into the main
> database file. However, if a database is detached before anyone
> connects to it, will the uncommitted transaction log data be rolled
> into the main database file before the detach is performed?
when a system stops (crashes or is unproperly shut down), all the pending
transactions are aborted ..
when the SQL Service restarts, it will stars and recover all databases...
that's to say committed transaction will be rolled forward and uncommitted
rolled back.. the algorithm is quiet complex as SQL Server searches the best
method to perform this task to put the db on line and available as soon as
possible..
so you are not entitled to detach the db before the db has been recovered..
you can perhaps move the physical files before starting SQL Server, but then
the db will be marked as corrupted as SQL Server can no longer find the db
files as registered in it's system tables, so this is not a good idea...
> 2. When exactly does the uncommitted transaction log data get rolled
> back into the database after a reboot in the middle of a transaction?
as soon as the db is recovered... if you open the log file of SQL Server, in
the \Log folder, and inspect one of the ERRORLOG files you'll see something
like
2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
2005-08-11 16:33:24.45 spid3 Recovery complete.
....
if something is pending fo a specific database you'll see rollforwad and
rollback operations...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
So that I can clarify what you are saying here. SQL Server will not allow
you to detach a database until it has completed the recovery process?
I basically want to know if it is safe to get rid of the LDF file after a
database has been detached without any adverse ramifications, like database
corruption.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m3sgtF159l9rU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> when a system stops (crashes or is unproperly shut down), all the pending
> transactions are aborted ..
> when the SQL Service restarts, it will stars and recover all databases...
> that's to say committed transaction will be rolled forward and uncommitted
> rolled back.. the algorithm is quiet complex as SQL Server searches the
best
> method to perform this task to put the db on line and available as soon as
> possible..
> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
> as soon as the db is recovered... if you open the log file of SQL Server,
in
> the \Log folder, and inspect one of the ERRORLOG files you'll see
something
> like
> 2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
> 2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
> 2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
> 2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
> 2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
> 2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
> 2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
> 2005-08-11 16:33:24.45 spid3 Recovery complete.
> ...
> if something is pending fo a specific database you'll see rollforwad and
> rollback operations...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
Ken Varn wrote:
> So that I can clarify what you are saying here. SQL Server will not
> allow you to detach a database until it has completed the recovery
> process?
until the recovery has been completed, SQL Server does not allow to touch
all dbs at all... it is still "starting"...

> I basically want to know if it is safe to get rid of the LDF file
> after a database has been detached without any adverse ramifications,
> like database corruption.
actually you should not get rid of the LDF file at all, as it is a needed
part of a database... even when the simple recovery model is used you should
always provide all transaction log files... you can use
sp_attach_single_file_db when they are not present but best practice implies
forwarding them too...
say you have a db file named [myDb] with 1 physical data file as
C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
you only provide me the C:\..\myDb.Mdf instructing me to attach it via
sp_attach_single_file_db ...
on my system I already have another company sw which uses a db with the very
same physical data files (and names)...
the corresponding datafile will be C:\..\myDb.Mdf as the log file in
C:\..\myDb.Ldf
as I can not put your db in the same folder I move it to
C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
now... SQL Server try to create a new t-log file using the available info
(sysfiles and sysfiles1 system table) and will point to the other company
database C:\..\myDb.Ldf physical file...
that log file will be pointed by 2 db and will immediately raises exception
marking 1 of the 2 db as suspect...
to solve this problem I need the t-log of your db and the use of
sp_attach_db indicating all the required physical files..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
It sounds as if as long as I do not try to attach a database onto a system
that has the same database name I can use sp_attach_single_file_db with no
adverse affects. Or, are there other issues that I should be concerned
with?
Basically, without trying to go into too much detail here, our system has an
automated process that periodically detaches databases, removes the ldf file
and archives the mdf file. The system may re-attach any of these databases
again at a later time depending on certain circumstances. We have been
seeing some database corruption issues and have tried to determine if this
process is the cause. We do know that occasionally the system is restarted
due to power failures (although the UPS is doing an orderly shutdown) and we
want to figure out if our detach and removal of LDF file process could be
the cause of databases getting corrupt after we re-attach them during a
restart from a power fail.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m444tF155s4gU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> until the recovery has been completed, SQL Server does not allow to touch
> all dbs at all... it is still "starting"...
>
> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
> as I can not put your db in the same folder I move it to
> C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
> now... SQL Server try to create a new t-log file using the available info
> (sysfiles and sysfiles1 system table) and will point to the other company
> database C:\..\myDb.Ldf physical file...
> that log file will be pointed by 2 db and will immediately raises
exception
> marking 1 of the 2 db as suspect...
> to solve this problem I need the t-log of your db and the use of
> sp_attach_db indicating all the required physical files..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
> It sounds as if as long as I do not try to attach a database onto a
> system that has the same database name I can use
> sp_attach_single_file_db with no adverse affects. Or, are there
> other issues that I should be concerned with?
should not... SQL Server will try to recreate the log file(s) based on the
original info... and if no "problem" is found it should be able to correctly
attach the db..

> Basically, without trying to go into too much detail here, our system
> has an automated process that periodically detaches databases,
> removes the ldf file and archives the mdf file. The system may
> re-attach any of these databases again at a later time depending on
> certain circumstances. We have been seeing some database corruption
> issues and have tried to determine if this process is the cause. We
> do know that occasionally the system is restarted due to power
> failures (although the UPS is doing an orderly shutdown) and we want
> to figure out if our detach and removal of LDF file process could be
> the cause of databases getting corrupt after we re-attach them during
> a restart from a power fail.
missing log files in recovery moment can be a problem... I'd archive the log
files as well, even in simple recovery model..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
> > I am in the process of returning a machine running SQL Server back to
> > our provider. However, I don't want them to retrieve any of our data
> > stored in the DB. So I have the following 2 options
> > a) delete the rows from tables
> > b) remove the MDB file
> > Which of the options is better?
> > If I just delete the rows, will the SQL Server delete them from the
> > MDB file immediately?
> > If I remove the MDB file, can anyone put it back?
> > For instance, the Exchange Server use SQL Server and deleting a
> > mailbox will retain the data for about 14 days. Is there a similar
> > provision in SQL Server that retains the data. I don't want our
> > provider to retrieve any of the data.
> > Many thanks for reading and looking forward to replies
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
>> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
>> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
>> EM -> select DB -> all tasks -> detach database
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive or
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
>
>
>
>
>
>
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.

> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> I don't have names. You will have to look for yourself. Sorry.
>
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)

> robert
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive o
r
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to replies
On 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert

Question about authentication

Hello :).

I have a web application running under .NET 1.1 and Reporting Services 2005. What I need is to display reports in my app.

Here I have two problems:

1. SSRS uses web service as a data source (it is in my application). Access to the service should be permitted only for known (and logged in) users.

2. Access to SSRS should be permitted only for known (and logged in) users.

So, what is the best approach to integrate Reporting Services and my application according to the security limitations?

Any advise on this subject is more than welcome :)

Thank you.

You have basically two options - trusted account and custom security. See this article for more info. I am curious to know how SSRS uses a web service as a data source. Did you write a custom data extension?|||

Teo, thank you very much for your great articles and reply. They were very helpful for me.

Talking about web service as a data source I mean simple XML data source. I.e. DataProvider is 'XML', and connection string refers to the web service. So, I didn't write any custom data extension, as it was already written in the SSRS 2005 :).

Sorry if original post was unclear (maybe it's because I'm really novice in the Reporting Services :) ).

Question

Does anyone know why:
i have an application running and it depends on my sql
databases
when i execute a query in QA, it runs smoothly with no
problems.
when i execute the same query using sql data tools
(enterprise manager -> open database -> choose table right
click and choose query -> execute query there) it causes
locks with the queries executed by the application...
any ideas?EM uses cursors and stuff in order to present the result in the grid. EM was
never designed to be an end-user data entry tool. I suggest you do your
query test in QA and let the users modify the data using your application.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudy" <anonymous@.discussions.microsoft.com> wrote in message
news:507001c3ffa5$55611ba0$a101280a@.phx.gbl...
> Does anyone know why:
> i have an application running and it depends on my sql
> databases
> when i execute a query in QA, it runs smoothly with no
> problems.
> when i execute the same query using sql data tools
> (enterprise manager -> open database -> choose table right
> click and choose query -> execute query there) it causes
> locks with the queries executed by the application...
> any ideas?

Question

Does anyone know why:
i have an application running and it depends on my sql
databases
when i execute a query in QA, it runs smoothly with no
problems.
when i execute the same query using sql data tools
(enterprise manager -> open database -> choose table right
click and choose query -> execute query there) it causes
locks with the queries executed by the application...
any ideas?EM uses cursors and stuff in order to present the result in the grid. EM was
never designed to be an end-user data entry tool. I suggest you do your
query test in QA and let the users modify the data using your application.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Claudy" <anonymous@.discussions.microsoft.com> wrote in message
news:507001c3ffa5$55611ba0$a101280a@.phx.gbl...
> Does anyone know why:
> i have an application running and it depends on my sql
> databases
> when i execute a query in QA, it runs smoothly with no
> problems.
> when i execute the same query using sql data tools
> (enterprise manager -> open database -> choose table right
> click and choose query -> execute query there) it causes
> locks with the queries executed by the application...
> any ideas?