Get Backup Directory

I am writing a stored procedure, and I want to set a variable to the default Backup Directory for the current instance. (7.0 or SQL2000).

What is the Standard"way of doing this

Do I have to write a C program that calls HRESULT GetBackupDirectory(SQLDMO_LPBSTR pRetVal);

Or should I install DtReg.exe on all my servers?

/* get default backup location -- by Bob Sturnfield */
--DtReg.exe can be found at http://www.tamedos.com/downloads
set nocount on
declare @.string varchar(4000),
@.regloc varchar(100),
@.BackupDirectory varchar(1000),
@.servernm varchar(30)

select @.regloc='MSSQLServer'
select @.servernm=rtrim(convert(varchar(30),SERVERPROPERTY ('servername')))
if CHARINDEX('\', @.servernm)>0
select @.regloc='Microsoft SQL Server\' + substring(@.servernm, CHARINDEX('\', @.servernm)+1, 30)

create table #DtReg( BackupDirectory varchar(4000))

select @.string='xp_cmdshell ''DtReg -ListValue "HKEY_LOCAL_MACHINE\Software\Microsoft\' +
@.regloc + '\MSSQLServer\BackupDirectory"'''

insert into #DtReg exec(@.string)

select top 1 @.BackupDirectory=substring(BackupDirectory,8,1000) from #DtReg
Where BackupDirectory like 'REG_SZ%'

if @.@.rowcount<>1
Select * from #DtReg

drop table #DtReg

print @.BackupDirectoryHave you looked at xp_regread ?

exec xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'BackupDirectory'

Value Data
------------------------
BackupDirectory C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP|||Thank you,

xp_regread -- not in books on-line

http://www.4guysfromrolla.com/webtech/101499-1.shtml

There's a little known xp procedure in the master database called xp_regread. This stored procedure accepts three parameters. The first one being the root key, next is the path to the key, and finally the key value you are looking to return.

http://www.swynk.com/friends/green/xp_reg.asp

xp_regread [@.rootkey=]'rootkey', [@.key=]'key'[, [@.value_name=]'value_name'][, [@.value=]@.value OUTPUT]

xp_regwrite [@.rootkey=]'rootkey', [@.key=]'key', [@.value_name=]'value_name', [@.type=]'type', [@.value=]'value'

http://www.sql-server-performance.com/ac_extended_stored_procedures.asp

These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

This is great, I very much appreciate the response

Bob Sturnfield

/* get default backup location -- by Bob Sturnfield */
set nocount on
declare @.regloc varchar(100),
@.BackupDirectory varchar(1000)

select @.regloc=
'Software\Microsoft\MSSQLServer\MSSQLServer'

if CHARINDEX('\\', @.@.servername)>0
select @.regloc='Software\Microsoft\Microsoft SQL Server\' +
substring(@.@.servername, CHARINDEX('\', @.@.servername)+1, 30)+ '\MSSQLServer'

execute master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key=@.regloc, @.value_name='BackupDirectory',
@.value=@.BackupDirectory OUTPUT

print @.BackupDirectory

Get back calculated column from insert

I have the following in my Stored Procedure, where I am just adding a record
that will set the ApplicantID to the largest number + 1 or 1000 if null.
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
How do I find out what the ApplicantID is? This is similar to what an
Identity column would do, but I need to do it manually.
Thanks,
TomYour table should have a natural key defined in addition to the
surrogate ApplicantID. Use the natural key to retrieve the last ID. For
example, if this is for an online application you might make the email
address a key, therefore:
SET @.last_id =
(SELECT applicantid
FROM dbo.Applicant
WHERE email = @.email)
If you don't declare a natural key then you can't guarantee uniqueness
and integrity and therefore you can't reliably retrieve the Applicantid
after insert.
David Portas
SQL Server MVP
--|||Why do you need to "do it manually"? What do you think is going to happen
if two transactions hit this at exactly the same time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> I have the following in my Stored Procedure, where I am just adding a
record
> that will set the ApplicantID to the largest number + 1 or 1000 if null.
> INSERT INTO ftsolutions.dbo.Applicant
>
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> How do I find out what the ApplicantID is? This is similar to what an
> Identity column would do, but I need to do it manually.
> Thanks,
> Tom
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ef63V1fFFHA.392@.TK2MSFTNGP09.phx.gbl...
> Why do you need to "do it manually"? What do you think is going to happen
> if two transactions hit this at exactly the same time?
Because there are cases where I will need to add another ApplicantID in the
insert statement.
The create is:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
What would happen here is I could add a record that has no ApplicantID
asssociated with it and the insert would have take the max and add 1 and the
Applicant Unique would be 1. If there is already an ApplicantID out there,
I would have the same ApplicantID with ApplicantUnique =
max(ApplicantUnique)+1. Since the ApplicantID would need to be in this
Insert statement, it can't be an Identity.
Actually, we originally designed this to only allow one ApplicantID per
person without the PositionID, but now we need to allow multiple PositionIDs
per ApplicationID's, so we changed ApplicantID to be a normal bigint.
Tom

>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> record
> (ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,
Da
> tePosted)
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108760606.023053.73570@.l41g2000cwc.googlegroups.com...
> Your table should have a natural key defined in addition to the
> surrogate ApplicantID. Use the natural key to retrieve the last ID. For
> example, if this is for an online application you might make the email
> address a key, therefore:
> SET @.last_id =
> (SELECT applicantid
> FROM dbo.Applicant
> WHERE email = @.email)
> If you don't declare a natural key then you can't guarantee uniqueness
> and integrity and therefore you can't reliably retrieve the Applicantid
> after insert.
>
Actually, I do have key.
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
The insert statement that gets the ApplicantID is:
****************************************
************************************
***************
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
****************************************
************************************
**************
If it does exist, then I would use the same ApplicantID and increase the
ApplicantUnique by one
I used to have ApplicantID as an identity (as I mentioned in my other post)
and after the insert I would do a:
Select SCOPE_IDENTITY() as ApplicantID
to pass the ApplicantID back.
I could do the same thing by doing another select (inside a transaction to
make sure no one else increases it before I get it back) and then pass it
back, but I was hoping there was a way to get it from the insert/select
statement.
Thanks,
Tom

> --
> David Portas
> SQL Server MVP
> --
>|||> The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
An artificial key should not be required to make a candidate key. You
haven't even got First Normal Form. Normalize your schema (apparently a
subset of these columns belongs in another table) and then your problem
will be solved.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
I have a Client Table that is the ClientID only. I also have a Postions
table which is ClientID and PositionID (could have the same PositionID for
different Clients). The we have the Applicant table, where we have multiple
Positions for each Client and each Applicant could have more than one
instance of the same position (which is why I need the ApplicantUnique).
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||An artificial key is an arbitrary value, such as an incrementing count,
that is not a real attribute of the entity you are modelling. By
definition you are carrying redundant data if you don't have a Natural
key - ie. some subset of the attributes (real attributes) that is
unique.
It's far from clear to me what all these columns are but you seem to
have a high level of non-key dependencies in this table. Are you
familiar with the concept of Normalization? Normalization is a basic
design step in which you eliminate duplicated information by
decomposing data into appropriate tables based on functional
dependencies. If this process is performed correctly then it won't be
possible to end up with a structure such as you have. For example I
would not expect to see data about Applicants (actual names and email
addresses of people) in the same table as data about Applications
(positions applied for and who applied for them). Here's a wild guess,
not perfect, but all the tables have at least one natural key:
CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
NULL, lastname VARCHAR(30) NOT NULL)
CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
(client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
(client_id, client_job_code))
CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
REFERENCES Positions (client_id, client_job_code), application_date
DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
client_job_code, application_date))
Regarding PositionUnique. I don't see any other information associated
with multiple instances of a Position for an Applicant. If there isn't
any such information then you don't actually need to add a new row. For
completeness I've added Application_Date into the key.

> The problem is that this is just a temporary table which may not go
any
> farther.
Then you surely won't need another artifical key at all. The only use
for the INSERT you originally asked for is if new rows of data are
being inserted. If you need more help then I expect you can get better
advice if you tell us what your actual *objective* is rather than
propose half a solution to a problem you haven't really explained. The
following article describes the best way to post your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108778173.839320.171900@.z14g2000cwz.googlegroups.com...
> An artificial key is an arbitrary value, such as an incrementing count,
> that is not a real attribute of the entity you are modelling. By
> definition you are carrying redundant data if you don't have a Natural
> key - ie. some subset of the attributes (real attributes) that is
> unique.
> It's far from clear to me what all these columns are but you seem to
> have a high level of non-key dependencies in this table. Are you
> familiar with the concept of Normalization? Normalization is a basic
> design step in which you eliminate duplicated information by
> decomposing data into appropriate tables based on functional
> dependencies.
I understand Normalization and do a certain amount of it but not completely
(to the consternation of many).

>If this process is performed correctly then it won't be
> possible to end up with a structure such as you have.
That's true.

>For example I
> would not expect to see data about Applicants (actual names and email
> addresses of people) in the same table as data about Applications
> (positions applied for and who applied for them).
Not true here.
I would agree with you in other cases, however. I might have a persons data
in a separate table than the application table. But in our case, the person
may want to have different basic information for each position. He will
typically gear the information to the position. We need to keep a snapshot
of each application of each person. So in our case, it is just easier to
keep the personal information along with the application data. We do have a
separate table for each position (whose key is the PostitionID).

>Here's a wild guess,
> not perfect, but all the tables have at least one natural key:
> CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
> email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
> NULL, lastname VARCHAR(30) NOT NULL)
> CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
> VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
> (client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
> (client_id, client_job_code))
> CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
> Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
> VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
> REFERENCES Positions (client_id, client_job_code), application_date
> DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
> client_job_code, application_date))
Your table is pretty close to mine. If I were to separate my tables the you
have, I would also need to have the ClientID in the Applicants table also -
as we need to keep our applicants for our clients separate from each other.
I did make some changes, after looking at your last post about artificial
keys. I realized that I do have another table where I keep summary data
(JobApplicant) for each application and gave that a specific ID by making it
an identity field. There is one JobApplicant record for each application
filed. So I replace the ApplicantUnique with JobApplicant as that would
uniquify the record. The JobApplicant key "artificial key" would be the
JobID which is an identity field.
Also, a person can apply to multiple positions (so for the same ApplicantID
there could be multiple PositionIDs). Also, a person could apply to the
same position more than once. This was why I had the ApplicantUnique
before. But since there is only one JobApplicant record for each Applicant
and each position applied for - I can replace it with that.
Here is what I came up with and how it is currently on my system (there are
more fields in the tables, but these are the pertinent data).
CREATE TABLE [dbo].[Client] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TABLE [dbo].[Position] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PositionID] [int] IDENTITY (1, 1) NOT NULL ,
[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatePosted] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Position] WITH NOCHECK ADD
CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED
(
[ClientID],
[PositionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
ALTER TABLE [dbo].[Position] ADD
CONSTRAINT [FK_Position_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Applicant] WITH NOCHECK ADD
CONSTRAINT [DF_Applicant_PositionID] DEFAULT (0) FOR [PositionID],
CONSTRAINT [DF_Applicant_ApplicantUnique] DEFAULT (0) FOR [JobID],
CONSTRAINT [DF_Applicant_ResumeUnique] DEFAULT (0) FOR [ResumeUnique],
CONSTRAINT [PK_Applicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[JobApplicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Resume] [datetime] NULL ,
[Application] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobApplicant] WITH NOCHECK ADD
CONSTRAINT [DF_jobApplicant_Interviews] DEFAULT (0) FOR [Interviews],
CONSTRAINT [PK_jobApplicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[ApplicantPosition] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateApplied] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApplicantPosition] WITH NOCHECK ADD
CONSTRAINT [DF_ApplicantPosition_JobID] DEFAULT (0) FOR [JobID],
CONSTRAINT [PK_ApplicantPosition] PRIMARY KEY CLUSTERED
(
[ApplicantID],
[ClientID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Tom

> Regarding PositionUnique. I don't see any other information associated
> with multiple instances of a Position for an Applicant. If there isn't
> any such information then you don't actually need to add a new row. For
> completeness I've added Application_Date into the key.
>
> any
> Then you surely won't need another artifical key at all. The only use
> for the INSERT you originally asked for is if new rows of data are
> being inserted. If you need more help then I expect you can get better
> advice if you tell us what your actual *objective* is rather than
> propose half a solution to a problem you haven't really explained. The
> following article describes the best way to post your problem for the
> group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>sql

Get all User Databases

When I execute the sp_databases proc all I get are the databases that the
logged in user has rights to. I want a stored procedure that will return
just the names of all the non-system databases that I can populate a
dropdown list with. I really don't want to add the user to every database
or give them an Administrator role just to see the user table names. Any
help on this would be much appreciated.
JohnHi, John
Use something like this:
SELECT name FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('master','tempdb','model','msdb')
ORDER BY name
Razvan|||John,
Try:
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL','DISTR
IBUTOR')
HTH
Jerry
"john wright" <riley_wright@.hotmail.com> wrote in message
news:%23jo41JN0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> When I execute the sp_databases proc all I get are the databases that the
> logged in user has rights to. I want a stored procedure that will return
> just the names of all the non-system databases that I can populate a
> dropdown list with. I really don't want to add the user to every database
> or give them an Administrator role just to see the user table names. Any
> help on this would be much appreciated.
>
> John
>|||>> I want a stored procedure that will return just the names of all the
You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Great. This works just fine.
John
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ulawJVN0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
> system table to the the list of all databases. You can avoid the master,
> model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
> clause to get the list of non-system databases.
> --
> Anith
>sql

Get a return value when calling a SP from within ASP

I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
<HTML>
<HEAD><TITLE>test</TITLE></HEAD>
<BODY>
<%
Set ADODBCon = Server.CreateObject("ADODB.Connection")
Dim StrConn, rs, MyInput, MyResult
MyInput = 123
ADODBCon.Provider = "SQLOLEDB"
strConn = "Data Source=MySQLServer;"
strConn = strConn & "Initial Catalog=MyDatabase;"
strConn = strConn & "User Id=JohnD;"
strConn = strConn & "Password=MyPwd;"
ADODBCon.Connectionstring = strConn
ADODBCon.open
sSql = "usp_test " & MyInput
Set rs = ADODBCon.Execute(sSql, out MyResult)
%>
</BODY>
</HTML>"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.co...y/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:

> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> <HTML>
> <HEAD><TITLE>test</TITLE></HEAD>
> <BODY>
> <%
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
> </BODY>
> </HTML>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

Get a return value when calling a SP from within ASP

I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
&

---

test"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.com/en-us/library/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> &

---

> test
>
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
>
>

---

>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

get a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

Get @@rowcount data from MSSQL using SqlDataSource with delete command

Hi

I'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.

In my SQL im using:

DECLARE @.table1CountintDELETE FROM Table1WHERE id = @.new_idSET @.table1Count=@.@.rowcountSELECT @.table1Count

I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @.table1Count back into my aspx page?

Thanks

use OUT parameter

CREATE PROC dbo.SP_DeleteID(@.new_id int, @.effRowCnt intOUT)

As

begin

DELETE FROM Table1WHERE id = @.new_id

SET @.effRowCnt=@.@.rowcount

end

|||

ok, but how do i reference the @.effRowCnt in the code behind page?

thanks

|||

If you are just trying to get a count of the number of rows deleted, an easier way to do it is to use the Deleted methodof the DataSource, something like:

ProtectedSub SQLDataSource1_Deleted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SQLDataSource1.Deleted

NumberofRowsDeleted = e.AffectedRows

EndSub

HTH

get # of rows affected

I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,

I set as follows:

set nocount off

What and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?

Thank you.

Hi

Here is snippet fromBuilding and using a 3-tiered data architecture with ASP.NET 2.0(Creating a Business Logic Layer) :

 [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update,true)]public bool UpdateProduct(string productName,int? supplierID,int? categoryID,string quantityPerUnit,decimal? unitPrice,short? unitsInStock,short? unitsOnOrder,short? reorderLevel,bool discontinued,int productID) { Northwind.ProductsDataTable products = Adapter.GetProductByProductID(productID);if (products.Count == 0)// no matching record found, return falsereturn false; Northwind.ProductsRow product = products[0];// Business rule check - cannot discontinue a product that's supplied by only // one supplierif (discontinued) {// Get the products we buy from this supplier Northwind.ProductsDataTable productsBySupplier = Adapter.GetProductsBySupplierID(product.SupplierID);if (productsBySupplier.Count == 1)// this is the only product we buy from this supplierthrow new ApplicationException("You cannot mark a product as discontinued if its the only product purchased from a supplier"); } product.ProductName = productName;if (supplierID ==null) product.SetSupplierIDNull();else product.SupplierID = supplierID.Value;if (categoryID ==null) product.SetCategoryIDNull();else product.CategoryID = categoryID.Value;if (quantityPerUnit ==null) product.SetQuantityPerUnitNull();else product.QuantityPerUnit = quantityPerUnit;if (unitPrice ==null) product.SetUnitPriceNull();else product.UnitPrice = unitPrice.Value;if (unitsInStock ==null) product.SetUnitsInStockNull();else product.UnitsInStock = unitsInStock.Value;if (unitsOnOrder ==null) product.SetUnitsOnOrderNull();else product.UnitsOnOrder = unitsOnOrder.Value;if (reorderLevel ==null) product.SetReorderLevelNull();else product.ReorderLevel = reorderLevel.Value; product.Discontinued = discontinued;// Update the product recordint rowsAffected = Adapter.Update(product);// Return true if precisely one row was updated, otherwise falsereturn rowsAffected == 1; }

|||

@.@.ROWCOUNT will return you no.of records affected in database hence in sp. so you need to return it as output parameter from sp to upper layers.

thanks,

satish.

sql

German Language in SQL Server 2005 ?

hi all,

i need to know how to enable my database that is stored onSQLServer 2005 to be able to storeGerman characters, because as you know that when it finds a unique character that is not supported by theCollationlanguage it puts instead a "?" character.

so i need to know the correctCollation item that is equivelant to the German language or if there is any extra download or package i should get to make it work.

Thank you very much.

Simply using NVARCHAR/NTEXT to store Unicode characters. And when inserting the Unicode string, you can put a N in front of the string:

insert into mytable (name) values(N'吕布')

The collation 'Latin1_General' should be the right one for German. You can use both Window Collations and SQL Collations in SQL Server. And you may also need to specify some extra attributes such asComparisonStyle for the collation. To understand how character data is stored, please refers to 'Collations', 'SQL Collation Name' and 'Window Collation Name' topic in SQL2000 Books Online. Below is from SQL2000 BOL:

Code pages define bit patterns for uppercase and lowercase characters, digits, symbols, and special characters such as !, @., #, or %. Each European language, such as German or Spanish, has its own single-byte code page. Although the bit patterns used to represent the Latin alphabet characters A through Z are the same for all the code pages, the bit patterns used to represent accented characters such as 'é' and 'á' vary from one code page to the next. If data is exchanged between computers running different code pages, all character data must be converted from the code page of the sending computer to the code page of the receiving computer.

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:

IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:

If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Generic Stored procedure

Hi All!

i want to retrieve reults from table "tblCategory" by passing

search string as parameter and column name as parameter as well.

CREATE Procedure uspSearchCategory

(

@.Search nvarchar(255),

@.column varchar(100)

)

AS

SELECT

*

FROM

tblCategory

WHERE

@.column LIKE '%' + @.Search+'%'

This doesn't work as @.column in last line is incorrect. Can anybody tell me how can i achieve that.

If i write

name LIKE '%' + @.Search+'%' or

ID LIKE '%' + @.Search+'%'

it works.But can it works as general for ant column name i pass as @.column.

thanx

You need to turn your query into a string, then run the string using code like this:

exec sp_executesql @.query

where @.query is the nvarchar that contains the SQL to run

generating XML to validated against the schema

hi,
I have a sample xml data and sample xsd as follows: the xsd is really big
and runs into 4 pages. I need to stored data into the data base
and generate xml that validates against this schema.
the sqlxml bulk load and view mapper fails to recognize the xsd fine.
Can some one point me to the right direction .
Infact storing is working but generating the xml
using EXPLICIT option is really a pain.
Regards,
Balajee
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="StrategicPerformanceContract"
targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:documentation>Strategic Performance Contract</xs:documentation>
</xs:annotation>
<xs:element name="root" type="contractType" />
<xs:complexType name="contractType">
<xs:sequence>
<xs:element name="transactionHeader" type="transactionHeaderType" />
<xs:element name="documentHeader" type="documentHeaderType" />
<xs:element name="strategicDirection" type="strategicDirectionType" />
<xs:element name="strategicChoices" type="strategicChoicesType" />
<xs:element name="riskManagement" type="riskManagementType" />
<xs:element name="performanceContract" type="performanceContractType" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="transactionHeaderType">
<xs:sequence>
<xs:element name="userName" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="documentHeaderType">
<xs:sequence>
<xs:element name="contractType" type="xs:ID" />
<xs:element name="abbr" type="xs:string" />
<xs:element name="leftTitle1" type="xs:string" />
<xs:element name="leftTitle2" type="xs:string" />
<xs:element name="rightTitle1" type="xs:string" />
<xs:element name="asofDate" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="strategicDirectionType">
<xs:sequence>
<xs:element name="header" type="headerType" />
<xs:element name="strategicObjective" type="strategicObjectiveType"
minOccurs="0" maxOccurs="unbounded" />
<xs:element name="footnotes" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="headerType">
<xs:sequence>
<xs:element name="vpuMission" type="xs:string" />
<xs:element name="strategicContext" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="strategicObjectiveType">
<xs:sequence>
<xs:element name="objective" type="objectiveType" />
<xs:element name="subObjective" type="objectiveType" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
What are the problems you get when using the Schema and adding mapping
annotations?
Thanks
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
> hi,
> I have a sample xml data and sample xsd as follows: the xsd is really big
> and runs into 4 pages. I need to stored data into the data base
> and generate xml that validates against this schema.
> the sqlxml bulk load and view mapper fails to recognize the xsd fine.
> Can some one point me to the right direction .
> Infact storing is working but generating the xml
> using EXPLICIT option is really a pain.
> Regards,
> Balajee
>
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="StrategicPerformanceContract"
> targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:documentation>Strategic Performance Contract</xs:documentation>
> </xs:annotation>
> <xs:element name="root" type="contractType" />
> <xs:complexType name="contractType">
> <xs:sequence>
> <xs:element name="transactionHeader" type="transactionHeaderType" />
> <xs:element name="documentHeader" type="documentHeaderType" />
> <xs:element name="strategicDirection" type="strategicDirectionType" />
> <xs:element name="strategicChoices" type="strategicChoicesType" />
> <xs:element name="riskManagement" type="riskManagementType" />
> <xs:element name="performanceContract" type="performanceContractType" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="transactionHeaderType">
> <xs:sequence>
> <xs:element name="userName" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="documentHeaderType">
> <xs:sequence>
> <xs:element name="contractType" type="xs:ID" />
> <xs:element name="abbr" type="xs:string" />
> <xs:element name="leftTitle1" type="xs:string" />
> <xs:element name="leftTitle2" type="xs:string" />
> <xs:element name="rightTitle1" type="xs:string" />
> <xs:element name="asofDate" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="strategicDirectionType">
> <xs:sequence>
> <xs:element name="header" type="headerType" />
> <xs:element name="strategicObjective" type="strategicObjectiveType"
> minOccurs="0" maxOccurs="unbounded" />
> <xs:element name="footnotes" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="headerType">
> <xs:sequence>
> <xs:element name="vpuMission" type="xs:string" />
> <xs:element name="strategicContext" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="strategicObjectiveType">
> <xs:sequence>
> <xs:element name="objective" type="objectiveType" />
> <xs:element name="subObjective" type="objectiveType" minOccurs="0"
> maxOccurs="unbounded" />
> </xs:sequence>
> </xs:complexType>
|||Michael,
I am trying to map the schema using sql viewmapper and
it gives me an error :
Error while parsing
:Incorrect definition for the root element in schema.
Line 7 Position 63
xmlns:xs="http://www.w3.org/2001/XMLSchema">
regards,
Balajee
"Michael Rys [MSFT]" wrote:

> What are the problems you get when using the Schema and adding mapping
> annotations?
> Thanks
> Michael
> "balajee" <balajee@.discussions.microsoft.com> wrote in message
> news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
>
>
|||Unfortunately, the SQL Viewmapper only works for the older Microsoft schema
language known as XDR and not with the W3C XML Schema language (that you
seem to be using). This unfortunately means that you either need a
third-party tool vendor or notepad.
Best regards
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:0EA7E4AD-F94D-4021-8A22-FE327F1B786B@.microsoft.com...[vbcol=seagreen]
> Michael,
> I am trying to map the schema using sql viewmapper and
> it gives me an error :
> Error while parsing
> :Incorrect definition for the root element in schema.
> Line 7 Position 63
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> regards,
> Balajee
>
> "Michael Rys [MSFT]" wrote:
sql

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

Generating wrapper code for SQL Server Stored Procedure

Hi

How Can I Generating wrapper code for SQL Server Stored Procedure ??

If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ?

and thanks with my regarding

FraasYou are probably talking about the Data Adapter wizard in the design view. See this walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkWalkthroughUsingDataGridWebControlToReadWriteData.asp

Generating unique nvarchar unique key

Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks

generating sql scripts of stored procedures

Can someone tell me why I get this behavior?
Whenever I build a new stored procedure into my database I specifically
set the ansi nulls and quoted identifier on like so
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xxxx AS
It compiles fine, but when I go back to Enterprise Mgr and right click
on the stored procedure and "generate script" it puts
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
before stored proc, and
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
at the end of the script. All my settings in sql server and database
say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
restarted it.. What could be causing this> restarted it.. What could be causing this
IMO, design miss in EM...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <daveygf@.yahoo.com> wrote in message
news:1108059473.053294.211600@.z14g2000cwz.googlegroups.com...
> Can someone tell me why I get this behavior?
> Whenever I build a new stored procedure into my database I specifically
> set the ansi nulls and quoted identifier on like so
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE xxxx AS
> It compiles fine, but when I go back to Enterprise Mgr and right click
> on the stored procedure and "generate script" it puts
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> before stored proc, and
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> at the end of the script. All my settings in sql server and database
> say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
> QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
> restarted it.. What could be causing this
>