Finding Max Length of ntext column in SP

I have upsized an access database and am trying to find all the maximum
lengths of the columns, so I can restructure the DB. I have the query that
will give me the max length of the column, but since I have a bunch of
columns, I would like to write a SP to do it for all the columns.
Here is my query
SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
FROM tblIPPCore2
ORDER BY MembersLenMax DESC;
How can I automate this query?
Thanks,
DrewI have modified my query to the following,
SELECT MAX(DATALENGTH([Members Present])) AS MembersPresentLenMax
FROM tblIPPCore2
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>|||Here is the answer...
This Procedure takes @.TableName, and returns the maximum lengths for each
column in the table. There is no error correction, so column names must not
have spaces or weird characters.
CREATE PROCEDURE spMaxLen
@.TableName varchar(20)
AS
declare @.v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000))
DECLARE @.vMaxCount int, @.iCount int, @.sSQL varchar(1000)
INSERT into @.v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' +
replace(column_name, ' ', '') + 'MaxLen FROM ' + @.TableName
FROM information_schema.columns
where table_name = @.TableName
SET @.vMaxCount =@.@.ROWCOUNT
set @.iCount = 1
WHILE @.iCount < @.vMaxCount + 1
BEGIN
SELECT @.sSQL = sSQL from @.v_tempTable where myid = @.iCount
exec (@.ssql)
SET @.iCount = @.iCount + 1
END
GO
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Find out heavily accessed tables in the database

Dear All,
I want to make access counter statistic monthly for each table in the
database. The purpose is to find out the heavily accessed tables.
Does anyone know how to do that?
Thanks
Robert LieConcider creating the table that has information about all the tables in the
database.
Write a trigger for each table in the database. The trigger should update
the hit-count column in the table that you created recently.
This is a tedious process but i believe that it answers your question.
thanks and regards
Chandra
"Robert Lie" wrote:

> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
>|||Could you give some code example about the trigger you're proposing to.
Thanks
Robert Lie
Chandra wrote:[vbcol=seagreen]
> Concider creating the table that has information about all the tables in t
he
> database.
> Write a trigger for each table in the database. The trigger should update
> the hit-count column in the table that you created recently.
> This is a tedious process but i believe that it answers your question.
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>|||Here is the sample:
===============
Sample Table should look like:
CREATE TABLE STATS(
TABLE_NAME varchar(20),
HIT_COUNT integer default 0
)
Say you are writing trigger on Employee Table
insert into STATS values ('Employee')
Now:
CREATE TRIGGER TR_EMP_STAT
ON EMPLOYEE
FOR INSERT,UPDATE, DELETE
AS
BEGIN
UPDATE STATS
SET HIT_COUNT = HIT_COUNT + 1
WHERE TABLE_NAME = 'EMPLOYEE'
END
GO
================
hope you are satisified...
thanks and regards
Chandra
"Robert Lie" wrote:

> Could you give some code example about the trigger you're proposing to.
> Thanks
> Robert Lie
>
> Chandra wrote:
>|||Ok. Thanks for your suggestion.
But I need more than that, I need to know how many users who access the
tables which means it can be SELECT, INSERT, UPDATE dan DELETE.
Thanks
Chandra wrote:[vbcol=seagreen]
> Here is the sample:
> ===============
> Sample Table should look like:
> CREATE TABLE STATS(
> TABLE_NAME varchar(20),
> HIT_COUNT integer default 0
> )
>
> Say you are writing trigger on Employee Table
> insert into STATS values ('Employee')
> Now:
> CREATE TRIGGER TR_EMP_STAT
> ON EMPLOYEE
> FOR INSERT,UPDATE, DELETE
> AS
> BEGIN
> UPDATE STATS
> SET HIT_COUNT = HIT_COUNT + 1
> WHERE TABLE_NAME = 'EMPLOYEE'
> END
> GO
> ================
> hope you are satisified...
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>|||Robert
Have you looked at SQL Server Profiler to capture data ?
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie|||I think is quite difficult to use SQL Profiler for that purpose since we
have to run the SQL Profiler in certain period.
Uri Dimant wrote:
> Robert
> Have you looked at SQL Server Profiler to capture data ?
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
>
>
>|||Robert
You don't need to run Profiler on the server, run it on the client connected
to the server.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think is quite difficult to use SQL Profiler for that purpose since we
> have to run the SQL Profiler in certain period.
>
> Uri Dimant wrote:|||I agree with Uri , Profile is the best option for Roberts requirement.
Regards
Vishal Gandhi
"Uri Dimant" wrote:

> Robert
> You don't need to run Profiler on the server, run it on the client connect
ed
> to the server.
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>

Find out heavily accessed tables in the database

Dear All,
I want to make access counter statistic monthly for each table in the
database. The purpose is to find out the heavily accessed tables.
Does anyone know how to do that?
Thanks
Robert Lie
Concider creating the table that has information about all the tables in the
database.
Write a trigger for each table in the database. The trigger should update
the hit-count column in the table that you created recently.
This is a tedious process but i believe that it answers your question.
thanks and regards
Chandra
"Robert Lie" wrote:

> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
>
|||Could you give some code example about the trigger you're proposing to.
Thanks
Robert Lie
Chandra wrote:[vbcol=seagreen]
> Concider creating the table that has information about all the tables in the
> database.
> Write a trigger for each table in the database. The trigger should update
> the hit-count column in the table that you created recently.
> This is a tedious process but i believe that it answers your question.
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
|||Here is the sample:
===============
Sample Table should look like:
CREATE TABLE STATS(
TABLE_NAME varchar(20),
HIT_COUNT integer default 0
)
Say you are writing trigger on Employee Table
insert into STATS values ('Employee')
Now:
CREATE TRIGGER TR_EMP_STAT
ON EMPLOYEE
FOR INSERT,UPDATE, DELETE
AS
BEGIN
UPDATE STATS
SET HIT_COUNT = HIT_COUNT + 1
WHERE TABLE_NAME = 'EMPLOYEE'
END
GO
================
hope you are satisified...
thanks and regards
Chandra
"Robert Lie" wrote:

> Could you give some code example about the trigger you're proposing to.
> Thanks
> Robert Lie
>
> Chandra wrote:
>
|||Ok. Thanks for your suggestion.
But I need more than that, I need to know how many users who access the
tables which means it can be SELECT, INSERT, UPDATE dan DELETE.
Thanks
Chandra wrote:[vbcol=seagreen]
> Here is the sample:
> ===============
> Sample Table should look like:
> CREATE TABLE STATS(
> TABLE_NAME varchar(20),
> HIT_COUNT integer default 0
> )
>
> Say you are writing trigger on Employee Table
> insert into STATS values ('Employee')
> Now:
> CREATE TRIGGER TR_EMP_STAT
> ON EMPLOYEE
> FOR INSERT,UPDATE, DELETE
> AS
> BEGIN
> UPDATE STATS
> SET HIT_COUNT = HIT_COUNT + 1
> WHERE TABLE_NAME = 'EMPLOYEE'
> END
> GO
> ================
> hope you are satisified...
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
|||Robert
Have you looked at SQL Server Profiler to capture data ?
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
|||I think is quite difficult to use SQL Profiler for that purpose since we
have to run the SQL Profiler in certain period.
Uri Dimant wrote:
> Robert
> Have you looked at SQL Server Profiler to capture data ?
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
>
>
|||Robert
You don't need to run Profiler on the server, run it on the client connected
to the server.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think is quite difficult to use SQL Profiler for that purpose since we
> have to run the SQL Profiler in certain period.
>
> Uri Dimant wrote:
|||I agree with Uri , Profile is the best option for Roberts requirement.
Regards
Vishal Gandhi
"Uri Dimant" wrote:

> Robert
> You don't need to run Profiler on the server, run it on the client connected
> to the server.
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>

Find out heavily accessed tables in the database

Dear All,
I want to make access counter statistic monthly for each table in the
database. The purpose is to find out the heavily accessed tables.
Does anyone know how to do that?
Thanks
Robert LieConcider creating the table that has information about all the tables in the
database.
Write a trigger for each table in the database. The trigger should update
the hit-count column in the table that you created recently.
This is a tedious process but i believe that it answers your question.
thanks and regards
Chandra
"Robert Lie" wrote:
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
>|||Could you give some code example about the trigger you're proposing to.
Thanks
Robert Lie
Chandra wrote:
> Concider creating the table that has information about all the tables in the
> database.
> Write a trigger for each table in the database. The trigger should update
> the hit-count column in the table that you created recently.
> This is a tedious process but i believe that it answers your question.
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
>>Dear All,
>>I want to make access counter statistic monthly for each table in the
>>database. The purpose is to find out the heavily accessed tables.
>>Does anyone know how to do that?
>>Thanks
>>Robert Lie|||Here is the sample:
===============Sample Table should look like:
CREATE TABLE STATS(
TABLE_NAME varchar(20),
HIT_COUNT integer default 0
)
Say you are writing trigger on Employee Table
insert into STATS values ('Employee')
Now:
CREATE TRIGGER TR_EMP_STAT
ON EMPLOYEE
FOR INSERT,UPDATE, DELETE
AS
BEGIN
UPDATE STATS
SET HIT_COUNT = HIT_COUNT + 1
WHERE TABLE_NAME = 'EMPLOYEE'
END
GO
================
hope you are satisified...
thanks and regards
Chandra
"Robert Lie" wrote:
> Could you give some code example about the trigger you're proposing to.
> Thanks
> Robert Lie
>
> Chandra wrote:
> > Concider creating the table that has information about all the tables in the
> > database.
> > Write a trigger for each table in the database. The trigger should update
> > the hit-count column in the table that you created recently.
> >
> > This is a tedious process but i believe that it answers your question.
> >
> > thanks and regards
> > Chandra
> >
> >
> > "Robert Lie" wrote:
> >
> >
> >>Dear All,
> >>
> >>I want to make access counter statistic monthly for each table in the
> >>database. The purpose is to find out the heavily accessed tables.
> >>Does anyone know how to do that?
> >>
> >>Thanks
> >>
> >>Robert Lie
> >>
>|||Ok. Thanks for your suggestion.
But I need more than that, I need to know how many users who access the
tables which means it can be SELECT, INSERT, UPDATE dan DELETE.
Thanks
Chandra wrote:
> Here is the sample:
> ===============> Sample Table should look like:
> CREATE TABLE STATS(
> TABLE_NAME varchar(20),
> HIT_COUNT integer default 0
> )
>
> Say you are writing trigger on Employee Table
> insert into STATS values ('Employee')
> Now:
> CREATE TRIGGER TR_EMP_STAT
> ON EMPLOYEE
> FOR INSERT,UPDATE, DELETE
> AS
> BEGIN
> UPDATE STATS
> SET HIT_COUNT = HIT_COUNT + 1
> WHERE TABLE_NAME = 'EMPLOYEE'
> END
> GO
> ================> hope you are satisified...
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
>>Could you give some code example about the trigger you're proposing to.
>>Thanks
>>Robert Lie
>>
>>Chandra wrote:
>>Concider creating the table that has information about all the tables in the
>>database.
>>Write a trigger for each table in the database. The trigger should update
>>the hit-count column in the table that you created recently.
>>This is a tedious process but i believe that it answers your question.
>>thanks and regards
>>Chandra
>>
>>"Robert Lie" wrote:
>>
>>Dear All,
>>I want to make access counter statistic monthly for each table in the
>>database. The purpose is to find out the heavily accessed tables.
>>Does anyone know how to do that?
>>Thanks
>>Robert Lie
>>|||Robert
Have you looked at SQL Server Profiler to capture data ?
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie|||I think is quite difficult to use SQL Profiler for that purpose since we
have to run the SQL Profiler in certain period.
Uri Dimant wrote:
> Robert
> Have you looked at SQL Server Profiler to capture data ?
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
>>Dear All,
>>I want to make access counter statistic monthly for each table in the
>>database. The purpose is to find out the heavily accessed tables.
>>Does anyone know how to do that?
>>Thanks
>>Robert Lie
>
>|||Robert
You don't need to run Profiler on the server, run it on the client connected
to the server.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
> I think is quite difficult to use SQL Profiler for that purpose since we
> have to run the SQL Profiler in certain period.
>
> Uri Dimant wrote:
> > Robert
> > Have you looked at SQL Server Profiler to capture data ?
> >
> >
> > "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> > news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> >
> >>Dear All,
> >>
> >>I want to make access counter statistic monthly for each table in the
> >>database. The purpose is to find out the heavily accessed tables.
> >>Does anyone know how to do that?
> >>
> >>Thanks
> >>
> >>Robert Lie
> >
> >
> >|||I agree with Uri , Profile is the best option for Roberts requirement.
Regards
Vishal Gandhi
"Uri Dimant" wrote:
> Robert
> You don't need to run Profiler on the server, run it on the client connected
> to the server.
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
> > I think is quite difficult to use SQL Profiler for that purpose since we
> > have to run the SQL Profiler in certain period.
> >
> >
> > Uri Dimant wrote:
> > > Robert
> > > Have you looked at SQL Server Profiler to capture data ?
> > >
> > >
> > > "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> > > news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> > >
> > >>Dear All,
> > >>
> > >>I want to make access counter statistic monthly for each table in the
> > >>database. The purpose is to find out the heavily accessed tables.
> > >>Does anyone know how to do that?
> > >>
> > >>Thanks
> > >>
> > >>Robert Lie
> > >
> > >
> > >
>
>

Find empty values in a column

Hello,
I am trying to replicate somthing I did in my access reports.
I need to count how many 0 dollar values or null values show up in a
combination of two colums.
I dont know where to begin in Reporting Services. In Acces this was VBA.
Here is the VBA CODE.
End If
If Nz(Me.sQuotedPrice, 0) = "0" Or Len(Me!sQuotedPrice) = 0 And
Nz(Me.sGuessPrice, 0) = "0" Or Len(Me!sGuessPrice) = 0 Then
sJobsMissingPrices = sJobsMissingPrices + 1
End If
I try this in Reporting Services and it says it is not with in the correct
scope?
Public Shared Function GetEmptyValues()
Dim JobsMissingPrices
If ReportItem!JobPrice.Value= 0 Or Len(ReportItems!JobPrice.value) = 0 And
NzReportItems!Guess_Price = "0" Or Len(ReportItems!Guess_Price) = 0 Then
JobsMissingPrices = JobsMissingPrices + 1
End If
End Function
Any help would be appreciated.
--
Thank You, LeoHi Leo,
It seems there is something wrong here, an additional "Nz" was not removed
NzReportItems!Guess_Price = "0"
If removing Nz doesn't resolve this issue, please provide more detailed
information about this two values and I would like to make a reproduce on
my side.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Leo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Find database access by tcp connection

Ok, lets see if I can put what I am looking for into words.
I run a MSSQL server, I show in my logs that a connection is being made to my server from a remote ip. I have contact the owner of the ip in question and they state that they have no buisness with us and they should not be connected to us. According to netstat the connection to us is established on 2 different remote ports to 1433. My question, is there any logging on sql that I can turn on, look at to see what database this connection is accessing? ThanksHow about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?|||Originally posted by Brett Kaiser
How about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?

They must have a userid and password to connect yes, I am trying to find out who it is. None of our clients that I know of are connecting from this location. I'm not familiar with the sql profiler. Where might this be.

Thanks|||It comes with the sql server client tools...

And this guy gave a presentation on it...

http://weblogs.sqlteam.com/billg/archive/2003/11/08/460.aspx|||Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?|||Originally posted by MCrowley
Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?

Is this a setup?

http://weblogs.sqlteam.com/brettk/archive/2003/10/02/223.aspx

Finalizing a field

Hello all

I have SQL Server 2005 in as the engine and Access 2003 on the front end. And I was wondering if the SQL has a feature where you can allow users to update a field but once its updated they can not edit it. Once the form has been filled out they cant make any changes to it without a supervisor logging in. Does that make sense?? Is this possible??there is no write - once unless I am an admin feature I know of. This you would have to handle in the application code for the interface.|||Dear my Desire,

You would need to have a column that has an indicator or a datetime filed that indactes that the row has been updated.

The front end would then interogate that field.

If it exists, then you need to "protect" the field from updates.

From a backend perspective, you could write a trigger to do the same thing, and rollback the trans if an update is trying to occur against a row that's already been modified.

BUT

This makes no sense|||I'm just wondering; should the question be

Can I allow all users to INSERT and only allow supervisors to UPDATE?|||Dear my Desire,

You would need to have a column that has an indicator or a datetime filed that indactes that the row has been updated.

The front end would then interogate that field.

If it exists, then you need to "protect" the field from updates.

From a backend perspective, you could write a trigger to do the same thing, and rollback the trans if an update is trying to occur against a row that's already been modified.

BUT

This makes no sense

I was just curious guys, the thing is supervisors dont want users to be able to edit anyones data in the database once its been entered. they dont want anyone manipulating the system, does that make any sense??|||...ok, let's go out on a limb here, and say that the users may be falable

What if the data is enetered incorrectly

Can't they fix it?

In any case, I would use a history tabel and a trigger to track all data changes|||I'm arriving late to this party, but I'm 100% with Brett on this. Assuming that users will always get data right on the first try is a great party joke, but not appropriate until after you've had enough drinks that you aren't able to dance on the table even with your shoes off.

-PatP|||Thank you guys, we are going to use a history table and track all the changes that are being done, because thats just ridiculous. A history table would make allot more sense. If I can get sql to track when they login, when they insert, update and modify the data, like a creation date and a modified date..etc. That makes allot more sense. thanks guys I know that was a stupid question, but how do you know if you dont ask|||Do you need some help with that?

Don't store the inserts in history, just leave them in the base table

Also, how does the application login to the database?|||THANK YOU SO MUCH,

I wanted to create a history table that will link to the main table. and in this history table I wanted sql to keep track of the date they logged in, the date they modified, updated and deleted data.

Am I on the right track??|||Generates History tables

SET NOCOUNT ON
GO
/*
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)

-- Populate the audit driver table with the table you want...you can use any type of process for this

TRUNCATE TABLE myAudit99

INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE 1=1
AND TABLE_NAME NOT LIKE '%_H'
AND TABLE_NAME NOT LIKE 'v_%'
AND TABLE_NAME <> 'dtproperties'
AND TABLE_NAME <> 'myAudit99'
AND TABLE_NAME <> 'myColumns45'
AND TABLE_NAME <> 'myColumns45'
AND TABLE_NAME <> 'MySprocs99'
AND TABLE_NAME <> 'MyStage99'
AND TABLE_NAME <> 'myTables45'
AND TABLE_NAME <> 'myTableSearch99'
AND TABLE_NAME <> 'MyWork99'
AND TABLE_NAME <> 'sysconstraints'
AND TABLE_NAME <> 'syssegments'

SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE CHARACTER_MAXIMUM_LENGTH > 6000

*/
-- Lets create some audit tables based on the Driver

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99
-- WHERE TABLE_NAME <> 'myAudit99'
WHERE TABLE_NAME <> 'MEP'

DECLARE @.TABLE_CATALOG sysname, @.TABLE_SCHEMA sysname, @.TABLE_NAME sysname, @.COLUMN_NAMES varchar(8000)
, @.sql varchar(8000), @.drop varchar(8000)
SELECT @.COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'CREATE TABLE ' + @.TABLE_CATALOG + '.' + @.TABLE_SCHEMA + '.' + @.TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @.COLUMN_NAMES = @.COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @.TABLE_CATALOG AND TABLE_SCHEMA = @.TABLE_SCHEMA AND TABLE_NAME = @.TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @.SQL = @.SQL + @.COLUMN_NAMES + ')'

SELECT @.drop =
'if exists (select * from dbo.sysobjects where id = object_id(N'
+''''+'['+@.TABLE_SCHEMA+'].['+@.TABLE_NAME+'_H]'
+''''+') and OBJECTPROPERTY(id, N'
+''''+'IsUserTable'
+''''+') = 1)'+CHAR(13)+CHAR(10)
+'drop table ['+@.TABLE_SCHEMA+'].['+@.TABLE_NAME+'_H]'

--SELECT @.DROP
EXEC(@.drop)

-- EXEC(@.SQL)
--SELECT @.sql

SELECT @.SQL = '', @.COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO|||Generates Triggers

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
--FROM INFORMATION_SCHEMA.Tables -- To wipe out all history cursors
FROM myAudit99
WHERE 1=1
--AND TABLE_NAME <> 'myAudit99'
AND TABLE_NAME <> 'MEP'
--AND TABLE_NAME NOT IN ('MySprocs99','Folder', 'dtproperties')

DECLARE @.TABLE_CATALOG sysname, @.TABLE_SCHEMA sysname, @.TABLE_NAME sysname, @.COLUMN_NAMES varchar(8000), @.sql varchar(8000)

DECLARE @.DROP varchar(8000)

SELECT @.COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.COLUMN_NAMES = @.COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @.TABLE_CATALOG AND TABLE_SCHEMA = @.TABLE_SCHEMA AND TABLE_NAME = @.TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @.SQL = 'CREATE TRIGGER ' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR ON ' + @.TABLE_SCHEMA + '.' +@.TABLE_NAME
+ ' FOR UPDATE, DELETE AS SET NOCOUNT ON ' + CHAR(13) + CHAR(10)
+ ' DECLARE @.HOSTNAME sysname, @.DESC varchar(50) ' + CHAR(13) + CHAR(10)
+ ' SELECT @.HOSTNAME = hostname from master.dbo.sysprocesses where spid = @.@.SPID ' + CHAR(13) + CHAR(10)
+ ' IF EXISTS(SELECT * FROM ' + @.TABLE_NAME + ') SELECT @.DESC = '
+ '''' + '''' + ' ELSE SELECT @.DESC = ' + '''' + 'MASS DELETE' + '''' + CHAR(13) + CHAR(10)
+ ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ''U'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted' + CHAR(13) + CHAR(10)
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @.DESC = '''' '
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ''D'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted' + CHAR(13) + CHAR(10)
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @.DESC <> '''' ' + CHAR(13) + CHAR(10)
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted'


SELECT @.DROP = 'if exists (select * from dbo.sysobjects where id = object_id(N'
+ ''''+'[dbo].'
+ '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]'
+ ''''
+') and OBJECTPROPERTY(id, N'
+ ''''
+'IsTrigger'
+ ''''
+') = 1)'+ CHAR(13) + CHAR(10)
+ ' drop trigger [dbo].'
+ '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]'

--SELECT @.DROP
EXEC(@.DROP)

-- EXEC('DROP TRIGGER ' + '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]')

--EXEC(@.SQL)

--SELECT @.sql

SELECT @.SQL = '', @.COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO|||they want fields audited as well as the entire table. for example if someone enters data into a field, closes out of the database then goes back in and edits that field they want an indicator to tell them when and what time did they do that. Does that make sense? I was trying to find a date function I could use to do that