Active Tables

I have a live database. In that Database so many tables
and stored procedures are not using. I have to find those
and delete or move to a different database. Is there any
way in System tables (Or using profiler) or some other
method to find unwanted tables and SP? I mean. I want to
run a query to find last two months not accessed objects
(Tables and SP)Renny,
As you mentioned, Profiler is your friend here. You might want to check the
Entegra auditing utility (www.lumigent.com) as well.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Renny P" <rpannikodu@.aquarion.com> wrote in message
news:0b8a01c3aeaa$2b892f30$a301280a@.phx.gbl...
> I have a live database. In that Database so many tables
> and stored procedures are not using. I have to find those
> and delete or move to a different database. Is there any
> way in System tables (Or using profiler) or some other
> method to find unwanted tables and SP? I mean. I want to
> run a query to find last two months not accessed objects
> (Tables and SP)|||You can also have a look at SQLClean from www.lockwoodtech.com, which can
actually check the source code of your application for unused database
objects if you have it.
You might also find the following script useful:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=629
to look for columns that are used but have only one distinct value in them
and similar issues.
--
Jacco Schalkwijk
SQL Server MVP
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OphQq6qrDHA.1184@.TK2MSFTNGP10.phx.gbl...
> Renny,
> As you mentioned, Profiler is your friend here. You might want to check
the
> Entegra auditing utility (www.lumigent.com) as well.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Renny P" <rpannikodu@.aquarion.com> wrote in message
> news:0b8a01c3aeaa$2b892f30$a301280a@.phx.gbl...
> > I have a live database. In that Database so many tables
> > and stored procedures are not using. I have to find those
> > and delete or move to a different database. Is there any
> > way in System tables (Or using profiler) or some other
> > method to find unwanted tables and SP? I mean. I want to
> > run a query to find last two months not accessed objects
> > (Tables and SP)
>|||Hi all,
We have a few databases that we need to convert from LATIN1_GENERAL_BIN
to UNICODE. What are the steps involved in doing this?
Thanks!sql

Active Directory / Groups + MS SQL (yikes)

When configuring ms sql to permit authentication from users over a
domain. Is it possible to poll from system tables like sysusers /
sysmembers / sysgroups / sysprocesses / etc what active directory
group's a specific user is a member of once they are authenticated into
a ms sql server? I've been searching the newsgroups looking for a
solution but so far I'm 'unable' to find any solutions. Any input would
be greatly appreciated.
Thanks,
:)The list of groups that a user is a member of is not stored anywhere on the
SQL server. You could is the IS_MEMBER() function to determine if a user is
a
member of a specified group, but not list the groups the user is a member of
.
Although I know nothing about it, you could use the new CLR features of SQL
2005 along with ADSI to get the listing of groups a user is a member of
inside a function. In SQL 2000, you could use LDAP ADSI to get some
attributes out of AD into a rowset, but not group memberships; that attribut
e
is multi-valued.
"jebuskrust@.gmail.com" wrote:

> When configuring ms sql to permit authentication from users over a
> domain. Is it possible to poll from system tables like sysusers /
> sysmembers / sysgroups / sysprocesses / etc what active directory
> group's a specific user is a member of once they are authenticated into
> a ms sql server? I've been searching the newsgroups looking for a
> solution but so far I'm 'unable' to find any solutions. Any input would
> be greatly appreciated.
> Thanks,
> :)
>

Active Directory / Groups + MS SQL (yikes)

When configuring ms sql to permit authentication from users over a
domain. Is it possible to poll from system tables like sysusers /
sysmembers / sysgroups / etc what active directory group's a specific
user is a member of once they are authenticated into a ms sql server?
I've been searching the newsgroups looking for a solution but so far
I'm unavailable to find any solutions. Any input would be greatly
appreciated.
Thanks,
Hi.
Try wit the IS_MEMBER function
Regards
Antonio Soto
Solid Quality Learning
<jebuskrust@.gmail.com> escribi en el mensaje
news:1140634046.553729.223840@.f14g2000cwb.googlegroups.com...
> When configuring ms sql to permit authentication from users over a
> domain. Is it possible to poll from system tables like sysusers /
> sysmembers / sysgroups / etc what active directory group's a specific
> user is a member of once they are authenticated into a ms sql server?
> I've been searching the newsgroups looking for a solution but so far
> I'm unavailable to find any solutions. Any input would be greatly
> appreciated.
> Thanks,
>
>

Active Directory <-> SQL Integration ?

We have our user store in Active Directory. I am designing the data model
for some new applications, and a number of tables require fields such as
ApprovedBy, CreatedBy, etc. I want to be able to link these values back to
the Active Directory user store. I know I can just shove the AD user name i
n
there, but I want more. I want to have referential integrity between the tw
o
systems. Is this currently possible?
I'm going to assume it isn't possible, and if not is there a SQL Server wish
list/feedback email I can use to suggest this for a future product release?
I think this would be an extremely useful feature. (or if you could use SQL
Server as your Active Directory data store, that should solve the problem
also I believe, but I don't know if that is possible...).Hi
Check out the ADSI OLEDB provider.
http://msdn.microsoft.com/library/d...r />
_94fn.asp
If you can use check constraint that calls a function to implement RI, but
this sort of thing will probably prove prohibitably slow.
John
"Optikal" wrote:

> We have our user store in Active Directory. I am designing the data model
> for some new applications, and a number of tables require fields such as
> ApprovedBy, CreatedBy, etc. I want to be able to link these values back t
o
> the Active Directory user store. I know I can just shove the AD user name
in
> there, but I want more. I want to have referential integrity between the
two
> systems. Is this currently possible?
> I'm going to assume it isn't possible, and if not is there a SQL Server wi
sh
> list/feedback email I can use to suggest this for a future product release
?
> I think this would be an extremely useful feature. (or if you could use S
QL
> Server as your Active Directory data store, that should solve the problem
> also I believe, but I don't know if that is possible...).

Accumulating snapshot folder

Hi all,
I found that my testing server is accumulating shapshot folder in repldata. Everytime, we refresh the tables (snapshot publication), a new folder created without the old folders. And I found that there is at most 1 snapshot folder remain in production server. Any parameter to adjust the retention period of the snapshot folder? Thanks in advanceWhat version of SQL Server are you using?|||MSSQL2005 SP2|||Retention setting can be adjusted in publication property.

accumulating shapshot folder in repldata

Hi all,
I found that my testing server is accumulating shapshot folder in repldata.
Everytime, we refresh the tables (snapshot publication), a new folder created
without the old folders. And I found that there is at most 1 snapshot
folder remain in production server. Any parameter to adjust the retention
period of the snapshot folder? Thanks in advance
It is normal to create a date stamped snapshot folder within your main
publication snapshot folder, for example within here
C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\repldata\unc\Publisher_P2P1_P 2P1
These are normally cleaned up by one of the clean up jobs.
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
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:24DD103F-DFB5-4331-B04C-B2E3E9ECBB12@.microsoft.com...
> Hi all,
> I found that my testing server is accumulating shapshot folder in
> repldata.
> Everytime, we refresh the tables (snapshot publication), a new folder
> created
> without the old folders. And I found that there is at most 1 snapshot
> folder remain in production server. Any parameter to adjust the retention
> period of the snapshot folder? Thanks in advance
|||This should remain until the distribution retention period is reached (named
subscriber not yet initialized or anonymous subscribers enabled) or all
subscribers have been initialized. After that the cleanup agent will delete
the folder.
Paul Ibison
|||I found the distribution transaction retention is 1-12 hrs only and there is
2 snapshot folders generated more than 1 weeks ago. Any idea? Thanks a lot.
"Paul Ibison" wrote:

> This should remain until the distribution retention period is reached (named
> subscriber not yet initialized or anonymous subscribers enabled) or all
> subscribers have been initialized. After that the cleanup agent will delete
> the folder.
> Paul Ibison

accidentally populate tables into System databases/master database

Hi,

I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?

Thank you very much!

I would transfer the tables and data to the database where they 'should' be located, and then DELETE the ones in the master database.

|||

To move a table to another disk you have to
-create a secondary file group on an another location (or separate) of physical disk.
-drop the clustered index of the table and re-create it on that file group (Alter Table ... | MOVE TO { partition_scheme_name ( column_name ) | filegroup
| "default"}
- see Books Online).

This procedure can be use on large tables; if the size of your tables is not so big you can copy these tables with import/export wizard to a test db and copy backb in database with that filegroup made default.

|||

Thank you All,

I just deleted all the talbes were accidentally created in System/Master Database in sql server 2005. Sql server seems working fine.

Accidentally dropped these tables

I dropped these tables MSreplication_subscriptions and MSsubscription_agents
used for replication on the subscribing end.
How can I recreate them since its a system table.
From sp_MScreate_sub_tables
CREATE TABLE dbo.MSreplication_subscriptions
(
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NULL,
independent_agent bit NOT NULL,
subscription_type int NOT NULL,
distribution_agent sysname NULL,
time smalldatetime NOT NULL,
description nvarchar(255) NULL,
transaction_timestamp varbinary(16) NOT NULL,
-- SyncTran
update_mode tinyint NOT NULL,
agent_id binary(16) NULL,
subscription_guid binary(16) NULL,
subid binary(16) NULL,
immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
)
CREATE TABLE dbo.MSsubscription_agents
(
id int identity,
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscription_type int NOT NULL,
queue_id sysname NULL,
update_mode tinyint default 0 not null, -- 0 = read only, 1 =
sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
failover
failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
spid int NOT NULL,
login_time datetime NOT NULL,
allow_subscription_copy bit default 0 not null,
attach_state int default 0 not null, -- 0: not attached 1 attached but
not processed 2 attached and processed.
attach_version binary(16) default newid() not null,
last_sync_status int NULL, -- allow null for upgrade
last_sync_summary sysname NULL, -- allow null for upgrade
last_sync_time datetime NULL, -- allow null for upgrade
queue_server sysname NULL -- only used for MSMQ based updating
subscribers
)
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> I dropped these tables MSreplication_subscriptions and
MSsubscription_agents
> used for replication on the subscribing end.
> How can I recreate them since its a system table.
>
|||But I guess just creating those tables may not be enough.. Am i right ? I
know it has some data in there .. Will the agents still fail ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O6Qwki6XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> From sp_MScreate_sub_tables
> CREATE TABLE dbo.MSreplication_subscriptions
> (
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NULL,
> independent_agent bit NOT NULL,
> subscription_type int NOT NULL,
> distribution_agent sysname NULL,
> time smalldatetime NOT NULL,
> description nvarchar(255) NULL,
> transaction_timestamp varbinary(16) NOT NULL,
> -- SyncTran
> update_mode tinyint NOT NULL,
> agent_id binary(16) NULL,
> subscription_guid binary(16) NULL,
> subid binary(16) NULL,
> immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
> )
>
> CREATE TABLE dbo.MSsubscription_agents
> (
> id int identity,
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NOT NULL,
> subscription_type int NOT NULL,
> queue_id sysname NULL,
> update_mode tinyint default 0 not null, -- 0 = read only, 1 =
> sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
> failover
> failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
> spid int NOT NULL,
> login_time datetime NOT NULL,
> allow_subscription_copy bit default 0 not null,
> attach_state int default 0 not null, -- 0: not attached 1 attached but
> not processed 2 attached and processed.
> attach_version binary(16) default newid() not null,
> last_sync_status int NULL, -- allow null for upgrade
> last_sync_summary sysname NULL, -- allow null for upgrade
> last_sync_time datetime NULL, -- allow null for upgrade
> queue_server sysname NULL -- only used for MSMQ based updating
> subscribers
> )
> --
> 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
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> MSsubscription_agents
>

Access-SQL Serv Autonumber

I have an Access database that was upsized. Access F/E, S2k back. I cannot
update tables that I had to create an index for the upsize. All my queries
come back with "cannot insert null" into index field. Access calls it
Autonumber and we never have to do anything else.
I'm gusssing there is a constraint problem ? Where should I start looking.
Its a primary key field, unique, indenty=1. I thought that defined it as
Autonumber.
Help !You need to set the datatype on the SQL Server to Int. Then check the
checkbox that says 'Identity' field in the Manage indexes dialog.
"Stuart" wrote:

> I have an Access database that was upsized. Access F/E, S2k back. I cannot
> update tables that I had to create an index for the upsize. All my queries
> come back with "cannot insert null" into index field. Access calls it
> Autonumber and we never have to do anything else.
> I'm gusssing there is a constraint problem ? Where should I start looking.
> Its a primary key field, unique, indenty=1. I thought that defined it as
> Autonumber.
> Help !

Access-SQL Serv Autonumber

I have an Access database that was upsized. Access F/E, S2k back. I cannot
update tables that I had to create an index for the upsize. All my queries
come back with "cannot insert null" into index field. Access calls it
Autonumber and we never have to do anything else.
I'm gusssing there is a constraint problem ? Where should I start looking.
Its a primary key field, unique, indenty=1. I thought that defined it as
Autonumber.
Help !
You need to set the datatype on the SQL Server to Int. Then check the
checkbox that says 'Identity' field in the Manage indexes dialog.
"Stuart" wrote:

> I have an Access database that was upsized. Access F/E, S2k back. I cannot
> update tables that I had to create an index for the upsize. All my queries
> come back with "cannot insert null" into index field. Access calls it
> Autonumber and we never have to do anything else.
> I'm gusssing there is a constraint problem ? Where should I start looking.
> Its a primary key field, unique, indenty=1. I thought that defined it as
> Autonumber.
> Help !

accessing to an oracle 10 linked server

Hello,
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
Thx
Not sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:

>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx

accessing to an oracle 10 linked server

Hello,
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
ThxNot sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:

>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx

Accessing the name of all the related tables name

Hi Everyone,

I would like to ask a question, Is there any way or tool available in the sql server 2000, that tells the user, how many tables contains the same column name (such as Col_N, is a primary key and I want to know all the table names that have the same Foreign key (COL_N)).

If anybody knows that, pls help me.

Thanking you

Gaurav

Using SQL 2000, you may wish to examine the Information_Schema views for a list of which tables contain a particular column, such as:

SELECT Table_Name
FROM Information_Schema.Columns
WHERE Column_Name = @.x

Accessing tables in another server!

hi this might seem simple to all the techies out there but pls bear with me cos i am a novice to SQL Server 2000.

Q: Is it possible to create a trigger on a table in a current server(For eg. Server1) that can select/insert/delete/update a table in ANOTHER SERVER(Server2)?

I was exploring options of using a distributed partitioned view but i am still very much lost...Use a linked server. See BOL for details.|||If i used a linked server is a distributed partitioned view still necessary? can i just use a four-part-name in my queries to modify data? eg. servername.dbname.dbo.tablename|||Can someone just list out the steps briefly for me? Or just tell me if i am right..

1. create linked server(eg.server2) (I have done that)

2. create distributed views on server1

-creating the distributed views i understand that i can use OPENDATASOURCE or OPENROWSET or just a four-partname right?

After creating the distributed views can i use a four-part name to make references to the remote databases?Can i also insert to those databases?

Accessing Tables From Another Server

Hello All.

I need your advise on how I could access table from another server.

Let me explain what I am doing. Currently, I have 2 servers running parallel to one another other each loading from the same set of data into its own database in the night.

I would like to write a stored procedure to report the number of records from selected tables from the 2 servers to see if they match in records loaded. Thereafter, email me the results (I have already settle the sending of email part).

Have picked up some tips in this forum to come up with the below statements and it works fine for me: -

use AABBCC (database name)
select Server='Server 009', name, rows, SystemDate=getdate() from sysindexes
where name in ('invoice_line', 'invoice_line_import', 'sales_order_line', 'sales_delivery_line','Cust_dely_reliabity','sales _order_proc_time','AU1016','NZ_Data')
order by name

Result: -
Server Table Name Rows

Server 002 AU1016 0
Server 002 Cust_Dely_Reliabity 5017342
Server 002 invoice_line 4397800
Server 002 invoice_line_import 12120
Server 002 NZ_Data 649745
Server 002 Sales_Delivery_Line 5323875
Server 002 Sales_Order_Line 5356989
Server 002 Sales_order_proc_time 3651362

Instead of running the above scipt or stored procedure from 2 servers and received 2 email (one from server 002 & one from server 009), I would like to access the other server within the same stored procedure.

I have used sp_addlinkedserver (may not be the correct method) and <servername>.<databasename>.dbo.sysindexes (doesn't work)

Please help. Thanks a million.

The desired result could be:

Server 002 ....
Server 002 ....
.
.

Server 009 .... (result from the 2nd server)
Server 009 ....
.
.

Best regardsOK...WHY doesn't it work...what's the error..

And did you use sp_addlinkedsrvlogin?|||Originally posted by Brett Kaiser
OK...WHY doesn't it work...what's the error..

And did you use sp_addlinkedsrvlogin?

Hello Brett.

Thanks for replying. There is no error. I just want to know if I could access tables of another server within the same stored procedures. Instead of running the above script twice and receive 2 emails, I would like to run the script within one SP so I could compare the results in one glance.

Thanks again.

Accessing System Tables (books online)

I have taken this comment from the SQL Server 2005 Books Online documentation but am having problems accessing the system tables (elsewhere it is stated that there is NO modification of system views, etc)

"to privileged users that can either access system tables over the DAC port or directly access database files"

How does one access the system tables over the DAC port? I have no issues connecting to the DAC port in admin mode? Is this an error within the documentation?
you can simply query the system tables over DAC connection which is otherwise not allowed. Here is one example:

select * from sys.sysprivs this succeeds on DAC but fails on non-DAC connection as

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.sysprivs'.

Thanks

|||

But, I am still having problems accessing the obfuscated text of an encrypted stored procedure. Following comment is from Books Online for CREATE PROCEDURE statement:

“ENCRYPTION

Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files.”

How could one retrieve the obfuscated text over the DAC port? In which system table is the text of the stored procedures stored? Could you please provide a query?

Accessing System Tables (books online)

I have taken this comment from the SQL Server 2005 Books Online documentation but am having problems accessing the system tables (elsewhere it is stated that there is NO modification of system views, etc)

"to privileged users that can either access system tables over the DAC port or directly access database files"

How does one access the system tables over the DAC port? I have no issues connecting to the DAC port in admin mode? Is this an error within the documentation?you can simply query the system tables over DAC connection which is otherwise not allowed. Here is one example:

select * from sys.sysprivs this succeeds on DAC but fails on non-DAC connection as

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.sysprivs'.

Thanks

|||

But, I am still having problems accessing the obfuscated text of an encrypted stored procedure. Following comment is from Books Online for CREATE PROCEDURE statement:

“ENCRYPTION

Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files.”

How could one retrieve the obfuscated text over the DAC port? In which system table is the text of the stored procedures stored? Could you please provide a query?

accessing sql7 database using ADO and ASP web page.

Hello all!

I am kinda new to using sql 7.0 and am trying to set up a web page that will access a sql7 database. I have a database with several tables in it created on my machine. My asp page uses a connection string that looks like:

"strConnect = "Provider=SQLOLEDB; PersistantSecurity Info= False; User ID = sa; password = 1234; Initial Catalog=equip;Initial file Name =c:\mssql7\data\tracking_data.mdf""

When I try to load this page my web browser displays the "page cannot be displayed error" and shows an error type of

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Device activation error. The physical file name 'c:\mssql7\data\tracking_data.mdf' may be incorrect.
/tracking/rptsvrc.asp, line 22"

Line 22 of the page rptsvrc.asp contains the code:
"objCommand.ActiveConnection = strConnect"
I am at a lose as to why this doesn't work. The file name and path are correct and they do exist.

ANy suggestions here?

Thanks CVC505Your connection string should be something like this:

"provider=SQLOLEDB;server=SQLServerName;uid=userid; password=password;database=DatabaseName;Trusted_Co nnection=No"

DatabaseName points to the logical db name not the filename.

Hope this helps...|||Thanks for the suggestion but it still does not work. I have changed my programs to that list 23 reads:

objConn.Open "Provider=SQLOLEDB; Server=rick2; User ID = sa; Database = equip; Trusted_connection = no"

Where rick2 is the server name ( I changed servers for the weekend)
equip is the database name as it appears in the EM list.

and line 24 is now:
objRS.Open "user1", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

where user1 is the table name in the equip database.

When I try to make the connection now I get an error that says:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'user1'.
/tracking/rptsvrc.asp, line 24

This has me stumped because it will work about once then quit.
One other thing I have noticed; when I created this database in the EM, I had named it Tracking ( In the data subdirectory the file is named tracking_data.mdf). The first time I ran the program the name in the EM changed to equip. ( still is named Tracking_data.mdf in the dat subdirectory). I am so new to this I don't have a clue what to look for.
cvc505|||Your connection seems to be working now but it is not finding the table user1.

Try changing you recordset open to a simple query:

'SELECT * FROM syscolumns'

See if that returns anything.

You can also try prefixing user1 with its owner. I.E. dbo.user1

Accessing SQL Server 2005 stored procedures from Visual Studio.NET

Hello,
What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment.

Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table.

My guess is that there is security involved, if so, what do I set on the new server?
If I'm wrong, and it's something else entirely, please advise.

Thanks!

See Books Online for answers on permission required by all database operations. For example, the command to create a table is CREATE TABLE - the Permission section of the article tells you what permission you need to have to be able to perform this operation.

Thanks
Laurentiu

Accessing SQL data from an ACCESS app...

I have just imported into SQL server all of my Access 2000 tables. In my
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a[vbcol=seagreen]
> Server
>|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()' where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a[vbcol=seagreen]
>|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Connect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.

> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the[vbcol=seagreen]
> and
> and
> get
>