How do I add an AS400 system as a Linked server?

I have been reading for 2 hours and testing different way to create a linked
server and I am just not placing the right data in the correct fields... any
recommendations on what to read or where to find resources
What error are you getting?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:OXue2r9FHHA.1804@.TK2MSFTNGP02.phx.gbl...
>I have been reading for 2 hours and testing different way to create a
>linked server and I am just not placing the right data in the correct
>fields... any recommendations on what to read or where to find resources
>

How do I add an AS400 system as a Linked server?

I have been reading for 2 hours and testing different way to create a linked
server and I am just not placing the right data in the correct fields... any
recommendations on what to read or where to find resourcesWhat error are you getting?
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:OXue2r9FHHA.1804@.TK2MSFTNGP02.phx.gbl...
>I have been reading for 2 hours and testing different way to create a
>linked server and I am just not placing the right data in the correct
>fields... any recommendations on what to read or where to find resources
>

How do I add an AS400 system as a Linked server?

I have been reading for 2 hours and testing different way to create a linked
server and I am just not placing the right data in the correct fields... any
recommendations on what to read or where to find resourcesWhat error are you getting?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:OXue2r9FHHA.1804@.TK2MSFTNGP02.phx.gbl...
>I have been reading for 2 hours and testing different way to create a
>linked server and I am just not placing the right data in the correct
>fields... any recommendations on what to read or where to find resources
>sql

How do I access sql system stored procs from code

Hello:

Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!

ThanksOkay i am stupid again and did not do a google search. So after do this I found an excellent resource for this particular problem. I found it at:

http://www.ftponline.com/vsm/2003_01/magazine/columns/databasedesign/default.aspx

Shows how to access Table Metadata programmatically using C# and VB.Net AWESOME! Working on my own code generator and now I am finally getting to a point where I can start to finish it. All I needed was a way to generate stored procs from table metadata. Now I have the table metadata and a way to generate stored procs I am all set. AWESOME.

Thanks for those that read this post hope it helps you out in the future. Remeber a real programmer does not write code but writes code that generates code for him/her.|||Just the same as any other stored procedure...you need permissions to run the proc and the path to the proc, e.g master

How disable IDENTITY on column

Hello,

I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||

something to start with

use northwind

select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

ROLLBACK TRANSACTION
SELECT * FROM #ALLTABLES

rollback transaction

|||

I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.

You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.

Expand and a better answer can possibly be arrived at.

|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||

hey take a look at this

use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

it says 33 rows affected

|||Try this:

DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements

If you are sure that all the tables have identity columns you can use this:

sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"

How disable IDENTITY on column

Hello,

I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||

something to start with

use northwind

select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

ROLLBACK TRANSACTION
SELECT * FROM #ALLTABLES

rollback transaction

|||

I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.

You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.

Expand and a better answer can possibly be arrived at.

|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||

hey take a look at this

use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

it says 33 rows affected

|||Try this:

DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements

If you are sure that all the tables have identity columns you can use this:

sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"sql

How cursors work internally and what's the most efficient way?

Hello-
The system in question is on SQL Server 7.0/sp4.
I use a cursor to rebuild index (DBCC DBREINDEX). The database has size of around 57+ GB while the fragmentation causes the size to hover around 100 GB Mark (drive where data device exists is around 140 GB while the log device drive has 35 GB empty space as well... so space is not a problem).
What's happening is that previously when there were around 1600+ tables, the scheduled task run without a problem. However, for last two weeks the scheduled task ends in failure. I checked and found 2 tables with errors that I fixed with DBCC CHECKTABLE.
What puzzles me is that there were some (I think 2 tables) that got stuck as I try to run the same script through Query Analyzer that I run scheduled. However, when rebuilt indexes manually for those tables individually, it was done in no time. What kind of problem does this show?
For using the STATIC option, how much space should I have on tempdb? currently we have 10 GB.
Thanks a lot!
MZeeshanHi MZeeshan,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
According on your description, I am not quite clear what the problem is on
your side. I would like you to provide more information so that I can help
you narrow down this issue.
1. What is your accurate concern? As I understand, the job failed and table
occurred on your side, however, after you fix using DBCC CHECKTABLE, the
error always occurs next time on the same table. If I have misunderstood,
please feel free to let me know and describe it in detail.
2. If you DBCC DBREINDEX on the 2 tables in another job, did the same
problem occur again?
Also, I would like you to provide the following detailed information so
that I can perform further research on my side.
1. When the job failed, what is the error message? Please provide the
detailed error message.
2 Please provide the Sqldiag.txt using Sqldiag utility. You can send it to
me at v-yshao@.microsoft.com.
For more in formation regarding sqldiag utility, please refer to the
following article on SQL Server Books Online.
Topic: "Sqldiag Utility"
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Muhammad,
According to your description, I understand that when you performed DBCC
DBREINDEX individually for each table, there is not any problem. However,
when you run the scripts in a job, which performed the DBCC DBREINDEX
command for very small tables in a loop with cursor, the job didn't
complete in expected time. If I have misunderstood, please feel free to let
me know.
It is a hard work reviewing the scripts and Sqldiag.txt. According to the
error log, it seems that there are not any abnormal things occurring.
I would like you to provide the following information so that I can narrow
down this issue.
1. Can you please tell me how long it takes to finish running the SQL
scripts? I also would like to know your expected time.
2. Please try to perform the same script on another database, does the same
problem occur? Does the problem only occur on a specific database having
many small tables?
Please try to use the following script to see if it will decrease the
execute time.
//////////////////
CREATE PROCEDURE REINDEX_ALL
WITH RECOMPILE AS
SET NOCOUNT ON
DECLARE @.TableName varchar(300)
DECLARE @.DisplayString varchar(255)
/*----
--
--
Select all table names for REINDEX
----
--
--*/
DECLARE MainTableNamesCursor CURSOR FOR
SELECT o.name
FROM sysobjects o
WHERE o.type = 'U'
ORDER BY o.name
OPEN MainTableNamesCursor
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
SELECT @.DisplayString = 'REINDEX ' + @.TableName
SELECT @.DisplayString
EXEC ('DBCC DBREINDEX (' + @.TableName + ')')
FETCH NEXT FROM MainTableNamesCursor INTO @.TableName
END
END
DEALLOCATE MainTableNamesCursor
SET NOCOUNT OFF
//////////////////////
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

How Change FullText Path?

We needed to change the drive path for one of our SQL
Server Instances. All system databases have been changed,
but we are having trouble changing the FullText. We have
edited the Registry, but the FullText service still fails
with a path error when attempting to bring online within
Cluster Administrator. What are the steps to changing the
FullText path?
| Content-Class: urn:content-classes:message
| From: "michael [multnomah]" <anonymous@.discussions.microsoft.com>
| Sender: "michael [multnomah]" <anonymous@.discussions.microsoft.com>
| Subject: How Change FullText Path?
| Date: Tue, 5 Oct 2004 19:32:51 -0700
| Lines: 7
| Message-ID: <0f0501c4ab4c$c7058b30$a601280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| thread-index: AcSrTMcFUHcHTCYIRLe5RnswyCyFGQ==
| Newsgroups: microsoft.public.sqlserver.clustering
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.clustering:15145
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| We needed to change the drive path for one of our SQL
| Server Instances. All system databases have been changed,
| but we are having trouble changing the FullText. We have
| edited the Registry, but the FullText service still fails
| with a path error when attempting to bring online within
| Cluster Administrator. What are the steps to changing the
| FullText path?
|
################################################## ###########
Hello Michael,
Have you gone through the following KB artilce that discusses how to move
full text catalogs:
240867 How to move, copy, and back up full-text catalog folders and files
http://support.microsoft.com/?id=240867
If the above does not seem to help you, then you can go about rebuilding
the full text resources in your cluster environmnet using the steps in the
below KB artilce:
812666 How to recover a failed full-text search resource on a clustered
http://support.microsoft.com/?id=812666
HTH,
Shashank Pawar
SQL Server Support Engineer, Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.com
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>
|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
>

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.comSELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
>

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.comSELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > How can you tell the date of the last full database backup?
> >
> > Is there a system table that can be queried?
> >
> > Robert Alexander
> > Robert.Alexander@.cca-audit.com
> >
> >
> >
> >
>

How can you prevent the data changes message during editing a row

I just upsized my Access application (mail-order processing system) and
linked to SQL server 2000 via ODBC tables links from an Access 2000 front
end.
By doing so the standard optimistic locking of SQL server 2000 comes into
force and the option "lock edited record" on the Access Client gets ignored.
I cannot work with optimistic locking because if e.g. an order gets entered
in a table and e.g. a stock allocation job runs in the background that
updates the order header,
all the entered data will be lost (or must be copied to the clipboard and
edited ...).
Do you know how to overcome this problem - problems like this should
actually be quite common ...
Several years ago I used to work with a database called DataFlex.
This database had the best locking mechanism I every have experienced.
If one enters dat in a form the pulled record does not get locked (like
optimistic locking).
It only gets locked shortly before the update - it workes like this:
read a record from the table and display in the form
user modifies data in form
user clicks update button
record gets locked and reread into the record buffer
client program compares every form field with the record buffer if any data
was changed
if a field was changed the record buffer was overwritten with the data the
user entered
the record gets saved and unlocked
This way only the changed data fields get updated and changes of other users
do not get overwritten!
Is there a way on SQL server to do something similar or how do you deal with
this problem?
I also thought of writting an INSTEAD OF UPDATE trigger and check if any
column is different from the Inserted to the "REREAD" record buffer and appl
y
the above mentioned logic of DataFlex but this streches my SQL knowledge jus
t
a bit too far ...
May be you can help?
Any comments are appreciated!
Thanks in advance.
OliverBracket your user actions between BEGIN TRANS...COMMIT TRANS and to an
UPDATE WITH(ROWLOCK) setting a dateModified field to getDate()
immediately after BEGIN. All subsequent modifications will be queued
until you fire your update and commit. You could also do a check on the
dateModified field and simply warn the user if it has incremented since
they read the record, but doing a field-by-field comparison is more
work than is necessary.
The warning is almost always the preferable solution, since it is
dangerous to assume that such a collision should proceed in whichever
more or less arbitrary order when human intervention is not only
possible, but desirable.|||Oliver wrote:
> I just upsized my Access application (mail-order processing system)
> and linked to SQL server 2000 via ODBC tables links from an Access
> 2000 front end.
> By doing so the standard optimistic locking of SQL server 2000 comes
> into force and the option "lock edited record" on the Access Client
> gets ignored. I cannot work with optimistic locking because if e.g.
> an order gets entered in a table and e.g. a stock allocation job runs
> in the background that updates the order header,
> all the entered data will be lost (or must be copied to the clipboard
> and edited ...).
> Do you know how to overcome this problem - problems like this should
> actually be quite common ...
> Several years ago I used to work with a database called DataFlex.
> This database had the best locking mechanism I every have experienced.
> If one enters dat in a form the pulled record does not get locked
> (like optimistic locking).
> It only gets locked shortly before the update - it workes like this:
> read a record from the table and display in the form
> user modifies data in form
> user clicks update button
> record gets locked and reread into the record buffer
> client program compares every form field with the record buffer if
> any data was changed
> if a field was changed the record buffer was overwritten with the
> data the user entered
> the record gets saved and unlocked
> This way only the changed data fields get updated and changes of
> other users do not get overwritten!
> Is there a way on SQL server to do something similar or how do you
> deal with this problem?
> I also thought of writting an INSTEAD OF UPDATE trigger and check if
> any column is different from the Inserted to the "REREAD" record
> buffer and apply the above mentioned logic of DataFlex but this
> streches my SQL knowledge just a bit too far ...
> May be you can help?
> Any comments are appreciated!
> Thanks in advance.
> Oliver
Yes. This is easy. Add a TIMESTAMP column to each table where you need
this support. A timestamp column is not a date, but a column that SQL
Server automatically changes each time a row is updated. When you
initially query the row data, select the TIMESTAMP as well. When you
save the data from your stored procedure (ideally, you 'll be using
stored procedures), compare the TIMESTAMP you selected with the
timestamp currently in the row. If they are different, then you know the
data was changed in the interim and can raise an error and have the
client application automatically re-query the data. Your update
statement can look something like this:
Update dbo.MyTable
Set
Col1 = @.Col1,
Col2 = @.Col2
Where
ColPK = @.ColPK
and
timestamp = @.timestamp
If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
RAISERROR ...
Else
-- Everything is good to go
David Gugick - SQL Server MVP
Quest Software|||Hi David,
Thanks for your tips but this sounds like a lot of programming to overcome
a problem that actually is a server's job.
Raising an error message when data changed in the background can only be
useful if there is a user at the other end.
What happens if a program gets caught out by a user e.g. the user changes da
ta
during the time the program read the row to update one column?
You will have to program some code to get arround the problem for every
transaction you are trying to do in a job like stock allocation, release
orders for delivery... - that would be too much work for me as my applicatio
n
is big!
I think it would be much better if the server could check in an update
trigger if there was a concurrent update of columns and just updates the
column(s) that were changed by the current transaction. This way all changes
other transactions did will be kept and nobody has to decide which
data/changes to keep.
I am quite new to SQL server programming and do not know all the ins and
outs of trigger transaction programming.
Could you or somebody else suggest some code how to achieve this?
Thanking you in advance.
Oliver
"David Gugick" wrote:

> Oliver wrote:
> Yes. This is easy. Add a TIMESTAMP column to each table where you need
> this support. A timestamp column is not a date, but a column that SQL
> Server automatically changes each time a row is updated. When you
> initially query the row data, select the TIMESTAMP as well. When you
> save the data from your stored procedure (ideally, you 'll be using
> stored procedures), compare the TIMESTAMP you selected with the
> timestamp currently in the row. If they are different, then you know the
> data was changed in the interim and can raise an error and have the
> client application automatically re-query the data. Your update
> statement can look something like this:
> Update dbo.MyTable
> Set
> Col1 = @.Col1,
> Col2 = @.Col2
> Where
> ColPK = @.ColPK
> and
> timestamp = @.timestamp
> If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
> RAISERROR ...
> Else
> -- Everything is good to go
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Oliver wrote:
> Hi David,
> Thanks for your tips but this sounds like a lot of programming to
> overcome
> a problem that actually is a server's job.
> Raising an error message when data changed in the background can only
> be useful if there is a user at the other end.
> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
> You will have to program some code to get arround the problem for
> every transaction you are trying to do in a job like stock
> allocation, release orders for delivery... - that would be too much
> work for me as my application is big!
> I think it would be much better if the server could check in an update
> trigger if there was a concurrent update of columns and just updates
> the column(s) that were changed by the current transaction. This way
> all changes other transactions did will be kept and nobody has to
> decide which data/changes to keep.
> I am quite new to SQL server programming and do not know all the ins
> and
> outs of trigger transaction programming.
> Could you or somebody else suggest some code how to achieve this?
> Thanking you in advance.
> Oliver
I don't agree with your assessment. This is an application programming
issue and not one for the database to manage on its own. You're
suggesting that the server update a row that has been updated in the
interim by another process just because the columns being updated are
not the same. I would argue that there's no way for SQL Server to know
if changing a single column value in a row would somehow affect business
rules and know whether or not the proposed row changes are valid.
Many applications can deal with this scenario by assuming the last
update should be the most current. In that scenario, there is no
additional programming required. If you need to manage concurrency and
changes to a row by another session to avoid overwriting those changes,
you should use a timestamp. You pass the timestamp value with the other
column values to your stored procedure and the procedure does the work.
The application should be able to handle the condition where the row was
not updated because an error was raised and then requery the data and
inform the end-user. All your DML should be done in stored procedures.
In the case where there is no end-user, your application code should log
an error condition that can be managed manually in an interactive
fashion or it could requery the data, assuming this is something it can
work around.
For a nightly batch process, you could lock the entire table within your
transaction.

> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
I'm not sure what you are describing here. A change is a change.
Presumably, you have a stored procedure to manage each type of change to
your data. Maybe you can elaborate on this part a little more.
David Gugick - SQL Server MVP
Quest Software

How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECTLTRIM(RTRIM(routine_name)) as Save_Query
FROM[B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.
What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:

>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECTLTRIM(RTRIM(routine_name)) as Save_Query
>FROM[B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using and
>they have System Administrator as server roles.
>
|||I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
the system tables instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information about
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using and
> they have System Administrator as server roles.
|||"Mike Hodgson" wrote:

> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'

> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000

> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
[vbcol=seagreen]
> Hamish Laws wrote:
|||"Tibor Karaszi" wrote:

> I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.

> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
>
>

How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.This is a multi-part message in MIME format.
--010400070406000708090003
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:
>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECT LTRIM(RTRIM(routine_name)) as Save_Query
>FROM [B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using and
>they have System Administrator as server roles.
>
--010400070406000708090003
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>What OLE DB provider is "Darmstadtium</tt><tt>"? What kind of
server is server B? Microsoft SQL Server? If so, what version of SQL
Server? Can you query any object on the remote server? (Every login
to a SQL server should be able to read master.dbo.sysobjects - can you
successfully execute "SELECT * FROM B.master.dbo.sysobjects"?)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Hamish Laws wrote:
<blockquote cite="midA2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com"
type="cite">
<pre wrap="">We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.
</pre>
</blockquote>
</body>
</html>
--010400070406000708090003--|||I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
the system tables instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information about
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using and
> they have System Administrator as server roles.|||"Mike Hodgson" wrote:
> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'
> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000
> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
> Hamish Laws wrote:
> >We've working on a system that is being used to populate various tables in
> >databases on multiple servers.
> >
> >If the application is on Server 'A' and is trying to find information about
> >stored procedures on Server 'B' we're getting problems with the following
> >query
> >
> >SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> >FROM [B].[dbName].INFORMATION_SCHEMA.routines
> >ORDER BY routine_name
> >
> >
> >the error message is
> >OLE DB provider 'Darmstadtium' does not contain table
> >'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> >or the current user does not have permissions on that table.
> >
> >Does anybody have any ideas for getting this working?
> >There is a logon for Server B with the same logon that Server A is using and
> >they have System Administrator as server roles|||"Tibor Karaszi" wrote:
> I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.
> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> > We've working on a system that is being used to populate various tables in
> > databases on multiple servers.
> >
> > If the application is on Server 'A' and is trying to find information about
> > stored procedures on Server 'B' we're getting problems with the following
> > query
> >
> > SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> > FROM [B].[dbName].INFORMATION_SCHEMA.routines
> > ORDER BY routine_name
> >
> >
> > the error message is
> > OLE DB provider 'Darmstadtium' does not contain table
> > '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> > or the current user does not have permissions on that table.
> >
> > Does anybody have any ideas for getting this working?
> > There is a logon for Server B with the same logon that Server A is using and
> > they have System Administrator as server roles.
>
>

How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:

>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECT LTRIM(RTRIM(routine_name)) as Save_Query
>FROM [B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using an
d
>they have System Administrator as server roles.
>|||I think you will have a problem with the INFORMATION_SCHEMA views. The views
actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), he
nce your problem. Try
the system tables instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information abou
t
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exis
t
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using a
nd
> they have System Administrator as server roles.|||"Mike Hodgson" wrote:

> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'

> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000

> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
[vbcol=seagreen]
> Hamish Laws wrote:
>|||"Tibor Karaszi" wrote:

> I think you will have a problem with the INFORMATION_SCHEMA views. The vie
ws actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database),
hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.

> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
>
>

how can Unzip file text file using SSIS

Hi,

I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.

Run your favorite unzip utility (there are plenty of gzip-compatible archivers out there, including gzip itself) using Execute Process task.

how can it automatically generating a ordered number

hi,

i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.

as asp.net is new for me, i would have some simple problems to ask.

1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me

2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.

thanks

Rgds, universe

1. I suggest you can have two columns: one used to record the inserted data for the row, another for row ID. So you can create a table as following:

create table tbl_test(id int identity(1,1),RecDate smalldatetime default getdate(), Description varchar(200))
create table tbl_test1(id uniqueidentifier default newid(),RecDate smalldatetime default getdate(), Description varchar(200))

insert into tbl_test(description) select 'This is a test row'

insert into tbl_test1(description) select 'This is a test row'

2. Sorry I'm not clear about this issue

How can Iget database structure of Document management system

How can Iget database structure of Document management system ???
please help me........check out the link in my sig. There's a tool there that should help you figure out the structure of any database.

How can I use getdate() to be an input parameter in a sproc?

I am trying to write a sproc that automatically uses the system date (i.e. -
getdate()) as an input parameter. Even though it displays it does not beha
ve like an input parameter.Casey
Do you need the entire date down the minutes and seconds, in other words, an
exact snapshot
of the date?
If not, you can just refer to GETDATE() right within your procedure and
bypass the
parameter part.
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.|||Hi,
Use the below sample,
alter proc test_proc2
as
begin
declare @.to_day smalldatetime
set @.to_day = getdate()
select @.to_day
end
Incase if it is a must to have date as input parameter then,
alter proc test_proc2 @.to_day datetime = '01/01/1900'
as
begin
set @.to_day = getdate()
select @.to_day
end
Thanks
Hari
MCDBA
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.

How can i uninstall MDAC

i need to uninstall MDAC from my system. my operating system is windows2000 proffessional.
pls help me... its very urgent....Don't know which version of MDAC you're referring, but you can uninstall MDAC 9.0 by launching mdacrb.exe.

If you want to remove the MDAC 2.6/2.7 or so, you can use the Component Checker (available from msdn.microsoft.com) to remove it from the system, or use dasetup.exe /U which rolls back to the previous
version of MDAC.