changes to sp's

If I create a new sp in EM I have the date of creation; however, if I modify
it, it retains the original date. Is there any way of finding out the most
recent modification date of an sp inside sql server 2000?
Thanks for any help.
Bernie Yaeger> Is there any way of finding out the most recent modification date of an sp
> inside sql server 2000?
No.
You should be using a source control softwware for that.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:u05XzeFrFHA.2996@.tk2msftngp13.phx.gbl...
> If I create a new sp in EM I have the date of creation; however, if I
> modify it, it retains the original date. Is there any way of finding out
> the most recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>|||you can explore the sql server transaction logs
how ever its not an easy thing to do
you can however use the lumigent log explorer
or apex sql log
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Bernie Yaeger" wrote:

> If I create a new sp in EM I have the date of creation; however, if I modi
fy
> it, it retains the original date. Is there any way of finding out the mos
t
> recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>
>|||If you are deciplined about the use of comments in the SP header, then that
is easy to tell what has been done. If you checking your scripts into Visual
Source Safe, then you can generate scripts from the current database to a
local folder and do a fiel comparison against what is in the VSS project.
Also, you can use a file comparison tool like like WinMerge to perform the
file comparisons against folders that contain different versions of scripts.
You can configure to list only scripts and individual lines that are
different.
http://groups.google.com/group/micr...f9cfe0c46abfa76
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:u05XzeFrFHA.2996@.tk2msftngp13.phx.gbl...
> If I create a new sp in EM I have the date of creation; however, if I
> modify it, it retains the original date. Is there any way of finding out
> the most recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>

Changes to cloned database are affecting the original

I am trying to use SQL Express to support unit testing of functionality which requires database access.

I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.

Here's what I do today:

Before Test:

The original database is copied to a unique location in the temp directory

A unique database name is generated (GUID-based)

I connect to the new database file with:

Server=.\SQLEXPRESS;AttachDbFilename="Path\To\Temp\File";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true

During the test all database access is routed to the temporary database GuidDBName

After Test:

Close all active database connections and clear the connection pool

Connect to the master database and detach the database:

Server=.\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'

Delete the temporary file

When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.

Can anyone explain why this is happening and/or provide a solution?

Short version - I have no idea what's happening here, I can't reproduce this behavior based on the information you've provided.

Long version - Your connection strings have duplicate information in them and I'm guessing that part of the problem is that you're connecting to different databases that you think you are. I also have question about you're original database; you don't mention how your are opening the original database to discover that it has been modified. I'm guessing you're using Managment Studio, but I'll bet you're looking at the parent instance SQLEXPRESS and not the User Instance you're creating with the connection string you've provided. A User Instance is a completely separate instance of SQL Server.

The duplication you have is that Database= and Initial Catalog= are fundamentally the same thing. (Check out this BOL topic for the gorey details on connection string keywords in SNAC.) On top of that, you don't really need to use either of them when you use AttachDbFilename in combination with User Instance=True since SQL Express will automatically named the database with the file path.

I wrote a little test routine to try and cause this, but I couldn't get it to fail. Here is what I did if you want to compare it to your code solution:

static void Main(string[] args)

{

// Create a new copy of the database

string path1 = "C:\\Users\\mikewa\\Documents\\RANUTest\\Source\\foo.mdf";

string path2 = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo.mdf";

string path2log = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo_log.ldf";

try

{

File.Delete(path2);

File.Delete(path2log);

File.Copy(path1, path2);

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

// Attach the temp database to the User Instance

string connectionString = "Server=.\\SQLEXPRESS;AttachDbFilename=" + path2 + ";Integrated Security=True;User Instance=True";

using (SqlConnection cnn = new SqlConnection(connectionString))

{

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "CREATE TABLE dbo.tblFoo (myField int NULL)";

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cmd.ExecuteNonQuery();

cnn.Close();

}

SqlConnection.ClearAllPools();

// Now kill the temp database in SQL

string masterConnectionString = "Server=.\\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=True";

using (SqlConnection cnn = new SqlConnection(masterConnectionString))

{

SqlCommand cmd = new SqlCommand("sp_detach_db '" + path2 + "'");

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cnn.Open();

cmd.ExecuteNonQuery();

}

}

As you can see, I've stripped the extra keywords out of my connection string and just used the complete file path, represented by the variable 'path2' as the file name. This automatic naming happens automatically (hence the name) as part of attaching the file to a User Instance. I use the same variable later to detach the file. After I run this, I'm able to examine my source file and see that the modification does not exist (i.e. no table named tblFoo).

Check around to ensure that you don't have multiple copies of your original database laying around. If you're looking at the "original file" in Management Studio while connected to the parent instance, it's possible that you're actually looking at a different file than the one you're copying.

Mike