HELP!!! Can not restore database from only back up I have

I had to transfer my database from one server to another. Both are running SQL 2000. I backed the database up and moved it to the new server however when I try to do a restore I get the following message:

The media set for database "xxx' has 2 family members but only 1 are provided. RESTORE DATABASE is terminating abnormally.

I did some searching and found a couple of posts that mentioned something about the back up including 2 files.

My problem is that the machine I backed this up from crashed when it was shut down and would not reboot so all of the data and the original database do not exist. Is there any hope of restoring this database. It is pretty critical.

Thanks,

Melissa :-)

Do "restore filelistonly from <backup_device>" to see what is in the backup device. Also, please post the "restore" command here so we can see what's going on.|||When you say "My problem is that the machine I backed this up from crashed when it was shut down and would not reboot so all of the data and the original database do not exist" - can you pull the drive that contained the original databases out of the machine and add it to the new one? (I'm assuming from your description that the database is on a DAS drive rather than a SAN)|||

I wish. The administrator who was trying to get the machine to boot determined it was beyond help and reformatted the drive and installed a new operating system on it. So all is lost.

Melissa : -(

|||

Ok here is the results from the restore filelistonly we ran:

LiveStats.XSP

C:\Program Files\Microsoft SQL Server\MSSQL\Data\LiveStats.XSP.mdf D PRIMARY 1188560896 35184372080640 LiveStats.XSP_log

C:\Program Files\Microsoft SQL Server\MSSQL\Data\LiveStats.XSP_log.LDF L NULL 13623296 35184372080640

I just did the restore from the menu.

|||Can you post the restore command you're using please?|||

I am using the restore wizard. Right clicking on the blank database (I also tried restoring without creating a blank database) Selecting Restore. Then I select From Device>>>browse to the location the back up file is>>>Restore backup set...database complete and I am not changing anything under the options tab.

Melissa :-)

|||

Please! Please! Please! tell me there is a way to restore this database. It is the only copy I have of webstats for almost 100 clients for the past year. The raw files were on the same hard drive that got wiped out.

Melissa :-)

|||

What happens when you run this (directly through a client connection, not from any wizard):

restore database LiveStats.XSP from <backupdevice>

|||Server: Msg 3132, Level 16, State 1, Line 1
The media set for database 'LiveStatsXSP' has 2 family members but only 1 are provided. All members must be provided.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.|||

Greetings.

It looks as if there is more to the backup than the one file which you are specifying.

Is there a chance that your backup produced more than one file, and that you still have access to both?

The command RESTORE LABELONLY FROM <backup_device> will tell us more about the mackup media.

|||

Here are the results from the Restore Label Only

NULL {525AA069-12A6-4E37-92CF-737C4FD9E4E2} 2 2 {F9E38D61-0000-0000-0000-000000000000} 1 0 NULL Microsoft SQL Server 4608 2006-06-30 16:42:25.000

Unfortunately that is the only file that was zipped up and moved to the new server. :-(

|||

That error tells us that when the backup was done, it was a striped backup, so the data was written to two different files in alternating stripes similar to the way a striped disk works. That means that we have to find that other file for us to have any chance of restoring the database.

The good news is that it is highly likely to be in the same location as the first backup file that you're using to restore.

The bad news is that without that other file, we cannot restore the database. Do you have ANY other copies of the backup anywhere? Even a system backup might be helpful.

|||Wow. Allowing the user to stripe across two devices is so risky. I was hoping to use a dummy striped member but it looks like you have the GUID set for each mediaset. Thus, the restore still fails. Another suggestion , make it like RAID-5. Only allow striping across 3 or more devices.|||

I had a feeling the news was not going to be good. It sounds like I am pretty much out of luck.I can not find a back up of that data anywhere.

I guess some lessons are just more painful to learn than others. If there is anything else I can try please let me know.

Melissa

sql

HELP!!! Can not restore database from only back up I have

I had to transfer my database from one server to another. Both are running SQL 2000. I backed the database up and moved it to the new server however when I try to do a restore I get the following message:

The media set for database "xxx' has 2 family members but only 1 are provided. RESTORE DATABASE is terminating abnormally.

I did some searching and found a couple of posts that mentioned something about the back up including 2 files.

My problem is that the machine I backed this up from crashed when it was shut down and would not reboot so all of the data and the original database do not exist. Is there any hope of restoring this database. It is pretty critical.

Thanks,

Melissa :-)

Do "restore filelistonly from <backup_device>" to see what is in the backup device. Also, please post the "restore" command here so we can see what's going on.|||When you say "My problem is that the machine I backed this up from crashed when it was shut down and would not reboot so all of the data and the original database do not exist" - can you pull the drive that contained the original databases out of the machine and add it to the new one? (I'm assuming from your description that the database is on a DAS drive rather than a SAN)|||

I wish. The administrator who was trying to get the machine to boot determined it was beyond help and reformatted the drive and installed a new operating system on it. So all is lost.

Melissa : -(

|||

Ok here is the results from the restore filelistonly we ran:

LiveStats.XSP

C:\Program Files\Microsoft SQL Server\MSSQL\Data\LiveStats.XSP.mdf D PRIMARY 1188560896 35184372080640 LiveStats.XSP_log

C:\Program Files\Microsoft SQL Server\MSSQL\Data\LiveStats.XSP_log.LDF L NULL 13623296 35184372080640

I just did the restore from the menu.

|||Can you post the restore command you're using please?|||

I am using the restore wizard. Right clicking on the blank database (I also tried restoring without creating a blank database) Selecting Restore. Then I select From Device>>>browse to the location the back up file is>>>Restore backup set...database complete and I am not changing anything under the options tab.

Melissa :-)

|||

Please! Please! Please! tell me there is a way to restore this database. It is the only copy I have of webstats for almost 100 clients for the past year. The raw files were on the same hard drive that got wiped out.

Melissa :-)

|||

What happens when you run this (directly through a client connection, not from any wizard):

restore database LiveStats.XSP from <backupdevice>

|||Server: Msg 3132, Level 16, State 1, Line 1
The media set for database 'LiveStatsXSP' has 2 family members but only 1 are provided. All members must be provided.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.|||

Greetings.

It looks as if there is more to the backup than the one file which you are specifying.

Is there a chance that your backup produced more than one file, and that you still have access to both?

The command RESTORE LABELONLY FROM <backup_device> will tell us more about the mackup media.

|||

Here are the results from the Restore Label Only

NULL {525AA069-12A6-4E37-92CF-737C4FD9E4E2} 2 2 {F9E38D61-0000-0000-0000-000000000000} 1 0 NULL Microsoft SQL Server 4608 2006-06-30 16:42:25.000

Unfortunately that is the only file that was zipped up and moved to the new server. :-(

|||

That error tells us that when the backup was done, it was a striped backup, so the data was written to two different files in alternating stripes similar to the way a striped disk works. That means that we have to find that other file for us to have any chance of restoring the database.

The good news is that it is highly likely to be in the same location as the first backup file that you're using to restore.

The bad news is that without that other file, we cannot restore the database. Do you have ANY other copies of the backup anywhere? Even a system backup might be helpful.

|||Wow. Allowing the user to stripe across two devices is so risky. I was hoping to use a dummy striped member but it looks like you have the GUID set for each mediaset. Thus, the restore still fails. Another suggestion , make it like RAID-5. Only allow striping across 3 or more devices.|||

I had a feeling the news was not going to be good. It sounds like I am pretty much out of luck.I can not find a back up of that data anywhere.

I guess some lessons are just more painful to learn than others. If there is anything else I can try please let me know.

Melissa

HELP! Please Server Crash

I have had a piece of hardware fail (motherboard died),
and I don't have a back up of the entire SQL Box. What I
do have is all the files, master, tempdb, etc etc... in
the directory on the hard drive they were on. They are
fine.
I can't get SQL to run on another box tho, and there is a
humongous amount of jobs, and dts' that I need from there.
The data is fine, but is there a way to get this server
to run on another box?
I am in desparate need.
THank you
SvenInstall SQL Server on the new box .If you remember the paths for the install
,set it up in that fashion. Once completed, stop the SQL server service and
if you remember the exact path names/locations of your data files and log
files , you could restore all those files exactly in the same location
before you start the SQL Server instance on the other box. Give that a try..
"Sven Hansen" <shansen@.westjet.com> wrote in message
news:00c001c3c914$177c3210$a001280a@.phx.gbl...
> I have had a piece of hardware fail (motherboard died),
> and I don't have a back up of the entire SQL Box. What I
> do have is all the files, master, tempdb, etc etc... in
> the directory on the hard drive they were on. They are
> fine.
> I can't get SQL to run on another box tho, and there is a
> humongous amount of jobs, and dts' that I need from there.
> The data is fine, but is there a way to get this server
> to run on another box?
> I am in desparate need.
> THank you
> Sven
>|||After you install SQL , then stop the services and copy all of the files
back. You would then have to use sp_attach_db to attach the non-system
databases..
THe copying over of master, model , tempdb and msdb may or may not work...
The very least thing is that you MUST use the same collation that was
originally used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sven Hansen" <shansen@.westjet.com> wrote in message
news:00c001c3c914$177c3210$a001280a@.phx.gbl...
> I have had a piece of hardware fail (motherboard died),
> and I don't have a back up of the entire SQL Box. What I
> do have is all the files, master, tempdb, etc etc... in
> the directory on the hard drive they were on. They are
> fine.
> I can't get SQL to run on another box tho, and there is a
> humongous amount of jobs, and dts' that I need from there.
> The data is fine, but is there a way to get this server
> to run on another box?
> I am in desparate need.
> THank you
> Sven
>sql

Help! Parameter Conversion Unix Time

My dates are in unix time and the paramaters are entered as 7/6/2004. How can I convert the date in the parameter field back into unix time so it will retireve the records.
Unix time is the number of seconds from 1/1/1970 at 12:00am
ThanksFor a variable dateTime of type DateTime you can use the follwing
expression:
dateTime.Subtract(new DateTime(1970, 1, 1)).TotalSeconds
This returns floating point value and may contain fractions of a second.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:52981B6F-5FB5-4638-8959-8CFE298D5503@.microsoft.com...
> My dates are in unix time and the paramaters are entered as 7/6/2004. How
can I convert the date in the parameter field back into unix time so it will
retireve the records.
> Unix time is the number of seconds from 1/1/1970 at 12:00am
> Thanks

Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison

HELP! Corrupt MDF

While on vacation, a customer of ours had their RAID die and nobody seemed
to follow a good recovery process so when I came back all I was left with is
a bad MDF. When I use EM to attach I get Error 823: I/O Error (Bad Page ID)
...
Is there any way to force this to attach?Well I got it attached but I think it's toast because everything I try comes
back with an error of some kind or other.
Of course, backups were not functioning (not my network, I'm just trying to
help :p)
Me-thinks they're screwed.
"Bob Castleman" <nomail@.here> wrote in message
news:euC8AIbeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> While on vacation, a customer of ours had their RAID die and nobody seemed
> to follow a good recovery process so when I came back all I was left with
> is a bad MDF. When I use EM to attach I get Error 823: I/O Error (Bad Page
> ID) ...
> Is there any way to force this to attach?
>|||Hey Bob,
I'm contractor and just got a call similar to your situation. But worse yet
is that the IT dept caused it! They had failed drive in RAID 5 config and
were pulling it to replace with spare but yanked wrong drive which then
failed the array...
There proud of themselves for forcing some kind of rebuild but they got 2
suspect DBs so they thought they'd detach and re-attach. Detach worked,
re-attach is giving the 823 torn page error.
So now they call me. Last back was mid-March. If I can recover any data,
I'd sure like to. How did you get yours to re-attach'
peace - kathy|||Hey Bob,
I'm contractor and just got a call similar to your situation. But worse yet
is that the IT dept caused it! They had failed drive in RAID 5 config and
were pulling it to replace with spare but yanked wrong drive which then
failed the array...
There proud of themselves for forcing some kind of rebuild but they got 2
suspect DBs so they thought they'd detach and re-attach. Detach worked,
re-attach is giving the 823 torn page error.
So now they call me. Last back was mid-March. If I can recover any data,
I'd sure like to. How did you get yours to re-attach'
peace - kathy|||Your best bet is to restore from a backup. If no backup is available, you
can do the following and live with some loss_of_data.
1. create a new empty db with the same name/path.
2. put the db into emergency mode (see alter db for sql2k5, modify
sysdatabases for sql2k)
3. take the db offline (see alter db)
4. swap the db files
5. put the db online (alter db)
6. run dbcc checkdb
-oj
"Kathy_Automation" <KathyAutomation@.discussions.microsoft.com> wrote in
message news:3039CC2A-8DF5-4B2C-A6AD-5016A681DA57@.microsoft.com...
> Hey Bob,
> I'm contractor and just got a call similar to your situation. But worse
> yet
> is that the IT dept caused it! They had failed drive in RAID 5 config and
> were pulling it to replace with spare but yanked wrong drive which then
> failed the array...
> There proud of themselves for forcing some kind of rebuild but they got 2
> suspect DBs so they thought they'd detach and re-attach. Detach worked,
> re-attach is giving the 823 torn page error.
> So now they call me. Last back was mid-March. If I can recover any data,
> I'd sure like to. How did you get yours to re-attach'
> peace - kathy|||Your best bet is to restore from a backup. If no backup is available, you
can do the following and live with some loss_of_data.
1. create a new empty db with the same name/path.
2. put the db into emergency mode (see alter db for sql2k5, modify
sysdatabases for sql2k)
3. take the db offline (see alter db)
4. swap the db files
5. put the db online (alter db)
6. run dbcc checkdb
-oj
"Kathy_Automation" <KathyAutomation@.discussions.microsoft.com> wrote in
message news:3039CC2A-8DF5-4B2C-A6AD-5016A681DA57@.microsoft.com...
> Hey Bob,
> I'm contractor and just got a call similar to your situation. But worse
> yet
> is that the IT dept caused it! They had failed drive in RAID 5 config and
> were pulling it to replace with spare but yanked wrong drive which then
> failed the array...
> There proud of themselves for forcing some kind of rebuild but they got 2
> suspect DBs so they thought they'd detach and re-attach. Detach worked,
> re-attach is giving the 823 torn page error.
> So now they call me. Last back was mid-March. If I can recover any data,
> I'd sure like to. How did you get yours to re-attach'
> peace - kathy

HELP! Corrupt MDF

While on vacation, a customer of ours had their RAID die and nobody seemed
to follow a good recovery process so when I came back all I was left with is
a bad MDF. When I use EM to attach I get Error 823: I/O Error (Bad Page ID)
...
Is there any way to force this to attach?Well I got it attached but I think it's toast because everything I try comes
back with an error of some kind or other.
Of course, backups were not functioning (not my network, I'm just trying to
help :p)
Me-thinks they're screwed.
"Bob Castleman" <nomail@.here> wrote in message
news:euC8AIbeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> While on vacation, a customer of ours had their RAID die and nobody seemed
> to follow a good recovery process so when I came back all I was left with
> is a bad MDF. When I use EM to attach I get Error 823: I/O Error (Bad Page
> ID) ...
> Is there any way to force this to attach?
>|||Hey Bob,
I'm contractor and just got a call similar to your situation. But worse yet
is that the IT dept caused it! They had failed drive in RAID 5 config and
were pulling it to replace with spare but yanked wrong drive which then
failed the array...
There proud of themselves for forcing some kind of rebuild but they got 2
suspect DBs so they thought they'd detach and re-attach. Detach worked,
re-attach is giving the 823 torn page error.
So now they call me. Last back was mid-March. If I can recover any data,
I'd sure like to. How did you get yours to re-attach'
peace - kathy|||Your best bet is to restore from a backup. If no backup is available, you
can do the following and live with some loss_of_data.
1. create a new empty db with the same name/path.
2. put the db into emergency mode (see alter db for sql2k5, modify
sysdatabases for sql2k)
3. take the db offline (see alter db)
4. swap the db files
5. put the db online (alter db)
6. run dbcc checkdb
--
-oj
"Kathy_Automation" <KathyAutomation@.discussions.microsoft.com> wrote in
message news:3039CC2A-8DF5-4B2C-A6AD-5016A681DA57@.microsoft.com...
> Hey Bob,
> I'm contractor and just got a call similar to your situation. But worse
> yet
> is that the IT dept caused it! They had failed drive in RAID 5 config and
> were pulling it to replace with spare but yanked wrong drive which then
> failed the array...
> There proud of themselves for forcing some kind of rebuild but they got 2
> suspect DBs so they thought they'd detach and re-attach. Detach worked,
> re-attach is giving the 823 torn page error.
> So now they call me. Last back was mid-March. If I can recover any data,
> I'd sure like to. How did you get yours to re-attach'
> peace - kathy

HELP! BACK button not working on matrix

I have a report that has a summary matrix containing all urls for a client
which will drill to a second report which is a detail matrix that shows
detail for the selected url from report #1. The problem is that when they
drill to report #2 ... if they expand the matrix to show individual detail
for past 30 days and then want to go back to the previous report and choose
another url ... the back button wont work unless they hit it twice. If they
happen to expand AND contract the detail and THEN want to go back to the prev
report they have to hit the browser back button THREE times. Report manager
or the browser seems to be keeping track of key strokes - however my user is
NOT HAPPY. How can I solve this? I need the equivalent of a BACK button.
But then ... do I have to put in all the parameters that the first report
required again? I am not sure what to do but I have to do it fast!Are you using the browser's back button or the report's back button? If
you're not using the browser's, try it. That should bring you back to
report #1.
You could also launch report two in a new window so that they could
just toggle between the two as they see fit, but I've had trouble
getting a report to launch in a new window.
Mike|||Mike,
Thanks for your reply. I am using the browser's back button (the report
doesnt have one that I can see that will take it to the previous report). I
have not had this problem with other reports ... just the matrix report and I
it has to do with the toggle to expand/contract detail. Launching another
window from the summary (first rpt) isnt allowed because this is deployed to
a portal and I am already launching a separate window there from an asp .net
page to pass report parameters to the first report. They dont want yet
*another* window to get the detail. Any other suggestions? I am not an asp
.net or vb .net wizard - someone else coded the initial UI that launches the
report so if you suggest something along those lines ... please be specific
with an example if possible. Thanks verry much!!
"Bassist695" wrote:
> Are you using the browser's back button or the report's back button? If
> you're not using the browser's, try it. That should bring you back to
> report #1.
> You could also launch report two in a new window so that they could
> just toggle between the two as they see fit, but I've had trouble
> getting a report to launch in a new window.
> Mike
>

HELP! - Cast from DBNull when there is Data

Hi,

I have built a few pages and a stored procedure and a class on the back of a SQL2000 dbase. and I get the following error:

Cast from type 'DBNull' to type 'String' is not valid.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.

Source Error:

Line 111: Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATALine 112:Line 113: myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)Line 114: myWorkJobs.WorkID = CStr(parameterWorkID.Value)Line 115: myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()

Source File:C:\Inetpub\wwwroot\Commerce\Components\WorkJobs.vb Line:113

My Database has 1 line of data (for testing) and all fields are populated. I am Querying a column called IndividualID which has a value of 3425243 at the moment. This is hardcoded in the aspx.vb at the moment.

ASPX VB:

Public Class WorkRequest
Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

'for now, send this value (stored in dbase under individualID) to the querystring

Dim IndividualID As String = "3425243"

Response.Redirect("WorkRequestMain.aspx?IndividualID=" & IndividualID)
End Sub
End Class

COMPONENTS\WorkJobs.vb (This is the class file)

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Namespace ASPNET.StarterKit.Commerce

Public Class WorkJobsDATA

Public CustomerID As String
Public WorkID As String
Public DateOfQuote As String
Public QuoteAmount As String
Public Title As Decimal
Public FirstName As String
Public Surname As String
Public FirstLine As String
Public District As String
Public Town As String
Public Postcode As String
Public Telephone As String
Public Requirements As String
Public WorkRequired As String
Public EmailAddress As String

End Class

Public Class WorkJobs

Public Function GetWorkDetails(ByVal IndividualID As String) As WorkJobsDATA

Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("SP_PendingQuotes", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterIndividualID As SqlParameter = New SqlParameter("@.IndividualID", SqlDbType.NVarChar, 50)
parameterIndividualID.Value = IndividualID
myCommand.Parameters.Add(parameterIndividualID)

Dim parameterCustomerID As SqlParameter = New SqlParameter("@.CustomerID", SqlDbType.BigInt, 8)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)

Dim parameterWorkID As SqlParameter = New SqlParameter("@.WorkID", SqlDbType.NVarChar, 50)
parameterWorkID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkID)

Dim parameterDateOfQuote As SqlParameter = New SqlParameter("@.DateOfQuote", SqlDbType.DateTime, 8)
parameterDateOfQuote.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDateOfQuote)

Dim parameterQuoteAmount As SqlParameter = New SqlParameter("@.QuoteAmount", SqlDbType.Money, 8)
parameterQuoteAmount.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterQuoteAmount)

Dim parameterTitle As SqlParameter = New SqlParameter("@.Title", SqlDbType.NVarChar, 50)
parameterTitle.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTitle)

Dim parameterFirstName As SqlParameter = New SqlParameter("@.FirstName", SqlDbType.NVarChar, 50)
parameterFirstName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstName)

Dim parameterSurname As SqlParameter = New SqlParameter("@.Surname", SqlDbType.NVarChar, 50)
parameterSurname.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterSurname)

Dim parameterFirstLine As SqlParameter = New SqlParameter("@.FirstLine ", SqlDbType.NVarChar, 50)
parameterFirstLine.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstLine)

Dim parameterDistrict As SqlParameter = New SqlParameter("@.District", SqlDbType.NVarChar, 50)
parameterDistrict.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDistrict)

Dim parameterTown As SqlParameter = New SqlParameter("@.Town", SqlDbType.NVarChar, 50)
parameterTown.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTown)

Dim parameterPostcode As SqlParameter = New SqlParameter("@.Postcode", SqlDbType.NVarChar, 50)
parameterPostcode.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterPostcode)

Dim parameterTelephone As SqlParameter = New SqlParameter("@.Telephone", SqlDbType.NVarChar, 50)
parameterTelephone.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTelephone)

Dim parameterRequirements As SqlParameter = New SqlParameter("@.Requirements", SqlDbType.NVarChar, 3500)
parameterRequirements.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRequirements)

Dim parameterWorkRequired As SqlParameter = New SqlParameter("@.WorkRequired", SqlDbType.NVarChar, 3500)
parameterWorkRequired.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkRequired)

Dim parameterEmailAddress As SqlParameter = New SqlParameter("@.EmailAddress", SqlDbType.NVarChar, 100)
parameterEmailAddress.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterEmailAddress)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

parameterEmailAddress.Value.GetType()

Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA

myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)
myWorkJobs.WorkID = CStr(parameterWorkID.Value)
myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
myWorkJobs.Title = CStr(parameterTitle.Value).Trim()
myWorkJobs.FirstName = CStr(parameterFirstName.Value).Trim()
myWorkJobs.Surname = CStr(parameterSurname.Value).Trim()
myWorkJobs.FirstLine = CStr(parameterFirstLine.Value).Trim()
myWorkJobs.District = CStr(parameterDistrict.Value).Trim()
myWorkJobs.Town = CStr(parameterTown.Value).Trim()
myWorkJobs.Postcode = CStr(parameterPostcode.Value).Trim()
myWorkJobs.Telephone = CStr(parameterTelephone.Value).Trim()
myWorkJobs.Requirements = CStr(parameterRequirements.Value).Trim()
myWorkJobs.WorkRequired = CStr(parameterWorkRequired.Value).Trim()
myWorkJobs.EmailAddress = CStr(parameterEmailAddress.Value).Trim()

Return myWorkJobs

End Function

End Class
End Namespace

And finally my stored procedure:

CREATE Procedure SP_PendingQuotes
(
@.IndividualID nvarchar,
@.CustomerID bigint OUTPUT,
@.WorkID nvarchar(50) OUTPUT,
@.DateOfQuote datetime OUTPUT,
@.QuoteAmount money OUTPUT,
@.Title nvarchar(50) OUTPUT,
@.FirstName nvarchar(50) OUTPUT,
@.Surname nvarchar(50) OUTPUT,
@.FirstLine nvarchar(50) OUTPUT,
@.District nvarchar(50) OUTPUT,
@.Town nvarchar(50) OUTPUT,
@.Postcode nvarchar(50) OUTPUT,
@.Telephone nvarchar(50) OUTPUT,
@.Requirements nvarchar(3500) OUTPUT,
@.WorkRequired nvarchar(3500) OUTPUT,
@.EmailAddress nvarchar(100) OUTPUT
)
AS

SELECT
@.IndividualID = IndividualID,
@.CustomerID = CustomerID,
@.WorkID = WorkID,
@.DateOfQuote = DateOfQuote,
@.QuoteAmount = QuoteAmount,
@.Title = Title,
@.FirstName = FirstName,
@.Surname = Surname,
@.FirstLine = FirstLine,
@.District = District,
@.Town = Town,
@.Postcode = Postcode,
@.Telephone = Telephone,
@.Requirements = Requirements,
@.WorkRequired = WorkRequired,
@.EmailAddress = EmailAddress

FROM
PendingQuotes

WHERE
IndividualID = @.IndividualID
GO

Any ideas anyone?

I appreciate this is a big amount of data, but if anyone wants to chat to me i'm available on MSN Messenger underwolvokid@.msn.com

The problem is in the stored procedure, you've defined @.IndividualID as nvarchar, specify the length, as it is defaulting to 1. Your query then returns no values because there is no record where IndividualID='3'|||

Call me muppett! It all becomes a blur after you go over and over everything a million times trying to find out whats wrong.

Thanks very much. It works now!

Help woth stored procedure!

Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi

Help with XQuery

I am trying to query an XML Datatype column in SQL Server, but getting no data back, and I suspect the XQuery but don't know what I am doing wrong:

SELECT

nref.value('AdvTxnID[1]', 'integer') AdvTxnID,

nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode

FROM

myData CROSS APPLY [xmldata].nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">

<AdvanceTxn>

<AdvTxnID>4125281</AdvTxnID>

<SvcSysCode>MS</SvcSysCode>

<LoanID>8484664</LoanID>

<AdvTypeCode>P&amp;I</AdvTypeCode>

<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>

<AdvTxnAmt>.04</AdvTxnAmt>

<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>

</AdvanceTxn>

<TxnCount>1</TxnCount>

</AdvanceTxnDoc>

What am I doing wrong?

Need to declare namespace in your xquery. Two way to do it as following example:

declare @.x xml
set @.x =
N'<AdvanceTxnDoc xmlns="http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc">
<AdvanceTxn>
<AdvTxnID>4125281</AdvTxnID>
<SvcSysCode>MS</SvcSysCode>
<LoanID>8484664</LoanID>
<AdvTypeCode>P&amp;I</AdvTypeCode>
<AdvEffDate>2005-08-25T00:00:00</AdvEffDate>
<AdvTxnAmt>.04</AdvTxnAmt>
<TxnCrtDate>2005-08-19T00:00:00</TxnCrtDate>
</AdvanceTxn>
<TxnCount>1</TxnCount>
</AdvanceTxnDoc>'

with xmlnamespaces(default 'http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc')
SELECT
nref.value('AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

SELECT
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; AdvTxnID[1]', 'integer') AdvTxnID,
nref.value('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc"; SvcSysCode[1]', 'varchar(2)') SvcSysCode
FROM
@.x.nodes('declare default element namespace "http://GMAC.RFC.COM/RCG/XML/Schema/ALM.AdvanceTxnDoc";/AdvanceTxnDoc/AdvanceTxn') AS R(nref)

|||

Worked great. Thanks!

-Kory