about a WHERE

Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.If you want daily totals per product for the given data, try:
select
convert (datetime, convert (char (8), SaleDate, 112), 112) as SaleDate
, Product
, sum (Price) as Total
from
Sales
group by
convert (datetime, convert (char (8), SaleDate, 112), 112)
, Product
order by
SaleDate
, Product
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.|||This should answer your question..
given that the SaleDate is a datetime (you have seconds in your second
snippet)
DECLARE @.Date datetime
SET @.Date = '20050218' -- no seconds in here
... WHERE SaleDate>=@.Date AND SaleDate<@.Date+1
-- note the >= on LHS and the < on RHS to prevent overlaps
If possible, dont cast the column in your table to compare it as that
precludes the optimiser from using an index s (I hate it when the
optimiser does implicit casts on the column rather than the variable).
Mr Tea
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
> Hi, I think this is an easy stuff but not for me.. I have an application
> running on a production server.
> In my DB I have a Sales Table like this
> SaleDate Product Price$
> 2005-02-18 00:00:00.000 1 500
> 2005-02-18 00:00:00.000 1 100
> 2005-02-18 00:00:00.000 3 200
> Using SP I'm getting a total by Date by product (e.g For SaleDate =
> 2005-02-18 Product 1 = 600, Product 3 = 200)
> Now also I need to Store the times like this
> SaleDate Product Price$
> 2005-02-18 09:37:39.000 1 500
> 2005-02-18 09:30:09.000 1 100
> 2005-02-18 14:20:10.000 3 200
> How should I modify my SP to get the same..I tried this:
> Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
> CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
> but it does nothing, the SP return a total for each row
> thks.
>
>

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlog
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0 |64|0|0|0|0000141b:00000a52:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
Thanks
I would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:

> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server errorlog
> or job log. Using lumigent I was able to extract following information about
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0 |64|0|0|0|0000141b:00000a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks
sql

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlo
g
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a5
2:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
ThanksI would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:

> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server error
log
> or job log. Using lumigent I was able to extract following information abo
ut
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000
a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlog
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a52:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
ThanksI would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:
> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server errorlog
> or job log. Using lumigent I was able to extract following information about
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks

Abnormal Timeout Issue on Production System

Hi all,
My production system is experiencing abnormal timeouts during posting of
transaction. The abnormal part is:
System usually works fine for about 6 ws; but one unlucky day, suddenly
for no apparent reason, system experiences massive timeout for about 2 - 3
hours; after that everything is fine again.
A background on the production system:
? It is a sales and stock control system. With a fair number (nothing
massive) of sales transactions every operation days
? Approx 50 client users
? Developed using VB .Net, VS 2003
? Running on .Net Framework 2.0
? Running on SQL Server database
? Supports multi-user, single database
It is also running on a pretty high-end server (from what I was told) with
spec:
? HP ML 530 G2 machine
? 2-processors, each 3 GHz, running Xeon HT
? 4 GB ECC RAM
? HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
? SQL Server 2003 standard edition with sp3
? Windows 2003 Server standard edition
? Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and Verita
s
9.1
USUALLY DURING THE FINE DAYS…
System works fine during both peak and off-peak hours with very rare
timeouts. Those timeouts are caused mainly by updating of "constant" tables,
such as stock when two customers are buying the same item at the same time.
That is acceptable as data integrity on those kinds of tables must be
maintained.
Database maintenance plan is in-placed to reorganized data and index pages,
scheduled to run once a w, during non-operation hours.
HDD defragmentation is scheduled to run twice a w, also during
non-operation hours.
The codes for the frequently used sales modules have been optimized to
ensure that transaction control is well handled, and unnecessary queries
removed.
HOWEVER WHEN THE UNLUCKY DAY OCCURRED…
Timeout occurs when user posts a sales transaction during the peak hours.
But the volume of transaction is similar to other peak hours during other
fine days!
I tried restarting the server and the client machines and applications but
the posting still timeout.
I even tried having only one user posting a transaction but that also timeou
t!
I checked the Profiler and it reveals that SQL Server is “stuck” while
attempting to insert some rows into a few tables at the start of the
transaction. These tables have small number of rows, from 10K to 200K each.
I
repeated the same test for only one user, and SQL Server remains “stuck”
as
well. BUT during fine days, SQL Server never has problem with those tables.
It can even insert data quickly into big tables with 2M+ rows!
I also checked the SQL EM Current Activities, and can find no tables being
blocked when only one user is posting. Tables are only blocked when more tha
n
one user is posting, and that is understandable coz SQL Server is “stuck
on
the 1st user’s transaction, thus blocking everyone else.
The SQL Server just become spastic, obsessed with the 1st (or only) user
transaction, never wanting to finish it off that I know it can.
And the weird thing is after 2-3 hours, everything goes back to normal, with
SQL Server at its best again.
PUZZLED…
The server specs is top-end; The program should be well optimized as far as
the frequently used sales modules are concerned; Maintenance Plans and
defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
backup are only done during non-operation hours; It works fine for about
six-w, then it gets “stuck” on a few small tables, only to “unstuck
” after
2-3 hours break; The weirdest of all, it even timeouts when only one user
(having all the server and database resources to him /herself) is posting a
sales transaction!
Can anyone please kindly advice what are the possible causes and resolutions
for this anomalies be? Can the hardware spec affect it, even though it is
supposed to be high-end? Could other applications such as Veritas co-exist
with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
Cleanup, that can slow down its performance?
TQ in advance.Hi
The fact that it is stuck sounds like blocking issues.
If the application does not finish it's transaction on a specific set of
row(s) in a table, and another one comes in to work on the same row(s), the
2nd one has to wait.
When this happens, run sp_who2 and see what is blocking what.
Look up "blocks, avoiding" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
> Hi all,
> My production system is experiencing abnormal timeouts during posting of
> transaction. The abnormal part is:
> System usually works fine for about 6 ws; but one unlucky day, suddenly
> for no apparent reason, system experiences massive timeout for about 2 - 3
> hours; after that everything is fine again.
>
> A background on the production system:
> . It is a sales and stock control system. With a fair number (nothing
> massive) of sales transactions every operation days
> . Approx 50 client users
> . Developed using VB .Net, VS 2003
> . Running on .Net Framework 2.0
> . Running on SQL Server database
> . Supports multi-user, single database
> It is also running on a pretty high-end server (from what I was told) with
> spec:
> . HP ML 530 G2 machine
> . 2-processors, each 3 GHz, running Xeon HT
> . 4 GB ECC RAM
> . HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
> . SQL Server 2003 standard edition with sp3
> . Windows 2003 Server standard edition
> . Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and
> Veritas
> 9.1
>
> USUALLY DURING THE FINE DAYS.
> System works fine during both peak and off-peak hours with very rare
> timeouts. Those timeouts are caused mainly by updating of "constant"
> tables,
> such as stock when two customers are buying the same item at the same
> time.
> That is acceptable as data integrity on those kinds of tables must be
> maintained.
> Database maintenance plan is in-placed to reorganized data and index
> pages,
> scheduled to run once a w, during non-operation hours.
> HDD defragmentation is scheduled to run twice a w, also during
> non-operation hours.
> The codes for the frequently used sales modules have been optimized to
> ensure that transaction control is well handled, and unnecessary queries
> removed.
>
> HOWEVER WHEN THE UNLUCKY DAY OCCURRED.
> Timeout occurs when user posts a sales transaction during the peak hours.
> But the volume of transaction is similar to other peak hours during other
> fine days!
> I tried restarting the server and the client machines and applications but
> the posting still timeout.
> I even tried having only one user posting a transaction but that also
> timeout!
> I checked the Profiler and it reveals that SQL Server is "stuck" while
> attempting to insert some rows into a few tables at the start of the
> transaction. These tables have small number of rows, from 10K to 200K
> each. I
> repeated the same test for only one user, and SQL Server remains "stuck"
> as
> well. BUT during fine days, SQL Server never has problem with those
> tables.
> It can even insert data quickly into big tables with 2M+ rows!
> I also checked the SQL EM Current Activities, and can find no tables being
> blocked when only one user is posting. Tables are only blocked when more
> than
> one user is posting, and that is understandable coz SQL Server is "stuck"
> on
> the 1st user's transaction, thus blocking everyone else.
> The SQL Server just become spastic, obsessed with the 1st (or only) user
> transaction, never wanting to finish it off that I know it can.
> And the weird thing is after 2-3 hours, everything goes back to normal,
> with
> SQL Server at its best again.
>
> PUZZLED.
> The server specs is top-end; The program should be well optimized as far
> as
> the frequently used sales modules are concerned; Maintenance Plans and
> defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
> backup are only done during non-operation hours; It works fine for about
> six-w, then it gets "stuck" on a few small tables, only to "unstuck"
> after
> 2-3 hours break; The weirdest of all, it even timeouts when only one user
> (having all the server and database resources to him /herself) is posting
> a
> sales transaction!
> Can anyone please kindly advice what are the possible causes and
> resolutions
> for this anomalies be? Can the hardware spec affect it, even though it is
> supposed to be high-end? Could other applications such as Veritas co-exist
> with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
> Cleanup, that can slow down its performance?
> TQ in advance.|||Hi,
TQ for reply.
I don't think it is a blocking issue coz EM Current Activities shows no
blocks, no locks and whatsoever, and the codes have been optimized to handle
transaction properly. And as mentioned, weird things like why everything
works fine for about six ws, but fails for 3 hours; and even
one-and-only-one user gets timeout during posting. The system just degraded
for no apparent reason.
TQ for the sp_who2 thou. Will try it out (fingers crossed) in abt six ws
time! ;)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> The fact that it is stuck sounds like blocking issues.
> If the application does not finish it's transaction on a specific set of
> row(s) in a table, and another one comes in to work on the same row(s), th
e
> 2nd one has to wait.
> When this happens, run sp_who2 and see what is blocking what.
> Look up "blocks, avoiding" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
>
>

AARGGHH Fulltext indexing woes...

We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
------
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
------
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK = dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK = dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK = dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied = (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ------
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ------
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK => dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK => dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK => dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied => (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
>
>
>|||Hmmm...we found changing the freetext(tblStock.* to be owner qualified
fixed the problem....
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23XNPPrXlGHA.3776@.TK2MSFTNGP03.phx.gbl...
> I've discovered that the joins are what cause this. As soon as I add any
> join to the simple query below that is working, it then fails.
> HELP!!! Why is this?
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>> We're feverishly in QA mode here getting ready to rollout production to
>> SQL2K5...
>> There is this one query which is returning the following error:
>> Msg 7617, Level 16, State 1, Line 1
>> Query does not reference the full-text indexed table or indexed view.
>> I'm really at a lost here. What is wrong with this first query'
>> I don't actually do select tblStock.* in the production version but it
>> succeeds/fails consistently either way so to shorten it up....
>> Here is the DDL used to create the catalog...
>> ------
>> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
>> AUTHORIZATION dbo;
>> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
>> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
>> ALTER FULLTEXT INDEX ON tblStock ENABLE;
>> ------
>> Here is the query that does NOT work (followed by one that does...)
>>
>> SELECT DISTINCT tblStock.*
>> FROM dbo.tblStock LEFT OUTER JOIN
>> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =>> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
>> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =>> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
>> dbo.tblCategories ON
>> dbo.tblCategorizedProducts.lngCategoryFK =>> dbo.tblCategories.lngCategoryPK LEFT OUTER JOIN
>> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =>> dbo.tblToolTypes.lngToolTypePK
>> WHERE (dbo.tblPriceFile.dtmDateApplied =>> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
>> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
>> (dbo.tblStock.blnActiveItem = 1)
>> and freetext(tblStock.*,'marriage')
>> ----
>> (the next one works)
>> select distinct * from tblStock where freetext(tblStock.*,'marriage')
>> ----
>>
>>
>sql

A typical publishing configuration

Hi,
Can anyone tell me the best way to configure sql server to allow
publication of data from a pre to a production environment.
I'm thinking along the lines of a typical content mgmt app where users
can work in pre and publish to prod when ready, how would this normally
be configured?
Thanks
Kieran
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Probably by creating a publication that filters the articles by a column
which has a status value in it indicating whether the content is ready for
publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kieran" <anonymous@.devdex.com> wrote in message
news:OP3KYH6qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anyone tell me the best way to configure sql server to allow
> publication of data from a pre to a production environment.
> I'm thinking along the lines of a typical content mgmt app where users
> can work in pre and publish to prod when ready, how would this normally
> be configured?
> Thanks
> Kieran
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks Hilary, is a replication something that can be set up on the sql
server?
Thanks
Kieran
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||it sure is, go to tools, replication and go from there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kieran" <anonymous@.devdex.com> wrote in message
news:%23kQdFl6qEHA.2724@.TK2MSFTNGP14.phx.gbl...
> Thanks Hilary, is a replication something that can be set up on the sql
> server?
> Thanks
> Kieran
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

a server hardware/environment question..

my network admin gives me two servers to set up for sql2k.
one will be for dev/testing qa, and one will be for production (mostly
reporting , not business operation type of stuff)
one server is good (raid 5), and the other is very good (raid 10).
the type of development will be in VB and data warehousing areas with
about 5 developers. the audience for the production reports can range
from a few people to a couple of hundreds of employees.
what would make the most sense in terms of server allocation?
should i give the best server to dev/testing? or to production (mainly
for reports)?
thank you.I tend to always give production the edge when building a dev server
(if I ca't have identical boxes); it will be more likely that
performance issues will show up on the weakest server, and I would
prefer to deal with them during development rather than production.
What would make this question interesting would be the processor and
memory specs as opposed to just the hard drive allocations...
HTH,
Stu|||On the development server when designing queries and report design
templates, you will probably want to work with more manageable subsets of
data, so the lessor equipped server should suffice. My development server is
typically just my PC with a local intall of SQL Server Developer edition.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1121891806.861200.159450@.g49g2000cwa.googlegroups.com...
> my network admin gives me two servers to set up for sql2k.
> one will be for dev/testing qa, and one will be for production (mostly
> reporting , not business operation type of stuff)
> one server is good (raid 5), and the other is very good (raid 10).
> the type of development will be in VB and data warehousing areas with
> about 5 developers. the audience for the production reports can range
> from a few people to a couple of hundreds of employees.
> what would make the most sense in terms of server allocation?
> should i give the best server to dev/testing? or to production (mainly
> for reports)?
> thank you.
>|||> What would make this question interesting would be the processor and
> memory specs as opposed to just the hard drive allocations...
Yes, definitely if you can, have more than one processor in dev if your
production machines have > 1.
(Often problems due to parallel processing / plans can be missed because the
first time the queries have a chance to utilize that hardware is in
production.)
Also, try to have the same amount of memory, e.g. if production uses AWE
then you should have the same settings in dev (for the same reasons as
above).

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.sqlmonster.com
Hi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default...b;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>
|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:

> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>
|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.c om...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Applicati
on DBAs restore databases from Production SQL server a lot of time. So I'd l
ike to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/defaul...kb;en-us;314546
Yih-Yoon Lee
Do Park via droptable.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via droptable.com" wrote:

> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQ
droptable.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

A Question on SQL Cluster -Urgent

We are planning the following setup as part of our Business Continuity
Planning.
We have an active passive cluster in the production and we will also have
the same setup in the DR setup. But the issue is that we need to keep the
same SQL virtual name in both Production and DR. So in this scenario if we
have to do some maintenance on the DR setup for eg: to apply Service Packs
or any fine tuning, how do we do that? Is there any recommendation or best
practices?
Waiting on an immediate response.
Abhi
Consider using a DNS alias. The apps use the alias, while the virtual
servers can be whatever you want.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Abhi" <Abhi@.discussions.microsoft.com> wrote in message
news:F4A782D4-8BD3-4B46-ABFA-B6BECE82908D@.microsoft.com...
We are planning the following setup as part of our Business Continuity
Planning.
We have an active passive cluster in the production and we will also have
the same setup in the DR setup. But the issue is that we need to keep the
same SQL virtual name in both Production and DR. So in this scenario if we
have to do some maintenance on the DR setup for eg: to apply Service Packs
or any fine tuning, how do we do that? Is there any recommendation or best
practices?
Waiting on an immediate response.
Abhi
|||"Abhi" <Abhi@.discussions.microsoft.com> wrote in message
news:F4A782D4-8BD3-4B46-ABFA-B6BECE82908D@.microsoft.com...
> We are planning the following setup as part of our Business Continuity
> Planning.
> We have an active passive cluster in the production and we will also have
> the same setup in the DR setup. But the issue is that we need to keep the
> same SQL virtual name in both Production and DR. So in this scenario if we
> have to do some maintenance on the DR setup for eg: to apply Service
> Packs
> or any fine tuning, how do we do that? Is there any recommendation or best
> practices?
You might also consider using database mirroring.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
July 10-13 in Denver
July 16-19 in New York
Sep 3 - 6 in Copenhagen

A query to determine a role or ids access to an object... Do-able?

Hey there.

I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.

Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!

Hi Bill,

I'm a little confused after reading your question. Could you please elaberate your question a little more?

the ID used in bothAcceptance andProduction will have the necessary access.

What do you mean by "Acceptance" and "Production" ? Are they tables or something? Also, what do you mean by "the ID used", based on my understanding, we don't need an "id" when executing a stored procedure. Thanks for your understanding

|||

Thanks for responding.

Bottom line is that I need a query that provides me the permission (roles/IDs) that have access to an object (table/views/stored procedures).

To be more specific to your questions...

You can log into SQL SERVER with an ID or with TRUSTED AUTHENTICATION. In my work environment, our support staff generally log into SQL SERVER using TRUSTED AUTHENTICATION... However, we use a number of ID's during the automated phase of interacting with SQL SERVER.

In this, we have many automated processes that run throughout the day and generally speaking, these are executables which modify our data based on a pre-defined set of business rules and they log into the database using a controlled set of IDs. So, at a regularly appointed time an exe kicks off and uses an embedded ID/PW (not really embedded) to open a connection with a database and then performs the necessary work. This is standard business practice.

Development, Acceptance (test), Production (release) are standard business environments that are virtually identical to each other that help creators of a system build, test and then finally release their system for use by the business.

Virtually the same...

Picture yourself as a deity in charge of a universe occupied by 'real' souls (production). Not really 'in change' since there's this thing called 'free will' but pretty down close... You can make whatever change you want but you really want to make sure the change will work and since you're a 'good' god, you want the changes to have a positive impact. You know that making the slightest change to your universe could have magnificent changes, good or bad. You decide that changes here should be made with care and you therefore limit who can do what here (ie, only you have access to make changes... your angels are out of luck).

So you decide to simulate your production universe by creating a sort of playground universe, a sandbox of sorts where you can build new things, test new ideas... You call this new place your development universe. No souls here... just you and the angels throwing out ideas and building new things and of course testing what is built based on how you 'think' it will be used. Your angels get to work building the change and once you believe you've got it right, you decide to mirror the changes to your production universe... but... you recall that sometimes what you've built breaks in your production enviroment (you wrote that holy bood well but for whatever reason the souls misinterpreted it and killed a bunch of people for oil - go figure)...

So you decide that your testing isn't enough and again mirror your production environment into a testing (acceptance) environment and recruit a bunch of souls (business folk) to test the changes since they have a better sense of how things work in the 'real' world. In this universe, there's sort of a mix of how things are governed... You still have the ultimate authority regards what happens here and can change things as needed but, you resisit the temptation since, in the real universe, on-the-fly changes are strongly discouraged. So, you allow on-the-fly changes here ONLY to grease the wheel as needed and insist that any of the changes should made here should also be made in the development universe... In essence, you figure out how to get it right in acceptance then make the changes in development and finally repromote everything back to acceptance so that the final test and sign-off by the testing 'souls' is generally on a change that requires NO on-the-fly fixes.

So, the souls sign-off on the change and you finally push the change to your real universe (production). All is well right? Well, you hope so... but their still killing for oil... woman are still being treated as second class citizens... kids are still being mistreated... geez...

It is standard business practice to have a Development, Acceptance and production environment. Each has it's own database, it's own programs, it's own security model and as you move from development to production, the security is tighter and tighter so that, what might have worked in development, won't work in Acceptance and certanly not in production... What worked in Acceptance SHOULD have worked in Production but it didn't. Knowing permission of an object in all three environments 'up front' is very helpful to make sure there are fewer surprises once your change is in production.

Sure, you SHOULD already know what the rules are but frankly, people change (along with their view of how security should work) and you just aren't always in the best position to know what's up.

I've been burned before... What worked in Acceptance didn't work in Production and it should have... Why didn't it? Because security folk were lax in Acceptance: they made a change 1st in Production rather than making the change 1st in Acceptance. It broke my system and I of course took the political hit and I intend that never to happen again.