Foreign and primary keys

I have an application in which i need to get the foreign key fields
from a table and then get all the foreign keys primary key field from
the linking table. Could some one tell me how i do this using
INFORMATION_SCHEMA. I have tried and can get the foreign keys but not
sure how to get the associated primary keys.See:
http://groups.google.com/group/micr...a0218d9e069531c

Razvan

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
Paul G
Software engineer.
Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name =
'mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.
|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:

> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name =
> 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
>
>

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
Paul G
Software engineer.Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name =
'mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints
,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:

> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name
=
> 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
>
>

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
--
Paul G
Software engineer.Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name ='mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:
> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name => 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> > Hi just wondering if there is a way to have a script that creates a table
> > write in a desired string for the first table entry?
> > I have an application that I am allowing the de-activation of a record. I
> > could not allow a delete since this could result in an orphan record. So
> > basically the table at startup (when first created) would look like
> > ****************************
> > * pri key * varchar (20)*
> > ****************************
> > * 1 * Not Active *
> > ****************************
> > the application would add records to the table as it is used.
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Force protocol encryption

I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?

Thanks!If using OLEDB, add this to the connection string:

Use Encryption for Data=True

force password for sql login

Hi all,
I am using third party application that can only use sql authentication. If
we use windows authentication it will accept any password once the username
is correct. If we are force to use sql authentication, is there a script or a
way that we can force the sql users to change password every 3 months for
example. Any help will be greatly appreciated.Hi,
If you are using SQL Server 2005, you can enforce your windows password
policy.
But in case you are using SQL Server 2000, you will have to write a few
proc's to maintain things like password expiry etc. I am sure there are
articles regarding this. In fact I have gone thru' one, but dont have the
link at the moment.
Thank you.
Regards,
Karthik
"deseotu5" wrote:
> Hi all,
> I am using third party application that can only use sql authentication. If
> we use windows authentication it will accept any password once the username
> is correct. If we are force to use sql authentication, is there a script or a
> way that we can force the sql users to change password every 3 months for
> example. Any help will be greatly appreciated.|||Password and user policy is incorporated only from SQL 2005. If you have SQL
2005 then you can
enable the "Enforse Password Expiry" option for the required logins.
For SQL 2000 or older versions do not have Password policies for SQL Server
logins. The only way is
by 2 layer of authentication. First layer will be SQL Server and then it
will be creating application tables for authentication with a modified date
column. So verify the application table whne the password update happend
last time and based on that force the application change password
or expiry.
Thanks
Hari
SQL Server MVP
"deseotu5" <deseotu5@.discussions.microsoft.com> wrote in message
news:3C08100E-B7A7-4674-B424-48C29989F787@.microsoft.com...
> Hi all,
> I am using third party application that can only use sql authentication.
> If
> we use windows authentication it will accept any password once the
> username
> is correct. If we are force to use sql authentication, is there a script
> or a
> way that we can force the sql users to change password every 3 months for
> example. Any help will be greatly appreciated.

force password for sql login

Hi all,
I am using third party application that can only use sql authentication. If
we use windows authentication it will accept any password once the username
is correct. If we are force to use sql authentication, is there a script or
a
way that we can force the sql users to change password every 3 months for
example. Any help will be greatly appreciated.Hi,
If you are using SQL Server 2005, you can enforce your windows password
policy.
But in case you are using SQL Server 2000, you will have to write a few
proc's to maintain things like password expiry etc. I am sure there are
articles regarding this. In fact I have gone thru' one, but dont have the
link at the moment.
Thank you.
Regards,
Karthik
"deseotu5" wrote:

> Hi all,
> I am using third party application that can only use sql authentication. I
f
> we use windows authentication it will accept any password once the usernam
e
> is correct. If we are force to use sql authentication, is there a script o
r a
> way that we can force the sql users to change password every 3 months for
> example. Any help will be greatly appreciated.|||Password and user policy is incorporated only from SQL 2005. If you have SQL
2005 then you can
enable the "Enforse Password Expiry" option for the required logins.
For SQL 2000 or older versions do not have Password policies for SQL Server
logins. The only way is
by 2 layer of authentication. First layer will be SQL Server and then it
will be creating application tables for authentication with a modified date
column. So verify the application table whne the password update happend
last time and based on that force the application change password
or expiry.
Thanks
Hari
SQL Server MVP
"deseotu5" <deseotu5@.discussions.microsoft.com> wrote in message
news:3C08100E-B7A7-4674-B424-48C29989F787@.microsoft.com...
> Hi all,
> I am using third party application that can only use sql authentication.
> If
> we use windows authentication it will accept any password once the
> username
> is correct. If we are force to use sql authentication, is there a script
> or a
> way that we can force the sql users to change password every 3 months for
> example. Any help will be greatly appreciated.

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriag
e
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J JustinHello J,
As far as I recall, no, there's no particular issue with this if you're usin
g
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. I checked my data again. You are right. Carriage return is also
stored on couple of rows. If I exclude those rows in the WHERE clause, then
all are working fine. A custom developed web service is using this data.
How to make sure that SQL query with FOR XML statement will return all data
without issues regardless of whether carriage return is present in a row or
not?
J Justin
"Kent Tegels" wrote:

> Hello J,
> As far as I recall, no, there's no particular issue with this if you're us
ing
> a tool that reads it all as byte stream. Are you sure the data in question
> doesn't have a return? How are you reading the data?
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Hello J,
a.) clean up the existing data to remove the returns
b.) enforce good data validation on entry to not allow returns
c.) consider using an FOR XML EXPLICT query to emit the questionable field
as CDATA (I think this is possible)
kt

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriage
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J Justin
Hello J,
As far as I recall, no, there's no particular issue with this if you're using
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

for some reason when i deploy my C# application on windows 2003 occasionaly rdr[0].ToStrin

for some reason when i deploy my C# application on windows 2003 occasionaly
rdr[0].ToString() hangs where rdr is a System.Data.SqlClient.SqlDataReader.
Is there a fix for this? is my visual studio.net out dated?Doubtless, it is not related with your Visual Studio :)
Can you post a part of your code to get wider vision on your problem?