field mapping

Hi all,
I have written many stored procedures in our database to import data from
another database. Now I need to create a report that lists the source table
and column names and their corresponding destination table and column names.
I think it will be difficult and time-consuming for me to open each
procedure and copy this information manually. Are there any procedures or
queries that can help me in generating this report.
Thanks in advance.Hi,
maybe this will help you ...
SELECT OBJECT_NAME(id) AS Source, OBJECT_NAME(depid) AS Uses
FROM dbo.sysDepends WHERE id = OBJECT_ID('proc_sys_InsertPropertyValue'
)
All dependencies of objects are stored in the relation dbo.sysdepends!
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htmsql

few questions about sql server MSDE

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

See my response to your identical posting in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

few questions about MSDE...

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

MSDE has been replaced with SQL Server 2005 Express.

For answers to your questions, as well as a source for tutorials, refer to Books Online.

SQL Server 2005 Express
http://msdn2.microsoft.com/en-us/sql/aa336346.aspx

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||pls if u don't know the answer then don't reply any post ... and thanks for googling for me|||

Some times it is not bad approach to provide the link which answers all your qurestions. Arnie posted all the MSDN URLs, you need not google it. If you read these MSDN you will get answer for all your questions.

|||here we come to get answers.. ofcourse after going thru whole book every1 will get answer and if we have to go thru books then there is no point to come on such platforms. i put that question on two forums and this person asked me on other forum that i shouldn't post here .. i feel convinient to put on different so i could have early and better response. i don't think so any1 should mind on that atleast.
i went on these links but .. anyhow here i don't think so other should ask what to do and what not.. and if there is some question of any1 then that person shouldn't be forwarded to books atleast he can tell me chapter anyhow thanks m disappointed|||

Muhammad,

You have to understand that this forum is simply a medium, most often an unnecessary medium, where you'll find many patterns in responses. For example, the higher the posting count the reponder has, the more likely they will reply with links.

I share an equal frustration with you on not receiving a direct response here. I have found only 1 use for these forums and that is to post actual code, when I encounter a problem, simply for a second or third set of eyes. Outside of that, you'll find no real experts here and you'll discover that this forum has developed into an automated link service.

Just my twist on it,

Adamus

|||no man actually i don't agree with you. here i got answers most of the time and correct and to the point information. all fingers are not same. every person has his/her own approach. its very good platform. i m just waiting for answer here, not references or comments or advises .. i hope you got my point

i still say thanks to that person who try to answer my question but in fact i don't like stupid advises or comments.

i didnt mean to hurt any1. sorry if anything of mine hurt any1.

Feed stored procedure with SELECT resultset

I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
about 50 input parameters. PROC2 is the main procedure that does some
data modifications and afterwards calls PROC1 using an EXECUTE
statement.

The input parameter values for PROC1 are stored in a table in my
database. What I like to do is passing those values to PROC1 using a
SELECT statement. Currently, all 50 parameters are read and stored in
a variable, and afterwards they are passed to PROC1 using:

EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50

Since it is a lot of code declaring and assigning 50 variables, I was
wondering if there is a possibility to run a statement like:

EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)

Any help on this is greatly appreciated!On 21 Oct 2004 07:19:02 -0700, Dieter Gasser wrote:

> I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
> about 50 input parameters. PROC2 is the main procedure that does some
> data modifications and afterwards calls PROC1 using an EXECUTE
> statement.
> The input parameter values for PROC1 are stored in a table in my
> database. What I like to do is passing those values to PROC1 using a
> SELECT statement. Currently, all 50 parameters are read and stored in
> a variable, and afterwards they are passed to PROC1 using:
> EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50
> Since it is a lot of code declaring and assigning 50 variables, I was
> wondering if there is a possibility to run a statement like:
> EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)
> Any help on this is greatly appreciated!

You could build up a dynamic SQL statement and execute it that way, but I
think it would be vastly better if your spPROC1 read its inputs to be in a
table, rather than as parameters.

Feature Request: 2005 Beta 2

Greetings,
I have a feature request for the Enterprise Studio.
We have about 500+ stored procedures in our CRM application and opening up
Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at a
huge list of stored procedures and find the one I'm specifically looking
for. Granted, in 2005 there is a "filter" it is rather unintuitive.
I'd like to request the abililty to add my own "folder" to the list where I
can create categories to group my stored procedures. for example, I can
create a "Invoices" group where all my invoice related SP's go, and a
"Policies" group where all my policies related SP's will go. In addition to
this, we have more tables than SPs, so we should be able to group any
object, Table, SP, View, etc.
Not sure if you've considered this already but the filtering isn't as
intuitive. It requires me to have to constantly enter in new filters to
filter the list before I can browse around. I'd like to browse directly to
what I'm looking for.
Thanks,
Shawn
Hi Shawn,
Have you considered using Schemas for this? (Introduced in SQL Server 2005
also).
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||What are schemas in this context? after a cursory google and help topics
lookup, I have no clue what they can do for me.
Thanks,
Shawn
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Shawn,
> Have you considered using Schemas for this? (Introduced in SQL Server 2005
> also).
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
up[vbcol=seagreen]
at[vbcol=seagreen]
where[vbcol=seagreen]
addition
>
|||I believe this has been suggested before but to make sure please send the
details of your request to sqlwish@.microsoft.com
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||Hi Shawn,
Take a look at the Northwind sample database in SQL Server 2005 Beta if you
have access to it from the MSDN site. Whereas Northwind's tables, procs,
etc. before were just one big list, now there are separate schemas for
HumanResources, etc. with only those objects relevant to that part of the
app in them.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
> What are schemas in this context? after a cursory google and help topics
> lookup, I have no clue what they can do for me.
>
> Thanks,
> Shawn
>
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...
> up
> at
> where
> addition
>
|||Sorry, brain explosion - meant to point you to the Adventureworks sample,
not Northwind.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OLy2UoU0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Hi Shawn,
> Take a look at the Northwind sample database in SQL Server 2005 Beta if
> you have access to it from the MSDN site. Whereas Northwind's tables,
> procs, etc. before were just one big list, now there are separate schemas
> for HumanResources, etc. with only those objects relevant to that part of
> the app in them.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
>

Fastest way to delete hundreds of table triggers and hundreds of stored procedures?

How can i delete all user stored procedures and all table triggers very fast
in
a single database?

Thank youYou can run the following script in Query Analyzer. Be certain you are in
the correct database.

USE MyDatabase
DECLARE @.DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N'DROP ' +
CASE xtype
WHEN 'P' THEN N'PROCEDURE '
WHEN 'TR' THEN N'TRIGGER '
END +
QUOTENAME(USER_NAME(uid)) +
N'.' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN('P', 'TR')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> How can i delete all user stored procedures and all table triggers very
> fast
> in
> a single database?
> Thank you|||I will try this today.

Thank you

> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements|||"serge" <sergea@.nospam.ehmail.com> wrote in message
news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
> I will try this today.

As we discover Bank of America goes offline since their databases somehow
lost their entire schema in a hacker attack. :-)

> Thank you
> > You can run the following script in Query Analyzer. Be certain you are
in
> > the correct database.
> > USE MyDatabase
> > DECLARE @.DropStatement nvarchar(4000)
> > DECLARE DropStatements CURSOR
> > LOCAL FAST_FORWARD READ_ONLY FOR
> > SELECT N'DROP ' +
> > CASE xtype
> > WHEN 'P' THEN N'PROCEDURE '
> > WHEN 'TR' THEN N'TRIGGER '
> > END +
> > QUOTENAME(USER_NAME(uid)) +
> > N'.' +
> > QUOTENAME(name)
> > FROM sysobjects
> > WHERE xtype IN('P', 'TR')
> > OPEN DropStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM DropStatements INTO @.DropStatement
> > IF @.@.FETCH_STATUS = -1 BREAK
> > EXEC(@.DropStatement)
> > END
> > CLOSE DropStatements
> > DEALLOCATE DropStatements|||Well, at least the tables are still there :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Xxh0d.21421$2s.7550@.twister.nyroc.rr.com...
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
>> I will try this today.
>
> As we discover Bank of America goes offline since their databases somehow
> lost their entire schema in a hacker attack. :-)
>
>>
>> Thank you
>>
>> > You can run the following script in Query Analyzer. Be certain you are
> in
>> > the correct database.
>>> > USE MyDatabase
>> > DECLARE @.DropStatement nvarchar(4000)
>> > DECLARE DropStatements CURSOR
>> > LOCAL FAST_FORWARD READ_ONLY FOR
>> > SELECT N'DROP ' +
>> > CASE xtype
>> > WHEN 'P' THEN N'PROCEDURE '
>> > WHEN 'TR' THEN N'TRIGGER '
>> > END +
>> > QUOTENAME(USER_NAME(uid)) +
>> > N'.' +
>> > QUOTENAME(name)
>> > FROM sysobjects
>> > WHERE xtype IN('P', 'TR')
>> > OPEN DropStatements
>> > WHILE 1 = 1
>> > BEGIN
>> > FETCH NEXT FROM DropStatements INTO @.DropStatement
>> > IF @.@.FETCH_STATUS = -1 BREAK
>> > EXEC(@.DropStatement)
>> > END
>> > CLOSE DropStatements
>> > DEALLOCATE DropStatements
>>
>>|||What's the fastest way to delete tables? :)

> Well, at least the tables are still there :-)|||I ran this and it was fast, it's what i was looking for.

I am trying to figure out how to delete ONLY the user objects and not the
system objects.

By changing the WHERE condition to become:

WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

I tried running it and it seems it did not delete the Stored Procedures with
TYPE = System.

I just want to make sure this condition I am using :

AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

is the right one, that I am not screwing other things unknowingly?

Thank you

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> > How can i delete all user stored procedures and all table triggers very
> > fast
> > in
> > a single database?
> > Thank you|||>> Well, at least the tables are still there :-)

"serge" <sergea@.nospam.ehmail.com> wrote in news:BRJ0d.35921$lP4.2446119
@.news20.bellglobal.com:

> What's the fastest way to delete tables? :)

USE master
DROP DATABASE CriticalFinancialInfo
GO|||> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

Yes, this is correct. I should have included this in the script I posted.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:tdK0d.36158$lP4.2450872@.news20.bellglobal.com ...
>I ran this and it was fast, it's what i was looking for.
> I am trying to figure out how to delete ONLY the user objects and not the
> system objects.
> By changing the WHERE condition to become:
>
> WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> I tried running it and it seems it did not delete the Stored Procedures
> with
> TYPE = System.
> I just want to make sure this condition I am using :
> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> is the right one, that I am not screwing other things unknowingly?
> Thank you
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
>> You can run the following script in Query Analyzer. Be certain you are
>> in
>> the correct database.
>>
>> USE MyDatabase
>> DECLARE @.DropStatement nvarchar(4000)
>> DECLARE DropStatements CURSOR
>> LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT N'DROP ' +
>> CASE xtype
>> WHEN 'P' THEN N'PROCEDURE '
>> WHEN 'TR' THEN N'TRIGGER '
>> END +
>> QUOTENAME(USER_NAME(uid)) +
>> N'.' +
>> QUOTENAME(name)
>> FROM sysobjects
>> WHERE xtype IN('P', 'TR')
>> OPEN DropStatements
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DropStatements INTO @.DropStatement
>> IF @.@.FETCH_STATUS = -1 BREAK
>> EXEC(@.DropStatement)
>> END
>> CLOSE DropStatements
>> DEALLOCATE DropStatements
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "serge" <sergea@.nospam.ehmail.com> wrote in message
>> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
>> > How can i delete all user stored procedures and all table triggers very
>> > fast
>> > in
>> > a single database?
>>> > Thank you
>>>>>
>>|||My quest continues, maybe i should explain what i am trying to achieve.

There is a database with 3000+ Stored Procedures. We give copies to other
people
and we continue making updates to the *development* database. When we want
to give the other people our latest stored procedures, we have code that
deletes all
stored procedures one by one, thus taking maybe 30 minutes to delete.
Then we recreate all the SPs.

Now i wanted to find out if there was a way to speed this process, i
originally thought
that deleting all SPs one shot could do the trick. But the further i analyze
it, i see some complications.

For example, the other people could very well have created their own SPs,
how can
i NOT delete those SPs?

Do you or anyone else have any idea how i can accomplish this?

Thank you

> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> Yes, this is correct. I should have included this in the script I posted.|||"serge" <sergea@.nospam.ehmail.com> wrote in
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com :

> My quest continues, maybe i should explain what i am trying to
> achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people
> and we continue making updates to the *development* database. When we
> want to give the other people our latest stored procedures, we have
> code that deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can
> i NOT delete those SPs?

You *need* version control.

I suppose the simplest way without spending any money would be to add a
table with the current version number of each stored procedure. Your
"update db with new stored procedures" would have to check that version
number and decide whether to drop and recreate the stored procedure.

Taking the idea a step further, the version table would also contain the
complete source of the stored procedure. Then another stored procedure
could cursor over the table, doing the drops and creates. Distributing
your new procedures would involve exporting this table to an external file
(say an MDB) and distributing it to the other recipients.|||serge (sergea@.nospam.ehmail.com) writes:
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people and we continue making updates to the *development*
> database. When we want to give the other people our latest stored
> procedures, we have code that deletes all stored procedures one by one,
> thus taking maybe 30 minutes to delete. Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought that deleting all SPs one shot could do the trick.
> But the further i analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can i NOT delete those SPs?

First of all, does their license permit them to add their own stored
procedures?

As Ross said, you need version control.

In our shop we have all our stored procedures, triggers, tables, in short
all database objects under version control in SourceSafe, and SourceSafe
is the master for all building efforts.

To build and install we have a toolset, called AbaPerls. One tool is
DBBUILD which builds an entire database from scripts, that is tables,
stored procedures, el todo. DBBUILD is also what we add when we add
a new component, or subsystem as we call it, to the database. Then we
have another tool DBUPDGEN which reads SourceSafe, and finds all changes
between two labels and that produces an update script. (For changed tables
you get a template to move over the data, but in most cases you have to
modify the generated code.) Furthermore, AbaPerls has its own set of
tables, so we know what we have loaded into a database. There is also
a stored procedure which lists mismatches between AbaPerls and SQL Server's
own system tables. Customer-added code would end up there.

I have made AbaPerls available as freeware on http://www.abaris.se/abaperls/
But as Ross outlined, you can achieve something a lot simpler with quite
easy means, and it may be enough for your organization.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I understand and I do agree your idea is very good. But at the moment, I am
trying
and hoping that there is a simpler way to rely on the WHERE Condition and
using
some custom field or something in the sysobjects table to decide whether
that SP
is one of our own SP and not delete it.

Thanks for your idea. I'll have to keep this in mind, I might have to use
this
approach in the future.

> You *need* version control.
> I suppose the simplest way without spending any money would be to add a
> table with the current version number of each stored procedure. Your
> "update db with new stored procedures" would have to check that version
> number and decide whether to drop and recreate the stored procedure.
> Taking the idea a step further, the version table would also contain the
> complete source of the stored procedure. Then another stored procedure
> could cursor over the table, doing the drops and creates. Distributing
> your new procedures would involve exporting this table to an external file
> (say an MDB) and distributing it to the other recipients.|||We do have SourceSafe on our end but not to go into details here,
that wouldn't be a viable solution at the time being. Maybe in the future.

Like i just replied to Ross, at the moment, I am trying and hoping that
there is a simpler way to rely on the WHERE Condition and using
some custom field or something in the sysobjects table to decide whether
that SP is one of our own SP and not delete it.

It's very appreciated that you've made your AbaPerls toolset freeware for
everyone to use. I have taken note of the link and I will have to look at
this
in great length in the future. At this moment, it's very hard to switch to
using
tools for what i require. If i can get away with a simple WHERE condition
to NOT delete SPs not created by us, I'll be very interested to use.

Due to time constraints, I don't want to add a new project to work on.
This task of deleting SPs fast is already not part of my regular work, add
to that I have to figure out ways to improve some slow SPs we have (again
not part of my regular work).

Thank you

> First of all, does their license permit them to add their own stored
> procedures?
> As Ross said, you need version control.
> In our shop we have all our stored procedures, triggers, tables, in short
> all database objects under version control in SourceSafe, and SourceSafe
> is the master for all building efforts.
> To build and install we have a toolset, called AbaPerls. One tool is
> DBBUILD which builds an entire database from scripts, that is tables,
> stored procedures, el todo. DBBUILD is also what we add when we add
> a new component, or subsystem as we call it, to the database. Then we
> have another tool DBUPDGEN which reads SourceSafe, and finds all changes
> between two labels and that produces an update script. (For changed tables
> you get a template to move over the data, but in most cases you have to
> modify the generated code.) Furthermore, AbaPerls has its own set of
> tables, so we know what we have loaded into a database. There is also
> a stored procedure which lists mismatches between AbaPerls and SQL
Server's
> own system tables. Customer-added code would end up there.
> I have made AbaPerls available as freeware on
http://www.abaris.se/abaperls/
> But as Ross outlined, you can achieve something a lot simpler with quite
> easy means, and it may be enough for your organization.|||serge (sergea@.nospam.ehmail.com) writes:
> Like i just replied to Ross, at the moment, I am trying and hoping that
> there is a simpler way to rely on the WHERE Condition and using
> some custom field or something in the sysobjects table to decide whether
> that SP is one of our own SP and not delete it.

There is no such custom field. Possibly you could add some condition which
looked in syscomments for things you recognize, but that would be completely
bizarre to do.

You would have to have list of known procedures to delete. Then again, that
is not very difficult to make effecient:

CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
DECLARE @.proc sysname
DECLARE drop_cur INSENSITIVE CURSOR FOR
SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
JOIN sysobjects o ON i.nstr = o.name
WHERE o.xtype = 'P'
OPEN drop_cur
WHILE 1 = 1
FETCH drop_cur INTO @.proc
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP PROCEDURE ' + @.proc)
END
DEALLOCATE drop_cur

iter_charlist_to_tbl is on
http://www.sommarskog.se/arrays-in-...list-of-strings

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As already mentioned, a manifest and/or version control to probably the best
long term solution. In the interim. you might consider adding extended
properties to the objects you own so that you can more easily identify
these. You'll need to include the extended properties in your DDL scripts.

CREATE PROC MyProcedure
AS
SELECT 'MyProcedure'
GO

EXEC sp_addextendedproperty
'OwnedBy',
'MyApplication',
'USER',
'dbo',
'PROCEDURE',
'MyProcedure'
GO

--this
SELECT *
FROM sysobjects o
JOIN ::fn_listextendedproperty(
'OwnedBy',
'USER',
'dbo',
'PROCEDURE',
NULL,
NULL,
NULL
) ep ON o.name = ep.objname
WHERE o.xtype = 'P' AND
ep.value = 'MyApplication'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com ...
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to other
> people
> and we continue making updates to the *development* database. When we want
> to give the other people our latest stored procedures, we have code that
> deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze
> it, i see some complications.
> For example, the other people could very well have created their own SPs,
> how can
> i NOT delete those SPs?
> Do you or anyone else have any idea how i can accomplish this?
> Thank you
>
>> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
>>
>> Yes, this is correct. I should have included this in the script I
>> posted.|||Thanks again for the post. I'll have to look into this more closely
in the next few days (hopefully).

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95636012ECE1Yazorman@.127.0.0.1...
> serge (sergea@.nospam.ehmail.com) writes:
> > Like i just replied to Ross, at the moment, I am trying and hoping that
> > there is a simpler way to rely on the WHERE Condition and using
> > some custom field or something in the sysobjects table to decide whether
> > that SP is one of our own SP and not delete it.
> There is no such custom field. Possibly you could add some condition which
> looked in syscomments for things you recognize, but that would be
completely
> bizarre to do.
> You would have to have list of known procedures to delete. Then again,
that
> is not very difficult to make effecient:
> CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
> DECLARE @.proc sysname
> DECLARE drop_cur INSENSITIVE CURSOR FOR
> SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
> JOIN sysobjects o ON i.nstr = o.name
> WHERE o.xtype = 'P'
> OPEN drop_cur
> WHILE 1 = 1
> FETCH drop_cur INTO @.proc
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP PROCEDURE ' + @.proc)
> END
> DEALLOCATE drop_cur
> iter_charlist_to_tbl is on
> http://www.sommarskog.se/arrays-in-...list-of-strings
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks interesting. Having extended properties to the SP.
I'll have to investigate this further, hopefully soon.

Thank you again.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:rnh1d.10373$yp2.9988@.newssvr30.news.prodigy.c om...
> As already mentioned, a manifest and/or version control to probably the
best
> long term solution. In the interim. you might consider adding extended
> properties to the objects you own so that you can more easily identify
> these. You'll need to include the extended properties in your DDL
scripts.
> CREATE PROC MyProcedure
> AS
> SELECT 'MyProcedure'
> GO
> EXEC sp_addextendedproperty
> 'OwnedBy',
> 'MyApplication',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> 'MyProcedure'
> GO
> --this
> SELECT *
> FROM sysobjects o
> JOIN ::fn_listextendedproperty(
> 'OwnedBy',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> NULL,
> NULL,
> NULL
> ) ep ON o.name = ep.objname
> WHERE o.xtype = 'P' AND
> ep.value = 'MyApplication'

faking modules

We have a pretty large code base of stored procedures ( 700 ),
functions ( 300 ) , and views ( 200 ) at our medium-sized company. Why
someone would implement the majority of their business logic in
transact-sql , I don't know, but we are way beyond the point of being
able prevent that from happening. And have a few years before we are
out of it.
So, the major thing I find myself griping over as we continue to roll
out new sets of code into the database and maintain the large legacy
codebase, is the lack of a module / package / namespace system for
organzing our stored procedures, functions, and views.
(My other major gripes are: lack of strong array / list support outside
of table variables and temp tables, especially the ability to pass
around table variables as parameters; inability to define custom
aggregation functions (addressed in 2005); inability to create
"on-the-fly" views or table expressions(addressed in 2005); the
arbitrary limit of insert exec's; the inability to use scalar udf's
because of their ugly performance)
So, I want to know about anybody out there who has attempted to fake
modules in someway or another by conventions or anything. Any
particular methods that were useful for meaningfully organzing hundreds
/ thousands of stored procedures?
Right now, i've been using the convention of putting a module name and
underscore before each function, stored proc, and view. I haven't
written the queries against the information_schema views or made any
handy utilities for browsing the database parsing these names, but
that's probably next on my list.
Also, why does this not seem to even be addressed in 2005'
nposternposter (code4breakfast@.yahoo.com) writes:
> We have a pretty large code base of stored procedures ( 700 ),
> functions ( 300 ) , and views ( 200 ) at our medium-sized company. Why
> someone would implement the majority of their business logic in
> transact-sql , I don't know,
I know. Because T-SQL is where the data is. In our shop, we are strong
believers in having business logic in stored procedures. Actually, one
guy in our shop thought we should move logic up to the middle layer,
and conducted a test where he rewrote a complex stored procedure. The
test was a total fiasco: performance was awful and it did not scale.
And before you ask: we have around 3700 stored procedures.

> So, the major thing I find myself griping over as we continue to roll
> out new sets of code into the database and maintain the large legacy
> codebase, is the lack of a module / package / namespace system for
> organzing our stored procedures, functions, and views.
There is in SQL 2005: schemas. Well, there are schemas in SQL 2000 as well,
but as they are tied to users in SQL 2000, they are useless.

> (My other major gripes are: lack of strong array / list support outside
> of table variables and temp tables, especially the ability to pass
> around table variables as parameters;
Have a look at http://www.sommarskog.se/share_data.html for some tips.

> inability to define custom
> aggregation functions (addressed in 2005); inability to create
> "on-the-fly" views or table expressions(addressed in 2005);
But beware that CTEs are basically macros, and recalculated each time
they occur in a query.

> the arbitrary limit of insert exec's;
A limit of 1 does not look very arbitrary to me.

> the inability to use scalar udf's because of their ugly performance)
This has improved in SQL 2005, where you also can write UDFs in the
CLR.
To be honest, it seems to me that your main problem is that you are
trying to use T-SQL as if it is a traditional programming langauge.
It isn't. Yes, T-SQL is a bit poor on general constructs for
modularisation, but just get used to it. Don't try to write in T-SQL
as if it was C++ or Visual Basic.

> So, I want to know about anybody out there who has attempted to fake
> modules in someway or another by conventions or anything. Any
> particular methods that were useful for meaningfully organzing hundreds
> / thousands of stored procedures?
Actually, we have a subsystem concept that is implemented through our
loadtool. The main reason we have done this, is that we don't ship
all parts of the system to all customers. So this is a concept that
has been added for configuration management rather than programming.
Our largest subsystem - which is large by legacy - has over 1000 stored
procedures.

> Also, why does this not seem to even be addressed in 2005'
As I said, the separation of user and schema is an important step in
this direction.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> . Why someone would implement the majority of their business logic in tra
nsact-sql , I don't know, .. <<
Because the database is where the data is and most of the business
logic probably has to do with rules about relationships in the data. I
also hope you have a lot of DRI, defaults and constraints, too.
Think about what a disaster it would be if you did this in the front
end. What if 699 of your procedures believe there is a rule that says
(age > 18) and one that believes the rule is (age >= 18)?
You do business rules in the schema so you have them in ONE place, ONE
way, ONE time.
T-SQL was never meant to be a development language. In fact, the old
rule of thumb was never write a proc over one page long. But you can
find some code control tools out there which will work with T-SQL.
lity to create "on-the-fly" views or table expressions(addressed in 2005); t
he
arbitrary limit of insert exec's; the inability to use scalar udf's
because of their ugly performance) <<
In short, you are not a SQL programmers at all! Let's go down the list
of things you missed about SQL
1) We don't use arrays and lists -- ever hear of 1NF?
2) Table variables as parameters would mix data and meta-data
3) I have been able to write custom aggregation functions with the
math avaialble to me in Standard SQL. The statistical and analytics
should be done in a stat package that can handle the floating point
problems.
4) We design schemas carefully and never use "on-the-fly" views or
table. It means that you are probably converting 3GL programs into
T-SQL and need 1950's styule scratch files.
5) What does "the arbitrary limit of insert exec's" mean?
6) Why do you need scalar udf's? You are not doing computations in the
Database, are you? How would put such things into an optimizer --even
in theory?
So much for the ISO-11179 Standards!
Your problem is that you "don't get it" -- SQL is not for development;
it is for data. The joke we had in ANSI X3H2 was that SQL stood for
"Scarcely Qualifies as a Language" because all it does is maintain,
validate and persist data.|||Erland Sommarskog wrote:
> nposter (code4breakfast@.yahoo.com) writes:
> I know. Because T-SQL is where the data is. In our shop, we are strong
> believers in having business logic in stored procedures. Actually, one
> guy in our shop thought we should move logic up to the middle layer,
> and conducted a test where he rewrote a complex stored procedure. The
> test was a total fiasco: performance was awful and it did not scale.
> And before you ask: we have around 3700 stored procedures.
>
This is obviously a long discussion, and I don't have enough background
writing large, scalable data layers to really argue. The angle I was
coming from is that most people would agree that you can write more
cleanly organized and modularized code outside of transact-sql for
things beyond simple queries. On writing pure reports or processing
large data sets, sure I would vote for transact-sql, but for small
hits, single update or inserts with complex validation checks, small
data set queries on not a very heavy transactional db, I would vote for
the taking the logic out.

> There is in SQL 2005: schemas. Well, there are schemas in SQL 2000 as well
,
> but as they are tied to users in SQL 2000, they are useless.
okay ! i might not invest as much energy into rolling my own module
system then. i've only begun to skim the 2005 docs.

>
> Have a look at http://www.sommarskog.se/share_data.html for some tips.
>
i haven't read this site before, but upon reading it now, i have used
each one of these techniques. they are still limiting and a pain to
implement for something as simple as just passing aroudn a table /
list. its like i have to compile my code into this lower level language
of passing around temp tables.

> But beware that CTEs are basically macros, and recalculated each time
> they occur in a query.
>
> A limit of 1 does not look very arbitrary to me.
>
Why limit to 1? Because "we said so". This coudl have been used as a
general mechanism for calling stored procs from other stored procs if
it wasn't for the limitation... yeah , i know, make a table-valued udf,
but what if i want to pass tables into and out of the table-valued udf.
which leads you back to temp tables and stored procs.

> To be honest, it seems to me that your main problem is that you are
> trying to use T-SQL as if it is a traditional programming langauge.
> It isn't. Yes, T-SQL is a bit poor on general constructs for
> modularisation, but just get used to it. Don't try to write in T-SQL
> as if it was C++ or Visual Basic.
>
i have embraced t-sql. i hate the fact that i have, but i have. i use
views, table-valued udf's, stored procs in the style i think is
considered the "t-sql way". i gave up the expectation of being able to
write reusable , efficient scalar functions. but i really don't think
its too much to ask to simply ask for a way to group my code logically.
these weren't top secret ideas when t-sql was created.

> Actually, we have a subsystem concept that is implemented through our
> loadtool. The main reason we have done this, is that we don't ship
> all parts of the system to all customers. So this is a concept that
> has been added for configuration management rather than programming.
> Our largest subsystem - which is large by legacy - has over 1000 stored
> procedures.
>
custom written loadtool? i was thinking of using make/ant scripts with
isqlw commands to load sets of code in. then check that into source
control. can you give any more details about the loadtool and how you
use it? how do you browse the subsystems?|||> ... On writing pure reports or processing
> large data sets, sure I would vote for transact-sql, but for small
> hits, single update or inserts with complex validation checks, small
> data set queries on not a very heavy transactional db, I would vote for
> the taking the logic out.
I would vote for keeping the logic in, for a number of reasons: (1)
assert. (2) deadlocks. (3) flexibility. (4) performance. (5)
scalability. (6) security. The list continues.
"nposter" <code4breakfast@.yahoo.com> wrote in message
news:1124669275.064403.271190@.g44g2000cwa.googlegroups.com...
> Erland Sommarskog wrote:
> This is obviously a long discussion, and I don't have enough background
> writing large, scalable data layers to really argue. The angle I was
> coming from is that most people would agree that you can write more
> cleanly organized and modularized code outside of transact-sql for
> things beyond simple queries. On writing pure reports or processing
> large data sets, sure I would vote for transact-sql, but for small
> hits, single update or inserts with complex validation checks, small
> data set queries on not a very heavy transactional db, I would vote for
> the taking the logic out.
>
well,
> okay ! i might not invest as much energy into rolling my own module
> system then. i've only begun to skim the 2005 docs.
>
outside
> i haven't read this site before, but upon reading it now, i have used
> each one of these techniques. they are still limiting and a pain to
> implement for something as simple as just passing aroudn a table /
> list. its like i have to compile my code into this lower level language
> of passing around temp tables.
>
> Why limit to 1? Because "we said so". This coudl have been used as a
> general mechanism for calling stored procs from other stored procs if
> it wasn't for the limitation... yeah , i know, make a table-valued udf,
> but what if i want to pass tables into and out of the table-valued udf.
> which leads you back to temp tables and stored procs.
>
> i have embraced t-sql. i hate the fact that i have, but i have. i use
> views, table-valued udf's, stored procs in the style i think is
> considered the "t-sql way". i gave up the expectation of being able to
> write reusable , efficient scalar functions. but i really don't think
> its too much to ask to simply ask for a way to group my code logically.
> these weren't top secret ideas when t-sql was created.
>
hundreds
> custom written loadtool? i was thinking of using make/ant scripts with
> isqlw commands to load sets of code in. then check that into source
> control. can you give any more details about the loadtool and how you
> use it? how do you browse the subsystems?
>|||nposter (code4breakfast@.yahoo.com) writes:
> This is obviously a long discussion, and I don't have enough background
> writing large, scalable data layers to really argue. The angle I was
> coming from is that most people would agree that you can write more
> cleanly organized and modularized code outside of transact-sql for
> things beyond simple queries.
Admittedly, code in C#, Ada or whatever can be prettier than in T-SQL.
(Then again, you can write it in C++, and don't tell me that is
prettier! :-)
However, pretty code is not always the as effecient code. The cost for
moving the logic out the database can be enourmous with all data that
has to go forth and back.
You can do code reuse in T-SQL as well, but it is certainly a little
clumsier. But one should also keep in mind that code reuse is not really
the same virtue in T-SQL as it is client languages. For instance, accessing
a view because it pre-computes some result you need may not be a good idea
after all, because the view accesses four tables irrelevant to your
task.

> i haven't read this site before, but upon reading it now, i have used
> each one of these techniques. they are still limiting and a pain to
> implement for something as simple as just passing aroudn a table /
> list. its like i have to compile my code into this lower level language
> of passing around temp tables.
I certainly can't disagree that this is a point where T-SQL could serve
from improvement. Being able to pass a table as a parameter would be a
great addition.
In SQL 2005 there is, by the way, a new workaround: since you know can
receive the result of FOR XML queries into a variable of the new xml
data type, you can pass a table in an xml parameter. (And you don't need
OPENXML to shred it anymore.) Yes, this is really, really ugly. A lot
better novelty in SQL 2005 is statement recompilation. This makes the
cost for sharing temp tables less expensive.

> Why limit to 1? Because "we said so".
More probably because permitting more levels would have made the
implementation more complicated. I have no idea, but I can imagine that
doing it in one layer was something that almost could be done for free
by using something already there, like placing the result set in the
output buffer, and the intercept that. But that would be possible to
do in several layers without considerable re-architecture.
And in any case, INSERT-EXEC have several other issues as well, as I
discuss in my article. From the point of view of modular programming,
I dislike INSERT-EXEC because you can change the callee and wreck a
caller you don't know about. It's simply not robust enough.

> custom written loadtool? i was thinking of using make/ant scripts with
> isqlw commands to load sets of code in. then check that into source
> control. can you give any more details about the loadtool and how you
> use it? how do you browse the subsystems?
Sure, the full documentation is on http://www.abaris.se/abaperls
One more feature that we have that may be of particular interest to
you, is our pre-processor.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> nposter (code4breakfast@.yahoo.com) writes:
> I know. Because T-SQL is where the data is. In our shop, we are strong
> believers in having business logic in stored procedures. Actually, one
> guy in our shop thought we should move logic up to the middle layer,
> and conducted a test where he rewrote a complex stored procedure. The
> test was a total fiasco: performance was awful and it did not scale.
> And before you ask: we have around 3700 stored procedures.
>
> There is in SQL 2005: schemas. Well, there are schemas in SQL 2000 as well
,
> but as they are tied to users in SQL 2000, they are useless.
>
Well, they're tied to users or roles. In fact, we've had a reasonable
amount of success here with a database split into (at last count) 9
different schemas (as new subsystems are added to the rest of the
project, 1 or 2 new schemas are created), all of which are owned by
roles.
I do, of course, look forward to being able to do away with these roles
and have them as pure schemas, but it works reasonably well now. We've
even managed to get Merge Replication working with it (after a few
false starts, we went live with this a couple of ws ago)
Damien|||Can you explain this a little further. Do you have the tables in one
centralized schema. Then each new schema encapsulates sets of stored
procedures, functions, and views? This is in 2000 still I assume. So
you have something like:
server = PRODSERVER
database/schema1 = PROD_TABLES
database/schema2 = PROD_PROCEDURE_SCHEMA1
database/schema3 = PROD_PROCEDURE_SCHEMA2
or am i missing the definition of schemas. are schemas lower level than
the database level?|||Very intersting. this is exactly what i was envisioning somebody out
there had already created. I don't know if i will use it directly, but
I will probably write up my own simplified version inspired by your
system|||nposter wrote:
> Can you explain this a little further. Do you have the tables in one
> centralized schema. Then each new schema encapsulates sets of stored
> procedures, functions, and views? This is in 2000 still I assume. So
> you have something like:
> server = PRODSERVER
> database/schema1 = PROD_TABLES
> database/schema2 = PROD_PROCEDURE_SCHEMA1
> database/schema3 = PROD_PROCEDURE_SCHEMA2
> or am i missing the definition of schemas. are schemas lower level than
> the database level?
How I'm using it is pretty well how it'll work under 2005. I'm not
saying it's the right way, or the best way, but it's working here, and
avoiding come collisions.
So, we create a database, and a couple of schemas:
create database MyBigDatabase
go
use MyBigDatabase
go
sp_addrole 'ClientServices' --This will change to create Schema in SS
2005
go
sp_addrole 'CreditorServices'
go
--And so on, for each schema needed, then
create table ClientServices.Users
UserID uniqueidentifier not null,
--Columns specific to the concept of a User within Client Services
go
create table CreditorServices.Users
UserID uniqueidentifier not null,
--Columns specific to the concept of a User within Creditor Services
--Create Proc, Create View, etc, all within their own schemas
Some of the tables we use are in fact still owned by dbo - these are
tables which are globally required/accessable within the individual
services. Before anyone asks about my use of Uniqueidentifier columns
for what are obviously (with more DDL) going to be Primary Keys, I'd
like to point out that we always knew we wanted to support replication,
so it just saved time. And yes, there are unique constraints on the
natural keys.
Damien

Failure to authendicate domain/users

I have several access databases in mind to migrate to SQL server. I installed MS SQL 2005 Express on my machine. I will have procedures to run with authorizations beyond that of a common user, such as database administrative work where server agent is not available, I may rely on users' log-on prompt to do some maintenance work. However, I cannot get the EXEC AS 'Domain\User' to work. The procedures can be created OK. But whenever they are called, the following message shows up:

Msg 15404, Level 16, State 19, Procedure XXX, Line 0
Could not obtain information about Windows NT group/user 'Domain\User', error code 0xea.

I tried to tweak with the account under which the server service is running. There are three options under built-in account: Local system, Local service, and Network Service. My understanding is that Network Service will use the log-on of the current user of the computer. I have admin right of the computer. None of the three options work. Additionally, when I specify an account (my own account), it's the same thing.

The procedure xp_logininfo always fails when I query a specific domain\user.

The ADHelper is configured to run manually.

I could not think of other ways to get a possible solution. Any help is much appreciated.

hi,

try having a look at http://support.microsoft.com/kb/834124/en-us

regards

|||

Hi, there,

I did several times. The problem is that the article does not contain an explanation of state 19. It has explanations for state 21 and above. Do you have any idea what state 19 relates to?

Thanks,