Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change default location of DB's in 2005 Express

I've installed SQL Server 2005 Express, MSSMSE, and SQL BOL and they seem to work. How can I change the default location of the DB's I create in this environment (and/or move them after I create them)?

Thanks!

Answered myself:

Right click the top line ("servers") in the MSSMSE TOC window > properties > database...there are the options

change default database path

Hi:

I'm usingSQL SERVER 2005 EXPRESS

My server name isSERVER1\SQLEXPRESS

When I create a new data base on my server it saves at

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

How Can I change this default path?

Thanks!!

Hi, you can take a look at theStep 3: Point your web.config file at the new SQL Databasesection in this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Change date with trigger

I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Quote:

Originally posted by strellita
I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Needs:
INNER JOIN i.[id column] = preuba.[id column]
after your FROM statement
then it will work correctly.
Full example:
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE p SET datepm = getdate()
FROM inserted i
INNER JOIN preueba p ON i.[ID] = p.[ID]
END
GO
Peace,
tree

change datasource with sqlReportingService2005 class

hi pro , i created model project with .net 2005 after that , create report with reportbuilder that it use this model. it s ok.

i have project and i want to change datasource (connection string ) in runtime when User click on report ,i use sqlReportingService2005 class :

Dim rs As New ReportingService2005()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "Data Source=" & serverName & ";Initial Catalog=" & DataBaseName & ";Integrated Security=True"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = False ' True
definition.Prompt = Nothing
definition.WindowsCredentials = True ' False

Try
rs.SetDataSourceContents("/Data Sources/" & DataSourceName, definition)

Catch ex As SoapException
Console.WriteLine(ex.Detail.OuterXml)
End Try
End Sub

error "An unhandled exception of type 'System.Net.WebException' occurred in System.Web.Services.dll

Additional information: The request failed with HTTP status 404: Object Not Found." happend

( i checked Enable Unmanged code debugging =true)

please help me

tanks

This error occurs only when the report server URL is given wrong or does not exists. Do check your report server URL assigned to the rs.Url property.

|||

tanks for reply. i was forgotten to say : i have 2 machin ,the first one uses Windows 2003 Server and i installed sql server on it , another machin uses windows a xp and i run project on it( it s like client). when i take project on sql server 2005 it s work nice but when i run project on xp it dosent work.

|||

Are you able to open the report server url in browser?

change connectionstring for dataset

hi,

I'm having this application using the express way to create the dategridview by having the query string builder. However, my computer recently crashed and I have no idea to change the connection string. so when I load the whole windows application, I am unable to view the information that are supposed to be in the datagridview. however, it returned an exception.

after finding out the main culprit, I realised that the database is using the old sql server's login. since it is using the old database's login, therefore it is unable to log the information into the datagrid view.

Please help! Thanks.

I'm sorry to hear that your computer crashed. Did you mean to change connection strings for typed DataSet which you created via "Data Source Configuration Wizard"? If so, you only need to change the corresponding Data Connection in the Server Explorer, as all Typed DataSet generated by the wizard use connectionstrings defined in Data Connections. If you want to configure connection string for individual TableAdapter, you need to open the typed DataSet in Design view, and right click on the TableAdapter->choose Configure...->press Previous button untill you rearch the "Choose Your Data Connection" step.

Hope this helps.

|||

thanks for the guide..I'll try and see if it works in other computer..

Cheers,

Joelle

Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John

Change BackgroundColor for subtotals in Matrix

Hi,
I want to create a Matrix like this:
Group 1 Group 2 Value
A 1 2
2 3
Subtotal 5
B 1 8
2 10
Subtotal 18
...
I want the subtotals to have another BackgroundColor (grey) to differentiate
between the detail values and the subtotal value.
Please help !
ThomasIf you click on the little green triangle of the matrix heading cells, you
will notice that the VS properties windows shows properties that apply
specifically for subtotals. You can then set the background color on the
subtotal to grey.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> Hi,
> I want to create a Matrix like this:
> Group 1 Group 2 Value
> A 1 2
> 2 3
> Subtotal 5
> B 1 8
> 2 10
> Subtotal 18
> ...
> I want the subtotals to have another BackgroundColor (grey) to
> differentiate
> between the detail values and the subtotal value.
> Please help !
> Thomas|||It´s that easy ! Thank you very much.
I used the Inscope-Function in the detail-cell to change colors and
Borderstyle for the subtotal, but this is much better.
Thomas
"Robert Bruckner [MSFT]" wrote:
> If you click on the little green triangle of the matrix heading cells, you
> will notice that the VS properties windows shows properties that apply
> specifically for subtotals. You can then set the background color on the
> subtotal to grey.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> > Hi,
> > I want to create a Matrix like this:
> >
> > Group 1 Group 2 Value
> > A 1 2
> > 2 3
> > Subtotal 5
> > B 1 8
> > 2 10
> > Subtotal 18
> > ...
> >
> > I want the subtotals to have another BackgroundColor (grey) to
> > differentiate
> > between the detail values and the subtotal value.
> >
> > Please help !
> >
> > Thomas
>
>|||Using InScope() approach is useful if you the style should depend on the
cell's value. If you want formatting for subtotals in general, it is easier
to use the properties on the subtotal heading.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:7FB87177-A383-4356-A67A-6D5332B1AEBA@.microsoft.com...
> It´s that easy ! Thank you very much.
> I used the Inscope-Function in the detail-cell to change colors and
> Borderstyle for the subtotal, but this is much better.
> Thomas
> "Robert Bruckner [MSFT]" wrote:
>> If you click on the little green triangle of the matrix heading cells,
>> you
>> will notice that the VS properties windows shows properties that apply
>> specifically for subtotals. You can then set the background color on the
>> subtotal to grey.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
>> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
>> > Hi,
>> > I want to create a Matrix like this:
>> >
>> > Group 1 Group 2 Value
>> > A 1 2
>> > 2 3
>> > Subtotal 5
>> > B 1 8
>> > 2 10
>> > Subtotal 18
>> > ...
>> >
>> > I want the subtotals to have another BackgroundColor (grey) to
>> > differentiate
>> > between the detail values and the subtotal value.
>> >
>> > Please help !
>> >
>> > Thomas
>>

Challenge: nested FOR XML EXPLICIT query with data not in target t

I am trying to create a nested FOR XML EXPLICIT query where the majority of the data used in the query does not come from the target table being referenced.
Actually, only a few fields will come from the target table and when those fields appear in the data I want the FOR XML EXPLICIT query to duplicate the entire document structure for each time this happens only changing those elements to which the data cor
responds to (since all of the other elements are static anyway).
What I want is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
However what I am getting is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><ITE
M>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
The <standard> tag is repeating multiple times in a single document where I want only 1 <standard> tag to appear a document. Additional <standard> tags should trigger additional documents to be generated.
Here is the FOR XML EXPLICIT query I am currently using:
SET NOCOUNT ON
SELECT
1 AS Tag
,NULL AS Parent
,NULL AS [docs!1]
,NULL AS [doc!2!ordering!hide]
,NULL AS [doc!2]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ordering!hide]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
,NULL AS [multiused!5]
,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,ID
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,'?'
,'?'
,'?'
,'?'
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
ORDER BY
[doc!2!ordering!hide]
,[settings!3!ordering!hide]
,[standard!4!ordering!hide]
,[multiused!5!ordering!hide]
,[item!6!ordering!hide]
FOR XML EXPLICIT
Any assistance would be most helpful.
Thank you.
The solution can be found by envisioning how the relation that is being
aggregated needs to look like:
1 row for every element. Thus if you want as many documents as <standard>
elements, you need to generate more than one and use some common id to group
them with their children.
Try for example (I simplified the query a bit without loss of
functionality):
SELECT
1 AS Tag
,NULL AS Parent
,ID AS [docs!1!id!hide]
,NULL AS [doc!2!ordering!hide]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
--,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
FROM vConsolidationPrinting
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,ID
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,ID
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,'?'
,'?'
,'?'
,'?'
FROM vConsolidationPrinting
/*
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
*/
ORDER BY
[docs!1!id!hide],[doc!2!ordering!hide],Tag,[settings!3!ordering!hide]
,[standard!4!ITEM!element]
,[multiused!5!ordering!hide]
, [item!6!AttachmentID!element]
FOR XML EXPLICIT
HTH
Michael
PS: BTW, this can be done much easier in SQL Server 2005 with the new PATH
mode. Here it is:
SELECT
'Letter_w_Attachments' as "settings/doctype",
'ENGLISH' as "settings/language",
'CoverLetter' as "settings/type",
'Mailroom' as "settings/branch",
'PRT1' as "settings/printer",
ID as "standard/ITEM",
'?' as "multiused/item/AttachmentID",
'?' as "multiused/item/Comment",
'?' as "multiused/item/DocName",
'?' as "multiused/item/NbrOfPages"
FROM vConsolidationPrinting
FOR XML Path('doc'), ROOT('docs')
"JRutberg" <JRutberg@.discussions.microsoft.com> wrote in message
news:303784BC-7CEC-4AA2-A1AD-1CAF38C1CE1D@.microsoft.com...
>I am trying to create a nested FOR XML EXPLICIT query where the majority of
>the data used in the query does not come from the target table being
>referenced.
> Actually, only a few fields will come from the target table and when those
> fields appear in the data I want the FOR XML EXPLICIT query to duplicate
> the entire document structure for each time this happens only changing
> those elements to which the data corresponds to (since all of the other
> elements are static anyway).
> What I want is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> However what I am getting is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><I
TEM>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> The <standard> tag is repeating multiple times in a single document where
> I want only 1 <standard> tag to appear a document. Additional <standard>
> tags should trigger additional documents to be generated.
> Here is the FOR XML EXPLICIT query I am currently using:
>
> SET NOCOUNT ON
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,NULL AS [docs!1]
> ,NULL AS [doc!2!ordering!hide]
> ,NULL AS [doc!2]
> ,NULL AS [settings!3!ordering!hide]
> ,NULL AS [settings!3!doctype!element]
> ,NULL AS [settings!3!language!element]
> ,NULL AS [settings!3!type!element]
> ,NULL AS [settings!3!branch!element]
> ,NULL AS [settings!3!printer!element]
> ,NULL AS [standard!4!ordering!hide]
> ,NULL AS [standard!4!ITEM!element]
> ,NULL AS [multiused!5!ordering!hide]
> ,NULL AS [multiused!5]
> ,NULL AS [item!6!ordering!hide]
> ,NULL AS [item!6!AttachmentID!element]
> ,NULL AS [item!6!Comment!element]
> ,NULL AS [item!6!DocName!element]
> ,NULL AS [item!6!NbrOfPages!element]
> UNION ALL
> SELECT
> 2 AS Tag
> ,1 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 3 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,'Letter_w_Attachments'
> ,'ENGLISH'
> ,'CoverLetter'
> ,'Mailroom'
> ,'PRT1'
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 4 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,ID
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM vConsolidationPrinting
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 6 AS Tag
> ,5 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,'?'
> ,'?'
> ,'?'
> ,'?'
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ORDER BY
> [doc!2!ordering!hide]
> ,[settings!3!ordering!hide]
> ,[standard!4!ordering!hide]
> ,[multiused!5!ordering!hide]
> ,[item!6!ordering!hide]
> FOR XML EXPLICIT
>
> Any assistance would be most helpful.
> Thank you.
>

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

Cerating and updating new record using ADO

Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??Originally posted by fizikas
Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??

see the following article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175168

CDATA & FOR XML PATH

Hi,
Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is it
possible to create a node creating cdata?
For example, the statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit, type)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
Produces;
<info>
<details>
<description>text...</description>
<description>text...</description>
</details>
</info>
Using the nested for xml explicit I was hoping to be able to display the
second description node as;
<description><![CDATA[text...]]></description>
When the 'type' directive is specified it ignores the fact it should contain
cdata.
Am I missing something?...Is this possible to do?
Thanks
Pete
Hi Pete
If you use a FOR XML expression with the TYPE directive, you get an XML
datatype. And the XML datatype (since it is based on the XQuery Datamodel)
does not preserve the CDATA section information.
So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
directive at the top.
Now, I would like to better understand why you want to generate a CDATA
section in the first place. The only impact it has is to allow people to
author certain XML content without having to explicitly entitize characters
such as <, & etc.. Why would that be important during serializing a FOR XML
result?
Thanks
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is
> it possible to create a node creating cdata?
> For example, the statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit, type)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> Produces;
> <info>
> <details>
> <description>text...</description>
> <description>text...</description>
> </details>
> </info>
> Using the nested for xml explicit I was hoping to be able to display the
> second description node as;
> <description><![CDATA[text...]]></description>
> When the 'type' directive is specified it ignores the fact it should
> contain cdata.
> Am I missing something?...Is this possible to do?
> Thanks
> Pete
>
|||Hi Michael,
Thanks for your reply. My belief is that the use of the cdata directive is
necessary as I have html content stored in the database and need to be able
to be rendered as html when producing an xslt transformation. is there an
alternative way that you know of to do this in SQL Server 2005.
When I try using the same statement;
select
pt_description as 'description',
(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit)
from pt
where pt_id = 9999999
for xml path ('details'), root('info')
without using the 'TYPE' directive the result displays as follows;
<info>
<details>
<description>text...</description>
<description><![CDATA[text...]]></description>
</details>
</info>
How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '<element>...')?...Is
there any alternative way to do this that I'm missing?
Thanks
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi Pete
> If you use a FOR XML expression with the TYPE directive, you get an XML
> datatype. And the XML datatype (since it is based on the XQuery Datamodel)
> does not preserve the CDATA section information.
> So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
> directive at the top.
> Now, I would like to better understand why you want to generate a CDATA
> section in the first place. The only impact it has is to allow people to
> author certain XML content without having to explicitly entitize
> characters such as <, & etc.. Why would that be important during
> serializing a FOR XML result?
> Thanks
> Michael
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
>
|||XSLT should not make a difference between the CDATA section and content that
just had been entitized.
Did you try to take your original output (with the TYPE directive) and pass
it through your XSLT style sheet?
Alternatively, if you need to preserve your CDATA section, you need to use a
top-level EXPLICIT mode query:
select 1 as Tag, NULL as Parent, pt_description as
[details!1!description!element],pt_description as
[details!1!description!cdata]
from property where pt_id = 8957627
for xml explicit, root('info')
Best regards
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eHmIDS0dFHA.1612@.tk2msftngp13.phx.gbl...
> Hi Michael,
> Thanks for your reply. My belief is that the use of the cdata directive
> is necessary as I have html content stored in the database and need to be
> able to be rendered as html when producing an xslt transformation. is
> there an alternative way that you know of to do this in SQL Server 2005.
> When I try using the same statement;
> select
> pt_description as 'description',
> (select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
> from property where pt_id = 8957627 for xml explicit)
> from pt
> where pt_id = 9999999
> for xml path ('details'), root('info')
> without using the 'TYPE' directive the result displays as follows;
> <info>
> <details>
> <description>text...</description>
> <description><![CDATA[text...]]></description>
> </details>
> </info>
> How can I ensure the both the html and the element containing the cdata
> are rendered correctly (eg. <element> rather than
> '<element>...')?...Is there any alternative way to do this that I'm
> missing?
> Thanks
> Pete
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>

Categorise Bar Chart

How to create a bar chart by setting the static category myself?

I mean, for example, I have a dataset which record the number of coins different people have.

I would like to draw a bar chart which shows 3 bars with the following 3 different categories:

1) n < 5,

2) n >= 5 and n <10,

3) n>=10.

where n is the number of coins.

How can this be expressed in the categories grouping?

Thanks in advance.

Sorry, this is currently not directly supported through chart groupings. You would need to write the query (or add a calculated field on the dataset) so that you get this categorization in the data and then use those fields in the chart.

-- Robert

Catalog and characters with accent

I have a question about SQL Server 2000 Full Text Index.
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***