Antivirus install to db server: February 2012

Saturday, February 25, 2012

Question about "Connect to the sql server 2000 installed on windows xp sp2"

I have another question now.

As the topic said, my sql server 2000 installed on windows xp sp2, the computer's name is "ADMIN" and the server 2000's name is "ADMIN".

When i startup the sql server 2000, it just listening on shared memory and name pipes, not including the "TCP/IP" . So i can not connect to the sql server using the port 1433.

Some people said that the reason was the system's firewall. I also found some supports on the websites 'www.support.microsoft.com'. It said that you can set the port 1433 or the sqlservr.exe to the execption. I also do that, but still not works.

Is there any things i forget and how can i solve the problems! Thanks for advances.

Try disabling your firewall first then connect to SQL Server. I am using SQL Server 2000 and SQL Server 2005 (instance) on my Windows XP Pro SP2 and everything works fine. Also, check you Server Network Utility and Client Network Utility. Since your client is also your server (machine), the enabled protocols in your Server Network Utility should be the same as your Client Network Utility|||

Like bass_player stated, you should check the server's network settings and verify that TCP/IP has been enabled. Otherwise, please provide the error text that you're receiving and the connection string that you used.

Il-Sung.

|||

Thanks.

I am sorry. I will describe my sql server 2000's environment now.

OS: windows xp sp2, Firewall had been shutdown

Server Network Utility : just enable the TCP/IP and use the default port 1433

Client Network Utility : also just enable the TCP/IP default port 1433

I found in the sql server's log that it just listening on shared memory.

|||

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

|||

joeydj wrote:

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

As joeydj said, i had been try like that. But it still not works. Now the problem is not on the client side, but the server side. The Sql Server 2000 do not listen on the port 1433 or others is the major problem.

Anyway, Thanks!

|||

I find another error message now.

It is "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"

Why?

|||

hi,

what version of Sql server are you using
for best performance. I recommend sql server enterprise on
windows 2000 server or windows 2003 server.
I think you computer is currently in trouble
you may have to reformat it

regards,

joey

|||

Hi, joey.

The version of my sql server 2000 is just evolution edition. Is there any problem? Can you tell me the reason?

And should i reinstall my os now?

|||Recently, i enable the multiprotocol on both the client and server side(in fact the client and server is on the same computer), i can connect to the sql server 2000 local , but i can not connect to it from another computer, because the tcp/ip doesn't listen on the port 1433 all the same.|||

I get the answer now.

First, i should update the sql server 2000 to sp4.

Second, the firewall should set an exception to the port (default is 1433).

Third, we should guarantee the sequence of the protocol on the client side. That means if your sql server just listoning on shared memory or name pipe or both of them, then you should not use TCP/IP first, otherwise you may not connect to the server.

Question about "WITH (ROWLOCK)" hint.

(SQL Server 2000, SP3a)
Hello all!
I've got a trigger on a table that I want to cause a column in another table
to be
updated:
update t
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t with (rowlock)
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
Is the WITH (ROWLOCK) hint any different if I express it this way:
update t with (rowlock)
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
My guess is that it isn't (since the compiler will warn you if there are inc
ompatible lock
types between the "alias" and the table) -- so I just thought it'd be the sa
me. But, I'm
not really sure and was hoping for clarification.
Thanks for any help you can provide!
John PetersonJohn,
I don't believe so but your example is a bad one<g>. First off you don't
need to issue a NOLOCK on the Inserted table since the session doing the
updating is the only one that has access to it. I don't know if it even
does anything to tell you the truth. And the ROWLOCK should not be needed
either. Your joining on a PK and unless your updating most of the table it
should use rowlocks anyway. Hints should only be used where absolutely
necessary. You need to prove to yourself they are needed before using them
otherwise you risk getting poor performance overall.
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a trigger on a table that I want to cause a column in another
table to be
> updated:
> update t
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t with (rowlock)
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> Is the WITH (ROWLOCK) hint any different if I express it this way:
> update t with (rowlock)
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> My guess is that it isn't (since the compiler will warn you if there are
incompatible lock
> types between the "alias" and the table) -- so I just thought it'd be the
same. But, I'm
> not really sure and was hoping for clarification.
> Thanks for any help you can provide!
> John Peterson
>|||> from [dbo].[MyOtherTable] as t with (rowlock)
What do you think you're gaining here?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hello Andrew!
Thanks -- yeah, I figured my NOLOCK hint was likely superfluous. The table
that being
updated is a fairly high-use table, and explicitly providing a ROWLOCK hint
has been
helpful in the past. But, your words of caution are appreciated!
Thanks for your help!
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYCc4xb8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> John,
> I don't believe so but your example is a bad one<g>. First off you don't
> need to issue a NOLOCK on the Inserted table since the session doing the
> updating is the only one that has access to it. I don't know if it even
> does anything to tell you the truth. And the ROWLOCK should not be needed
> either. Your joining on a PK and unless your updating most of the table i
t
> should use rowlocks anyway. Hints should only be used where absolutely
> necessary. You need to prove to yourself they are needed before using the
m
> otherwise you risk getting poor performance overall.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> table to be
> incompatible lock
> same. But, I'm
>|||"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23wEOE0b8DHA.452@.TK2MSFTNGP11.phx.gbl...
> What do you think you're gaining here?
I had hoped to mitigate the possibility of conversion deadlocks with this ap
proach
(deadlocks that are caused due to row-->page lock escalation). I was just c
urious to
learn whether specifying the hint at one or the other locations might have a
ny difference.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

Question about "WITH (ROWLOCK)" hint.

(SQL Server 2000, SP3a)
Hello all!
I've got a trigger on a table that I want to cause a column in another table to be
updated:
update t
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t with (rowlock)
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
Is the WITH (ROWLOCK) hint any different if I express it this way:
update t with (rowlock)
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
My guess is that it isn't (since the compiler will warn you if there are incompatible lock
types between the "alias" and the table) -- so I just thought it'd be the same. But, I'm
not really sure and was hoping for clarification.
Thanks for any help you can provide!
John PetersonJohn,
I don't believe so but your example is a bad one<g>. First off you don't
need to issue a NOLOCK on the Inserted table since the session doing the
updating is the only one that has access to it. I don't know if it even
does anything to tell you the truth. And the ROWLOCK should not be needed
either. Your joining on a PK and unless your updating most of the table it
should use rowlocks anyway. Hints should only be used where absolutely
necessary. You need to prove to yourself they are needed before using them
otherwise you risk getting poor performance overall.
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a trigger on a table that I want to cause a column in another
table to be
> updated:
> update t
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t with (rowlock)
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> Is the WITH (ROWLOCK) hint any different if I express it this way:
> update t with (rowlock)
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> My guess is that it isn't (since the compiler will warn you if there are
incompatible lock
> types between the "alias" and the table) -- so I just thought it'd be the
same. But, I'm
> not really sure and was hoping for clarification.
> Thanks for any help you can provide!
> John Peterson
>|||> from [dbo].[MyOtherTable] as t with (rowlock)
What do you think you're gaining here?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hello Andrew!
Thanks -- yeah, I figured my NOLOCK hint was likely superfluous. The table that being
updated is a fairly high-use table, and explicitly providing a ROWLOCK hint has been
helpful in the past. But, your words of caution are appreciated!
Thanks for your help!
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYCc4xb8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> John,
> I don't believe so but your example is a bad one<g>. First off you don't
> need to issue a NOLOCK on the Inserted table since the session doing the
> updating is the only one that has access to it. I don't know if it even
> does anything to tell you the truth. And the ROWLOCK should not be needed
> either. Your joining on a PK and unless your updating most of the table it
> should use rowlocks anyway. Hints should only be used where absolutely
> necessary. You need to prove to yourself they are needed before using them
> otherwise you risk getting poor performance overall.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I've got a trigger on a table that I want to cause a column in another
> table to be
> > updated:
> >
> > update t
> > set t.[DateLastUpdated] = getdate()
> > from [dbo].[MyOtherTable] as t with (rowlock)
> > inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> >
> > Is the WITH (ROWLOCK) hint any different if I express it this way:
> >
> > update t with (rowlock)
> > set t.[DateLastUpdated] = getdate()
> > from [dbo].[MyOtherTable] as t
> > inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> >
> > My guess is that it isn't (since the compiler will warn you if there are
> incompatible lock
> > types between the "alias" and the table) -- so I just thought it'd be the
> same. But, I'm
> > not really sure and was hoping for clarification.
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23wEOE0b8DHA.452@.TK2MSFTNGP11.phx.gbl...
> > from [dbo].[MyOtherTable] as t with (rowlock)
> What do you think you're gaining here?
I had hoped to mitigate the possibility of conversion deadlocks with this approach
(deadlocks that are caused due to row-->page lock escalation). I was just curious to
learn whether specifying the hint at one or the other locations might have any difference.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

question about "System.Data.SqlClient.SqlException"

I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005...however, i have the following error during the compilation process

Code in webform2.aspx.vb:

Partial Class webform2
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near '='."
Number=102
Procedure=""
Server="DCPRJ007\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at webform2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite7\webform2.aspx.vb:line 10
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Are you sure you are getting value in the Request.QueryString("id") ? If not, your select statement will have an incorrect syntax and so is the exception. keep a break point and debug to find out.

Thanks

|||

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

|||

Are you sure, you have some value in Request.QueryString("id") ?

Thanks

|||

gaze:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?
error message is : Incorrect syntax near '='.
thx a lot!!

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)

try

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id='" & Request.QueryString("id") & "'", cnn)

means ... use single cote before and after the your id.

If you think this post helped you marked as read.

Question about "servername\instancename"

I'm working on the setup .ini file and wondering about
RSDATABASESERVER="servername\instancename"
If the server has only one instance is the instancename the same as the
servername?Normally you would just refer to it as the servername. But occasionally
in 2005 I've seen the default instance referred as MSSQLServer.
Check this page in BOL;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/60e0a0b2-8a47-4eda-a
5df-3e5e403dbdbc.htm
especially note that it says;
InstanceName
Specifies the name of the report server instance. Report server
instancing is based on SQL Server instancing. This value specifies a
SQL Server instance name. By default, this value is MSSQLSERVER. Do not
modify this setting.
There is a configuration tool to save you having to mod the ini files
direct called rsconfig, look it up in BOL.
--
Regards
Chris
Al wrote:
> I'm working on the setup .ini file and wondering about
> RSDATABASESERVER="servername\instancename"
> If the server has only one instance is the instancename the same as
> the servername?

Question about "Object permissions" & "Statement permissions"

Thanks, Liliya
"Uri Dimant" wrote:

> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>
>
|||Dear All.
Excuse me for a shot message. Additional:
- Book "Microsoft? SQL Server 2005 Implementation and Maintenance
Study Guide (Exam 70–431)" by Joseph L. Jorden Dandy Weyn
In this book I found the descriptions:
- "Statement permissions have nothing to do with the actual data; they allow
users to create the structure that holds the data."
- "Once the structure exists to hold the data, you need to give users
permission to start working with the data in the databases, which is
accomplished by granting object permissions to your users."
Is these terms correct for SQL 2005 in this context?
"Uri Dimant" wrote:

> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>
>
|||"RedFox" wrote:

> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoft? SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70–431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
not really correct without the context. for example this particular
statement in the context as is is not applicable at all to statement
permissions
select, delete, update, insert, because they are not ddl

> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
that is not correct in the context given also, because create database
statement permission for example can not deal with any data yet, they just do
not exist, if other 'create' statement permissions have anything to do with
the data is outside of the context of the quotation above as well, they are
for ddl after all.

> Is these terms correct for SQL 2005 in this context?
I would not say the statements are correct as given, however in its own
context of the book they may be.
If you want to find your what permissions the user has, then that is
sp_helprotect or permission function.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/81625a56-b160-4424-91c5-1ce8b259a8e6.htm
or
http://msdn2.microsoft.com/en-us/library/ms186915.aspx
can find your own permissions as well fn_my_permissions.
statement permission can be granted, revoked, denied and audited. Can a user
XYZ do action ABC on the object Q123 is defined by the chain of ownership
(what rights are granted, denied, revoked through what and on what).
Thanks, Liliya
[vbcol=seagreen]
> "Uri Dimant" wrote:

Question about "Object permissions" & "Statement permissions"

I'm trying to find the answer on question about "Object permissions" &
"Statement permissions".
I read some books and received the discrepant (contradictory) information.
Can you help me to find the truth?what is the question then?
sql server version would be useful as well. and the description of your
problem/case, for example.
--
Thanks, Liliya
"RedFox" wrote:
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||The truth about what exactly? Can you be more specific?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||RedFox
Object permissions means ( as I understand it) that you do not have any
access on. For instance you table called "Product" and cannot
INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
Statement permissions is a little bit difference as you may have SELECT
statement permission to read the data, however , you cannot modify any data
(UPDATE permission)
"RedFox" <redfox_net@.ukr.net> wrote in message
news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> I'm trying to find the answer on question about "Object permissions" &
> "Statement permissions".
> I read some books and received the discrepant (contradictory) information.
> Can you help me to find the truth?|||your question is...? ask one :)
--
Thanks, Liliya
"Uri Dimant" wrote:
> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > I'm trying to find the answer on question about "Object permissions" &
> > "Statement permissions".
> > I read some books and received the discrepant (contradictory) information.
> > Can you help me to find the truth?
>
>|||Dear All.
Excuse me for a shot message. Additional:
- Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
In this book I found the descriptions:
- "Statement permissions have nothing to do with the actual data; they allow
users to create the structure that holds the data."
- "Once the structure exists to hold the data, you need to give users
permission to start working with the data in the databases, which is
accomplished by granting object permissions to your users."
Is these terms correct for SQL 2005 in this context?
"Uri Dimant" wrote:
> RedFox
> Object permissions means ( as I understand it) that you do not have any
> access on. For instance you table called "Product" and cannot
> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> Statement permissions is a little bit difference as you may have SELECT
> statement permission to read the data, however , you cannot modify any data
> (UPDATE permission)
>
>
> "RedFox" <redfox_net@.ukr.net> wrote in message
> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > I'm trying to find the answer on question about "Object permissions" &
> > "Statement permissions".
> > I read some books and received the discrepant (contradictory) information.
> > Can you help me to find the truth?
>
>|||This is more about terminology than about technicalities. In earlier versions of SQL Server, use
used to table about "object permissions", for example:
GRANT SELECT ON Customers TO RedFox
We also talked about "statement permissions", such as:
GRANT CREATE TABLE TO RedFox
GRANT BACKUP DATABASE TO RedFox
With SQL Server 2005, MS tries to align terminology with what we have in Windows (and AD etc). So,
we talk about principals (user, login, role) and securables (table, procedure, view etc). A
securable lives in some specific scope (a table lives in a schema, an assembly lives in a database,
an endpoiint lives in a server). So, we have now for instance "Database level permissions", such as:
GRANT CREATE TABLE TO RedFox
Above doesn't apply to some securable, and is what we used to call "statement permission".
We also have, for instance "Database Level Securable Permissions", such as
GRANT EXECUTE ON ASEMBLY::myAssembly TO RedFox
We also have for instance "Schema Level Securable Permissions", such as:
GRANT SELECT ON Customers TO RedFox
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RedFox" <redfox_net@.ukr.net> wrote in message
news:1E0081B4-B193-48E0-8F13-57E866F9A736@.microsoft.com...
> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
> Is these terms correct for SQL 2005 in this context?
> "Uri Dimant" wrote:
>> RedFox
>> Object permissions means ( as I understand it) that you do not have any
>> access on. For instance you table called "Product" and cannot
>> INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
>> Statement permissions is a little bit difference as you may have SELECT
>> statement permission to read the data, however , you cannot modify any data
>> (UPDATE permission)
>>
>>
>> "RedFox" <redfox_net@.ukr.net> wrote in message
>> news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
>> > I'm trying to find the answer on question about "Object permissions" &
>> > "Statement permissions".
>> > I read some books and received the discrepant (contradictory) information.
>> > Can you help me to find the truth?
>>|||"RedFox" wrote:
> Dear All.
> Excuse me for a shot message. Additional:
> - Book "Microsoftâ?¢ SQL Server 2005 Implementation and Maintenance
> Study Guide (Exam 70â'431)" by Joseph L. Jorden Dandy Weyn
> In this book I found the descriptions:
> - "Statement permissions have nothing to do with the actual data; they allow
> users to create the structure that holds the data."
not really correct without the context. for example this particular
statement in the context as is is not applicable at all to statement
permissions
select, delete, update, insert, because they are not ddl
> - "Once the structure exists to hold the data, you need to give users
> permission to start working with the data in the databases, which is
> accomplished by granting object permissions to your users."
that is not correct in the context given also, because create database
statement permission for example can not deal with any data yet, they just do
not exist, if other 'create' statement permissions have anything to do with
the data is outside of the context of the quotation above as well, they are
for ddl after all.
> Is these terms correct for SQL 2005 in this context?
I would not say the statements are correct as given, however in its own
context of the book they may be.
If you want to find your what permissions the user has, then that is
sp_helprotect or permission function
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/81625a56-b160-4424-91c5-1ce8b259a8e6.htm
or
http://msdn2.microsoft.com/en-us/library/ms186915.aspx
can find your own permissions as well fn_my_permissions.
statement permission can be granted, revoked, denied and audited. Can a user
XYZ do action ABC on the object Q123 is defined by the chain of ownership
(what rights are granted, denied, revoked through what and on what).
--
Thanks, Liliya
> "Uri Dimant" wrote:
> > RedFox
> > Object permissions means ( as I understand it) that you do not have any
> > access on. For instance you table called "Product" and cannot
> > INSERT/DELETE/UPDATE/SELECT/EXECUTE ,nothing
> > Statement permissions is a little bit difference as you may have SELECT
> > statement permission to read the data, however , you cannot modify any data
> > (UPDATE permission)
> >
> >
> >
> >
> > "RedFox" <redfox_net@.ukr.net> wrote in message
> > news:BA9F61A6-95AF-4267-AA2F-37EF3669F4A2@.microsoft.com...
> > > I'm trying to find the answer on question about "Object permissions" &
> > > "Statement permissions".
> > > I read some books and received the discrepant (contradictory) information.
> > > Can you help me to find the truth?
> >
> >
> >

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.
The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>
|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards[vbcol=seagreen]
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for
> the
>
|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, then
> you are attempting to insert rows with a CourseID that does not exist in the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>
|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]
|||"Rick Sawtell" wrote:
..> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.
|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help you
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others working
> fine except these two. Anyway thanks for help.
>
|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
..
|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties
"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for[vbcol=seagreen]
> the
>|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, the
n
> you are attempting to insert rows with a CourseID that does not exist in t
he
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwar
ds
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
>
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]|||"Rick Sawtell" wrote:
.> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help yo
u
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others worki
ng
> fine except these two. Anyway thanks for help.
>|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
.|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

Question about "Distribution clean up: distribution" Job

Can the manner in which you remove a publication effect the ability of this
job to run?
I had been experiencing errors with this job. The error message was as
follows:
"Executed as user: <SQLServerAgentDomainAccount>. Could not remove directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed."
I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same error, I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.
Then I went into the job step and copied the command it was attempting to
run. This is the command:
"EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72"
I pasted this command into a query window and attempted to run it under my
own login, which is a member of the sysadmin role and is also a member of the
Local Administrators group. It still could not run.
The folders the command was trying to delete were used for Publications that
no longer existed. So I went ahead and deleted the folders manually. Now
the command ran successfully.
I am thinking something went wrong during the process of removing the
publications and subscriptions. Anyone have any idea what might have
happened?
Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing this
directory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
> Can the manner in which you remove a publication effect the ability of
> this
> job to run?
> I had been experiencing errors with this job. The error message was as
> follows:
> "Executed as user: <SQLServerAgentDomainAccount>. Could not remove
> directory
> 'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory. [SQLSTATE 42000] (Error 20015). The step
> failed."
> I run my SQL Server Agent process with an account that does not have
> sysadmin rights. After reading a few other posts with this same error, I
> experimented with running the job as a user with sysadmin rights.
> Unfortunately, I still had the same error.
> Then I went into the job step and copied the command it was attempting to
> run. This is the command:
> "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72"
> I pasted this command into a query window and attempted to run it under my
> own login, which is a member of the sysadmin role and is also a member of
> the
> Local Administrators group. It still could not run.
> The folders the command was trying to delete were used for Publications
> that
> no longer existed. So I went ahead and deleted the folders manually. Now
> the command ran successfully.
> I am thinking something went wrong during the process of removing the
> publications and subscriptions. Anyone have any idea what might have
> happened?
|||It is SQL Server 2005 and xp_cmdshell was already enabled.
"Hilary Cotter" wrote:

> Is this SQL 2005? If so you need to enable xp_cmdshell.
> If it is SQL 2000 you need to ensure that no one else is accessing this
> directory.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
>
>
|||Hi Ken,
I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the SQLServerAgent
service account Full Control rights on it.
-Raymond
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...[vbcol=seagreen]
> It is SQL Server 2005 and xp_cmdshell was already enabled.
> "Hilary Cotter" wrote:
|||You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still receiving
the error even after I granted the service account "Full Control" over the
share.
"Raymond Mak [MSFT]" wrote:

> Hi Ken,
> I am guessing that the SQLServerAgent service does not have sufficient
> rights to remove folders under P:\ReplData. As such, you may want to try
> right-clicking the folder in Windows Explorer and grant the SQLServerAgent
> service account Full Control rights on it.
> -Raymond
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...
>
>
|||I am guessing (again) that the SQL Server Agent service account is not what
xp_cmdshell running under in the cleanup scenario, another suspect will be
the SQL Server service account. In any case, you should be able to find out
exactly which security account is doing the delete using the filemon.exe
tool from sysinternals.com.
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...[vbcol=seagreen]
> You're right. Originally, the Agent service account had not been granted
> access to the Snapshot folder (P:\ReplData). However, I was still
> receiving
> the error even after I granted the service account "Full Control" over the
> share.
> "Raymond Mak [MSFT]" wrote:
|||You were right. I granted the SQL Server Service Account "Full Control" over
the Snapshot Folder and the job has been running successfully ever since.
Thanks for your help.
"Raymond Mak [MSFT]" wrote:

> I am guessing (again) that the SQL Server Agent service account is not what
> xp_cmdshell running under in the cleanup scenario, another suspect will be
> the SQL Server service account. In any case, you should be able to find out
> exactly which security account is doing the delete using the filemon.exe
> tool from sysinternals.com.
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...
>
>

Question about "Data Transformation Services " : How to define myselfs query without DTS W

hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS

the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard.

but my requirement seems can't be done by DTS wizard.

here are my requirement below.

[move online Database to offline Database ]

1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..)

2. we will have only 2 kind of preserve time one is 6 months another is 12 months

3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data

4. We will have to reference the history data preserve time to delete data after finished data movement

those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?

thank you very much and have a nice day

anyone ? please help...

|||

Hi,

Is there any field (such like datetime) in your datatable which can identify the time of the records. If so, you can use a query to specify the data to transfer. When you run your wizard, choose "use a query to transfer" item in "Specify table copy or query" step. Click on QueryBuilder button to build your filter sql statement and create your query criteria.(datetime filed) In this way, you can transform the data by datetime condition.

Hope that helps. Thanks.

|||

hi thank you is the only way ( run wizard) to finish this job?

could it be done by write a program? thank you

|||

Hi,

Of cause you can write programs to achieve your goal. Just filter the data according to the datetime condition and insert the data into your local(or backup) data server.But actually it's much complicated I think. Since we have a such a tool to ease our work, why don't we enjoy it Big Smile

Thanks.

question abot primary key

how can I set my primary key to star again from 1?? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.

is that possible??

thanks!I think you are talking about an IDENTITY column, which is not the same as a
Primary Key (although IDENTITY is used as a surrogate key).

DBCC CHECKIDENT ('TableName', RESEED,0)

--
David Portas
SQL Server MVP
--|||>> how can I set my primary key to start again from 1? <<

Please read a book on RDBMS; you have absolutely no idea what you are
doing.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

A primary key **BY DEFINITION** is a subset of attributes (columns).
Keys do not "start" anywhere. Each entity in the data model has a
unique primary key by its nature; it is not assigned by the PHYSICAL
storage used to hold the PHYSICAL representation of the DBMS.

If you had a 1950's magnetic tape file, then your question would make
sense. They start at record #1 each time you hang a new magnetic tape
on the drive, which is obviously your mental model. This is the
difference between a VIN number and a parking space number.

question ?

" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs
|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"

question ?

" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
ThanxsHi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs

Question : Transactional Replication Rollback ?

We have a sql2k db as a Distributor, with a few servers geographically
scattered acting as subscribers (backup). I'm setting up transaction
replication, however, is there a way to rollback in the event of
dataloss on the Distributor ?
Eg : 'delete tblProducts' run on the distributor. Wouldn't this
effectively delete tblProducts on every subscriber ? Is there anyway
to rollback in such an event.
Thanks,
Amit Chandel
Amit,
presumably when you say Distributor, you mean Publisher/Distributor?
If you run the TSQL "Delete * from Table1" on the publisher this will be
propagated via the logreader and distribution agents to the subscriber.
Turning off these agents before the command gets to the subscriber will
prevent the delete. Once it has occurred at the subscriber, you can not roll
it back. One posibility is you could back up the transaction log and do a
point-in-time restore. If the command is potentially 'dodgy' then you could
also wrap it in a transaction with normal error trapping and ROLLBACK
statements. If you give the transaction a name, it is possible to do a
restore as mentioned above, but not to a point in time, rather to just
before the named transaction was run.
HTH,
Paul Ibison

Question : How SQL chooses an index for a process

I have just tested 3 queries using QA. The complete
test information :

--
CREATE TABLE agls1
(fyear char(4) NULL ,
fprefix char(3) NULL ,
fvcno char(20) NULL ,
fdate datetime NULL ,
fid char(15) NULL ,
fiddate datetime NULL ,
fdesc char(60) NULL ,
facc char(12) NULL ,
fval decimal(18, 2) NULL ,
fcrc char(5) NULL ,
fsub char(1) NULL ,
fmaster char(9) NULL ,
fcode char(15) NULL )
CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
fdate, fvcno)
CREATE INDEX b ON agls1 (fyear, facc, fdate,
fprefix, fvcno)
CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)

insert into agls1
( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
( '2004','A','B','123','inv','20040101','01','111' )

--query-1
select * from agls1
where fyear = '2004' and fprefix = 'inv' and
fdate = '20040101' and fvcno = '01'

--query-2
select * from agls1
where fyear = '2004' and facc = '111' and
fdate = '20040101' and fprefix = 'inv' and fvcno = '01'

--query-3
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
--

The execution plan shows that the index a
is always used for all 3 select queries above.

I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
c. How does SQL choose an index for a process ?

Could anyone help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?

There is only one row in the table, that makes the test somewhat
meaningless.

But the general is that there is always a tradeoff whether to use a
non-clustered index or not. When SQL Server finds row through a non-
clustered index, it has to go to the data page and get data requested
in the query which is not present in the index. This means that it
can be more expensive to use the index than to scan table, if the
optimizer estimates that the index will find many rows.

> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?

No, that depends on how the data looks like. Let's first take query/index
c. Say that there is over a million rows with year = 2004. In this case,
without the index, query c would have to scan all those rows in the
clustered index, whereas with the non-clustered index can find the
matching rows faster. But if there are say, 10000 rows that matches
query c, I would execpt SQL Server to use the clustered index.

As for index b, there are situations where this index could help, but
in this case, there must be many duplicates in the clustered index,
so that you actually make the query significantly more precise by adding
that extra column.

> c. How does SQL choose an index for a process ?

SQL Server uses a cost-based optimizer which makes its decisions from
statistics about the table column. Therefore the same query can get
different query plans with different data.

There is material in Books Online you can study. I can also recommend
Kalen Delaney's "Inside SQL Server 2000", which covers this topic
in detail.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks a lot for the lucid explanation.
Very helpful.

I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Anita wrote:

> I have just tested 3 queries using QA. The complete
> test information :
> --
> CREATE TABLE agls1
> (fyear char(4) NULL ,
> fprefix char(3) NULL ,
> fvcno char(20) NULL ,
> fdate datetime NULL ,
> fid char(15) NULL ,
> fiddate datetime NULL ,
> fdesc char(60) NULL ,
> facc char(12) NULL ,
> fval decimal(18, 2) NULL ,
> fcrc char(5) NULL ,
> fsub char(1) NULL ,
> fmaster char(9) NULL ,
> fcode char(15) NULL )
> CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
> fdate, fvcno)
> CREATE INDEX b ON agls1 (fyear, facc, fdate,
> fprefix, fvcno)
> CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)
> insert into agls1
> ( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
> values
> ( '2004','A','B','123','inv','20040101','01','111' )
> --query-1
> select * from agls1
> where fyear = '2004' and fprefix = 'inv' and
> fdate = '20040101' and fvcno = '01'
> --query-2
> select * from agls1
> where fyear = '2004' and facc = '111' and
> fdate = '20040101' and fprefix = 'inv' and fvcno = '01'
> --query-3
> select * from agls1
> where fyear = '2004' and fsub = 'A' and fmaster = 'B'
> and fcode = '123'
> --
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?
> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?
> c. How does SQL choose an index for a process ?

The last question in particular has lots of book chapters on it.
The fact is that the table is so small that no index can really
help much. A blind table-scan is fastest with a one-row table.
To see more intuitive index use, you should probably test with
a table having thousands of well-distributed rows.
Joe Weinstein at BEA
> Could anyone help me
> Thanks in advance
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> I will continue testing using minimum amount of rows
> to see that SQL Server uses index c for query-3.
> The data must easily force SQL Server to use
> index c. If you do not mind, could you advice me
> how data looks like that I should create.

You can always use an index hint to convince SQL Server to use an index:

SELECT * FROM tbl WITH (INDEX = c)

To make it simple you should have fyear = 2004 in all rows you create.
But the values in fsub, fmastser and fcode should vary.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949BF26CFB825Yazorman@.127.0.0.1...
> Anita (anonymous@.devdex.com) writes:
> > I will continue testing using minimum amount of rows
> > to see that SQL Server uses index c for query-3.
> > The data must easily force SQL Server to use
> > index c. If you do not mind, could you advice me
> > how data looks like that I should create.
> You can always use an index hint to convince SQL Server to use an index:
> SELECT * FROM tbl WITH (INDEX = c)

Just to jump in, you can of course do that. However (and this is to Anita,
not Erland since I know he's aware of this), it's generally a fairly bad
idea to force an index hint, since your data may later change in such a way
to make the index less useful.

I'd recommend finding some of the papers Kalen Delany has written on this
subject as it may help.

> To make it simple you should have fyear = 2004 in all rows you create.
> But the values in fsub, fmastser and fcode should vary.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I have inserted 810 rows by following Erland's advice.
With these rows, SQL Server uses index c when executes
query :
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'

Thanks again to all of you that sent the replies

Anita Hery

Note :
Below is my insert test :

declare @.sub as int, @.master as int, @.code as int
set @.sub = 0 --max 15
set @.master = 0 --max 6
set @.code = 0 --max 15 digit

lsub:
set @.sub = @.sub + 1
set @.master = 0
set @.code = 0
lmaster:
set @.master = @.master + 1
set @.code = 0
lcode:
set @.code = @.code + 1
insert into agls1
(fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
('2004',str(@.sub,1),str(@.master,9),
str(@.code,15),'inv','20040101','01','111')

if @.code < 15 goto lcode
if @.master < 6 goto lmaster
if @.sub < 9 goto lsub

select * from agls1
where fyear = '2004' and fsub = 'A' and
fmaster = 'B' and fcode = '123'

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Question ...

... someone or some software removed data from
one table of my SQL Server 2005 database. How to check
who/when did that? What do i have to turn on for
the future investigation of that cause?Jacek Jurkowski wrote:
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?
Hi,
SQL Server it self hasn't got this feature directly. You'll have to
write some triggers that can fire on deletions/updates and update a
"logging"/"history" table with the necessary info.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi,
you will either have to implement triggers , start a profiler on the
database (which would bevery expensive in performance) or use a log
reader after things happened.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||In addition to others, in SQL Server 2005 you can create event notification
to catch this info
"Jacek Jurkowski" <jjurkowski@.data-comp.eu> wrote in message
news:F5D999D4-F77C-4DFB-A7D8-14EA589E70C9@.microsoft.com...
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?|||To add to the other responses, if data was very recently deleted and you
have the default trace enabled, you might get lucky and find the command in
the default trace. These default trace files are created in the log folder
and can be opened with Profiler or fn_trace_gettable. For example:
SELECT *
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\log.trc', default)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacek Jurkowski" <jjurkowski@.data-comp.eu> wrote in message
news:F5D999D4-F77C-4DFB-A7D8-14EA589E70C9@.microsoft.com...
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?

Question ...

... someone or some software removed data from
one table of my SQL Server 2005 database. How to check
who/when did that? What do i have to turn on for
the future investigation of that cause?Jacek Jurkowski wrote:
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?
Hi,
SQL Server it self hasn't got this feature directly. You'll have to
write some triggers that can fire on deletions/updates and update a
"logging"/"history" table with the necessary info.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Hi,
you will either have to implement triggers , start a profiler on the
database (which would bevery expensive in performance) or use a log
reader after things happened.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||In addition to others, in SQL Server 2005 you can create event notification
to catch this info
"Jacek Jurkowski" <jjurkowski@.data-comp.eu> wrote in message
news:F5D999D4-F77C-4DFB-A7D8-14EA589E70C9@.microsoft.com...
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?|||To add to the other responses, if data was very recently deleted and you
have the default trace enabled, you might get lucky and find the command in
the default trace. These default trace files are created in the log folder
and can be opened with Profiler or fn_trace_gettable. For example:
SELECT *
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\log.trc', default)
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacek Jurkowski" <jjurkowski@.data-comp.eu> wrote in message
news:F5D999D4-F77C-4DFB-A7D8-14EA589E70C9@.microsoft.com...
> ... someone or some software removed data from
> one table of my SQL Server 2005 database. How to check
> who/when did that? What do i have to turn on for
> the future investigation of that cause?

Question - SQL - Help - Pleased

Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
ESTUVO_univoldev,ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc),SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,
ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.
Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettXe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Xhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:

> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
> ESTUVO_univoldev,ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc),SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,
> ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.

Question - SQL - Help - Pleased

Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTU
BA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc
),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal)
,
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valba
ldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantf
unrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costl
ocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numer
o), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_
nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTU
VO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquett_e.asp?id=5006 and
example data as insert statements [url]http://vyaskn.tripod.com/code._htm#inserts[/url
]
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmente
d
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:

> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, EST
UBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impulo
c),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal
),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valb
aldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cant
funrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_cost
locrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_nume
ro), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, EST
UVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultatio
n
> is for the Harward of it schemes it but well I think that it is for some b
ad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.

Saturday, February 25, 2012

Question about "Connect to the sql server 2000 installed on windows xp sp2"

Question about "WITH (ROWLOCK)" hint.

Question about "WITH (ROWLOCK)" hint.

question about "System.Data.SqlClient.SqlException"

Question about "servername\instancename"

Question about "Object permissions" & "Statement permissions"

Question about "Object permissions" & "Statement permissions"

Question about "Foreign key constraint"

Question about "Foreign key constraint"

Question about "Distribution clean up: distribution" Job

Question about "Data Transformation Services " : How to define myselfs query without DTS W

question abot primary key

question ?

question ?

question ?

Question : Transactional Replication Rollback ?

Question : How SQL chooses an index for a process

Question ...

Question ...

Question - SQL - Help - Pleased

Question - SQL - Help - Pleased

Antivirus install to db server

Blog Archive

About Me