Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do SQL Server's developers even know what the datetime datatype is?

Just a bit of a rant here...
I've marveled for a long time that SQL Server developers chose to store
SQL Agent job history dates in in the sysjobhistory table as two
seperate columns in integer format instead of a single datetime column.
I mean come on.
Then today I discovered that the Windows 2003 Perfmon stores dates as
char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
Char (24) won't even do an explicit conversion to datetime. You have to
only grab the first 23 characters.
I think Bill G. needs to send around an internal memo reminding
everyone about the datetime data type.
/end rantI wish Gert had included some background on why this decision came about,
but otherwise a great resource:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm
As for Perfmon, isn't it possible it was formatted to CHAR(24) because it
usually logs to a file, and the file needs to be understood by more systems
than just SQL Server?
<pshroads@.gmail.com> wrote in message
news:1164659681.627479.274240@.h54g2000cwb.googlegroups.com...
> Just a bit of a rant here...
> I've marveled for a long time that SQL Server developers chose to store
> SQL Agent job history dates in in the sysjobhistory table as two
> seperate columns in integer format instead of a single datetime column.
> I mean come on.
> Then today I discovered that the Windows 2003 Perfmon stores dates as
> char(24) when the data is logged to a SQL Server database. CHAR(24)?!?!
> Char (24) won't even do an explicit conversion to datetime. You have to
> only grab the first 23 characters.
> I think Bill G. needs to send around an internal memo reminding
> everyone about the datetime data type.
> /end rant
>

Do SQL Server 2005/CLR Triggers support .config Files?

Does anyone know if it's possible to use the standard .config file within a CLR Trigger to read properties via the System.Configuration namespace

I guess it's not possible because the CLR Trigger needs to be compiled as an assembly which is hosted by SQL Server

Thanks

Jason

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6d635c2cd23fed7

CLR stuff in SQL Server does not work like those know from "normal" applications. Although you can use the Configuration class to load a configuration somewhere stored on disk, you have to keep in mind, that every namespace / class that you use with are not approved as safe will lower down your security, because you have to mark them as unsafe / external access.

Perhaps any other posters has some other experiences about that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

DO SQL perf counters intepret sysperfinfo internally ?

When SQL Server counters are used within perfmon, do they read off of values
in sysperfinfo and if so, what populates sysperfinfo and when does it run ?
And if there is no connection at all, then what is the use of sysperfinfo
and when can one use it and what can i make of it ?Sysperfinfo gets its data from the SAME sources as perfmon... Sysperfinfo is
used as the source for SQL Agent Performance Alerts..YOu can also use it for
your purposes but the formatting etc is strange... If I am not mistaken
there may be a white paper ( search on MS web site ) which describes how to
use sysperfinfo stats.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ei0$fAuiDHA.1940@.TK2MSFTNGP09.phx.gbl...
> When SQL Server counters are used within perfmon, do they read off of
values
> in sysperfinfo and if so, what populates sysperfinfo and when does it run
?
> And if there is no connection at all, then what is the use of sysperfinfo
> and when can one use it and what can i make of it ?
>|||Are you saying that the SQL Agent perf alerts are based upon the values in
sysperfino ? If so , then the source to perfmon\SQL Agent Perf counters is
sysperfino ..right ?
And the source to sysperfinfo is a stored proc that runs every couple of
secs I beleive ..sp_sqlagent_get_perf_counters ...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OA1LvN0iDHA.888@.TK2MSFTNGP09.phx.gbl...
> Sysperfinfo gets its data from the SAME sources as perfmon... Sysperfinfo
is
> used as the source for SQL Agent Performance Alerts..YOu can also use it
for
> your purposes but the formatting etc is strange... If I am not mistaken
> there may be a white paper ( search on MS web site ) which describes how
to
> use sysperfinfo stats.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ei0$fAuiDHA.1940@.TK2MSFTNGP09.phx.gbl...
> > When SQL Server counters are used within perfmon, do they read off of
> values
> > in sysperfinfo and if so, what populates sysperfinfo and when does it
run
> ?
> > And if there is no connection at all, then what is the use of
sysperfinfo
> > and when can one use it and what can i make of it ?
> >
> >
>|||The source for the (virtual) table sysperfinfo is a piece of shared
memory that is maintained all over the place in the sqlserver code.
Querying sysperfinfo, perfmon/sysmon refreshes and other actions (like
agent alerts) use that piece of shared memory as their source of
information.
"sql" <sql@.hotmail.com> wrote in message news:<OoFt271iDHA.1688@.TK2MSFTNGP12.phx.gbl>...
> Are you saying that the SQL Agent perf alerts are based upon the values in
> sysperfino ? If so , then the source to perfmon\SQL Agent Perf counters is
> sysperfino ..right ?
> And the source to sysperfinfo is a stored proc that runs every couple of
> secs I beleive ..sp_sqlagent_get_perf_counters ...
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:OA1LvN0iDHA.888@.TK2MSFTNGP09.phx.gbl...
> > Sysperfinfo gets its data from the SAME sources as perfmon... Sysperfinfo
> is
> > used as the source for SQL Agent Performance Alerts..YOu can also use it
> for
> > your purposes but the formatting etc is strange... If I am not mistaken
> > there may be a white paper ( search on MS web site ) which describes how
> to
> > use sysperfinfo stats.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.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
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ei0$fAuiDHA.1940@.TK2MSFTNGP09.phx.gbl...
> > > When SQL Server counters are used within perfmon, do they read off of
> values
> > > in sysperfinfo and if so, what populates sysperfinfo and when does it
> run
> ?
> > > And if there is no connection at all, then what is the use of
> sysperfinfo
> > > and when can one use it and what can i make of it ?
> > >
> > >
> >
> >sql

Do SQL Analysis service needs a separate database ?

As Adventure Works have Adventure works DW as a separate database, Do we also needs to create new database such as while working with "pubs" or any other do we need to create pubs DW separately or pubs will be sufficient for our working for sql analysis ?

Hi,

you should to create a new DW database (it′s a best pratice), but it′s not mandatory.

If you have a small production database, you will not percept the performance degradation...

So to create a DW (Dataware house) can be good to isolate OLAP from OLTP.

Regards

Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe there
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at £8,100.00 - which isnt too cheap(!)
Thanks.From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
--
David Portas
SQL Server MVP
--

Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe ther
e
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at ￡8,100.00 - which isnt too cheap
(!)
Thanks.From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
David Portas
SQL Server MVP
--

Do SQL 7 CALs count for a SQL 2000 install?

Hi,
I have a client with SQL 7 and they need to go to SQL 2000 via an upgrade to
run their now updated app. Do their existing SQL 7 CALs allow then to access
the SQL 2000 server or do they have to buy all the CALs again. Or maybe there
is some upgrade price.
Their vendor has priced 60 SQL 200 CALs at ￡8,100.00 - which isnt too cheap(!)
Thanks.
From the server EULA:
"Any CAL must have the same or later version number than the corresponding
version number of the Server Software being used."
http://www.microsoft.com/sql/howtobuy/default.asp
Ask the vendor about upgrade pricing.
David Portas
SQL Server MVP

Do Sql 2005 and Access not play well together?

Does anyone know if I can't put Access 2003 and SQL 2005 on the same server together? I've got an old, well not that old, application that was farmed out coming back home and apparently it's backend is Access and the frontend is ASP, I think. Anyway, the power's that be would like to upgrade to SQL 2005 and would like everything on the same box together. I've heard rumors that's not possible... Does anyone have any ideas? Thanks a ton.

Nicole (not Anna) Smith

It is NOT a problem to install both Access and SQL Server on the same computer.|||Well as Arnie said, in general it is no problem, don′t know if this is officially supported (like outlook and Exchange isn′t), but I also saw that many times working smootly.

Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Do somebody know how long (in chars) script(command) can be solved by SQL Command?

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
ThanksIn the case of SQL Server, your total command length must be no more than 8000 characters. It does indeed use sp_executesql, which imposes this limitation.

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do sequences of transactions logs are broken when a restore occur ?

Hi,
Do sequences of transactions logs are broken when a restore occur ?
Thank you
danny> Do sequences of transactions logs are broken when a restore occur ?
I am not sure if I understand the question. When you do the restore db and
log over an existing database, you overwrite the data files and the log
files. Sequence is constant and not broken in the restored database, but of
course in the original one you could have had different LSN. It is
overwritten, so why care?
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked th=is
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (with=out
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout l=osing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also cor=rupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the=
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and r=estore
all transactions log (sequentially) from june 07 untill now without losin=g data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to =June
28 even though the operator has restored the database (from June 14) on J=une 21
? In others words, do sequences of transactions logs has been broken on J=une 21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a =E9crit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db =and
> log over an existing database, you overwrite the data files and the log=
> files. Sequence is constant and not broken in the restored database, bu=t of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||Danny, now I see what you mean. When you issue the recovery process (i.e.
put the database in operational mode after last restore), SQL Server
restarts LSN (Log Sequence Number) from different number from the last
backup. Thus your log backups from
Fri. June 20 Transaction log backup
and
Sun. june 22 Transaction log backup
are not connected anymore- you have a hole in LSN's. So it is not possible
to restore the 2nd log backup mentioned after the 1st mentioned.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Danny Presse" <danny.presse@.sympatico.ca> wrote in message
news:3F0037BF.200A1DBD@.sympatico.ca...
Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked this
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (without
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout
losing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also
corrupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and
restore
all transactions log (sequentially) from june 07 untill now without losing
data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to
June
28 even though the operator has restored the database (from June 14) on June
21
? In others words, do sequences of transactions logs has been broken on June
21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a écrit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db and
> log over an existing database, you overwrite the data files and the log
> files. Sequence is constant and not broken in the restored database, but
of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.orgsql

Do scheduled jobs stay in memory?

My boss was looking at our SQL box, comparing the jobs list to the running
processes list. He noticed that the numbers are roughly analogous to each
other. He posed me the question, "do scheduled jobs stay in memory, or do
they release themselves when complete?"
I'm pretty sure the answer is that they release themselves, but I figured I
would bow to somebody from this group who had much more knowledge of the
matter.
Regards,
Scott
i don't know exactly what numbers you see in the job list.
if a job is running, you will see it in "current activity - process info"
to answer his question, more than likely that job (or at least pieces of it)
will stay in memory
some of its code may stay in the procedure cache.
some of its data may stay in the buffer cache.
sql server generally tries to keep everything in memory and only releases
things when it feels it's necessary.
Scott McNair wrote:

> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott
|||Scott,
if you look at the "current activity" in EM under Management, in Process
Info you will find the process running. You can find that when the jobs are
not running, you will not have processes related to the jobs. Yes, when a
job finishes, it releases itself.
A task (a job, a QA query etc) can possibly spin off multiple processes, so
you may have more processes then your currently running task. This might be
the reason that you sometimes see a relation between the number of processes
and processes, but that is completely coincidental.
Quentin
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns94E688FF6C32Asfmco@.207.46.248.16...
> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured
I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott

Do scheduled jobs stay in memory?

My boss was looking at our SQL box, comparing the jobs list to the running
processes list. He noticed that the numbers are roughly analogous to each
other. He posed me the question, "do scheduled jobs stay in memory, or do
they release themselves when complete?"
I'm pretty sure the answer is that they release themselves, but I figured I
would bow to somebody from this group who had much more knowledge of the
matter.
Regards,
Scotti don't know exactly what numbers you see in the job list.
if a job is running, you will see it in "current activity - process info"
to answer his question, more than likely that job (or at least pieces of it)
will stay in memory
some of its code may stay in the procedure cache.
some of its data may stay in the buffer cache.
sql server generally tries to keep everything in memory and only releases
things when it feels it's necessary.
Scott McNair wrote:
> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott|||Scott,
if you look at the "current activity" in EM under Management, in Process
Info you will find the process running. You can find that when the jobs are
not running, you will not have processes related to the jobs. Yes, when a
job finishes, it releases itself.
A task (a job, a QA query etc) can possibly spin off multiple processes, so
you may have more processes then your currently running task. This might be
the reason that you sometimes see a relation between the number of processes
and processes, but that is completely coincidental.
Quentin
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns94E688FF6C32Asfmco@.207.46.248.16...
> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured
I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott

Do scheduled jobs stay in memory?

My boss was looking at our SQL box, comparing the jobs list to the running
processes list. He noticed that the numbers are roughly analogous to each
other. He posed me the question, "do scheduled jobs stay in memory, or do
they release themselves when complete?"
I'm pretty sure the answer is that they release themselves, but I figured I
would bow to somebody from this group who had much more knowledge of the
matter.
Regards,
Scotti don't know exactly what numbers you see in the job list.
if a job is running, you will see it in "current activity - process info"
to answer his question, more than likely that job (or at least pieces of it)
will stay in memory
some of its code may stay in the procedure cache.
some of its data may stay in the buffer cache.
sql server generally tries to keep everything in memory and only releases
things when it feels it's necessary.
Scott McNair wrote:

> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured
I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott|||Scott,
if you look at the "current activity" in EM under Management, in Process
Info you will find the process running. You can find that when the jobs are
not running, you will not have processes related to the jobs. Yes, when a
job finishes, it releases itself.
A task (a job, a QA query etc) can possibly spin off multiple processes, so
you may have more processes then your currently running task. This might be
the reason that you sometimes see a relation between the number of processes
and processes, but that is completely coincidental.
Quentin
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns94E688FF6C32Asfmco@.207.46.248.16...
> My boss was looking at our SQL box, comparing the jobs list to the running
> processes list. He noticed that the numbers are roughly analogous to each
> other. He posed me the question, "do scheduled jobs stay in memory, or do
> they release themselves when complete?"
> I'm pretty sure the answer is that they release themselves, but I figured
I
> would bow to somebody from this group who had much more knowledge of the
> matter.
> Regards,
> Scott

Do replication for a non-Administrators

Can I do replication to copy tables in one database which was not create by
me to another database?
possibly, you need to be system administrator or dbo to create publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"M" <mxchen@.hotvoice.com> wrote in message
news:%23Lwd3w38EHA.1188@.tk2msftngp13.phx.gbl...
> Can I do replication to copy tables in one database which was not create
by
> me to another database?
>

Do regular stored procedures still run when Agent XPs are disabled?

Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
Spin
Spin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
sql

Do regular stored procedures still run when Agent XPs are disabled?

Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>

Do Queries Support Regular Expressions?

If I had the following string in a table how could I find all records that
contained the term 'Business' for example? Would Regular Expressions be
supported in this context? Your comments regarding performance of a category
schema such as this?
// Example
PrivateSector_Business_Services
<%= Clinton GallagherClinton,
You could use the LIKE operator with the % wildcard character as:
WHERE COLUMN LIKE '%Business%'
Optionally you could use a Full-Text index as well.
HTH
Jerry
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>|||Thank you Jerry. That was simple :-)
<%= Clinton Gallagher
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OZqoPlczFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Clinton,
> You could use the LIKE operator with the % wildcard character as:
> WHERE COLUMN LIKE '%Business%'
> Optionally you could use a Full-Text index as well.
> HTH
> Jerry
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||For what it's worth, you can make real regular expression query searches via
calls to external COM libraries.
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
However, if using the native LIKE operator satisfies your requirement, then
use that instead.
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>

do queries running from clr stored procs tie up one available connection

I'm wondering if one less external sql server connection is available when my clr stored proc querys my database inside of the db engine.

AFAIK, no

Niels

do process

how can I do
perform a stored procedure with paramert from my client application but my q
uestion is sent call but transfer process to sqlserver a for example i can c
lose my application but process continue running in a sqlserver until finiss
hed, similar a job but with
out job.
Thank You
AlfSome environments and providers (e.g. .NET) will allow you to make a stored
procedure call asynchronous, meaning it does not have to wait for the
result.
So, it is hard to say if your client application can do that, because you
forgot to tell us anything about your client application.
A
<ag> wrote in message news:%23iXkfN6QGHA.4740@.TK2MSFTNGP14.phx.gbl...
> how can I do
>
> perform a stored procedure with paramert from my client application but my
> question is sent call but transfer process to sqlserver a for example i
> can close my application but process continue running in a sqlserver until
> finisshed, similar a job but without job.
> Thank You
> Alf
>

Do pre-defined joins in SQL Server slown down inserts/deletions?

I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tablesbetween which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.
The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
Hi,
I do not know that is the exact control structure for an insert but ifyou have some constraints on your columns as well as foreign keys, therecord you are trying to insert is controlled by means of thoseconstraints.
For example, you have a foreign key on CityId column referencing Citiestable. Then the column value you are inserting for CityId must bepresent at Cities table. So this is a job to check if the cityid reallyexists in Cities table. Perhaps as simple as select * from cities wherecityid = @.cityid . But this brings an extra work during the insert.
This causes a delay. But maintains data integrity within your database.And data integrity is more important for me. Because you can improveperformance by many methods (by improving hardware, sql code, sqlserver parameters,etc) but if you lose your data integrity, it will notbe easy perhaps impossible to maintain it back.

Indexes are very useful. But if you use so many indexes on a tableespecially which is used only for insert and updates (not for so muchread), every insert will mean a new update or insert on each index usedon that table.
In short, we have the plus and minus side by side. The outcome depends how and where you use them.
Eralper
http://www.kodyaz.com

|||EDIT
I don't think DRI(declarative referential integrity) will slow down inserts and deletes because one thing it is not insert related it just guarantees updates and deletes will cascade up or down based on what you set up.I am assuming you marked the insert relationship also but that is just for data integrity by enforcing the relationship between tables. It is ANSI SQL if you say cascade on delete no action it will not affect the other table but if you say on delete cascade then it will affect the other table. If your tables are too big you should look into creating views that will be smaller. Run a search for cascade delete and cascade update in SQL Server BOL (books online). Hope this helps.sql

Do pass thru query connections persist?

I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi

Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.

BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.

HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.

I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?

Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?

Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.

Here is my code behind the report.

Private Sub Report_Open(Cancel As Integer)

Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset

conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"

conGlob.CursorLocation = adUseClient

conGlob.Open

With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With

rst.Open "usp_GetStates", , , , adCmdStoredProc

Set Me.Recordset = rst

rst.Close
conGlob.Close

End Sub|||Your best bet would be to use an access data project and use stored procedures

First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections

1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.

Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.

If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At
the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inser
ted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects
of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to
the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the cl
ustered index is the
>physical sort order of data in a table. And once i have this technique avai
lable to me, i can take
>advantage of it to group low-cardinality rows together, where an index woul
d not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tunin
g option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be
able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?
I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>
|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.
|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)
|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David
|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do nto send scheduled report when no data is present

Dear Friends:

Could somebody please help me to stop sending scheduled report when no data is present in the report? Actually I have a daily report which is sent to the client and in a week, two or three days, data will not be there and thus it delivers blank report to the client.

So how can I stop sending blank mails to the client?

Thanks in Advance

Emmanuel Mathew

One possibility would be creating a data-driven subscription with query that returns an empty list of recipients if no data for the report is available.sql

Do not SELECT

Hi,
Is there any way how to formulate SQL query to select all columns except
column D and E?
So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C
SELECT " * except D,E"
FROM myTable
Thanks,
Lubomir
SELECT A, B, C
FROM myTable
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||Hello,
If you want it permamanent then you can create a view and then query the
view
CREATE VIEW A1
as
Select A,B,C from myTable
After the creation query the view
Select * from A1
THanks
Hari
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.
From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.
Thanks,
Lubomir
"Hari Prasad" wrote:

> Hello,
> If you want it permamanent then you can create a view and then query the
> view
> CREATE VIEW A1
> as
> Select A,B,C from myTable
> After the creation query the view
> Select * from A1
> THanks
> Hari
>
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
>
>
|||On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

>The problem is, the tables are created on the fly, so I don't know what
>columns the particular table will have.
Hi Lubomir,
That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.
Could you explain in some more detail WHY your application does not have
a fixed data model?

>From that reason, it would be very convenient to make query like SELECT *
>and to specify those 2 columns to be exclusive.
There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.
In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:8587187A-53B8-4C53-A2DD-2F300FADFF3A@.microsoft.com...[vbcol=seagreen]
> The problem is, the tables are created on the fly, so I don't know what
> columns the particular table will have. I know however, every table has
> two
> columns ("help" columns) that will not be displayed, as they are used for
> another purposes.
> From that reason, it would be very convenient to make query like SELECT *
> and to specify those 2 columns to be exclusive.
> Thanks,
> Lubomir
> "Hari Prasad" wrote:
|||Hi Hugo,
The reason is, that that this application serves more application (like
plugins) with their own tables.
Lubomir
"Hugo Kornelis" wrote:

> On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:
>
> Hi Lubomir,
> That is indeed a problem. And it's also a sign that you are using the
> database in a way that it's not intended to be used - applications that
> need to create tables on the fly are almost always the result of some
> bad design decisions.
> Could you explain in some more detail WHY your application does not have
> a fixed data model?
>
> There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
> vote against it - personally, I'd rather remove the SELECT * than to
> extend it!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

>Hi Hugo,
>The reason is, that that this application serves more application (like
>plugins) with their own tables.
Hi Lubomir,
As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).
If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.
Ramunas
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> The reason is, that that this application serves more application (like
> plugins) with their own tables.
> Lubomir
>
> "Hugo Kornelis" wrote:
*[vbcol=seagreen]
|||Yes, that could work.
Thanks,
Lubomir
"Ramunas Balukonis" wrote:

> If you are talking about SQL 2005, then you can write DDL trigger and
> programically create view or procedure wich select all columns from table
> exept those 2.
> Ramunas
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...
> *
>
>

Do not render report with no data

Hi!
I would like to stop processing of a report that has no data in a specific
dataset. Is this somehow possible?
In Access its possible to not process a report if there is no data behind
it.
Or is the only way to write an application that checks if the data exist and
if not just skips the Render part?
Thanks for any hints!
rgds,
tomOr can I somehow throw an excpetion inside of the report if a specific
dataset has no data?
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:OUgDeGX$EHA.3372@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I would like to stop processing of a report that has no data in a
> specific dataset. Is this somehow possible?
> In Access its possible to not process a report if there is no data behind
> it.
> Or is the only way to write an application that checks if the data exist
> and if not just skips the Render part?
> Thanks for any hints!
> rgds,
> tom
>|||Thomas Kern wrote:
> Or can I somehow throw an excpetion inside of the report if a specific
> dataset has no data?
You can use the rowcount-property of the dataset and that the
report-visibility or your dataregion or elements to true or false.
regards
Frank
www.xax.de|||Where is the RowCount property of a dataset?
I need to limit mine...
thanks,
trint
Frank Matthiesen wrote:
> Thomas Kern wrote:
> > Or can I somehow throw an excpetion inside of the report if a
specific
> > dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de|||Use the CountRows aggregate function. E.g. =CountRows("DatasetName")
See also:
http://msdn.microsoft.com/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"trint" <trinity.smith@.gmail.com> wrote in message
news:1106067185.185429.115960@.z14g2000cwz.googlegroups.com...
> Where is the RowCount property of a dataset?
> I need to limit mine...
> thanks,
> trint
>
> Frank Matthiesen wrote:
> > Thomas Kern wrote:
> > > Or can I somehow throw an excpetion inside of the report if a
> specific
> > > dataset has no data?
> >
> > You can use the rowcount-property of the dataset and that the
> > report-visibility or your dataregion or elements to true or false.
> >
> > regards
> >
> > Frank
> > www.xax.de
>|||how can I set the report visibility to false?
I really want to prevent to report from beeing generated in this case.
thanks.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:354qr8F4969gvU1@.individual.net...
> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de
>
>|||I found the following solution but its more database-centric:
-) Check the @.@.rowcount of the query inside the Stored Procedure.
-) If @.@.rowcount = 0, RAISERROR
here we go: this is becomes an exception in the report and it is not
rendered!
tom
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:us3ocLa$EHA.2984@.TK2MSFTNGP09.phx.gbl...
> how can I set the report visibility to false?
> I really want to prevent to report from beeing generated in this case.
> thanks.
> "Frank Matthiesen" <fm@.xax.de> wrote in message
> news:354qr8F4969gvU1@.individual.net...
>> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
>> You can use the rowcount-property of the dataset and that the
>> report-visibility or your dataregion or elements to true or false.
>> regards
>> Frank
>> www.xax.de
>>
>

Do not lock tables option

sql2k sp3
From what I know the only problem with checking the "Do not lock tables
duing snapshot..." would be that the snapshot would take longer. Are there
any other downfalls?
Chris,
theoretically there would be potential of timeouts occurring due to
blocking, and even the posibility of deadlocks. Some time ago there was a
poster who was having exactly these issues with this option - these
posibilities are the price you pay for allowing greater concurrency. I
imagine most people (like myself) run snapshots out of hours so this option
isn't required.
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]

Do not lock table...

sql2k sp3
Whats good/ bad to checking/ not checking the "Do not
lock table during snapshot..." button?
TIA, ChrisR
when you select the concurrent snapshot option it takes longer to generate
the snapshot and there is less locking.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:10ab01c4a736$fe990410$a301280a@.phx.gbl...
> sql2k sp3
>
> Whats good/ bad to checking/ not checking the "Do not
> lock table during snapshot..." button?
> TIA, ChrisR

do not have permission error message

I have been trying to run a script that modifies objects in a SQL Server 2005 database. I am trying to drop an index but I am told the index does not exist or I do not have the correct permissions. An example of one of the commands in the script that I am trying to execute are as follows.

command:
DROP INDEX spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET;

response:
Msg 3701, Level 11, State 6, Line 1
Cannot drop the index 'spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET', because it does not exist or you do not have permission.

The index does exist in the table so the problem must be related to permissions. I am the one who created the database so I am the dbo. I should have permission to do anything in the database, right? So why am I getting this error when my script runs?

thanks,
shimo

The dbo should be able to drop the index.

Is it possible that you are not running as the dbo? Maybe an application role has been set or something like that.

One way to check is

select user_name() to determine your user context.

Another possiblility is that the spices table is not in dbo or your default schema.

Try using the two part name schema.spices.ADAPTER_MESSAGE.ADAPTER_MESSAGE_SET

HTH,

-Steven Gott

S/DET

SQL Server

|||

Is this a SQL x64 bit edition?

We have had similar issue and reported to MS Connect site.

sql

Do not have instance of SQL Express 2005 installed

I am attempting to install SQL Express 2005 on a laptop that already has MS Sql Server installed.

I downloaded the SQLEXPR_TOOLKIT.EXE from Microsoft's website and let it go through its installation routine accepting all the default answers.

When I open SQL Server Management Studio Express, I can only connect to the SQL 2000 instance. I have issued the 'SELECT @.@.VERSION' command, and When I try to connect to the instance COMPUTERNAME\SQLEXPRESS, the following error message is displayed:
Cannot connect to YOUR-F8A010A9D0\SQLEXPRESS.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure my be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance Specified) (Microsoft SQL Server)

When I go to remove SQL Express 2005 using the add/remove programs, there is a check box that says
"Remove SQL Server 2005 instance components"
and in the box underneath where it lists all the instances it says: "No instances have been installed"

How do I create an instance for SQL Server 2005?

Thank you
SusanYou go to the incorrect location to install SQL Express. Instead, you choose to install tools only. Please go to the directory ...\SQLEXPR\setup.exe. Note, if .NET Framework is still not installed on the machine, you need to install it first.

Do not export navigations

Hi,
Is there a way NOT to export the navigation within reports?
I often use navigation to other reports, but when I export
those reports to excel, the links are still there and are more
a pain in the *ss then helping me managing the data...

any ideas?
thx,
Frank M.One option is to have a report parameter (Export) and hide the controls when this parameter is selected.|||That would be an option, thx.
Do you know if it's also possible to 'hide' the "Hyperlink Action" in a similar way?
I can't put a function there to check if a parameter is true or false...|||I think you would use an =IF(condition,true,false) statement to check for the parameter directly in the hyperlink box.)
=IIF(Parameters!DrilldownType.Value="-1",false,Parameters!DrilldownType.Value)
The Drill Down Type parameter would be the name of the report you are trying to access.|||I can't get a function in the hyperlink-action...
it's a link to an other report|||Where is the hyperlink action you are trying to access? The one I am looking at is under the textbox properties, advanced, navigation.

Do not export navigations

Hi,
Is there a way NOT to export the navigation within reports?
I often use navigation to other reports, but when I export
those reports to excel, the links are still there and are more
a pain in the *ss then helping me managing the data...

any ideas?
thx,
Frank M.One option is to have a report parameter (Export) and hide the controls when this parameter is selected.|||That would be an option, thx.
Do you know if it's also possible to 'hide' the "Hyperlink Action" in a similar way?
I can't put a function there to check if a parameter is true or false...|||I think you would use an =IF(condition,true,false) statement to check for the parameter directly in the hyperlink box.)
=IIF(Parameters!DrilldownType.Value="-1",false,Parameters!DrilldownType.Value)
The Drill Down Type parameter would be the name of the report you are trying to access.|||I can't get a function in the hyperlink-action...
it's a link to an other report|||Where is the hyperlink action you are trying to access? The one I am looking at is under the textbox properties, advanced, navigation.

Do not Email if No Data

Is there a way to set up a subscription so that it does not email the
blank report if there was no data resulting from the query?
Thanks
BobBob,
Did you figure this out ? Just posted the same issue.
Thanks, Steve.
"Bob" wrote:
> Is there a way to set up a subscription so that it does not email the
> blank report if there was no data resulting from the query?
> Thanks
> Bob
>

Do not display the result of my long text string, approx about 400 characters

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
......If you are using the MS Query Analyzer from the SQL 2000 Client Tools, you can set the maximum column length. Select Tools | Options | Results and the length control is near the middle of the sheet.

-PatPsql

Do not Display "NULL" in Results tab

Is there a setting in Management Studio to NOT display "NULL" in the
Results tab for either Grid or Text view? I didn't see anything under
Options. I'm currently having to do search and replace to remove it
before passing on to users or using for imports to other apps. Third
party import programs typically treat this as text.
MarkChange the query to say COALESCE(stringValue, '')
"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
> Is there a setting in Management Studio to NOT display "NULL" in the
> Results tab for either Grid or Text view? I didn't see anything under
> Options. I'm currently having to do search and replace to remove it
> before passing on to users or using for imports to other apps. Third
> party import programs typically treat this as text.
>
> Mark|||This is what I was afraid of. It seems for every two steps forward in
Management Studio, you have to take one step back. So now if I have
100 varchar columns, I'll have to create 100 alias columns
(ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
carry over features in previous versions to the next. It seems they
could have added an option to "Query Results" to display NULL or blank
for the output. This wasn't a problem in QA.
On Fri, 30 Jun 2006 08:45:49 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Change the query to say COALESCE(stringValue, '')
>"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
> news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
>|||> (ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
> carry over features in previous versions to the next. It seems they
> could have added an option to "Query Results" to display NULL or blank
> for the output. This wasn't a problem in QA.
It's quite possible that they got a lot of complaints about that
"feature"... to me, at least, there is a big difference between NULL and
blank. If I can't visibly tell the difference, I'm going to have to change
something.
It's kind of like a car manufacturer choosing to put the cup holder on the
dash or in the center console. No matter which option they choose, they're
going to piss someone off.
A

Do not delete things, it bites

Hello,

I noticed that when you delete things or substitute tables, you get in trouble.

- Delete a named query used in a partition before deleting the partition

- Delete a named Calculation from a table in the Ds view, whathever you do

- Replace a table with another table which has less columns even if none of the lesser columns are used nowhere.

The XML do not get updated accordingly leading in all kinds of errors, some requiring restore from an older version.

Just a FYI.

BTY, I do not know how to submit bug reports.

Philippe

Build 9.00.2175.00

go to connect.microsoft.com|||

I think we have both come across the same problem (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640957&SiteID=1).

For permissions, I have manually cleaned up the xml files. If you post this on connect.microsoft.com, let me know as I cannot do that. I can vote for it.

btw, how did you get build 9.00.2175.00?

|||

Yes, the changes you made in DSV will not propagate to other linked ojects. Since you are deleting things from DSV, the related binding will be broken and it is up to the user to rebind or remove it. Anyway, you should get validation error when you try to deploy and find out the broken binding.

We have already got customer request on this issue. We will consider to improve the user experience in the next release.

|||I disagree. It is not a case of improving the user experience. It is a bug.|||

A bug means that the software is not doing what it is supposed to do in the design spec. This is not a bug because we don't have it in design spec. You can say it is design flaw. However, from the user experience, people hated to see something that deleted automatically without any control. Therefore, what I suggested is to enhance the user experience. After deleting the objects in DSV, we could show a list box to list all the broken bindings and give user the option to delete the broken objects. Some advanced users may want to leave the broken bindings so that they can bind to different things instead of regenerate the objects or redo all the modifications.

Anyway, we already addressed the issue. We will consider it in future release.

Thanks

|||

This often happens when building complex products such as SQL2005. Do not take it the wrong way.

When is the planned release of the fix?

Do Named Pipes work across a WAN?

Hi,
Do Named Pipes work across a WAN?
Are they carried on TCP/IP? What ports?
Thanks,
SA Dev
Hi
Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
packets. Named pipes is not efficient on slow connections.
Your best option is to use TCP/IP sockets.
Once you have firewalls in place, TCP/IP sockets is your only option.
http://support.microsoft.com/default...b;en-us;287932
http://support.microsoft.com/kb/269882/EN-US/
http://msdn.microsoft.com/library/de...setup_77g3.asp
Regards
Mike
"SA Development" wrote:

> Hi,
> Do Named Pipes work across a WAN?
> Are they carried on TCP/IP? What ports?
> Thanks,
> SA Dev
>
>
|||Hi Mike,

> Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
> packets. Named pipes is not efficient on slow connections.
> Your best option is to use TCP/IP sockets.
> Once you have firewalls in place, TCP/IP sockets is your only option.
Can you use windows authentication with TCP/IP or does it support SQL auth
only?
Does Named Pipes support both windows and sql auth?
Thanks,
SA Dev

Do Named Pipes work across a WAN?

Hi,
Do Named Pipes work across a WAN?
Are they carried on TCP/IP? What ports?
Thanks,
SA DevHi
Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
packets. Named pipes is not efficient on slow connections.
Your best option is to use TCP/IP sockets.
Once you have firewalls in place, TCP/IP sockets is your only option.
http://support.microsoft.com/default.aspx?scid=kb;en-us;287932
http://support.microsoft.com/kb/269882/EN-US
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_runsetup_77g3.asp
Regards
Mike
"SA Development" wrote:
> Hi,
> Do Named Pipes work across a WAN?
> Are they carried on TCP/IP? What ports?
> Thanks,
> SA Dev
>
>|||Hi Mike,
> Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
> packets. Named pipes is not efficient on slow connections.
> Your best option is to use TCP/IP sockets.
> Once you have firewalls in place, TCP/IP sockets is your only option.
Can you use windows authentication with TCP/IP or does it support SQL auth
only?
Does Named Pipes support both windows and sql auth?
Thanks,
SA Dev

Do Named Pipes work across a WAN?

Hi,
Do Named Pipes work across a WAN?
Are they carried on TCP/IP? What ports?
Thanks,
SA DevHi
Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
packets. Named pipes is not efficient on slow connections.
Your best option is to use TCP/IP sockets.
Once you have firewalls in place, TCP/IP sockets is your only option.
http://support.microsoft.com/defaul...kb;en-us;287932
http://support.microsoft.com/kb/269882/EN-US/
http://msdn.microsoft.com/library/d.../>
up_77g3.asp
Regards
Mike
"SA Development" wrote:

> Hi,
> Do Named Pipes work across a WAN?
> Are they carried on TCP/IP? What ports?
> Thanks,
> SA Dev
>
>|||Hi Mike,

> Windows 2000 and 2003 encapsulate the data in networking layer TCP/IP
> packets. Named pipes is not efficient on slow connections.
> Your best option is to use TCP/IP sockets.
> Once you have firewalls in place, TCP/IP sockets is your only option.
Can you use windows authentication with TCP/IP or does it support SQL auth
only?
Does Named Pipes support both windows and sql auth?
Thanks,
SA Devsql

Do mirrored servers have to be in same domain?

hi,
Can someone tell me if servers participating in SQL 2005 database mirroring
have to be in the same domain or can they just be stand alone servers? I
can't seem to find any documentation about this.
Thanks.No.
In case if the domain account for any of the server is different for example
the domain account for mirror server is different than the domain account of
the principal or the witness server then you can use the following statement
to grant permissions to the endpoint to that domain account: (You will need
to run the below statement on all the three participating servers)
Use Master; Create Login [Domain\Domain_user_account] From Windows; Gran
t
Connect on EndPoint ::Mirroring_Endpoint to [Domain\Domain_user_account]
;
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Steve" wrote:

> hi,
> Can someone tell me if servers participating in SQL 2005 database mirrorin
g
> have to be in the same domain or can they just be stand alone servers? I
> can't seem to find any documentation about this.
> Thanks.
>

Do mirrored servers have to be in same domain?

hi,
Can someone tell me if servers participating in SQL 2005 database mirroring
have to be in the same domain or can they just be stand alone servers? I
can't seem to find any documentation about this.
Thanks.No.
In case if the domain account for any of the server is different for example
the domain account for mirror server is different than the domain account of
the principal or the witness server then you can use the following statement
to grant permissions to the endpoint to that domain account: (You will need
to run the below statement on all the three participating servers)
Use Master; Create Login [Domain\Domain_user_account] From Windows; Grant
Connect on EndPoint ::Mirroring_Endpoint to [Domain\Domain_user_account];
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Steve" wrote:
> hi,
> Can someone tell me if servers participating in SQL 2005 database mirroring
> have to be in the same domain or can they just be stand alone servers? I
> can't seem to find any documentation about this.
> Thanks.
>

Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)