Question about simple query..

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.

I have the following table, which has name, address, city,state,zip
code, and phone number.

kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.

How can I erase that extra data, and get the table like this ? :

kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

First, I used this query, but it turned out all of my data was gone. :
(

delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);

Please help !What is the criteria that you would use to pic which row to keep?
jaehwang wrote:

Quote:

Originally Posted by

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.
>
I have the following table, which has name, address, city,state,zip
code, and phone number.
>
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
>
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.
>
How can I erase that extra data, and get the table like this ? :
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
First, I used this query, but it turned out all of my data was gone. :
(
>
delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);
>
Please help !
>

|||You would have to define a criteria based on which a row will be kept or
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):

DELETE FROM Address
WHERE EXISTS (
SELECT *
FROM Address AS A
WHERE A.phone = Address.phone
AND A.name < Address.name)

You can easily reverse the above condition to A.name Address.name to keep
the MAX name.

After you are done you can alter the table and add UNIQUE constraint on the
phone column to prevent duplicate data in the future, something like this:

ALTER TABLE Address ADD CONSTRAINT uphone UNIQUE (phone)

HTH,

Plamen Ratchev
http://www.SQLStudio.comsql

Question about selectcommand when using sqldatasource

I'm try to achieve the following ;

<asp:SqlDataSourceID="CardDataSource"runat="server"ConnectionString="my connectionSelectCommand="String.Format("Select*fromcardswhereactivenotlike'0'oractiveisNULLand(cardlike' {0}%'orcardlike'{0}%')", Request.QueryString("db"))"/>

I'm trying to pass a value into my query from the sqldatasource, but I'm having trouble properly using the string. This code used to be in a vb code, but theres a considerable performance difference when I have it load from my vb file and when I front load it here.

Anyone know what i would have to add or remove to make the above script work? Do I have my single/double quotes mixed up?

Why don't you try just calling a Stored Procedure?

|||

For some reason it slows up the modules that I am using. I if I embed the select string iinto sqldatasource directly the modules work quickly, but if I do it any other way it locks up. I'm using DevExpress. They don't know why it does it. They looked at my code and everything checks out. But when I do this way, everything works fine. However, I can't seem to structure this string properly.

|||

Hi,

From the code you provided, actually you are passing a parameter to the select command and make your sqldatasource to retrieve data, right?

There two ways to declare the SqlDataSource object and parameters. One is in code behind, and another way is inline. In your case, it belongs to the second scenario.

You may declare your parameters in the SelectParameters node so that it can work.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [TABLE] whereUser=@.ParameterName">
<SelectParameters>
<asp:QueryStringParameter Name="ParameterName" QueryStringField="StringField" DefaultValue="DefaultVlaue" />
</SelectParameters>
</asp:SqlDataSource>

Thanks.

|||

Well the parameter is the table itself. Does this work in that scenario as well? I have three tables but only want to use one page. So I pass the table name through the url.

|||

Hi,

If what the parameter passes is the tablename, you should assign the select statement in your code behind file, see:

string sqlstr = "select * from ";
string TableName = Request.QueryString["db"].ToString(); // After it, you should remove all the special chars like '-','*' in variable TableName in order to prevent the sql injection.

sqlstr += TableName;
this.SqlDataSource1.SelectCommand = sqlstr; // Assume that you have declared a SqlDataSource called SqlDataSource1 on your webform.

Thanks.

Question about schema collection

Hi,
I've created a schema collection with the following statement:
/****** Object: XmlSchemaCollection [dbo].[IncVarTypeTestCollection]
Script Date: 12/04/2007 09:01:56 ******/
CREATE XML SCHEMA COLLECTION [dbo].[IncVarTypeTestCollection] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" />
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
This works fine except that it won't allow Nulls in the numeric fields.
I tried to add the minoccurs="0" to see whether that would work but get an
error message saying that it is not valid in this schema context.
Can anyone help?
JS> I tried to add the minoccurs="0" to see whether that would work but
JS> get an error message saying that it is not valid in this schema
JS> context.
I find that Nillable is easier to work with. Consider:
use scratch
go
create xml schema collection foo
as '<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" nillable="true"/>
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" nillable="true"/>
<xsd:element name="vInteger" type="xsd:integer" nillable="true"/>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
declare @.don xml(foo)
declare @.t1 table(vDateTime dateTime,
vSmallInt smallint,
vVarChar varchar(20),
vInteger int)
insert into @.t1 values (getdate(),null,null,null)
select @.don=(
select convert(nvarchar(50),vDateTime,127)+'Z' as vDateTime,vSmallInt,vVarChar,vInteger
from @.t1 for xml path(''),root('inc'),elements xsinil,type)
select @.don
go
drop xml schema collection foo
go
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi,
tried that but when I run the following:
insert into incVariables
(IDField, XMLField)
values
(Null,'<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vSmallInt></vSmallInt>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
I get the following error:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ''. Location:
/*:inc[1]/*:vSmallInt[1]
|||You are trying to have your cake and eat it too.
If you want to represent a null value, you have two choices: represent it
by an element marked as nil (which is what I do, nice and explicit) OR omit
the element for the document (which is what SQL Server normally does).
What you have in your example of vSmallInt isn't a null value as XML represents
them: its an empty element. XML distinguishes between empty elements and
elements marked nil for exactly this purpose.
And you can't emit an empty element and have minOccurs=0. Since you have
an element, it occurs. Since it occurs, it must comply with the xsd:shortInt
spec.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Ken,
Thanks for your time and patience.
What I'm trying to do is convert and existing table with about a million
records into a table that contains an ID field and a XML field.
The existing table contains a large number of fields, many of which are
rarely used or if one is used, another isn't. If that makes sense.
The bits I posted with my question are from a test where I set up one field
of each type currently used to try to work out exactly how I treat each type
when writing the conversion routine.
Because I'm going to create the routine, I don't mind whether the field is
translated into an empty element or left out completely as long as I can get
the data for that record to insert itself.
Following on from your latest post, I tried leaving out the element
completely whilst leaving the XML Schema element set to nilable="True" and I
still can't get it to run, so I must be doing something worng and therefore
it's fairly obvious that I don't have a clue what I'm doing.
Any more help you could give would be appreciated otherwise it looks like
we'll have to find someone from outside to come and set things up.
Thanks
|||Hi,
I reproduced your issue at my side. The empty element cannot be validated
in SQL Server typed xml field but can be validated in IE. Unfortunately I
have not found a clear explanation regarding why the empty element failed
the validation. Anyway I will try to consult the product team for the
confirmation and let you know the response as soon as possible. I also
recommend that you leave me (changliw_at_microsoft_dot_com) an email
response so that I can timely update you when I get the answer.
Look back to your issue, as a possible workaround, you may consider the
following two methods:
1. Add the attribute 'minOccurs="0"' to those elements which are allowed
empty. When you convert the records of your existing table to XML
statements, eliminate the related marks if the fields are NULL;
For example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
minOccurs="0">
If the original vSmallInt column is NULL, ensure that the generated XML
does not include vSmallInt mark:
<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>
2. You may assign each element a default value if the element is empty. For
example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
default="0"/>
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles, thanks for your reply.
I seem to have hit something strange. I tried to create the schema
collection including the minoccurs="0" and got an error message suggesting
that it is not relevent in that part of the script. I then deleted that bit
and tried it again and it worked. This seems to have happened several times.
Has this happened to anyone else?
Once I manage to get the schema collection created, the minoccurs="0" works
OK.
|||Hi,
I could not reproduce the issue regarding minOccurs. Is it stable now?
Unfortunately I have not been able to get the confirmation from the product
team. In this case I submitted a product issue request internally to the
product team. Also we recommend that you give Microsoft feedback via
https://connect.microsoft.com/sql. Your feedback will be routed to the
product team and if there is any response from the product team, you will
get an email notification.
Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
The current problem is that the insert fails if I include empty elements and
the XML from a SELECT ... FOR XML query seems to insist on putting them in
even if I don't ask for them.
I'm OK with everything else.
Thanks
JS
|||Hi,
What is the result if you run "SELECT * FROM [tablename] FOR XML AUTO"? I
performed a test at my side and it worked fine.
My test was based on the following xml schema and table:
//1. Create a xml schema
CREATE XML SCHEMA COLLECTION [dbo].foo1 AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" minOccurs="0"
nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
//2. Create a table
CREATE TABLE [dbo].[IncVariables](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLField] [xml](CONTENT [dbo].[foo1]) NOT NULL,
CONSTRAINT [PK_IncVariables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//3. Insert into a record
INSERT INTO IncVariables values ('<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
//4. Query on the table
SELECT * FROM IncVariables FOR XML AUTO
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Question about schema collection

Hi,
I've created a schema collection with the following statement:
/****** Object: XmlSchemaCollection [dbo].[IncVarTypeTestCollection]
Script Date: 12/04/2007 09:01:56 ******/
CREATE XML SCHEMA COLLECTION [dbo].[IncVarTypeTestCollection] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" />
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
This works fine except that it won't allow Nulls in the numeric fields.
I tried to add the minoccurs="0" to see whether that would work but get an
error message saying that it is not valid in this schema context.
Can anyone help?JS> I tried to add the minoccurs="0" to see whether that would work but
JS> get an error message saying that it is not valid in this schema
JS> context.
I find that Nillable is easier to work with. Consider:
use scratch
go
create xml schema collection foo
as '<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" nillable="true"/>
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" nillable="true"/>
<xsd:element name="vInteger" type="xsd:integer" nillable="true"/>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
declare @.don xml(foo)
declare @.t1 table(vDateTime dateTime,
vSmallInt smallint,
vVarChar varchar(20),
vInteger int)
insert into @.t1 values (getdate(),null,null,null)
select @.don=(
select convert(nvarchar(50),vDateTime,127)+'Z' as vDateTime,vSmallInt,vVarCh
ar,vInteger
from @.t1 for xml path(''),root('inc'),elements xsinil,type)
select @.don
go
drop xml schema collection foo
go
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Hi,
tried that but when I run the following:
insert into incVariables
(IDField, XMLField)
values
(Null,'<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vSmallInt></vSmallInt>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
I get the following error:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ''. Location:
/*:inc[1]/*:vSmallInt[1]|||You are trying to have your cake and eat it too.
If you want to represent a null value, you have two choices: represent it
by an element marked as nil (which is what I do, nice and explicit) OR omit
the element for the document (which is what SQL Server normally does).
What you have in your example of vSmallInt isn't a null value as XML represe
nts
them: its an empty element. XML distinguishes between empty elements and
elements marked nil for exactly this purpose.
And you can't emit an empty element and have minOccurs=0. Since you have
an element, it occurs. Since it occurs, it must comply with the xsd:shortInt
spec.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Hi Ken,
Thanks for your time and patience.
What I'm trying to do is convert and existing table with about a million
records into a table that contains an ID field and a XML field.
The existing table contains a large number of fields, many of which are
rarely used or if one is used, another isn't. If that makes sense.
The bits I posted with my question are from a test where I set up one field
of each type currently used to try to work out exactly how I treat each type
when writing the conversion routine.
Because I'm going to create the routine, I don't mind whether the field is
translated into an empty element or left out completely as long as I can get
the data for that record to insert itself.
Following on from your latest post, I tried leaving out the element
completely whilst leaving the XML Schema element set to nilable="True" and I
still can't get it to run, so I must be doing something worng and therefore
it's fairly obvious that I don't have a clue what I'm doing.
Any more help you could give would be appreciated otherwise it looks like
we'll have to find someone from outside to come and set things up.
Thanks|||Hi,
I reproduced your issue at my side. The empty element cannot be validated
in SQL Server typed xml field but can be validated in IE. Unfortunately I
have not found a clear explanation regarding why the empty element failed
the validation. Anyway I will try to consult the product team for the
confirmation and let you know the response as soon as possible. I also
recommend that you leave me (changliw_at_microsoft_dot_com) an email
response so that I can timely update you when I get the answer.
Look back to your issue, as a possible workaround, you may consider the
following two methods:
1. Add the attribute 'minOccurs="0"' to those elements which are allowed
empty. When you convert the records of your existing table to XML
statements, eliminate the related marks if the fields are NULL;
For example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
minOccurs="0">
If the original vSmallInt column is NULL, ensure that the generated XML
does not include vSmallInt mark:
<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>
2. You may assign each element a default value if the element is empty. For
example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
default="0"/>
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles, thanks for your reply.
I seem to have hit something strange. I tried to create the schema
collection including the minoccurs="0" and got an error message suggesting
that it is not relevent in that part of the script. I then deleted that bit
and tried it again and it worked. This seems to have happened several times.
Has this happened to anyone else?
Once I manage to get the schema collection created, the minoccurs="0" works
OK.|||Hi,
I could not reproduce the issue regarding minOccurs. Is it stable now?
Unfortunately I have not been able to get the confirmation from the product
team. In this case I submitted a product issue request internally to the
product team. Also we recommend that you give Microsoft feedback via
https://connect.microsoft.com/sql. Your feedback will be routed to the
product team and if there is any response from the product team, you will
get an email notification.
Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
The current problem is that the insert fails if I include empty elements and
the XML from a SELECT ... FOR XML query seems to insist on putting them in
even if I don't ask for them.
I'm OK with everything else.
Thanks
JS|||Hi,
What is the result if you run "SELECT * FROM [tablename] FOR XML AUTO"? I
performed a test at my side and it worked fine.
My test was based on the following xml schema and table:
//1. Create a xml schema
CREATE XML SCHEMA COLLECTION [dbo].foo1 AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" minOccurs="0"
nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
//2. Create a table
CREATE TABLE [dbo].[IncVariables](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLField] [xml](CONTENT [dbo].[foo1]) NOT NULL,
CONSTRAINT [PK_IncVariables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//3. Insert into a record
INSERT INTO IncVariables values ('<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
//4. Query on the table
SELECT * FROM IncVariables FOR XML AUTO
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Question about restrictions

Hi. I'm writing delete queries and I have the following question: if a table
has foreign keys, and I delete the fields in the tables that references the
table I want to delete, can I delete the table or must I delete the
restrictions too?
I'm having errors and I'm thinking if that's the problem.
Regards,
Diego F.You cannot drop the table that has a column referenced by another table. You
will have to remove the constraint before you can do that.
Anith|||There are basically two general ways to do this:
1) you can declare foreign keys with the ON DELETE CASCADE option - that way
when you delete the referenced (primary key table) row the referring (foreig
n
key table) rows are deleted; or
2) you delete referring rows manually (in the procedure) or use triggers to
delete referring rows when attempting to delete the referenced row.
Of course there are a few other ways to do this, however, the most important
thing to consider is the logical goal you are trying to achieve. Are you
deleteing erroneous records or are you deleting old records - the former and
the latter can also be archived, deactivated etc. Consider all options, and
use the appropriate one(s).
ML

Question about Replication

Hi
Let's suppose we have the following design (everything on the same server):
Database1 (Publisher)
Database2 (Publisher)
Database1 and Database2 have the same structure.
Now I create 2 subscribers on a database called 'DatabaseDest'
Subscriber 1-> Source: Database1, Destination: DatabaseDest
Subscriber 2-> Source: Database2, Destination: DatabaseDest
I want DatabaseDest to have data from Database1 and Database2.
Is this possible?
I'm having problems if for example, I'm transferring a record from
Database1 which has the same Primary Key of another record on Database2.
What do you guys suggest for this kind of architecture?
ThanksIf they are on the same server, how about using stored procedures or
triggers to keep the databases synchronized.
If you must use replication, read the BOL very carefully. It has examples
of how to handle PK issues and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick Sawtell wrote:
> If they are on the same server, how about using stored procedures or
> triggers to keep the databases synchronized.
> If you must use replication, read the BOL very carefully. It has examples
> of how to handle PK issues and so forth.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Right now is on the same server because I'm just testing. However, in
the real world will be different servers.
I thought that Replication would take case of PK issues automatically.
Isn't that true?|||> Right now is on the same server because I'm just testing. However, in
> the real world will be different servers.
> I thought that Replication would take case of PK issues automatically.
> Isn't that true?
>
That depends. Take a look at the NOT FOR REPLICATION option and so forth.
What type of replication are you looking to do? Merge, Transactional...
Are your subscribers going to be updating back to the publishers? Etc.
Rick Sawtell|||> That depends. Take a look at the NOT FOR REPLICATION option and so forth.
> What type of replication are you looking to do? Merge, Transactional...
> Are your subscribers going to be updating back to the publishers? Etc.
> Rick Sawtell
Ok, I'll take a look right now. I have been testing the 3 methods, but I
have noticed that at least for Snapshot and Merge, SQL server is always
overwriting everything, so I cannot have data from Publisher1 and
Publisher2 at the same time, if I have data with the same PK.
My destination database will be read-only, so the traffic will be always
one way, from the Publishers to the Subscriber. Which type of
replication do you think is the best in my case?
Thanks for your help.|||>
> My destination database will be read-only, so the traffic will be always
> one way, from the Publishers to the Subscriber. Which type of
> replication do you think is the best in my case?
>
Transactional would be best for that scenario.
Rick Sawtell
MCT, MCSD, MCDBA

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.com
Hi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com
|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A93BABEA594@.SQLMonster.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.sqlmonster.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via SQLMonster.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51B72295E6DA2@.SQLMonster.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to poin
t
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to poi
nt
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it ag
ain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore,
my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1

Question about Pages

I am looking into a locking issue and I want to find out the specific
rows that live on a page.
Sp_lock returns the following
page id - 1:3873267
But, I don't know of any way to view the data there or correlate it
with specific rows in a table. Is there a way to do this?1:3873267 means - datafile number 1, page number 3873267. You can try
undocummented DBCC PAGE statement to find the records on this page.
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <jbergmanster@.gmail.com> napisal w wiadomosci
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Have a look here:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Andrew J. Kelly SQL MVP
<jbergmanster@.gmail.com> wrote in message
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Thank you, Andrew for that Blog article. That helped me find the
records I need. I have blogged about my testing of the locking issue
at http://jeffbergman.com/cs/blogs/csjeff/archive/2007/08/13/12.aspx
which describes a locking issue I was having with ADO.Net and the
SqlDataReader.
On Aug 13, 11:30 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Have a look here:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/62565...
> --
> Andrew J. Kelly SQL MVP
> <jbergmans...@.gmail.com> wrote in message
> news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
> >I am looking into a locking issue and I want to find out the specific
> > rows that live on a page.
> > Sp_lock returns the following
> > page id - 1:3873267
> > But, I don't know of any way to view the data there or correlate it
> > with specific rows in a table. Is there a way to do this?

Question about normalization

I have a question about normalization
basically I have an excel file with the following columns that my boss wants to store in a small database.

Brand Name, Retail Store, Location Info

Each Retail store may have one or more address. Each Retail store handles one or more brands Some brands may NOT be available in some Locations

So basically I created the following entities.

Brand (BrandID, Brand) Company (CompanyID, Name, Type (retail, manufacturer, etc)) Cmp_Location (LocationID, Address info..., CompanyID) Location_Brand (LocationID, BrandID)

What do you guys think?

You've laid out a decent start.

I wonder if, however, it might be advantageous to Consider that a Brand may have multiple Products. Perhaps a BrandProduct table will prove useful. (And then of course, there may eventually be a need for a BrandProductsDetail table.)

The Retail Stores carry Products, and they may not carry ALL Products for a Brand.

As a minor point, I would name the Locations table something like: CompanyLocations -it will sort following the Company table in the event your project continues to grow and develop a need for more tables.

(And with large retail operations, Brand is just a sub-component of a Supplier. One Supplier may control multiple Brands.)

If you are using SQL 2005. refer to Books Online about the use of schemas. You could have a schema for Suppliers, one for Retailers, etc. That would make it both easier to use Table names that are meaningful without being complex AND keep them located together. Consider

Suppliers.Companys Suppliers.Products Suppliers.ProductDetails Suppliers.Locations Retailers.Companys Retailers.Locations Retailers.Products

Question about LIKE and wildcard operator (%)

Hi all,
Given the following code:
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
Why do all 3 statements match successfully?
I only want Test1 and Test2 to match and Test3 to fail...
My end code will be something like (pardon the pun):
SELECT * FROM Table T
WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
without the square brackets.
Regards,
Alextry now ;-)
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[[STRING1]]%' THEN 'yes' ELSE 'no' END AS
Test1,
CASE WHEN @.param LIKE '%[[STRING2]]%' THEN 'yes' ELSE 'no' END AS
Test2,
CASE WHEN @.param LIKE '%[[STRING3]]%' THEN 'yes' ELSE 'no' END AS
Test3
you have to escape the brackets
http://sqlservercode.blogspot.com/|||Right... nevermind!!
This is one of those D'oh!!! moments (banging head on the desk as I type
this).
I just realised that [] are used for wildcard character matching...
Changed it to using {} and it's all fine now.
Alex
"Alex" <nospam@.hotmail.com> wrote in message
news:OoPHLqRSGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Given the following code:
> DECLARE @.param nvarchar(100)
> SELECT @.param = '[STRING1][STRING2]'
> SELECT
> CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
> CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
> CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
> Why do all 3 statements match successfully?
> I only want Test1 and Test2 to match and Test3 to fail...
> My end code will be something like (pardon the pun):
> SELECT * FROM Table T
> WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
> ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
> without the square brackets.
> Regards,
> Alex
>
>|||Because square brackets are delimiters in wildcard searches; they
indicate that the search "Matches any single character within the
specified range or set that is specified inside the square brackets.".
So in your test, you are asking the optimizer if @.param contains any of
the characters S, T, R, I, N, G, (1-3) which, of course, meets all
conditions of your tests.
You'll either need to ESCAPE the square brackets, or don't use them.
Stu

Question about IIF() function?

I tried to replace the value on rows using the IIF() function and it did not work. The following is the mdx code.

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified"))) ON ROWS FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS FROM [AP Statistics by Patient Type])

What is wrong with the IIF() function....

IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

Thanks

The problem is that you are returning strings and the row axis needs a set of members. You would need to put your IIF in a calculated measure on the columns and also define a set of members on the rows.

At a guess it would probably need to look something like the following (my changes in red).

WITH MEMBER [Measures].[ParameterCaption]

AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[PatientCalc] AS IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

SELECT {[Measures].[ParameterCaption]

, [Measures].[ParameterValue]

, [Measures].[ParameterLevel]

, [Measures].[PatientCalc]}

ON COLUMNS ,

[DIM_Patient].[In Out Patient].Members ON ROWS

FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS

FROM [AP Statistics by Patient Type])

Question about GroupBY and Aggregate functions.

Ok I know this is totally a noob question but I'm working with a table
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.

> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.

Question about getting a value

I have the following script (Trimmed down to show you)

1 Dim myConnection As OleDbConnection
2 Dim myCommand As OleDbCommand
3 Dim intUserCount As Integer
4 Dim LoggedInUserId As Integer
5 Dim strSQL As String
6
78strSQL = "SELECT intUserID FROM tblUsers " _
9& "WHERE txtUsername='" & Replace(txtUsername.Text, "'", "''") & "' " & "AND txtPassword='" & Replace(txtPassword.Text, "'", "''") & "';"
1011 myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
12 & "Data Source=" & Server.MapPath("DB.mdb") & ";")
1314 myCommand = New OleDbCommand(strSQL, myConnection)
1516 myConnection.Open()
17LoggedInUserId = myCommand. <<<<
18 myConnection.Close()

I have bolded the line where I am stuck (17)... I need to return the the value of the selected row and append it to the variable shown... What command do I need to use?

since you would be returning only one value from your SELECT statement, ExecuteScalar would work the best.|||

Thanks ... I had actually put that and got it to work, but what if I need to return a specific row (For example say my select was grabbing 2 columns)?? What would I need to use then??

Thanks again in advance...

|||

Forgot to put as when I use ASP I can simply put something like

mycommand("intUserID")

?? Don't seem to be able to do anything like that with .NET ? Or can you??

|||You could use ExecuteReader.|||

Hey ndinakar ...

So how would I use that ..

ExecuteReader("intUserID") ... ?? Like that??

|||Please check out the documentation or even Google. I can point you in the right direction but I cannot write code for you.|||If you could point me towards a code sample using this method it would be appreciated... everything I find on Google doesn't show any examples?|||It took me 5 seconds to find this link on google:http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlCommandClassExecuteReader.aspx|||

From link:

C#:void ExecuteReaderDemo ( string query, string connString ) { SqlConnection myConn = new SqlConnection ( connString ); SqlCommand myCommand = new SqlCommand ( query, myConn ); myCommand.Connection.Open ( ); SqlDataReader myReader = myCommand.ExecuteReader ( CommandBehavior.CloseConnection );while ( myReader.Read ( ) ) { Response.Write ( myReader.GetString ( 0 ) ); } myReader.Close ( ); myConn.Close ( );}VB:Public Sub ExecuteReaderDemo ( queryAs String, connStringAs String ) Dim myConnAs New SqlConnection ( connString ) Dim myCommandAs New SqlCommand ( query, myConn ) myCommand.Connection.Open ( ) Dim myReaderAs SqlDataReader = _ myCommand.ExecuteReader ( CommandBehavior.CloseConnection )While myReader.Read ( ) Response.Write ( myReader.GetString ( 0 ) )End While myReader.Close ( ) myConn.Close ( )End Sub

You can also reference the columns like (Instead of myReader.GetString(MyColumnNumber)):

C#: myReader["Mycolumn"]

VB: myReader("MyColumn")

|||Fantastic ... Thanks!!!

Question about EXECUTE AS USER

Can anyone tell me why I'll receive a error via following steps? Thanks in advance!

1. Create a database “TESTDB” and a table “Table1”

2. Create a login “TestLogin”, which is db_owner roles of both msdb and TESTDB

3. Create a DML trigger for Table1 by following script

CREATE TRIGGER TRG1

ON Table1

FOR INSERT, UPDATE, DELETE

AS

SELECT * FROM msdb..sysjobs

GO

4. Open SSMS, login as TestLogin, execute following statement

SELECT * FROM msdb..sysjobs

It will succeed to select data in msdb..sysjobs

5. Open another SSMS, login as sa, execute following statement

use TEST

execute as user='TestLogin'

select * From msdb..sysjobs

I receive an error about permission to select data from sysjobs, why?

Add the EXECUTE AS to the end of the Query.

SELECT *

FROM msdb..sysjobs

EXECUTE AS user='TestLogin'

|||

The reason why “SELECT * FROM msdb..sysjobs”fails under an impersonated context (EXECUTE AS USER) is because the impersonation mechanism you are calling is (by default) bound only to the current database (TESTDB), but you are trying to access data from a different DB (msdb).

As Arnie suggested, one potential solution may be use the current execution context to gether the information from msdb, and after that impersonate, but it would really depend on what you are trying to accomplish on this task.

I would recommend the following topics from BOL:

· Understanding Context Switching (http://msdn2.microsoft.com/en-us/library/ms191296.aspx)

· Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx )

My guess s that the trigger you are trying to create is intended to have a controlled escalation of the privileges of the invoker in order to gather information from msdb and accomplish some task, correct?

If this is the case, I would suggest evaluating using digital signatures for this task. I have an example in my blog that probably may help you to get started (not exactly the same scenario, but I hope it will be useful):
http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

If you have further questions, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

question about dependent queries

Suppose I have the following table:

col1 col2
hammet jones
jlo afflect
afflect armand
wills snopt
armand hammet
jones smith

If someone choses armand, then I'd like to return
amand hammet jones smith

The first selection goes over to the second column, gets that value
and locates it back in column one and returns column 2 and so on.

One way of doing it is to set up a separate query for each one and
then construct a new query to get them all.

I'm thinking there's a more elegant way to do this. Any suggestions
would be appreciated.

-DavidHi David,

One way is to use the old-fashioned join. Not sure how efficient the
query is though. - Louis

create table #T (x varchar(10),y varchar(10))
insert into #T values ('hammet','jones')
insert into #T values ('jlo','afflect')
insert into #T values ('afflect','armand')
insert into #T values ('wills','snopt')
insert into #T values ('armand','hammet')
insert into #T values ('jones','smith')

select a.x,a.y,b.y,c.y
from #T as a, #T as b, #T as c
where a.x='armand' and a.y=b.x and b.y=c.x

returns:
x y y y
---- ---- ---- ----
armand hammet jones smith|||On 4 Nov 2003 08:29:01 -0800, louisducnguyen@.hotmail.com (louis
nguyen) wrote:

>Hi David,
>One way is to use the old-fashioned join. Not sure how efficient the
>query is though. - Louis
>create table #T (x varchar(10),y varchar(10))
>insert into #T values ('hammet','jones')
>insert into #T values ('jlo','afflect')
>insert into #T values ('afflect','armand')
>insert into #T values ('wills','snopt')
>insert into #T values ('armand','hammet')
>insert into #T values ('jones','smith')
>select a.x,a.y,b.y,c.y
>from #T as a, #T as b, #T as c
>where a.x='armand' and a.y=b.x and b.y=c.x

Lou, thanks. This is what I was looking for and it improved my
understanding of joins.

This is a great forum.

regards,
-David

question about "System.Data.SqlClient.SqlException"

I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005...however, i have the following error during the compilation process

Code in webform2.aspx.vb:

Partial Class webform2
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near '='."
Number=102
Procedure=""
Server="DCPRJ007\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at webform2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite7\webform2.aspx.vb:line 10
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Are you sure you are getting value in the Request.QueryString("id") ? If not, your select statement will have an incorrect syntax and so is the exception. keep a break point and debug to find out.

Thanks

|||

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

|||

Are you sure, you have some value in Request.QueryString("id") ?

Thanks

|||

gaze:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)

try

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id='" & Request.QueryString("id") & "'", cnn)

means ... use single cote before and after the your id.

If you think this post helped you marked as read.