assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

Assigning XML data to variables

Hi Guys...

Need some assistance here again.
What data type should I use for the variable that I receive from a resultset?

I tried setting it to object, but I encountered some problem later in the stage during the data flow because the XML source adapter cannot get the variable.

I tried setting it to String but get some error during runtime because of invalid datatype.

The main purpose here is actually to get the data from an XML column in a table and populate it into the XML Source Adapter so that I can do some transformations.

Any ideas?

Thanks.Hi,
Yes, this is definitely an issue. In the next release (post- CTP15) you will be able to put the XML result set into a string variable that can then be consumed by the XML Source Adapter. For now, you might be able to work around it with a script component that creates a new string variable and populates it with the data from the object variable. I'm sorry I don't have a better answer for you.

Thanks,
Mark

assigning weight to columns with fulltext (is it possible?)

Hello all,
I have a table which has several fulltext enabled columns.
Title, Summary, Description
I was wondering whether it is possible to assign some kind of weight
to the columns? For example, I find it more relevant if the keywords
are in the title than in the description. Therefore I would like
records that have the matching keywords in the title to come up first.
I'm thinking the way to achieve this is by assigning weight to the
columns, but am not sure how to go about doing this.
Any advice is much appreciated.
PS. I did have something going a while ago where I used CONTAINSTABLE
and severals join plus some variables which contained the weight and
used that on the RANK value. Thing was, it was REALLY really slow on
10,000+ records.
On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
<tacofl...@.gmail.com> wrote:
> Hello all,
> I have a table which has several fulltext enabled columns.
> Title, Summary, Description
> I was wondering whether it is possible to assign some kind of weight
> to the columns? For example, I find it more relevant if the keywords
> are in the title than in the description. Therefore I would like
> records that have the matching keywords in the title to come up first.
> I'm thinking the way to achieve this is by assigning weight to the
> columns, but am not sure how to go about doing this.
> Any advice is much appreciated.
|||Unfortunately the join approach is the only solution to something like this.
http://www.zetainteractive.com - Shift Happens!
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
"Pacific Fox - Web Design Brisbane" <tacofleur@.gmail.com> wrote in message
news:facf10c4-82e5-4fac-9752-3921c1504650@.d4g2000prg.googlegroups.com...
> PS. I did have something going a while ago where I used CONTAINSTABLE
> and severals join plus some variables which contained the weight and
> used that on the RANK value. Thing was, it was REALLY really slow on
> 10,000+ records.
> On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
> <tacofl...@.gmail.com> wrote:
>

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
Anith
|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
--
Anith|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
--
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning Variables

i have a snippit of a query
DECLARE @.INPUTRPT int
DECLARE @.ADDACSRPT int
SELECT a.companyname,
CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
from INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'INPUTRPT',
CASE WHEN EXISTS (Select Count(Licence)
from ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'ADDACSRPT'
how can i assign the variable to the case results"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B4146EC0-BA94-44BB-B7C0-AABBED189C77@.microsoft.com...

> how can i assign the variable to the case results
SELECT @.variable = CASE Column1
WHEN 1 THEN 'Hello'
ELSE 'World'
END AS SomeName
FROM...
Rick Sawtell
MCT, MCSD, MCDBA|||On Wed, 14 Dec 2005 05:50:24 -0800, Peter Newman wrote:

>i have a snippit of a query
>DECLARE @.INPUTRPT int
>DECLARE @.ADDACSRPT int
>SELECT a.companyname,
> CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
> from INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'INPUTRPT',
> CASE WHEN EXISTS (Select Count(Licence)
> from ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'ADDACSRPT'
>how can i assign the variable to the case results
Hi Peter,
Rick already answered the final question, but I believe that the query
can be simplified - you don;t need the CASE expressions (a COUNT
subquery always returns one row, so the EXISTS test will always result
in True).
SELECT a.companyname,
(Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence = a.licence))
as 'INPUTRPT',
(Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence = a.licence))
as 'ADDACSRPT'
(Later)
I just noticed that you try to assign a variable in a statement that
will also return rows to the client. That is not possible in SQL Server.
You either assign variables, OR you return data - never both.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

assigning variables

Thanks for all the help, just one last question on xml, using the following
code
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
when running this query, how can i assign the reportType to @.rptType and
reportNumber to @.RptNo
Peter,
You can try:
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select @.RptType = reportType, @.RptNo = reportNumber
from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
... Of course, you won't be able to select the rest of the values along with
these variable assignments, so if you want a result set back you'll have you
re-select from OpenXML without the variables.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:7627529A-C675-4DF8-AA27-78F44C66F5EB@.microsoft.com...
> Thanks for all the help, just one last question on xml, using the
following
> code
> DECLARE @.hDoc int
> Declare @.RptType varchar(10)
> Declare @.RptNo varchar(6)
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> Select * from OpenXML(@.hDoc, '//Header') with
> (reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
> reportSequenceNumber varchar(6), userNumber varchar(6) )
> EXEC sp_xml_removedocument @.hDoc
> when running this query, how can i assign the reportType to @.rptType and
> reportNumber to @.RptNo

assigning values to parameters dynamically

i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass theMembership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.

So How do I pass a variable to a stored proc parameter using a bound data grid.

I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.

please help

jim

Add the parameter to you parameters collection as a normal asp:parameter, then hook up to the data-source control's Inserting/Updating evnet (I assume you use a data-source control with the grid) and use the event argument to get access to the command object's parameters collection (if you use SqlDataSource), or the InputParameters if you use the ObjectDataSource.|||Or alternatively, when the user authenticates, store their id in a session variable, then anywhere in your app you can add it as a session parameter.

Assigning values to multiple variables (via subqueries) for use in an update

Hi, figured out where I was going wrong in my post just prior, but is
there ANY way I can assign several variables to then use them in an
Update statement, for example (this does not work):

ALTER PROCEDURE dbo.UpdateXmlWF
(
@.varWO varchar(50)
)
AS
DECLARE @.varCust VARCHAR(50)
SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

DECLARE @.varAssy VARCHAR(50)
SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

UPDATE statement here using declared variables...

I can set one @.variable but not multiple. Any clues? kinda new to
this.

Thanks,
KathyKathy,

See my response to your previous post.

I think you want to try this instead:

DECLARE @.varCust varchar(50), varAssy varchar(50)
SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

UPDATE table
SET field = @.varCust,
field2 = @.varAssy
where somefield = somevalue

In fact, you could condense this to the following:

UPDATE table
SET field = tblWorkOrders.Customer,
field2 = tblWorkOrders.Assy
FROM tblWorkOrders
JOIN table ON tblWorkOrders.PK = table.FK
WHERE tblWorkOrders.WorkOrder = @.varWO
AND otherconditions...

Hope this helps.

--
-Chuck Urwiler, MCSD, MCDBA
http://www.eps-software.com|||Kathy,

You don't need two separate SELECT statements to assign values to your
variables. You can do it like this:

DECLARE @.varCust VARCHAR(50),
@.varAssy VARCHAR(50)

SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

But I don't understand your problem with inserting. You can modify as many
columns as you want in UPDATE statement. What's your problem here?
By the way, if these variables have been declared just to use in your UPDATE
statement, then you don't really need them. You can join tblWorkOrders table
with whatever table you want update and fetch values from tblWorkOrders
directly into your destination table.

Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310030718.82cad7f@.posting.google.co m...
> Hi, figured out where I was going wrong in my post just prior, but is
> there ANY way I can assign several variables to then use them in an
> Update statement, for example (this does not work):
> ALTER PROCEDURE dbo.UpdateXmlWF
> (
> @.varWO varchar(50)
> )
> AS
> DECLARE @.varCust VARCHAR(50)
> SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> DECLARE @.varAssy VARCHAR(50)
> SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> UPDATE statement here using declared variables...
> I can set one @.variable but not multiple. Any clues? kinda new to
> this.
> Thanks,
> Kathy

assigning values to fields

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, 1, Desc1
Cust1, 2, Desc1
Cust1, 2, Desc2
How would you make those null values 3's or the MAX(Inv#) for Cust1?
thanks,
rodcharIf this is your entire table, you might need to clean it up to include a
key. Otherwise,
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# ) + 1
FROM tbl t1 )
WHERE Inv# IS NULL ;
Anith|||Try:
SELECT NAME, ISNULL(INV,3), DESCCOL
FROM YOURTABLE
OR
SELECT NAME, ISNULL(INV,(SELECT MAX(INV)FROM YOURTABLE)), DESCCOL
FROM YOURTABLE
OR
DECLARE @.VAL INT
SELECT @.VAL = MAX(INV) FROM YOURTABLE
SELECT NAME, ISNULL(INV,@.VAL), DESCCOL
FROM YOURTABLE
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||UPDATE YourTable
SET [Inv#] =
(
SELECT MAX([Inv#])
FROM YourTable Y1
WHERE Y1.Name = YourTable.Name
)
WHERE YourTable.[Inv#] IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||3 things for everyone:
1st: Thanks for the great replies
2nd:
UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;
This worked for me but what i thought would happen with this is that once it
updated the first null and made it a 3 the 2nd null would become a 4. Can
anyone explain please?
3rd:
This same query didn't work in an access database it said that this wasn't
an updateable query. any ideas?
thanks again,
rodchar
"Anith Sen" wrote:

> If this is your entire table, you might need to clean it up to include a
> key. Otherwise,
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# ) + 1
> FROM tbl t1 )
> WHERE Inv# IS NULL ;
> --
> Anith
>
>|||>> This worked for me but what i thought would happen with this is that once
I am not sure what to explain, since the code is simple and clear. In case,
you are looking for sequentially incrementing value to replace the NULLs,
you'd have to use a "ranking" mechanism like:
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# )
FROM tbl t1 ) +
( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Name = tbl.Name
AND t1.Descr <= tbl.Descr
AND t1.Inv# IS NULL )
FROM tbl
WHERE Inv# IS NULL ;
Depending on the ranking variations you need, you'll have to adjust the
correlations in the subquery using COUNT(*).
Access has different updateability rules and different UDPATE dialect than
SQL Server. If you are using MS Access, consider posting this question in an
Access forum.
Anith|||i'm sorry for not being clear. your code posting is exactly what i needed. i
was just curious why it didn't make the nulls sequential. Because once it
makes the first null record a 3 wouldn't that be the new MAX(InvNo), and in
turn making the last null value a 4.
just trying to understand how the engine thinks and works. this helped a lot
.
"Anith Sen" wrote:

> I am not sure what to explain, since the code is simple and clear. In case
,
> you are looking for sequentially incrementing value to replace the NULLs,
> you'd have to use a "ranking" mechanism like:
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# )
> FROM tbl t1 ) +
> ( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Name = tbl.Name
> AND t1.Descr <= tbl.Descr
> AND t1.Inv# IS NULL )
> FROM tbl
> WHERE Inv# IS NULL ;
> Depending on the ranking variations you need, you'll have to adjust the
> correlations in the subquery using COUNT(*).
>
> Access has different updateability rules and different UDPATE dialect than
> SQL Server. If you are using MS Access, consider posting this question in
an
> Access forum.
> --
> Anith
>
>|||>> i was just curious why it didn't make the nulls sequential.
With no correlation, the value is generated only once for the entire
dataset. With a correlation, the values are generated for each matching row
in the dataset.
No problem.
Anith|||thank you everyone for the help. this has been very productive.
"Anith Sen" wrote:

> With no correlation, the value is generated only once for the entire
> dataset. With a correlation, the values are generated for each matching ro
w
> in the dataset.
>
> No problem.
> --
> Anith
>
>|||On Fri, 14 Oct 2005 10:09:04 -0700, ari wrote:

>2nd:
>UPDATE Transactions
>SET InvNo = ( SELECT MAX( InvNo ) + 1
>FROM Transactions t1 )
>WHERE InvNo IS NULL ;
>This worked for me but what i thought would happen with this is that once i
t
>updated the first null and made it a 3 the 2nd null would become a 4. Can
>anyone explain please?
Hi ari,
In SQL, all operations are done "at once". At least in theory. In
practice, they will eventuelly, somewhere deep in the engine, be
processed one row at a time, but the DB should behave as if the complete
statement is executed at once.
That's why you can swap columns without temp storage to hold the old
value, like you would in procedural languages:
UPDATE SomeTable
SET A = B,
B = A
WHERE ...
The right-hand B and A both refer to the "old" values (before the
update). The DB can process this internally ion any order it wants, as
long as the result looks as if it was all executed at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

assigning values for variables from ref table

I have variables and values stored in a table in this format

process_id | t_variable | t_value
--
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows

how to assign values to variables in SSIS?

basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)

SELECT
@.varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@.varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
Have you tried using Execute SQL task? It may be used to assign values from a result set to a variable.|||This looks similar to what you get with a SQL Server or table based configuration. It has its own way of doing this, but you may want to look into it.

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

assigning values

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust2, null, Desc1
Cust2, null, Desc1
Cust3, null, Desc2
How would I make it:
Cust1, 1, Desc1
Cust1, 1, Desc2
Cust2, 2, Desc1
Cust2, 2, Desc1
Cust3, 3, Desc2
thanks,
rodcharselect name,right(name,1),Desc
from table
This will work for this example
http://sqlservercode.blogspot.com/
"rodchar" wrote:

> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust2, null, Desc1
> Cust2, null, Desc1
> Cust3, null, Desc2
> How would I make it:
> Cust1, 1, Desc1
> Cust1, 1, Desc2
> Cust2, 2, Desc1
> Cust2, 2, Desc1
> Cust3, 3, Desc2
>
> thanks,
> rodchar|||the invoice numbers don't come from the NAME field.
it should find the MAX(InvoiceID) and assign it to all transactions for a
single customer that have a NULL value, then for the next set of transaction
s
for the next customer the Invoice number should be incremented by 1.
CustA, 1, Desc1
CustA, 1, Desc2
CustB, 2, Desc1
CustB, 2, Desc1
CustC, 3, Desc2
"SQL" wrote:
> select name,right(name,1),Desc
> from table
> This will work for this example
> http://sqlservercode.blogspot.com/
> "rodchar" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor -- the keys have nothing whatsoever to do with the display in
the front end.
Let me fix those horrible data element names with some guesses. The
only possible key is (cust_name, item_description), while silly me, I
would have thought that invoice_nbr would be unique in an invoice
table.
What you posted does not make any sense.|||Sorry about that.
Alright, let me give a quick 30,000 ft view because my logic could be off
(it has been before many times.)
I'm trying generate invoices from a transactions table. so here's what i do.
i enter all the transactions for the month for all the customers. when
invoice time comes around here are my steps:
1. I assign invoice numbers to each record in the transactions table where
InvoiceID IS NULL.
2. Then I insert the records into the invoice headers table and invoice
details table.
Transactions table:
rowID int primary key
CustomerID int
InvoiceID int
TransactionType (bill,payment)
Cost money
so my records look like this
1, 122, null, bill, $20
2, 122, null, bill, $20
3, 105, null, bill, $20
4, 101, null, bill, $20
5, 102, null, bill, $20
now if my logic is sound
my transactions table will look like the following after assigning invoice
numbers to them:
1, 122, 1, bill, $20
2, 122, 1, bill, $20
3, 105, 2, bill, $20
4, 101, 3, bill, $20
5, 102, 4, bill, $20
Please advise. Is there a more clearer way to do this whole process that i'm
missing.
thanks,
rodchar
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to havs a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor -- the keys have nothing whatsoever to do with the display in
> the front end.
> Let me fix those horrible data element names with some guesses. The
> only possible key is (cust_name, item_description), while silly me, I
> would have thought that invoice_nbr would be unique in an invoice
> table.
> What you posted does not make any sense.
>

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
Asif
Use sp_addlogin for adding the login to the database, then use sp_grantdbaccess to grant your user access to the database. If you then want to add the user to a role use sp_addrolemember
hth,
Lance

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
AsifUse sp_addlogin for adding the login to the database, then use sp_grantdbacc
ess to grant your user access to the database. If you then want to add the
user to a role use sp_addrolemember
hth,
Lance

Assigning User to a Database

Hi

I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.

i am new to this concept.

i need to create a login, which i am successful in creating, but my problem is

I need to allow only this user to access my database and no other user should login my database.

please can any one explain how to do this.

its very urgent.

regards

James Alvin

Noone is granted access to a database unless you allow it, so go ahead and create a user and give him the appropiate permissions on the database. Make in addition sure, that if you want to restrict the sysadmin users (which is by default the sa and the members of the sysadmin group, e.g. the local administrators) you will have to remove them from the groups / disable the sa account.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi

how can we disable sa account in sql server 2000

|||

You can't disable sa on a SQL Server 2000 instance. Not in any way that would be supported. Some time ago, a few people hacked at the system tables and eventually removed sa but then they had continual problems, couldn't apply service packs and were on an unsupported system. So not in any way that would be supported or stable.

Another option, depending on your application, would be to use just Windows Authentication and use impersonation in your application to log in with just the one windows account you add to the users for this database.

Sysadmins will still be able to access that database though. If it's that critical to lock out everyone, you would want to look at auditing as well as explore third party options for encryption.

-Sue

.

|||

Yes, I ment dismanteling instead of disabling. What I do in reality is to give the sa a cryptic (long and non-guessable) password and lock this in the (virtual) safe. Noone should use that account beside emergencies (like locked accounts etc.) Then I create a new login which has the same rights but a non well-known name (like sa, everyone know that this is the system adminstrator and therefore you only have to guess the password as you already know the name). This account is then mainly used for tasks that cannot be done with the Windows authentication (like users which are not present in the AD). If you are able only use Windows authentication you can even discard this task.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Assigning User / Group thru Web service?

I can't find a way to set user / group to a role thru the web service, this
functionality is availible in the Reports interface, but not thru the web
service?
Just to note i'v implemented Custom Security, agains a users data store. We
need to bulk load 1500 users.
Can this be done thru the db? that would be a solution also if the web
methods are not availible?
sql server 2000, rs 1.0Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>I can't find a way to set user / group to a role thru the web service, this
> functionality is availible in the Reports interface, but not thru the web
> service?
> Just to note i'v implemented Custom Security, agains a users data store.
> We
> need to bulk load 1500 users.
> Can this be done thru the db? that would be a solution also if the web
> methods are not availible?
> sql server 2000, rs 1.0|||do these methods inclue seting a Role to a User / Group. I haven't seen it
in the documentation anywere. Can you show an example of setting a Role
(System Adminstrator) to a User (Nathan.Myers) via the Web services
SetPolicies or SetSystemPolicies?
"Lev Semenets [MSFT]" wrote:
> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
> >I can't find a way to set user / group to a role thru the web service, this
> > functionality is availible in the Reports interface, but not thru the web
> > service?
> >
> > Just to note i'v implemented Custom Security, agains a users data store.
> > We
> > need to bulk load 1500 users.
> >
> > Can this be done thru the db? that would be a solution also if the web
> > methods are not availible?
> >
> > sql server 2000, rs 1.0
>
>|||Use SetSystemPolicies to set System Administrator role for a user.
Basically you need to get system policies using GetSystemPolicies, add
policy for user, and then call SetSystemPolicies
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <NathanMyers@.discussions.microsoft.com> wrote in message
news:86EAE013-7C63-40AE-997C-CE263A8C58BA@.microsoft.com...
> do these methods inclue seting a Role to a User / Group. I haven't seen it
> in the documentation anywere. Can you show an example of setting a Role
> (System Adminstrator) to a User (Nathan.Myers) via the Web services
> SetPolicies or SetSystemPolicies?
> "Lev Semenets [MSFT]" wrote:
>> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
>> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>> >I can't find a way to set user / group to a role thru the web service,
>> >this
>> > functionality is availible in the Reports interface, but not thru the
>> > web
>> > service?
>> >
>> > Just to note i'v implemented Custom Security, agains a users data
>> > store.
>> > We
>> > need to bulk load 1500 users.
>> >
>> > Can this be done thru the db? that would be a solution also if the web
>> > methods are not availible?
>> >
>> > sql server 2000, rs 1.0
>>

Assigning to multipule categories

Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it

When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.

If you need more clarification, post here and let me know.

Thanks in Advance,
Aaron Hawn (aaron@.ionzion.net)The table structure you provided is sufficient to answer that question. Can you clarify your issue?|||Operative word being think, I think you are asking how to represent multiple relationships using the table schema you've described. One row in the table represents one cross reference from a link to a category. To represent multiple category relationships for a single link, you add multiple rows to the Cross-reference table for that LinkID.

-PatP

Assigning the value of variables in a Subpackage

Hi,

I have a parent SSIS package that executes various subpackages. Each of the subpackages contain variables that are required for their successful execution, e.g. one has a variable of datetime datatype and a variable of varchar datatype.

This date will essentially change with every running of the package as it specifies the date that additional data has been added to the back-end SQL Server 2005 database.

I can't find anything in the expressions of the Execute Package Task that would allow me to pass these variables into the package.

Can anyone advise?

Thanks,

Paul

Look at the documentation of "Execute Package" task @. http://msdn2.microsoft.com/en-us/library/ms137609.aspx

Section on "Passing Values to Child Packages"

Hope this helps.

Thanks,
Loonysan

|||

Thanks Loonysan

I'll give it a go