link to SQL Server Express from Access 2000

i'm trying to set a data source in Access 2000 to link to SQL Server express..I have mixed mode set..what user/password do I put in? I tried sa, but get error server does not exist, or access denied..
thanks!

Did you make SQL Server available for remote connections ?

http://www.datamasker.com/SSE2005_NetworkCfg.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?Hi
This sounds like you are using access? In which case you may have a better
response in an access newsgroup.
If you are using an ODBC data source then you may need to set up a system
DSN in the ODBC configuration applet found in the Administrators menu.
If this is a remote database that you are connecting to, then you will need
to make sure that any firewalls/routers are not blocking access to this
server.
Posting the full error number and message will help.
John
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23UZZ1ehMFHA.4028@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>sql

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
Hi
This sounds like you are using access? In which case you may have a better
response in an access newsgroup.
If you are using an ODBC data source then you may need to set up a system
DSN in the ODBC configuration applet found in the Administrators menu.
If this is a remote database that you are connecting to, then you will need
to make sure that any firewalls/routers are not blocking access to this
server.
Posting the full error number and message will help.
John
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23UZZ1ehMFHA.4028@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>
sql

Link opens a new window...How To?

We have a report that links to another report. The A report is a posting on
our intranet and we'd like to have it so when a user clicks on the link for
the other report it will open another window for them. Any ideas on how this
may be done?You can try putting someting like this
= "javascript:void(window.open('<insert url here>','_blank'))"
in the Jump to URL section of the action property.
David Siebert
"Marty" wrote:
> We have a report that links to another report. The A report is a posting on
> our intranet and we'd like to have it so when a user clicks on the link for
> the other report it will open another window for them. Any ideas on how this
> may be done?|||The better way of doing this is in the parent reports url place the following
as part of the url rc:LinkTarget=_Self
this will make all report links in the report open up where the parent
report is.
"David Siebert" wrote:
> You can try putting someting like this
> = "javascript:void(window.open('<insert url here>','_blank'))"
> in the Jump to URL section of the action property.
> David Siebert
> "Marty" wrote:
> > We have a report that links to another report. The A report is a posting on
> > our intranet and we'd like to have it so when a user clicks on the link for
> > the other report it will open another window for them. Any ideas on how this
> > may be done?|||David,
I have tried using the javascript code below on a report of mine and it does
not work. I put the following in the "Jump to URL" on a field as a test to
make sure the code works:-
="javascript:void(window.open('http://www.google.com','_blank'))"
I deployed the report and ran it (we are just using the standard report
manager web site), but when I click on the field to invoke the jump to all I
get is a "page cannot be displayed" message and this message is in the same
window as the report.
If I shift and click the field to force the link into a new window then I
get the same "page cannot be displayed" error, but as soon as this window
opens I do get a new window with google in it. So, the code seems to be
working directly from IE but does not work from within RS.
Any ideas why this is not working?
Thanks
Dave
"David Siebert" wrote:
> You can try putting someting like this
> = "javascript:void(window.open('<insert url here>','_blank'))"
> in the Jump to URL section of the action property.
> David Siebert
> "Marty" wrote:
> > We have a report that links to another report. The A report is a posting on
> > our intranet and we'd like to have it so when a user clicks on the link for
> > the other report it will open another window for them. Any ideas on how this
> > may be done?|||got the same problem, tried this but it doesn't work :( Looking at the
rendered page, it is putting 'TARGET='_top' into the a tag :/ ) They need to
add a 'target' property to the 'jump to url', bit of an oversight tbh :(
"David Siebert" <DavidSiebert@.discussions.microsoft.com> wrote in message
news:1306182F-57EF-48BF-93C4-596B53F79B78@.microsoft.com...
> You can try putting someting like this
> = "javascript:void(window.open('<insert url here>','_blank'))"
> in the Jump to URL section of the action property.
> David Siebert
> "Marty" wrote:
> > We have a report that links to another report. The A report is a posting
on
> > our intranet and we'd like to have it so when a user clicks on the link
for
> > the other report it will open another window for them. Any ideas on how
this
> > may be done?|||Hi,
I have the same problem :(
I want the hyperlinks in my report to open in a new window. I tried doing
this:
in Advanced->Navigation->JumptoURL of the textbox displaying the hyperlink,
I added "http://-the url"&" rc:LinkTarget=_blank" but it doesnt work - even
the javascript doesnt work :(
plz help
thanks.
"Anonymous Poster" wrote:
> got the same problem, tried this but it doesn't work :( Looking at the
> rendered page, it is putting 'TARGET='_top' into the a tag :/ ) They need to
> add a 'target' property to the 'jump to url', bit of an oversight tbh :(
> "David Siebert" <DavidSiebert@.discussions.microsoft.com> wrote in message
> news:1306182F-57EF-48BF-93C4-596B53F79B78@.microsoft.com...
> > You can try putting someting like this
> >
> > = "javascript:void(window.open('<insert url here>','_blank'))"
> >
> > in the Jump to URL section of the action property.
> >
> > David Siebert
> >
> > "Marty" wrote:
> >
> > > We have a report that links to another report. The A report is a posting
> on
> > > our intranet and we'd like to have it so when a user clicks on the link
> for
> > > the other report it will open another window for them. Any ideas on how
> this
> > > may be done?
>
>|||When you tried the javascript link, did you deploy it to the server and try
it, or just from visual studio?
My first attempt at this gave me an error regarding the link's schema type
and said it was invalid. But after deploying to the report server it worked
fine.
"Aravind" wrote:
> Hi,
> I have the same problem :(
> I want the hyperlinks in my report to open in a new window. I tried doing
> this:
> in Advanced->Navigation->JumptoURL of the textbox displaying the hyperlink,
> I added "http://-the url"&" rc:LinkTarget=_blank" but it doesnt work - even
> the javascript doesnt work :(
> plz help
> thanks.
> "Anonymous Poster" wrote:
> > got the same problem, tried this but it doesn't work :( Looking at the
> > rendered page, it is putting 'TARGET='_top' into the a tag :/ ) They need to
> > add a 'target' property to the 'jump to url', bit of an oversight tbh :(
> >
> > "David Siebert" <DavidSiebert@.discussions.microsoft.com> wrote in message
> > news:1306182F-57EF-48BF-93C4-596B53F79B78@.microsoft.com...
> > > You can try putting someting like this
> > >
> > > = "javascript:void(window.open('<insert url here>','_blank'))"
> > >
> > > in the Jump to URL section of the action property.
> > >
> > > David Siebert
> > >
> > > "Marty" wrote:
> > >
> > > > We have a report that links to another report. The A report is a posting
> > on
> > > > our intranet and we'd like to have it so when a user clicks on the link
> > for
> > > > the other report it will open another window for them. Any ideas on how
> > this
> > > > may be done?
> >
> >
> >|||David
Did you get this to work, I am getting the problem where it only works if I
use the shift key when pressing the mouse button.
Was it a server change or report as the same is happening on all reports?
Darren
"DaveW" wrote:
> David,
> I have tried using the javascript code below on a report of mine and it does
> not work. I put the following in the "Jump to URL" on a field as a test to
> make sure the code works:-
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> I deployed the report and ran it (we are just using the standard report
> manager web site), but when I click on the field to invoke the jump to all I
> get is a "page cannot be displayed" message and this message is in the same
> window as the report.
> If I shift and click the field to force the link into a new window then I
> get the same "page cannot be displayed" error, but as soon as this window
> opens I do get a new window with google in it. So, the code seems to be
> working directly from IE but does not work from within RS.
> Any ideas why this is not working?
> Thanks
> Dave
> "David Siebert" wrote:
> > You can try putting someting like this
> >
> > = "javascript:void(window.open('<insert url here>','_blank'))"
> >
> > in the Jump to URL section of the action property.
> >
> > David Siebert
> >
> > "Marty" wrote:
> >
> > > We have a report that links to another report. The A report is a posting on
> > > our intranet and we'd like to have it so when a user clicks on the link for
> > > the other report it will open another window for them. Any ideas on how this
> > > may be done?|||Since there are many who cannot get "LinkTarget" to work I want to share my
mistake.
Remember that it is the url to the "master"-report that will need the
parameter "LinkTarget", it is not the action-links to the "Detail"-report
that will need the parameter.
Url 1: http://localhost/rerportMaster&rc:LinkTarget=_blank
Url 2 (Action In report 2): Anything (renders with target=_blank)
I struggled with this for a long time before I realized my mistake.
Regards Maran
"Marty" wrote:
> We have a report that links to another report. The A report is a posting on
> our intranet and we'd like to have it so when a user clicks on the link for
> the other report it will open another window for them. Any ideas on how this
> may be done?

Line numbers in Report Builder

I have a report in which the user community would like line numbers associated with each row. Has anyone done this or have any ideas how to accomplish this?

Line Item Manufacturer
1 Mazda
2 Ford
3 BMW

ThanksThis seems like something you want to do in the sql query rather than the report. Not sure, exactly how...|||Since you drag elements into the design area and Report Builder is not based on a query, there is no way to create line item numbers within Report Builder.

Is that what you are saying?|||Hi,

You can use RunningValue function as the text value of the line number column.

If you want a numbering for rows within a dataset, you can use

=RunningValue(1, SUM, "Employees")

where Employees is the dataset name.

If you have groups in your reports, say one of the groups has a name "EmployeesByCity", then you can replace the last part of the RunningValue with this group name shown as below

=RunningValue(1, SUM, "EmployeesByCity")

You can check the below link for a sample

http://www.kodyaz.com/article.aspx?ArticleID=40

Eralper|||Thank you for your assistance. "RunningValue" is not available in Report Builder.|||

I've got a similar issue that RunningValue won't work for. Basically, what the business users want is a line number for each line in the report. Independant of the groupings. I believe this has to be done during the rendering of the report as it has to cross groups after all the report processing has been done. Here's a small sample of what they want it to look like.

Column A Column B Column C

1 Group 1 Header

2 Group 2 Header

3 Detail

4 Detail

5 Detail

6 Group 2 Footer

7 Group 1 Footer

The count can reset at the begging of each page, or continue, doesn't really matter. I'm trying everything I can think of.

Any ideas out there?

|||

If you add RowNumber("DSName") in the first column then it should work...

|||

Unfortunately, it's not that easy. Doing that, you get the total #f rows for each group on the group level headers. I need the count to be a straight rendered line number count and not be tied the underlying data set or grouping structure.

Line numbers in Report Builder

I have a report in which the user community would like line numbers associated with each row. Has anyone done this or have any ideas how to accomplish this?

Line Item Manufacturer
1 Mazda
2 Ford
3 BMW

ThanksThis seems like something you want to do in the sql query rather than the report. Not sure, exactly how...|||Since you drag elements into the design area and Report Builder is not based on a query, there is no way to create line item numbers within Report Builder.

Is that what you are saying?|||Hi,

You can use RunningValue function as the text value of the line number column.

If you want a numbering for rows within a dataset, you can use

=RunningValue(1, SUM, "Employees")

where Employees is the dataset name.

If you have groups in your reports, say one of the groups has a name "EmployeesByCity", then you can replace the last part of the RunningValue with this group name shown as below

=RunningValue(1, SUM, "EmployeesByCity")

You can check the below link for a sample

http://www.kodyaz.com/article.aspx?ArticleID=40

Eralper|||Thank you for your assistance. "RunningValue" is not available in Report Builder.|||

I've got a similar issue that RunningValue won't work for. Basically, what the business users want is a line number for each line in the report. Independant of the groupings. I believe this has to be done during the rendering of the report as it has to cross groups after all the report processing has been done. Here's a small sample of what they want it to look like.

Column A Column B Column C

1 Group 1 Header

2 Group 2 Header

3 Detail

4 Detail

5 Detail

6 Group 2 Footer

7 Group 1 Footer

The count can reset at the begging of each page, or continue, doesn't really matter. I'm trying everything I can think of.

Any ideas out there?

|||

If you add RowNumber("DSName") in the first column then it should work...

|||

Unfortunately, it's not that easy. Doing that, you get the total #f rows for each group on the group level headers. I need the count to be a straight rendered line number count and not be tied the underlying data set or grouping structure.

Limiting users to user sql server

Hello there
When i started using sql server i choose Windows authentication.
Now i need to move to sql server authentication so that not anyone can have
full access to Enterprice manager or the query anlyser
In fact i don't want that the administrator on the Windows 2000 server would
be the administrator on the sql server
What i need to do for this?
any help would be usefulYou can remove the administrators group from being able to log into SQL
Server. There is no changing to SQL authentication, you would change to
mixed mode which allows SQL and Windows authentication. But it sounds like
you are just wanting to restrict people in the admin group from accessing
and that is done by removing the admin groups from SQL.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:Og$rJBWxDHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> Hello there
> When i started using sql server i choose Windows authentication.
> Now i need to move to sql server authentication so that not anyone can

---

have

quote:

---

> full access to Enterprice manager or the query anlyser
> In fact i don't want that the administrator on the Windows 2000 server

---

would

quote:

---

> be the administrator on the sql server
> What i need to do for this?
> any help would be useful
>

---

|||Just remove the BUILTIN\Administrators login from SQL Server. But before
you do this make sure you know the sa password or have another NT account
that has sysadmin priviledge.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Limiting User Resources

I need to find a way to limit the resources that a user can hog on a SQL mac
hine. Is there any way to do this? I know that you can set priority level
to the SQL process, but we are actually wanting to limit a single user that
does mass updates periodica
lly throughout the day so that other users still get decent, if not stellar,
performance. Any ideas?
Thanks,
JoshJosh,
No, you can not throttle a user.
Perform the updates in smaller chucks by using SET ROWCOUNT and performing t
he update in a loop. Do not forget to use a unique where clause so that it s
kips the rows that were already updated in the next iteration.
Here is one article of many out on the web...
http://www.tek-tips.com/gfaqs.cfm/l...id/183/fid/3141
Norman
Norman

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien
Your specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegro ups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien
|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
David Gugick
Quest Software
www.quest.com
|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien
|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test
|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien
|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
David Gugick
Quest Software
www.quest.com
|||Yes you need to create an clustered index on it.
One more thing that I found about my last post is,
Suppose two users want to insert record and number of records are 2 in
the table when both of you checked it.so both of you will issue insert
commands. So first of them two should be succeeded. You have to put a
lock on the table.
so while you check you have to give lock hint of holdlock.
select count(*) from test (holdlock)
But then at a time only one user can insert into the table.
What is the reason for a table to limmit its number of records to some
number of rows.
Instead you can create a view to filter number of rows.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||The reason I am limiting is not a critical one. I just don't want some
of my customers to exceed a number of records I assign to them. If they
exceed by 1 or 2, it should be fine...
Once again, thanks for help.
Tascien
|||Ok in that case you dont need to put hold lock is you are ready to
accept 1 or 2 more records then decided. It will not create locks on
the table.
Regards
Amish

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
TascienYour specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegroups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
David Gugick
Quest Software
www.quest.com|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
David Gugick
Quest Software
www.quest.com|||Yes you need to create an clustered index on it.
One more thing that I found about my last post is,
Suppose two users want to insert record and number of records are 2 in
the table when both of you checked it.so both of you will issue insert
commands. So first of them two should be succeeded. You have to put a
lock on the table.
so while you check you have to give lock hint of holdlock.
select count(*) from test (holdlock)
But then at a time only one user can insert into the table.
What is the reason for a table to limmit its number of records to some
number of rows.
Instead you can create a view to filter number of rows.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||The reason I am limiting is not a critical one. I just don't want some
of my customers to exceed a number of records I assign to them. If they
exceed by 1 or 2, it should be fine...
Once again, thanks for help.
Tascien|||Ok in that case you dont need to put hold lock is you are ready to
accept 1 or 2 more records then decided. It will not create locks on
the table.
Regards
Amish

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
TascienYour specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegroups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
--
David Gugick
Quest Software
www.quest.com|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
--
David Gugick
Quest Software
www.quest.com

Limiting number of database connections per user

Hello,
I would like to find out if it is possible in SQL Server 2000 to limit the
number of concurrent database connections per user. I am new to SQL Server,
and not sure if it is possible to limit it through some sort of parameter.
Any help would be greatly appreciated.
Thank you,
DmitriyNo way to limit the number of concurrent database connections PER USER.
Use the user connections option to specify the maximum number of
simultaneous user connections allowed on Microsoft? SQL Server
use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
connections that your system allows
see "Setting Configuration Options" in BOL
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy|||Thank you for your quick response.
Spasibo,
Dima
"Aleksandar Grbic" wrote:
> No way to limit the number of concurrent database connections PER USER.
> Use the user connections option to specify the maximum number of
> simultaneous user connections allowed on Microsoft? SQL Server
> use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
> connections that your system allows
> see "Setting Configuration Options" in BOL
>
>
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Dmitriy" wrote:
>|||Hi
you can use the table "sysprocesses" to get the information abt the
connections to the database , and there by u can restrict each users
programatically
Renjith
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy

Limiting no of rows in a page

Hi All
We have requirement where a user restrict no of rows which can be displayed
on a page on a report in this report we will be showing invoices for the
check on the top and corresponding check at the bottom. Here a user can set
no of invoices to be displayed on a page. This will always be less or equal
to actual limit which can be displayed.
For example we have 15 invoices but limit set to show on a page is 6 then
report should display only 6 rows in 1st page and another 6 on second page
and remaining on 3rd page.
Can any body let me know how can we restrict no of rows to be displayed on
single page. and also show check at the bottom.
Thanks in advance
Thanks and regds
Rehan Mustafa KhanI have placed several examples of this on www.msbicentral.com Go to
downloads,Reporting Services, RDL
--
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
"Rehan Mustafa Khan" <RehanMustafaKhan@.discussions.microsoft.com> wrote in
message news:E46DEAC4-A036-4D6B-9052-C2218B2ED052@.microsoft.com...
> Hi All
> We have requirement where a user restrict no of rows which can be
displayed
> on a page on a report in this report we will be showing invoices for the
> check on the top and corresponding check at the bottom. Here a user can
set
> no of invoices to be displayed on a page. This will always be less or
equal
> to actual limit which can be displayed.
>
> For example we have 15 invoices but limit set to show on a page is 6 then
> report should display only 6 rows in 1st page and another 6 on second page
> and remaining on 3rd page.
>
> Can any body let me know how can we restrict no of rows to be displayed on
> single page. and also show check at the bottom.
>
> Thanks in advance
>
> Thanks and regds
> Rehan Mustafa Khan
>

Limiting Domain Admin access to SQL Server

How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
MattAdd only the trusted individuals to the sysadmin role, then remove
BUILTIN\Administrators from the sysadmin role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt Troska" <matt.troska@.co.mcleod.mn.us> wrote in message
news:01a701c3dae0$59f035f0$a601280a@.phx.gbl...
How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
Matt

Limited User Rights

Hi All,
How can I give the user view rights only?How can I give the user view rights only?I wouldn't grant them any other permissions.

-PatP|||I agree. But what command do I use to do that?|||I wouldn't grant them any other permissions.

-PatP

Don't you mean you would add the to the db_datareader database role?|||Make them members of the db_datareader and db_denydatawriter roles.