Grid display: March 2012

Thursday, March 29, 2012

Get Blocked Objects

Hi All,
Whenever there is a timeout occured from Application which was connected to
SQL Server.
I tried to get the Blocking objects from SysProcesses table of master DB.
But most of the time it will clear very soon so i couldnt able to find the
actual objects which is Blocking.
Could any one suggests some ways to get the blocking objects.
Thanks in advance...
Regards,
Herbert R.SQL Server Profiler may help, but be reminded to log only the required
information as logging eats up resources.
References
- SQL Server 2000 Administrator's Pocket Consultant: Profiling and
Monitoring (Solving Performance Problems with Profiler)
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
Martin C K Poon
Senior Analyst Programmer
====================================
"Herbert" <Herbert@.discussions.microsoft.com> bl
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com g...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.|||Check out http://support.microsoft.com/kb/271509/ for a script that can help
monitor blocking and record the objects involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
> to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.

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 data from Reports

I have just started working with RS 2005 and was wondering if / how I can do the following.

I created an asp.net website that has a report viewer to show my reports on the web.
In addition there is a page that lists all of the reports a user has access to, which is pretty much a series of links:

Report 1
Report 2
Report 3
.....

Now in my reports I generally create a grand total of one of the column and was wondering if there was some way to get that information back into an asp page, ie:

Report 1 - $125,713
Report 2 - ($23,111)
Report 3 - $1,762,142

I was thinking if there was some way to send a parameter to the report like Summary=True which would tell it to just return a XML bit of data holding the total for that report (Anything will work, doesn't have to be XML). So the page with the report links would call each of the reports.

I realize that it would probably be possible to do this through a stored procedure, but that would just create another point of maintenace. Instead I could create some function in all of my reports and have a standardized way of doing this so my webserver or database wouldnt have to be updated all the time.

Thanks, and let me know if there is any other information I can provide to make this clearer.

Try using the XML renderer. You will get a "report" which is really just an XML stream with the data. You can then XPath into it to get the values you are interested in.|||

Sounds promising. Being rather new to reports, are there any pages you can think of off hand that provide examples or explanations?

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 Available free space for data file

When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>

Get Available free space for data file

When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:

> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>

Get Authentication Error in upgrade SQL 2005 named instance to SP1 TCP

I got the following error using either NT or SQL Login ('sa') that are sysadmin in my SQL 2005 instance:

Your Account information could not be verified for one or more instances. Ensure that you can connect to all the selected instances using the account information provided. To process further deselect the instances to which connectivity cannot be established.

However, I would login to SQL 2005 named instance, either NT or SQL login 'sa' without any problems to do anything I want because both are sysadmin.

<PS> Note that my default SQL instance in my local server is SQL 2000 and SQL 2005 named instance.

Richard

Hi Richard, I just tried this on my machine and didn't hit any issues. Can you send a little more info about the state of your machine? Are you running SQL 2000 SP4? What SKU is SQL 2000 and what SKU is 2005? Are they both ENT? And how are you connecting to the 2005 named instance? I was able to install SP1 using Windows Authentication. Can you submit your install logs so I can try to help debug?

Here's what I did:
1) Install SQL 2000 ENT SKU as local system, setting SA password - default instance
2) Apply SQL 2000 SP4
3) Install SQL 2005 ENT SKU setting SQL to run under domain account - named instance
4) Install SQL 2005 SP1

Does that look similar to your repro steps?

Thanks,
Samuel Lester (MSFT)

|||

What I hae done is 1), 3), and get the error for 4). Attached is the log file for your information:

4 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:40.464 splevel = 0,1
04/03/2006 15:54:40.504 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:40.574 upgradecode = {1B117BA7-5BC1-419E-820E-7D4F3F412C7B}
04/03/2006 15:54:40.644 version = 9
04/03/2006 15:54:40.694
04/03/2006 15:54:40.714 File Group Details: MSI
04/03/2006 15:54:40.734 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:40.764 File Details: msxml6.msi
04/03/2006 15:54:40.784
04/03/2006 15:54:40.824 Instance Details: MSXML 6.0 Parser
04/03/2006 15:54:40.854 productcode = {AEB9948B-4FF2-47C9-990E-47014492A0FE}
04/03/2006 15:54:40.884
04/03/2006 15:54:40.904 Product Details:
04/03/2006 15:54:40.924 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixMsxml6_x64.inf
04/03/2006 15:54:40.944 baselinebuild = 1399
04/03/2006 15:54:40.994 build = 2040
04/03/2006 15:54:41.015 description = MSXML 6.0 Parser (64-bit)
04/03/2006 15:54:41.045 details = Service Pack for Microsoft XML 6.0 Parser.
04/03/2006 15:54:41.075 kbarticle = KB913090
04/03/2006 15:54:41.115 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:41.155 lcid = 1033
04/03/2006 15:54:41.195 legalproductname = MSXML 6.0 Parser (64-bit)
04/03/2006 15:54:41.215 machinetype = x64
04/03/2006 15:54:41.305 package = HotFixMsxml6_x64
04/03/2006 15:54:41.325 packagetype = Hotfix
04/03/2006 15:54:41.355 productname = Redist9
04/03/2006 15:54:41.405 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:41.435 splevel = 0,1
04/03/2006 15:54:41.455 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:41.475 upgradecode = {5BBED1F8-E6F3-4A02-BC97-26D35BE200CA}
04/03/2006 15:54:41.595 version = 9
04/03/2006 15:54:41.695
04/03/2006 15:54:41.776 File Group Details: MSI
04/03/2006 15:54:41.856 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:41.946 File Details: msxml6_x64.msi
04/03/2006 15:54:42.196
04/03/2006 15:54:42.296 Product Details:
04/03/2006 15:54:42.396 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlxml4.inf
04/03/2006 15:54:42.507 baselinebuild = 1399
04/03/2006 15:54:42.617 build = 2040
04/03/2006 15:54:42.687 description = SQLXML4
04/03/2006 15:54:42.757 details = Service Pack for Microsoft SQLXML 4.0.
04/03/2006 15:54:42.837 kbarticle = KB913090
04/03/2006 15:54:42.937 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:43.037 lcid = 1033
04/03/2006 15:54:43.138 legalproductname = SQLXML4
04/03/2006 15:54:43.238 machinetype = x86
04/03/2006 15:54:43.368 package = HotFixSqlxml4
04/03/2006 15:54:43.398 packagetype = Hotfix
04/03/2006 15:54:43.438 productcode = {8C62A94B-4AB6-485F-A111-93056684D340}
04/03/2006 15:54:43.458 productname = Redist9
04/03/2006 15:54:43.488 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:43.578 splevel = 0,1
04/03/2006 15:54:43.638 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:43.678 upgradecode = {D9CA3D82-6F1B-41A7-8141-B90ACA8F865B}
04/03/2006 15:54:43.708 version = 9
04/03/2006 15:54:43.748
04/03/2006 15:54:43.768 File Group Details: MSI
04/03/2006 15:54:43.809 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:43.829 File Details: sqlxml4.msi
04/03/2006 15:54:43.869
04/03/2006 15:54:43.899 Instance Details: SQLXML4
04/03/2006 15:54:43.919 productcode = {8C62A94B-4AB6-485F-A111-93056684D340}
04/03/2006 15:54:43.969
04/03/2006 15:54:43.999 Product Details:
04/03/2006 15:54:44.019 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlxml4_x64.inf
04/03/2006 15:54:44.069 baselinebuild = 1399
04/03/2006 15:54:44.099 build = 2040
04/03/2006 15:54:44.129 description = SQLXML4 (64-bit)
04/03/2006 15:54:44.169 details = Service Pack for Microsoft SQLXML 4.0.
04/03/2006 15:54:44.199 kbarticle = KB913090
04/03/2006 15:54:44.229 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:44.269 lcid = 1033
04/03/2006 15:54:44.299 legalproductname = SQLXML4 (64-bit)
04/03/2006 15:54:44.329 machinetype = x64
04/03/2006 15:54:44.359 package = HotFixSqlxml4_x64
04/03/2006 15:54:44.399 packagetype = Hotfix
04/03/2006 15:54:44.429 productname = Redist9
04/03/2006 15:54:44.459 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:44.510 splevel = 0,1
04/03/2006 15:54:44.600 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:44.650 upgradecode = {F457D8E6-7686-437D-9B17-E21D45CCABD8}
04/03/2006 15:54:44.680 version = 9
04/03/2006 15:54:44.720
04/03/2006 15:54:44.750 File Group Details: MSI
04/03/2006 15:54:44.780 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:44.820 File Details: sqlxml4_x64.msi
04/03/2006 15:54:44.850
04/03/2006 15:54:44.880 Product Details:
04/03/2006 15:54:44.920 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSQLServer2005_BC.inf
04/03/2006 15:54:44.950 baselinebuild = 1399
04/03/2006 15:54:44.980 build = 2040
04/03/2006 15:54:45.020 description = Backward Compatibility
04/03/2006 15:54:45.050 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
04/03/2006 15:54:45.080 kbarticle = KB913090
04/03/2006 15:54:45.120 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:45.160 lcid = 1033
04/03/2006 15:54:45.191 legalproductname = Microsoft SQL Server 2005 Backward Compatibility
04/03/2006 15:54:45.231 machinetype = x86
04/03/2006 15:54:45.261 package = HotFixSQLServer2005_BC
04/03/2006 15:54:45.291 packagetype = Hotfix
04/03/2006 15:54:45.321 productcode = {96327C3C-96BE-4C7A-A6F7-A71635E5949A}
04/03/2006 15:54:45.361 productname = Redist9
04/03/2006 15:54:45.391 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:45.421 splevel = 0,1
04/03/2006 15:54:45.461 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:45.491 upgradecode = {1E70C6C9-E1B7-4A74-BC8C-8EB5D010CEC9}
04/03/2006 15:54:45.521 version = 9
04/03/2006 15:54:45.561
04/03/2006 15:54:45.591 File Group Details: MSI
04/03/2006 15:54:45.621 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:45.661 File Details: SQLServer2005_BC.msi
04/03/2006 15:54:45.691
04/03/2006 15:54:45.721 Instance Details: Backward Compatibility
04/03/2006 15:54:45.761 productcode = {96327C3C-96BE-4C7A-A6F7-A71635E5949A}
04/03/2006 15:54:45.791
04/03/2006 15:54:45.821 Product Details:
04/03/2006 15:54:45.851 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSQLServer2005_BC_x64.inf
04/03/2006 15:54:45.892 baselinebuild = 1399
04/03/2006 15:54:45.922 build = 2040
04/03/2006 15:54:45.952 description = Backward Compatibility (64-bit)
04/03/2006 15:54:45.992 details = Service Pack for the Backward Compatibility components, including Data Transformation Services Runtime and SQL-DMO.
04/03/2006 15:54:46.022 kbarticle = KB913090
04/03/2006 15:54:46.052 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:46.092 lcid = 1033
04/03/2006 15:54:46.122 legalproductname = Microsoft SQL Server 2005 Backward Compatibility (64-bit)
04/03/2006 15:54:46.152 machinetype = x64
04/03/2006 15:54:46.192 package = HotFixSQLServer2005_BC_x64
04/03/2006 15:54:46.222 packagetype = Hotfix
04/03/2006 15:54:46.322 productname = Redist9
04/03/2006 15:54:46.362 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:46.402 splevel = 0,1
04/03/2006 15:54:46.432 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:46.462 upgradecode = {7B6BF434-3C72-4DB3-8049-FEF31AEAFF9A}
04/03/2006 15:54:46.492 version = 9
04/03/2006 15:54:46.532
04/03/2006 15:54:46.562 File Group Details: MSI
04/03/2006 15:54:46.593 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:46.633 File Details: SQLServer2005_BC_x64.msi
04/03/2006 15:54:46.663
04/03/2006 15:54:46.693 Product Details:
04/03/2006 15:54:46.733 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlWriter.inf
04/03/2006 15:54:46.763 baselinebuild = 1399
04/03/2006 15:54:46.793 build = 2040
04/03/2006 15:54:46.833 description = Microsoft SQL Server VSS Writer
04/03/2006 15:54:46.863 details = Service Pack for Microsoft SQL Server VSS Writer.
04/03/2006 15:54:46.893 kbarticle = KB913090
04/03/2006 15:54:46.933 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:46.973 lcid = 1033
04/03/2006 15:54:47.003 legalproductname = Microsoft SQL Server VSS Writer
04/03/2006 15:54:47.053 machinetype = x86
04/03/2006 15:54:47.083 package = HotFixSqlWriter
04/03/2006 15:54:47.113 packagetype = Hotfix
04/03/2006 15:54:47.153 productcode = {1CBE3804-20DF-48DA-B048-895C206E80A5}
04/03/2006 15:54:47.183 productname = Redist9
04/03/2006 15:54:47.213 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:47.253 splevel = 0,1
04/03/2006 15:54:47.284 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:47.314 upgradecode = {65D8E1DF-6201-4B53-A0F9-E654F8E80F97}
04/03/2006 15:54:47.344 version = 9
04/03/2006 15:54:47.384
04/03/2006 15:54:47.414 File Group Details: MSI
04/03/2006 15:54:47.444 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:47.484 File Details: SqlWriter.msi
04/03/2006 15:54:47.514
04/03/2006 15:54:47.544 Instance Details: Microsoft SQL Server VSS Writer
04/03/2006 15:54:47.584 productcode = {1CBE3804-20DF-48DA-B048-895C206E80A5}
04/03/2006 15:54:47.614
04/03/2006 15:54:47.644 Product Details:
04/03/2006 15:54:47.684 INF File Name: \\2003SERVER\d$\9a954d1afa2c57beebe098\HotFixSqlWriter_x64.inf
04/03/2006 15:54:47.714 baselinebuild = 1399
04/03/2006 15:54:47.744 build = 2040
04/03/2006 15:54:47.774 description = Microsoft SQL Server VSS Writer (64-bit)
04/03/2006 15:54:47.814 details = Service Pack for Microsoft SQL Server VSS Writer.
04/03/2006 15:54:47.844 kbarticle = KB913090
04/03/2006 15:54:47.874 kbarticlehyperlink = http://support.microsoft.com/?kbid=913090
04/03/2006 15:54:47.944 lcid = 1033
04/03/2006 15:54:47.995 legalproductname = Microsoft SQL Server VSS Writer (64-bit)
04/03/2006 15:54:48.045 machinetype = x64
04/03/2006 15:54:48.085 package = HotFixSqlWriter_x64
04/03/2006 15:54:48.155 packagetype = Hotfix
04/03/2006 15:54:48.225 productname = Redist9
04/03/2006 15:54:48.275 servicepackname = Microsoft SQL Server 2005 Service Pack 1 Setup
04/03/2006 15:54:48.345 splevel = 0,1
04/03/2006 15:54:48.405 supportdir = \\2003SERVER\d$\9a954d1afa2c57beebe098
04/03/2006 15:54:48.475 upgradecode = {E9031696-DD39-4C25-BAEB-425FF28279EA}
04/03/2006 15:54:48.625 version = 9
04/03/2006 15:54:48.716
04/03/2006 15:54:48.756 File Group Details: MSI
04/03/2006 15:54:48.786 sourcepath = <SUPPORTDIR>\<PACKAGE>\Files
04/03/2006 15:54:48.816 File Details: SqlWriter_x64.msi
04/03/2006 15:54:48.856
04/03/2006 15:55:14.993 Authenticating user using Windows Authentication
04/03/2006 15:55:15.073 SQL Service MSSQL$YUKON was previously running, ready for authentication
04/03/2006 15:55:15.114 SQL Agent Service SQLAgent$YUKON was not previously running
04/03/2006 15:55:24.247 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
04/03/2006 15:55:33.009 Failed to receive sysadmin status for RS instance: YUKON
04/03/2006 15:55:58.786 Authenticating user using SAPWD
04/03/2006 15:55:58.836 SQL Service MSSQL$YUKON was previously running, ready for authentication
04/03/2006 15:55:58.866 SQL Agent Service SQLAgent$YUKON was not previously running
04/03/2006 15:56:07.509 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
04/03/2006 15:56:16.151 Failed to receive sysadmin status for RS instance: YUKON
04/03/2006 15:57:24.440 Hotfix package closed

|||I get the same problem. I have installed SQL 2000 SP4 and it did not fix it.

Get Array value from a loop?

Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??

=====================================

DECLARE @.ABBR AS NVARCHAR(50)
SET @.ABBR = 'CHP,CNH,COW'

DECLARE @.SEP AS NVARCHAR(5)
SET @.SEP = ','

WHILE patindex('%,' + @.ABBR + ',%', @.ABBR ) > 0
BEGIN

-- do the rest

END

I used a stored procedure as this

Create proceduresp_ParseArray( @.Arrayvarchar(1000),@.separatorchar(1) )ASset nocount on-- @.Array is the array we wish to parse-- @.Separator is the separator charactor such as a commadeclare @.separator_positionint-- This is used to locate each separator characterdeclare @.array_valuevarchar(1000)-- this holds each array value as it is returned-- For my loop to work I need an extra separator at the end. I always look to the-- left of the separator character for each array valueset @.array = @.array + @.separator-- Loop through the string searching for separtor characterswhile patindex('%' + @.separator +'%' , @.array) <> 0begin-- patindex matches the a pattern against a stringselect @.separator_position = patindex('%' + @.separator +'%' , @.array)select @.array_value =left(@.array, @.separator_position - 1)-- This is where you process the values passed. -- Replace this select statement with your processing -- @.array_value holds the value of this element of the arrayselect Array_Value = @.array_value-- This replaces what we just processed with and empty stringselect @.array = stuff(@.array, 1, @.separator_position,'')endset nocount offgo

Hope this helpsql

Get Ancestor and Descendant in a Hierarchy

For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about Recursive CTEs in SQL Server Books Online, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.

And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.

First of all we have to create a table for the following functions and build some test data. The statemens look like:

create table ST_CATEGORY(
CATEGORYID uniqueidentifier not null default NEWID(),
PARENTID uniqueidentifier,
[NAME] varchar(128),
COMMENT varchar(4096),
CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
)
go

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.rootoid, NULL, 'ROOT', 'ROOT NODE')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.rootoid, 'Business Application', 'group for all business applications')

declare @.techoid uniqueidentifier
set @.techoid = NEWID()

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.techoid, @.rootoid, 'Tech101', 'technical tips')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.techoid, 'C#', 'C# tips')
go

Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.

CREATE PROCEDURE spGetAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find parents/ancestor
WITH Ancestor( CATEGORYID ) AS
(
SELECT PARENTID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM Ancestor, ST_CATEGORY
WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM Ancestor
END
GO

CREATE PROCEDURE spGetSelfAndAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and parents/ancestor
WITH SelfAndAncestor( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM SelfAndAncestor, ST_CATEGORY
WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM SelfAndAncestor
END
GO

CREATE PROCEDURE spGetDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find children/descendant
WITH Descendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE PARENTID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM Descendant, ST_CATEGORY
WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM Descendant
END
GO

CREATE PROCEDURE spGetSelfAndDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and children/descendant
WITH SelfAndDescendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM SelfAndDescendant, ST_CATEGORY
WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM SelfAndDescendant
END
GOUhm......OK. Thanks for sharing with us information that is available in Books Online...

Get Analysis Services Version string in c#.net 2.0

I want to get the version string for AS 2000 and 2005 in c#. Can I use the same object model to connect to both or do I need one for each. I saw a reference to DSO 8.5 which implied you could.

Either way could you let me know what DDL I should reference in Visual Studio.

Thanks

Steve

If you are using Microsoft.AnalysisServices.AdomdClient then you can use AdomdConnection.ServerVersion property to obtain textual representation of the version. If you use System.Runtime.InteropServices.Version object then you can construct one to parse such things like major and minor numbers.

It works for AS2005 and AS2000.|||Works a treat Andrew thanks

get an error on a sample code

This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:

USE AdventureWorks;
GO
DECLARE @.tablename sysname
SET @.tablename = N'Person.AddressType'
table_loop:
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.tablename = RTRIM(UPPER(@.tablename))
EXEC ('SELECT ''' + @.tablename + ''' = COUNT(*) FROM '
+ @.tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @.tablename
IF (@.@.FETCH_STATUS <> -1) GOTO table_loop
GO

The errors are:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist

The database is connected. Table Person.AddressType is a part of it.

What is wrong?

Thanks.

Did you get this from the GOTO help topic? This note is in the topic right above the example:

Note:

The tnames_cursor cursor is not defined. This example is for illustration only.

so effectively this example is expected not to work. If you need some specific help on using the GOTO syntax, I would suggest asking in the T-SQL forum, that's where the syntax gurus hang out.

Mike

get an arithmetic overflow with count(*)

Doing a select count(*) on a table that I believe has more rows than the max
value of an int gives me an arithmetic overflow.
How can I rewrite my query to find the no. of rows ?Hi
Look up COUNT_BIG in Books online.
John
"Hassan" wrote:

> Doing a select count(*) on a table that I believe has more rows than the m
ax
> value of an int gives me an arithmetic overflow.
> How can I rewrite my query to find the no. of rows ?
>
>

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 all unused numbers in range

What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.

For instance say I have the following data in table:

Account Name
--- ---
50100 Test1
50105 Test2
50106 Test4
..
..
..
I should see the results:
50101
50102
50103
50104

because 50101-50104 are available account numbers since not currently in
table.

Thanks."Jason" <NO-SPAM-xyz@.msn.com> wrote in message news:525Ka.43802$hI1.7823@.nwrddc01.gnilink.net...
> What I am trying to do is get all of the records in a table that are out of
> sequence so I know which account numbers I can reuse. I have a range
> of account numbers from 50100 to 70100. I need to know which account
> numbers are not stored in the table (not currently used) so I can use.
> For instance say I have the following data in table:
> Account Name
> --- ---
> 50100 Test1
> 50105 Test2
> 50106 Test4
> .
> .
> .
> I should see the results:
> 50101
> 50102
> 50103
> 50104
> because 50101-50104 are available account numbers since not currently in
> table.
> Thanks.

Here's a UDF that will fill in all missing integers between a
lower bound and an upper bound, both inclusive. By default,
the lower bound is 50100 and the upper bound is 70100.

CREATE TABLE Accounts
(
account_id INT NOT NULL PRIMARY KEY,
account_name VARCHAR(10) NOT NULL
)

CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)

INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d + 10000 * TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands

CREATE FUNCTION IntervalGaps
(@.lower INT = 50100, @.upper INT = 70100)
RETURNS @.missing TABLE (n INT NOT NULL PRIMARY KEY)
AS
BEGIN
IF @.lower > @.upper
RETURN
INSERT INTO @.missing (n)
SELECT @.lower + I.n
FROM (SELECT MIN(account_id)
FROM Accounts) AS A(least_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A.least_id - @.lower
INSERT INTO @.missing (n)
SELECT A1.account_id + I.n + 1
FROM Accounts AS A1
INNER JOIN
Accounts AS A2
ON A2.account_id > A1.account_id + 1 AND
NOT EXISTS (SELECT *
FROM Accounts
WHERE account_id > A1.account_id AND
account_id < A2.account_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A2.account_id - A1.account_id - 1
INSERT INTO @.missing (n)
SELECT A.greatest_id + I.n + 1
FROM (SELECT MAX(account_id)
FROM Accounts) AS A(greatest_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < @.upper - A.greatest_id
RETURN
END

-- Sample data
INSERT INTO Accounts (account_id, account_name)
SELECT 50102, 'test1'
UNION ALL
SELECT 50105, 'test2'
UNION ALL
SELECT 50106, 'test4'

SELECT n AS account_id
FROM IntervalGaps(DEFAULT, DEFAULT)

account_id
50100
50101
50103
50104
50107
50108
...
70100

Regards,
jag|||SET NOCOUNT ON

declare @.lowwer as int
declare @.upper as int

declare @.cur as int

set @.lowwer = 50100
set @.upper = 50200

set @.cur = @.lowwer

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing
--temp table to hold list of missing
create table #missing (
Missing int
--,NotMissing int
)

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Account]'))
Drop table #Account
-- temp table to hold your range
create table #Account(
Account_number int
)

insert into #Account (Account_number)
select Account from [your table]
where inv_number like '[0-9][0-9][0-9][0-9][0-9][0-9]'
and inv_number between @.lowwer and @.upper
-- and any other filter you think would be handy

While( @.cur between @.lowwer and @.upper)
Begin --while

if not exists (select Account_number from #Account
where inv_number = @.cur)
Begin --if
insert into #missing (Missing) values (@.cur)
End --if
--else
--Begin --else
--insert into #missing (NotMissing) values (@.cur)
--End --else

set @.cur = @.cur + 1

End --while
-- return the results
select * from #missing
--clean up
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Inv]'))
Drop table #Inv

Get all the records of the table from the SQL Server Database

Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.

Does anyone have a solution for me? Or there be other way?
I am always waiting for ur suggestion.You can take the backup of the database and then restore. This will restore everything (Table structure / SP's etc along with data.

Check the article for backup/restore:

http://vyaskn.tripod.com/backup_and_restore_with_sql_litespeed.htm|||Thank a lot.

Get all the logins

Hi,
How can I get all the login lists in my SQL Server 7.0 database.
And how to get the user list.
Since I was asked to provide such a list.
Thanks in advance
Frank
Check the sp_helplogins and sp_helpuser system stored procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>
|||There are many options... Below are two:
EXEC sp_helplogins
EXEC sp_helpuser
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank" <wangping@.lucent.com> wrote in message news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>
|||Frank wrote:
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
Try sp_helplogins for a list of logins. Use sp_helpuser for information
about each user in a database.
David G.
|||Dejan, Tibor, David,
Thanks very much
Frank
"Frank" <wangping@.lucent.com> wrote in message
news:#VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>

Get all the logins

Hi,
How can I get all the login lists in my SQL Server 7.0 database.
And how to get the user list.
Since I was asked to provide such a list.
Thanks in advance
FrankCheck the sp_helplogins and sp_helpuser system stored procedures.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>|||There are many options... Below are two:
EXEC sp_helplogins
EXEC sp_helpuser
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank" <wangping@.lucent.com> wrote in message news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>|||Frank wrote:
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
Try sp_helplogins for a list of logins. Use sp_helpuser for information
about each user in a database.
--
David G.|||Dejan, Tibor, David,
Thanks very much
Frank
"Frank" <wangping@.lucent.com> wrote in message
news:#VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>

Get all the logins

Hi,
How can I get all the login lists in my SQL Server 7.0 database.
And how to get the user list.
Since I was asked to provide such a list.
Thanks in advance
FrankCheck the sp_helplogins and sp_helpuser system stored procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Frank" <wangping@.lucent.com> wrote in message
news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>|||There are many options... Below are two:
EXEC sp_helplogins
EXEC sp_helpuser
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank" <wangping@.lucent.com> wrote in message news:%23VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl.
.
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>|||Frank wrote:
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
Try sp_helplogins for a list of logins. Use sp_helpuser for information
about each user in a database.
--
David G.|||Dejan, Tibor, David,
Thanks very much
Frank
"Frank" <wangping@.lucent.com> wrote in message
news:#VJn3cwiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I get all the login lists in my SQL Server 7.0 database.
> And how to get the user list.
> Since I was asked to provide such a list.
> Thanks in advance
> Frank
>sql

Get all table names with a given column name

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
SashiSelect table_name from information_Schema.columns where
column_name='column name'

Madhivanan

Sashi wrote:

Quote:

Originally Posted by

|||How can I do it?

One method is to query the INFORMATION_SCHEMA views:

SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE' AND
c.COLUMN_NAME = 'column order_date'
ORDER BY
c.TABLE_SCHEMA,
c.TABLE_NAME

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sashi" <smalladi@.gmail.comwrote in message
news:1152627046.364476.199010@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Get all SQL Server Names

hi,
can someone tell me how can i get all sql server names in my network.
thanks

You can use System.Data.Sql.SqlDataSourceEnumerator, please refer to:

http://msdn2.microsoft.com/en-us/library/a6t1z9x2.aspx

Get All Servers and Databases from MSSQL Server 2005 Express

Is there any way to get all available servers and databases from Microsoft SQL Server 2005 Express without connecting to it at all? I am trying to write an application that uses SS05E and I would like to have an option to display all available servers and databases.

check SQLCMD. Form command prompt ...

for all the sql server instance in a network - sqlcmd -L

to get all the database name from a sql server instance -- sqlcmd -q"Select *from sys.databases' -SserverName -Ppassword

Madhu

|||There is no way to pull a list of databases from the servers without connecting to the databases. sqlcmd with the -L switch can output all the SQL Servers on your Windows network. You would need to log into each one and pull the database list from the master.dbo.sysdatabases or sys.databases objects to see what databases are available on each.|||That will work fine. Is there any way I can run sqlcmd -L in a vb.net program and get the results put into a string array? I am writing an application that uses SQL Server and I want the servers/databases to be put into combo boxes or list boxes to make it easier to select which server/database to use.|||

I found something google that will work perfectly for what I need. Here is the code:

Code Snippet

Try

Dim myprocess As New Process

Dim StartInfo As New System.Diagnostics.ProcessStartInfo

StartInfo.FileName = "cmd" 'starts cmd window

StartInfo.RedirectStandardInput = True

StartInfo.RedirectStandardOutput = True

StartInfo.UseShellExecute = False 'required to redirect

StartInfo.CreateNoWindow = True 'creates no cmd window

myprocess.StartInfo = StartInfo

myprocess.Start()

Dim SR As System.IO.StreamReader = myprocess.StandardOutput

Dim SW As System.IO.StreamWriter = myprocess.StandardInput

SW.WriteLine("sqlcmd -L") 'the command you wish to run.....

SW.WriteLine("exit") 'exits command prompt window

Me.txtOutput.Text = SR.ReadToEnd 'returns results of the command window

SW.Close()

SR.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Thanks for your help in getting me this far! I really appreciate it

Get all records from Last Two Days?

Hi Everyone,

I use the following to get records from the last two days in MySql:
where date_entered <= curdate() and date_entered >=
DATE_SUB(curdate(),INTERVAL 2 day)

I'm looking to do the same in MS-Sql server but I'm just not getting it.
I've got this so far which does not work:
where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2
day)

then I tried this:
WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)
Essentially, I need all records from the last two days.

Any help or guidance in this matter would be greatly appreciated.

-JohnyBTry:

WHERE hit_date >= GETDATE() - 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John" <nothanks@.nope.com> wrote in message
news:7JKdnbE0F6Nc-tjZRVn-uQ@.adelphia.com...
Hi Everyone,

I use the following to get records from the last two days in MySql:
where date_entered <= curdate() and date_entered >=
DATE_SUB(curdate(),INTERVAL 2 day)

I'm looking to do the same in MS-Sql server but I'm just not getting it.
I've got this so far which does not work:
where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2
day)

then I tried this:
WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)
Essentially, I need all records from the last two days.

Any help or guidance in this matter would be greatly appreciated.

-JohnyB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:4Sd1g.5357$wK1.227431@.news20.bellglobal.com.. .
> Try:
> WHERE hit_date >= GETDATE() - 2
>
> --
> Tom

Yes. That's it.

Thanks!

> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "John" <nothanks@.nope.com> wrote in message
> news:7JKdnbE0F6Nc-tjZRVn-uQ@.adelphia.com...
> Hi Everyone,
> I use the following to get records from the last two days in MySql:
> where date_entered <= curdate() and date_entered >=
> DATE_SUB(curdate(),INTERVAL 2 day)
> I'm looking to do the same in MS-Sql server but I'm just not getting it.
> I've got this so far which does not work:
> where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL
> 2
> day)
> then I tried this:
> WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>
> Essentially, I need all records from the last two days.
> Any help or guidance in this matter would be greatly appreciated.
> -JohnyB|||Use the dateadd (Or Datediff) for that:

hit_date >= DATEADD(d,-2,GETDATE()), keep in mind that this will
substract the days including the current time, so a 04/19/2006 1:09
will result in 04/17/2006 1:09.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||John (nothanks@.nope.com) writes:
> I use the following to get records from the last two days in MySql:
> where date_entered <= curdate() and date_entered >=
> DATE_SUB(curdate(),INTERVAL 2 day)
> I'm looking to do the same in MS-Sql server but I'm just not getting it.
> I've got this so far which does not work:
> where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL
2
> day)
> then I tried this:
> WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>
> Essentially, I need all records from the last two days.

Since you appear to be trying out syntax at random, I must ask: did
you ever consider to consult Books Online?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do you mean yesterday and today's data only?

Select * from yourtable
where datecol>=Dateadd(day,datediff(day,o,getdate()),-1)

Madhivanan

Get all records from 1 table

I have a view with the following FROM clause. I want to be able to get ALL
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql

Get all records

Hi I have a table route with

routeid name

1 a

2 b

3 c

4 d

5 e

10 j

exExposure

caseid routeid

200 1

300 2

Now I have written a query which will join some other tables to get the results

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

When i run this query I am getting only the routes which have the values exist in exExposure table.If for some routes like i, j I don't have corresponding casesids.But i need to get all the routes which exist in Route table even they don't have caseids.

For this i am trying like this:

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

Union

Select er.RouteID,er.Name,0,0

From Route r

where r.routeID Not IN (Selelct RouteID From above Selelct query with the same where condtions and joins)

Any other better way for getting the unmatched routes other than this.

Thanks in advance.

I'm not sure what your question is, but I'll take a guess.

If you want the original query to contain all Routes (Route table), then name the Route table as the FIRST table in the series of joins, OR, in the current query, use a RIGHT JOIN with Routes. Either option 'should' give you what you seek.

|||

You can get your desired result using the following query..

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

Left Join exExposure e on c.caCaseID=e.caCaseID And {all Your exExposure table based conditions}

Left Join Route er on er.RouteID=e.RouteID And {all your Route table based conditions}

where {other conditions}

|||

Hi,

I placed all the ExExposure table condion after the exExposure table join this is for Route table also. But now also it is giving Routes which have the CaseID s only. Any other way.

Thanks in advance.

|||

The following query will be help you...

Code Snippet

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from Route er

Left Join exExposure e on er.RouteID=e.RouteID

Left Join cacase c on c.caCaseID=e.caCaseID

where other condition

Get All Possible Values of a Report Parameter

Hi,
I need an application that displays reports, and prompts the user
for the parameter values. I have used the GetReportParameters() to retrieve
the list of parameters. However, i could not find a way to get the available
values when the parameter is of a list type. For example a countries
parameter would include a number of countries to choose from ( retrieved
from a dataset ). How could i retrieve the list of all the countries
dynamically? Can someone help me out.
Thanks,
Loui MerciecaHi Loui:
It sounds as if you are looking for the ValidValues property of a
parameter. See my article here for some tips:
Using GetReportParameters
http://odetocode.com/Articles/123.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 29 Sep 2004 12:20:06 +0200, "Loui Mercieca" <loui@.gfi.com>
wrote:
>Hi,
> I need an application that displays reports, and prompts the user
>for the parameter values. I have used the GetReportParameters() to retrieve
>the list of parameters. However, i could not find a way to get the available
>values when the parameter is of a list type. For example a countries
>parameter would include a number of countries to choose from ( retrieved
>from a dataset ). How could i retrieve the list of all the countries
>dynamically? Can someone help me out.
>Thanks,
>Loui Mercieca
>|||Make sure to pass in true for the forRendering parameter. Check here for
more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_6x4n.asp
Here is the relevant text from that page:
If ForRendering has a value of false, the parameter meta data returned
represents the parameter data that is currently associated with the
specified report. If any parameters values are based on a query and you are
interested in returning the query-based parameters valid values list, you
need to set ForRendering to true, In addition, for query based parameters,
you need to ensure that you have passed in all of the credential information
required to return the query parameters.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Loui Mercieca" <loui@.gfi.com> wrote in message
news:eeseq3gpEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need an application that displays reports, and prompts the user
> for the parameter values. I have used the GetReportParameters() to
retrieve
> the list of parameters. However, i could not find a way to get the
available
> values when the parameter is of a list type. For example a countries
> parameter would include a number of countries to choose from ( retrieved
> from a dataset ). How could i retrieve the list of all the countries
> dynamically? Can someone help me out.
> Thanks,
> Loui Mercieca
>|||THANK YOU! That ForRendering = true worked great for me. I was having
a similar problem. I wish they explained better what all of these
setting were. But thanks to you, I bypassed this problem.

Get all databases in a SQL db

Is it possible to programmatically get a list of all the databases in a SQL
server db ?
TIA,
-FatemaSELECT name FROM master..sysdatabases
Jacco Schalkwijk
SQL Server MVP
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
> Is it possible to programmatically get a list of all the databases in a
> SQL
> server db ?
> TIA,
> -Fatema|||Thanks for the prompt reply.
I also need answers for the following:
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-Fatema
"Jacco Schalkwijk" wrote:

> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>
>|||Jacco
I can't believe that Jacco has suggested to query a system table :-)
select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>|||:-)
Jacco Schalkwijk
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Olf6sSoPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Jacco
> I can't believe that Jacco has suggested to query a system table :-)
> select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
>

Thursday, March 29, 2012

Get Blocked Objects

Get Backup Directory

Get back data from Reports

Get back calculated column from insert

Get Available free space for data file

Get Available free space for data file

Get Available free space for data file

Get Authentication Error in upgrade SQL 2005 named instance to SP1 TCP

Get Array value from a loop?

Get Ancestor and Descendant in a Hierarchy

Get Analysis Services Version string in c#.net 2.0

get an error on a sample code

get an arithmetic overflow with count(*)

Get all User Databases

Get all unused numbers in range

Get all the records of the table from the SQL Server Database

Get all the logins

Get all the logins

Get all the logins

Get all table names with a given column name

Get all SQL Server Names

Get All Servers and Databases from MSSQL Server 2005 Express

Get all records from Last Two Days?

Get all records from 1 table

Get all records

Get All Possible Values of a Report Parameter

Get all databases in a SQL db

Grid display

Blog Archive

About Me