Robert L. Stewart
rl_stewart at highstream.net
Tue Feb 28 13:09:10 CST 2006
Arthur,
To answer a little more of your question the other day, here is the SQl
statement that will give you all the stored proces and functions in SQL 2005.
Sorry, not ime for it in 2000. It will also give you the text of the
proc/function.
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
SPECIFIC_NAME, ROUTINE_CATALOG,
ROUTINE_SCHEMA, ROUTINE_NAME,
ROUTINE_TYPE, DATA_TYPE,
CHARACTER_SET_NAME, NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, ROUTINE_BODY,
ROUTINE_DEFINITION, IS_DETERMINISTIC,
SQL_DATA_ACCESS, IS_NULL_CALL,
SCHEMA_LEVEL_ROUTINE, MAX_DYNAMIC_RESULT_SETS,
IS_USER_DEFINED_CAST, IS_IMPLICITLY_INVOCABLE,
CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge
Quoting dba-sqlserver-request at databaseadvisors.com:
> Send dba-SQLServer mailing list submissions to
> dba-sqlserver at databaseadvisors.com
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> or, via email, send a message with subject or body 'help' to
> dba-sqlserver-request at databaseadvisors.com
>
> You can reach the person managing the list at
> dba-sqlserver-owner at databaseadvisors.com
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of dba-SQLServer digest..."
>
>
> Today's Topics:
>
> 1. Re: SELECT INTO question (Francisco Tapia)
> 2. HELP!!! sQL SERVER WON'T INSTALL (Kath Pelletti)
> 3. Wend Statement Not Allowed (Darren HALL)
> 4. Re: HELP!!! sQL SERVER WON'T INSTALL (Francisco Tapia)
> 5. Re: Wend Statement Not Allowed (Francisco Tapia)
> 6. Re: SQL Server Database Replication Between Two Servers
> (Francisco Tapia)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Mon, 27 Feb 2006 21:27:40 -0800
> From: "Francisco Tapia" <fhtapia at gmail.com>
> Subject: Re: [dba-SQLServer] SELECT INTO question
> To: dba-sqlserver at databaseadvisors.com
> Message-ID:
> <b874372a0602272127m1d52ddf3h794c8d37a8f144a3 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Typically you'd use it by saying something like,
>
> SELECT * INTO tblNewTableName From tblOldTableName where 1 = 0. This copies
> everything but the indexes....
>
> This method works great for the lazy typist out there who don't like to
> Write out the fields of their Temp Tables, as you know writing to a temp
> table w/ the where clause instead of explcitly defining it first or the
> method above, can cause a temporary lock while the table is created as it
> waits for the data to be retreived....
>
>
>
> On 2/27/06, David McAfee <dmcafee at pacbell.net> wrote:
> >
> > >From SQL Help
> >
> > INTO Clause
> > Creates a new table and inserts the resulting rows from the query into it.
> >
> > The user executing a SELECT statement with the INTO clause must have
> > CREATE
> > TABLE permission in the destination database. SELECT...INTO cannot be used
> > with the COMPUTE. For more information, see Transactions and Explicit
> > Transactions.
> >
> > You can use SELECT...INTO to create an identical table definition
> > (different
> > table name) with no data by having a FALSE condition in the WHERE clause.
> >
> >
> > But it seems to error each time for me, no matter how I use it.
> >
> > D
> >
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Susan
> > Harkins
> > Sent: Monday, February 27, 2006 3:07 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] SELECT INTO question
> >
> >
> > Not INSERT INTO, SELECT INTO...
> >
> > SELECT INTO creates a new table:
> >
> > SELECT * INTO newtable
> > FROM sourcetable
> >
> > In Jet, if newtable exists, the statement writes over the existing
> > instance
> > of newtable. In SQL Server Express, the statement fails with an error that
> > newtable already exists. I'm wondering which way SQL Server goes.
> >
> > Susan H.
> >
> > Sorry I read that wrong. In SQL 2000, if the correct data types are
> > selected, it will simply insert the values as new records:
> >
> >
> > INSERT INTO tblTest SELECT 'Test3' (table tblTest only has one field, a
> > char(10) )
> >
> >
> > If the table does not exist, the table will be created (this is what I was
> > referring to in my last post).
> >
> > If the table already exists but the number or columns are different or the
> > types don't match, then the user will get an error.
> >
> > David
> >
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of David
> > McAfee
> > Sent: Monday, February 27, 2006 2:51 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] SELECT INTO question
> >
> >
> > SQL Server 2000 works like access. Some coworkers like using this method.
> > I
> > don't.
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Susan
> > Harkins
> > Sent: Monday, February 27, 2006 2:40 PM
> > To: SQLList
> > Subject: [dba-SQLServer] SELECT INTO question
> >
> >
> > In Jet SQL, the SELECT INTO statement will overwrite an existing table if
> > the target table already exists. I just checked SQL Server Express and it
> > did NOT overwrite an existing table -- the statement failed with an error.
> > I
> > uninstalled SQL Server awhile back, so I can't check it -- can anybody
> > tell
> > me how SQL Server responds?
> >
> > Susan H.
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> -Francisco
> http://pcthis.blogspot.com |PC news with out the jargon!
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> ------------------------------
>
> Message: 2
> Date: Tue, 28 Feb 2006 20:14:03 +1100
> From: "Kath Pelletti" <KP at sdsonline.net>
> Subject: [dba-SQLServer] HELP!!! sQL SERVER WON'T INSTALL
> To: <dba-sqlserver at databaseadvisors.com>
> Message-ID: <001001c63c47$52d4a510$6401a8c0 at user>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Anyone help with this error?
>
> I have installed MS Visual Studio and then when trying top install SQL Server
> 2005, it does a system configuration check and gives me an error which
> prevents the install going through successfully.
>
> Error is:
>
> - COM Plus Catalog Requirement (Warning)
>
> Messages
>
> ? COM Plus Catalog Requirement
>
>
> If SQL Server Setup fails, Setup will roll back the installation but may not
> remove all .manifest files. The workaround is to rename the files and then
> rerun Setup. For more information, see How to: Work Around COM+ Check Failure
> in SQL Server Setup."
>
>
>
>
>
> I have looked thru help (and googled) and followed instructions there (to
> reinstall Component services snap in, and reinstall com) and I've done
> evrything suggested..
>
>
>
> But no luck - - getting desperate after 8 hours of this today. I even
> restored to a ghosted image and re-installed everything new only to get this
> again.
>
>
>
>
>
> Kath Pelletti
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 1 Mar 2006 00:22:00 +1100
> From: "Darren HALL" <darrend at nimble.com.au>
> Subject: [dba-SQLServer] Wend Statement Not Allowed
> To: "dba-SQL Server" <dba-sqlserver at databaseadvisors.com>
> Message-ID:
> <20060228132205.DLBH16720.omta01sl.mx.bigpond.com at DENZILLAP>
> Content-Type: text/plain; charset="us-ascii"
>
> Hello all
> In reporting services if I run the code below (from Lambert over at AccessD)
> I
> get the error message...
> "Wend statements are no longer supported . Use "End While" statements instead
>
> So If I replace wend with End while I don't get the desired result
>
> The desired result is
> From..... .C:\Program Files\Some Folder\some cool file.dll
> I want.....some cool file.dll
> But
> I am getting.....C:\Program Files\Some Folder
>
> any suggestions??
> Many thanks
>
> Darren
>
>
> Code Start>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> Function GetPath(aPath) As String
> Dim foo As Integer, aSlash As Integer
> aSlash = 0
> foo = InStr(aPath, "\")
> While (foo > 0)
> aSlash = foo
> foo = InStr(aSlash + 1, aPath, "\")
> end while
> If aSlash > 0 Then
> GetPath = Left$(aPath, aSlash)
> Else
> GetPath = aPath
> End If
> End Function
>
>
> ------------------------------
>
> Message: 4
> Date: Tue, 28 Feb 2006 09:14:58 -0800
> From: "Francisco Tapia" <fhtapia at gmail.com>
> Subject: Re: [dba-SQLServer] HELP!!! sQL SERVER WON'T INSTALL
> To: dba-sqlserver at databaseadvisors.com
> Message-ID:
> <b874372a0602280914pcfe5d7fjd98c0547b816f4b0 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> the following may not help a lot, but...
> visit vmware.com and download their "free" vmserver, next install a clean OS
> and retry your VS2005/SS2005. There may be other conflicts with your
> exisiting environment. are you doing the full version or the express?
>
> --
> Francisco
>
> On 2/28/06, Kath Pelletti <KP at sdsonline.net> wrote:
> >
> > Anyone help with this error?
> >
> > I have installed MS Visual Studio and then when trying top install SQL
> > Server 2005, it does a system configuration check and gives me an error
> > which prevents the install going through successfully.
> >
> > Error is:
> >
> > - COM Plus Catalog Requirement (Warning)
> >
> > Messages
> >
> > ? COM Plus Catalog Requirement
> >
> >
> > If SQL Server Setup fails, Setup will roll back the installation but may
> > not remove all .manifest files. The workaround is to rename the files and
> > then rerun Setup. For more information, see How to: Work Around COM+ Check
> > Failure in SQL Server Setup."
> >
> >
> >
> >
> >
> > I have looked thru help (and googled) and followed instructions there (to
> > reinstall Component services snap in, and reinstall com) and I've done
> > evrything suggested..
> >
> >
> >
> > But no luck - - getting desperate after 8 hours of this today. I even
> > restored to a ghosted image and re-installed everything new only to get
> this
> > again.
> >
> >
> >
> >
> >
> > Kath Pelletti
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> -Francisco
> http://pcthis.blogspot.com |PC news with out the jargon!
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> ------------------------------
>
> Message: 5
> Date: Tue, 28 Feb 2006 09:17:48 -0800
> From: "Francisco Tapia" <fhtapia at gmail.com>
> Subject: Re: [dba-SQLServer] Wend Statement Not Allowed
> To: dba-sqlserver at databaseadvisors.com
> Message-ID:
> <b874372a0602280917r2038f8c2v534ea27ecb515991 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> in RS where are you placing this code?
>
> On 2/28/06, Darren HALL <darrend at nimble.com.au> wrote:
> >
> > Hello all
> > In reporting services if I run the code below (from Lambert over at
> > AccessD) I
> > get the error message...
> > "Wend statements are no longer supported . Use "End While" statements
> > instead
> >
> > So If I replace wend with End while I don't get the desired result
> >
> > The desired result is
> > From..... .C:\Program Files\Some Folder\some cool file.dll
> > I want.....some cool file.dll
> > But
> > I am getting.....C:\Program Files\Some Folder
> >
> > any suggestions??
> > Many thanks
> >
> > Darren
> >
> >
> > Code Start>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >
> > Function GetPath(aPath) As String
> > Dim foo As Integer, aSlash As Integer
> > aSlash = 0
> > foo = InStr(aPath, "\")
> > While (foo > 0)
> > aSlash = foo
> > foo = InStr(aSlash + 1, aPath, "\")
> > end while
> > If aSlash > 0 Then
> > GetPath = Left$(aPath, aSlash)
> > Else
> > GetPath = aPath
> > End If
> > End Function
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> -Francisco
> http://pcthis.blogspot.com |PC news with out the jargon!
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> ------------------------------
>
> Message: 6
> Date: Tue, 28 Feb 2006 09:20:21 -0800
> From: "Francisco Tapia" <fhtapia at gmail.com>
> Subject: Re: [dba-SQLServer] SQL Server Database Replication Between
> Two Servers
> To: dba-sqlserver at databaseadvisors.com
> Message-ID:
> <b874372a0602280920j7b4386f2ga7fb33e193b22f32 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> Paul,
> If you can upgrade the servers to 2005 (do this first in dev), then you
> can utilize database mirroring wich is much easier to setup than replication
> and seems to handle collisions a lot better.
>
>
> On 2/26/06, Arthur Fuller <artful at rogers.com> wrote:
> >
> > I've been a bit tardy replying to messages on this list due to work-load,
> > but if you haven't already had a satisfactory answer, try using a trigger.
> > You will have to reference the other database explicitly of course, using
> > the standard dot notation: ServerName.DatabaseName.TableName.
> > HTH,
> > Arthur
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
> > Hartland
> > Sent: January 31, 2006 7:07 AM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: [dba-SQLServer] SQL Server Database Replication Between Two
> > Servers
> > Importance: High
> >
> > To all,
> > We have two servers (for the purpose of security lets call them SRV_1
> > and SRV_2) which both have a copy of SQL Server 2000 on them, onto which
> > I have placed a test database (Dbase_Rep). When a user updates the
> > database on SRV_1 I need the changes to also be made on SRV_2 either
> > instantly or at a desired time interval. I know this can be done and
> > have had a quick play and almost got it there.
> > Does anyone have an idiot proof step by step guide starting from scratch
> > or know where I can get one from.
> > Thanks in advance for any help on this.
> >
> > PAUL HARTLAND
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> -Francisco
> http://pcthis.blogspot.com |PC news with out the jargon!
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> ------------------------------
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>
>
> End of dba-SQLServer Digest, Vol 36, Issue 26
> *********************************************
>