Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I get the values of a level in a dimension by using AMO?

I wnat to get the values of a level in a dimension by using AMO(Analysis Management Object), but I don't know that is it possible ?

Does anyone know that,please tell me how to do and show a sample if possible . Thank you~

Hi Kasper,

Please try use ADOMD.net to get the values instead.

Yan

Can I get both Attributes and Elements values in same OpenXML query ?

As an input parameter for stored procedure I have the following XML, its values are contained in both attributes and elements. For attributes we have to use flag = 1, and for attributes 2. Is there any flag or technique that we can retrieve both ?

Here is my script:

declare
@.xml xml,
@.handle int

set @.xml =
'
<Sortable>
<Field ord="1" type="asc">LastName</Field>
<Field ord="2" type="desc">CreateDate</Field>
<Field ord="3" type="asc">ProspectNum</Field>
</Sortable>
'

exec sp_xml_preparedocument @.handle output, @.xml

select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) --'./Field'
)

exec sp_xml_removedocument @.handle

I exepect it to return the foillowing result set:

ord type Field
-- - --
1 asc LastName
2 desc CreateDate
3 asc ProspectNum

Obviously it returns NULL for my 'Field' column.

Thanks

Either of these

select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) '.'
)

select r.value('@.ord','int') as ord,
r.value('@.type','varchar(4)') as type,
r.value('.','varchar(20)') as Field
from @.xml.nodes('/Sortable/Field') as D(r)

Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can I do Regular Expressions using TSQL in SQL Server2005?

I am running PHP with SQL Server 2005. I have [bold] in some of the values in my result set, which needs to be striped out. I had a function in SQL Server2000 that did that, but the same function does not work in SQL Server2005. Any dieas?

CREATE FUNCTION

dbo.fn_regex(@.pattern varchar(255), @.matchstring varchar(8000))

RETURNS int

AS

BEGIN

declare @.obj int

declare @.res int

declare @.match bit

set @.match=0

exec @.res=sp_OACreate 'VBScript.RegExp',@.obj OUT

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'Pattern', @.pattern

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'IgnoreCase', 1

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OAMethod @.obj, 'Test',@.match OUT, @.matchstring

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OADestroy @.obj

return @.match

END

GO

When you say the functiuon doesn't work in SQL 2005; in what way doesn't it work? I see that you are using sp_OAxxx and friends; you are aware that you need to explicitly enable sp_OAxxx in S2K5, as a server setting?

Also, youwould be much, much, much better off using the CLR integration to do your stuff than sp_OA

Niels
|||Thanks for your response nielsb. Do you know if I can use CLR integration in a TSQL function. I am using PHP with SQL Server, so I do not have the option to use VB.net or C#?

Thanks!|||I don't fully understand the question? But, you can have s function written in C#, VB.NET, that's doing your stuff, and that function can be called by some T-SQL functions.

Niels
|||I cannot write the function in VB.NET or C#, as I am using PHP with SQL Server 2005 (which leave me the only option of write a TSQL function)|||Hmm, I still don't understand why you can not write a VB.NET or C# function. I.e. SQL Server 2005 hosts the CLR. You write a CLR function doing your regex stuff, this function is hosted by SQL Server and exposed by a T-SQL function. Your PHP code then calls the T-SQL function. Or am I totally off-track here?

Niels
|||Thanks for the response Niels! Do you think I can write a C# or VB.NET CLR function and call it within TSQL? Could you maybe give me an example of a CLR function. I have not used this before. your response is very much appreciated!

Can i debug/watch on the triggers INSERTED and DELETED records/values?

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.

Are there any documents, web links that describe ways
of debugging the trigger's INSERTED and DELETED?

Thank youOn Wed, 25 Jan 2006 13:29:59 -0500, serge wrote:

>When i debug a trigger is it possible to add a WATCH
>on the INSERTED or DELETED?

Hi Serge,

No. During debugging, it is (unfortunately) not possible to see the
contents of ANY tables.

>I think not, at least I couldn't figure out a way to do so.
>Does someone have a suggestion on how I can see the values?

You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.

Or you could use SELECT INTO or INSERT ... SELECT to store the values in
a persistant table.

>I did try to do something like
>INSERT INTO TABLE1(NAME)
>SELECT NAME FROM INSERTED
>but this didn't work. When the trigger completed and I
>went to see the TABLE1, there were no records in it.

Hey, that's just what I suggested! <g
This should work. Some potential reasons for why it didn't work for you
are:
- Maybe the code never even reached the insert into statement? This
might be the case if the table TABLE1 didn;t exist at all after trigger
execution.
- Did you check that the table TABLE1 did not exist before the trigger
was executed? If it did, the command above would result in an error (and
you should have gotten an error message).
- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
DELETE, or an INSERT .. SELECT that affected 0 rows)
- Don't use a temp table for this. It will be removed when the trigger
execution finishes, as it only exists in the scope of the trigger.
- In a DELETTE trigger, the inserted table is ALWAYS empty.

All the above are just guesses, of course. I'd have to see the actual
code to help you further.

>Are there any documents, web links that describe ways
>of debugging the trigger's INSERTED and DELETED?
>Thank you

--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> When i debug a trigger is it possible to add a WATCH
> on the INSERTED or DELETED?
> I think not, at least I couldn't figure out a way to do so.
> Does someone have a suggestion on how I can see the values?
> I did try to do something like
> INSERT INTO TABLE1(NAME)
> SELECT NAME FROM INSERTED
> but this didn't work. When the trigger completed and I
> went to see the TABLE1, there were no records in it.

In additions to Hugo's suggestions, keep in mind that if the trigger
fails, then the statement will be rolled back, and that includs the
data insertedvinto Table1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 25 Jan 2006 22:27:44 +0000 (UTC), Erland Sommarskog wrote:

>serge (sergea@.nospam.ehmail.com) writes:
>> When i debug a trigger is it possible to add a WATCH
>> on the INSERTED or DELETED?
>>
>> I think not, at least I couldn't figure out a way to do so.
>> Does someone have a suggestion on how I can see the values?
>>
>> I did try to do something like
>>
>> INSERT INTO TABLE1(NAME)
>> SELECT NAME FROM INSERTED
>>
>> but this didn't work. When the trigger completed and I
>> went to see the TABLE1, there were no records in it.
>In additions to Hugo's suggestions, keep in mind that if the trigger
>fails, then the statement will be rolled back, and that includs the
>data insertedvinto Table1

Ah, of course. How could I forget it?

Time for bed, I guess :-)

Thanks, Erland!

--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo, Erland.

Hugo,

>This should work. Some potential reasons for why it didn't work for you
>are:
>- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
>DELETE, or an INSERT .. SELECT that affected 0 rows)

I believe I was running an UPDATE statement with a zero-row operation.
But let's ignore that statement as now I've tested it again using a new
UPDATE statement that updates rows for sure. After testing the last 30
minutes I now understand things better.

I am able to INSERT the records from the trigger's INSERTED table
to the permanent table I created before running the update statement.

I also realized that when running in DEBUG mode I should make sure
to uncheck the DEBUG PROCEDURE's AUTO ROLL BACK check
box. This problem until I realized it kept me puzzled for 10 minutes.

>You could add a SELECT to the trigger code, then test your code from
>Query Analyzer. The values in the inserted and deleted pseudo-table
>would go to the Query Analyzer results pane.

Too bad ADD WATCH isn't available.
Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?

Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> Anyone knows if SQL 2005 allows to add watches and monitor the
> contents of the inserted and deleted when debugging triggers?

I haven't tried debugging in SQL 2005, as it you only can debug from
Visual Studio. But I would not really expect so.

Personally, I have more or less stopped using the debugger. It usually
works when you want to debug your local server, but when connecting to
another, there is so much red tape. Debug PRINTs and SELECTs are easier
to handle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Personally, I have more or less stopped using the debugger. It usually
> works when you want to debug your local server, but when connecting to
> another, there is so much red tape.

Sometimes I do debug remotely (maybe often). Are you saying it is not always
a
safe approach to debug remotely? "There is so much red tape": these
are known issues that happen frequently or in the very rare cases?
Would you know if there are MS KB on these problems or personal
experiences led you to stop debugging remotely?

Can you please list some situations where debugging remotely is not safe
or the problems you ran into are complicated to explain?

If this is the case then I should avoid debugging remotely and end up
using Terminal Services to connect to the SQL Server and debug locally?

> Debug PRINTs and SELECTs are easier to handle.

I just tried "SELECT * FROM INSERTED" and I saw the result
in the results pane so I am not sure why I had not tried this before.
Well, at least now I know and I won't need to create a table if I am
only interested in seeing the result during the debugging only.

Thanks Erland.|||On Thu, 26 Jan 2006 01:19:30 -0500, serge wrote:

(snip)
>>You could add a SELECT to the trigger code, then test your code from
>>Query Analyzer. The values in the inserted and deleted pseudo-table
>>would go to the Query Analyzer results pane.
>Too bad ADD WATCH isn't available.
>Anyone knows if SQL 2005 allows to add watches and monitor the
>contents of the inserted and deleted when debugging triggers?

Hi Serge,

I don't know. I haven't seen the debugging capabilities in SQL 2005 yet.

In SQL 2000, the debugger was integral part of the product. But that has
been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.

http://lab.msdn.microsoft.com/Produ...06-f50123f6d235

--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> Sometimes I do debug remotely (maybe often). Are you saying it is not
> always a safe approach to debug remotely? "There is so much red tape":
> these are known issues that happen frequently or in the very rare cases?
> Would you know if there are MS KB on these problems or personal
> experiences led you to stop debugging remotely?

Unsafe? Yes, a little, although that was not really what I meant with
red tape. What I mean is simply that there are so many things have to
be aligned for it to work, that I don't find it worth the hassle.

Some time back, we found that debugging did not work when you had Windows
XP SP2 installed. I did some investigation, and found that hotfix
8.00.944 addressed this problem. (This hotfix is included in SP4.) I
installed hotfix on client and server. I also had to open port 135 in
Windows firewall. Now, port 135 is not any port: this is RPC, and a
prime attack surfaces for viruses. So opening port 135 is a little unsafe,
so there is all reason to only open it for the SQL Servers you want to
debug. (If is possible to open a port only for a certain IP address in
Windows firewall.) Eventually I got it working.

Then some months later, I felt like debugging again, but now I was out
of luck again. I did some inquires, and apparently our Windows admin had
decided to cut the number of permissions for the SQL Server service
account. I don't know exactly what permissions that are required, but
as it writes back to the client, it needs more than plain-user rights.

At this point, I just gave it up. These are not the only thing that
can stop debugging from working. And after all, what you can dig out
from the debugger can easily be achieved in other ways. Of course,
code that uses iterative approaches can be painful to debug that
way. But good SQL should not have much such code anyway. :-)

And, oh, there is one more possible issue with the debugger. Single-
stepping through a transaction is not that friendly if other users
needs to access the data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Unsafe? Yes, a little, although that was not really what I meant with
> red tape. What I mean is simply that there are so many things have to
> be aligned for it to work, that I don't find it worth the hassle.
> Some time back, we found that debugging did not work when you had Windows
> XP SP2 installed. I did some investigation, and found that hotfix
> 8.00.944 addressed this problem. (This hotfix is included in SP4.) I
> installed hotfix on client and server. I also had to open port 135 in
> Windows firewall. Now, port 135 is not any port: this is RPC, and a
> prime attack surfaces for viruses. So opening port 135 is a little unsafe,
> so there is all reason to only open it for the SQL Servers you want to
> debug. (If is possible to open a port only for a certain IP address in
> Windows firewall.) Eventually I got it working.
> Then some months later, I felt like debugging again, but now I was out
> of luck again. I did some inquires, and apparently our Windows admin had
> decided to cut the number of permissions for the SQL Server service
> account. I don't know exactly what permissions that are required, but
> as it writes back to the client, it needs more than plain-user rights.
> At this point, I just gave it up. These are not the only thing that
> can stop debugging from working. And after all, what you can dig out
> from the debugger can easily be achieved in other ways. Of course,
> code that uses iterative approaches can be painful to debug that
> way. But good SQL should not have much such code anyway. :-)
> And, oh, there is one more possible issue with the debugger. Single-
> stepping through a transaction is not that friendly if other users
> needs to access the data.

Thanks for the detailed explanation. Some interesting information
that I'll keep in mind.|||> In SQL 2000, the debugger was integral part of the product. But that has
> been removed from SQL 2005 - you can now only debug triggers and stored
> procedures if you also have Visual Studio installed.
> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235

Then I presume SQL 2005 Studio Management that comes with SQL 2005
is not a flavor of Visual Studio that can debug triggers.

Thanks Hugo.|||On Thu, 26 Jan 2006 22:57:53 -0500, serge wrote:

>> In SQL 2000, the debugger was integral part of the product. But that has
>> been removed from SQL 2005 - you can now only debug triggers and stored
>> procedures if you also have Visual Studio installed.
>>
>> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235
>Then I presume SQL 2005 Studio Management that comes with SQL 2005
>is not a flavor of Visual Studio that can debug triggers.

Hi Serge,

That's correct. Management Studio is replacement for Enterprise Manager
plus Query Analyzer, but doesn't have the debugger.

--
Hugo Kornelis, SQL Server MVP

Can DTS automatically create primary key values upon export?

I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.

Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.

ddaveyou have to clear the enable identity insert checkbox if you want to allow the identity values to be poped by you
adding a check indicates that you want to programmatically provide the identity values
try clearing it and setting your identity on the column|||Thanks. I did do as you mention but I also had to create the destination table with a Primary Key with an identity field that incremented automatically by 1. I also had to create a source table that had NO primary key field. I then imported the source into the destination and the incoming rows were assigned Primary Key values in sequence.

ddave

you have to clear the enable identity insert checkbox if you want to allow the identity values to be poped by you
adding a check indicates that you want to programmatically provide the identity values
try clearing it and setting your identity on the column

Can have two measure values?

Hi,
Can we have two measure values in a Matrix?
for example that I want to display both unit Sales and Sales Count in a
Matrix.Yes you can. Just right-click on a matrix cell and select "Add Column" or
"Add Row". You can then calculate the sales count in the new cell.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Can we have two measure values in a Matrix?
> for example that I want to display both unit Sales and Sales Count in a
> Matrix.
>|||Why is it that when you add more than one value to a matrix report like this,
it will no longer export to HTMLOWC?
"Robert Bruckner [MSFT]" wrote:
> Yes you can. Just right-click on a matrix cell and select "Add Column" or
> "Add Row". You can then calculate the sales count in the new cell.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Can we have two measure values in a Matrix?
> > for example that I want to display both unit Sales and Sales Count in a
> > Matrix.
> >
> >
>
>|||Please read this section in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_43vn.asp
When adding additional "measures", you add static columns or static rows to
the matrix.
When a report is rendered to HTML with OWC, and you use static rows /
columns then you might run into one of these limitations:
* the matrix has both static columns and static rows
* dynamic columns or rows are nested inside static columns or rows
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"clortex" <clortex@.discussions.microsoft.com> wrote in message
news:657353CE-0AEE-4EFD-B795-005F0BD06DB0@.microsoft.com...
> Why is it that when you add more than one value to a matrix report like
this,
> it will no longer export to HTMLOWC?
> "Robert Bruckner [MSFT]" wrote:
> > Yes you can. Just right-click on a matrix cell and select "Add Column"
or
> > "Add Row". You can then calculate the sales count in the new cell.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > > Can we have two measure values in a Matrix?
> > > for example that I want to display both unit Sales and Sales Count in
a
> > > Matrix.
> > >
> > >
> >
> >
> >|||Thank, it is good guestion to me!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#WEWpFulEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Please read this section in BOL:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_43vn.asp
> When adding additional "measures", you add static columns or static rows
to
> the matrix.
> When a report is rendered to HTML with OWC, and you use static rows /
> columns then you might run into one of these limitations:
> * the matrix has both static columns and static rows
> * dynamic columns or rows are nested inside static columns or rows
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "clortex" <clortex@.discussions.microsoft.com> wrote in message
> news:657353CE-0AEE-4EFD-B795-005F0BD06DB0@.microsoft.com...
> > Why is it that when you add more than one value to a matrix report like
> this,
> > it will no longer export to HTMLOWC?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Yes you can. Just right-click on a matrix cell and select "Add Column"
> or
> > > "Add Row". You can then calculate the sales count in the new cell.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > > news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > > Can we have two measure values in a Matrix?
> > > > for example that I want to display both unit Sales and Sales Count
in
> a
> > > > Matrix.
> > > >
> > > >
> > >
> > >
> > >
>

Can connect to Database, but how do I insert values into SQL Database?

I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.
'Create(connection)
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
'open connection
conn.Open()

However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.

Sorry, I'm really new with VWD.

You need to read about ADO.NET before you do anything.

Google it.

can anyone solve this sql query?

hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......hardik wrote:

Quote:

Originally Posted by

hi friends i need help in this sql query
>
i have table like,
>
id fid
__ _____
autonumber text
>
and i am storing values like
>
id fid
___________________________________
1 1,2,3,4,5
>
2 11,12,13,14,15
>
now to find values i am using query
>
sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')
>
only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.

This particular query can be solved by

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

All of this assuming that you have no spaces in fid.

--
Regards,
Kristian Damm Jensen
"This isn't Jeopardy. Answer below the question."|||Am 16 Oct 2006 00:47:31 -0700 schrieb hardik:

...

Quote:

Originally Posted by

id fid
___________________________________
1 1,2,3,4,5
>
2 11,12,13,14,15
>
now to find values i am using query
>
sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')
>
only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

If ',' is your separator you can use:
SELECT * FROM test12 WHERE `fid` = '1' or `fid` LIKE ('1,%') or
`fid` LIKE ('%,1,%') or `fid` LIKE ('%,1')

bye, Helmut|||Thank you very much!
It works for me perfectly...
Kristian Damm Jensen wrote:

Quote:

Originally Posted by

hardik wrote:

Quote:

Originally Posted by

hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

>
It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.
>
This particular query can be solved by
>
select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line
>
All of this assuming that you have no spaces in fid.
>
--
Regards,
Kristian Damm Jensen
"This isn't Jeopardy. Answer below the question."

|||Kristian Damm Jensen wrote:

Quote:

Originally Posted by

hardik wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>hi friends i need help in this sql query
>>
>i have table like,
>>
>id fid
>__ _____
>autonumber text
>>
>and i am storing values like
>>
>id fid
>___________________________________
>1 1,2,3,4,5
>>
>2 11,12,13,14,15
>>
>now to find values i am using query
>>
>sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')
>>
>only problem in this query is it is selecting 1 and 11 and i require
>only 1 as i am giving one in %1%
>now any one have answer of this question then plz plz tell me ......

>
It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.

http://en.wikipedia.org/wiki/First_normal_form
You shouldn't store multiple values in a single column. Instead, change
your table to look like this:

id fid
-- --
1 1
1 2
1 3
1 4
1 5
2 11
2 12
2 13
2 14
2 15

and then you can simply do

select * from test12 where fid = 1

You should also use column names that are more descriptive than 'id' and
'fid', e.g. 'SalesOrderHeaderID' and 'SalesOrderLineID'.

can a textbox hold the value of more than 1 field? maybe use funct

Hello,
My question is if a textbox in a report can contain values from multiple
fields from the data source:
txt1.value
=Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
If this is doable, what is the method/correct method?
I can add multiple fields to one textbox in an MS Access Report. Can this
be done in a Reporting Services Report? I am thinking I could use a function
which would return the concatenated values of these fields as a string. What
would the code for that function look like?
Thanks,
RichI figured out my problem. I added some new fields to my dataset, but not to
the report. Gotta do that for them to compile without complaining.
"Rich" wrote:
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a function
> which would return the concatenated values of these fields as a string. What
> would the code for that function look like?
> Thanks,
> Rich|||You are correct, your format looks correct to me.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a
> function
> which would return the concatenated values of these fields as a string.
> What
> would the code for that function look like?
> Thanks,
> Rich|||Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
who can I report that too?
My actual project is using the reportviewer control that comes with VS2005
(it is almost the same as RS except doesn't require a server - and a few
other things). It works pretty good, but when I select a tractor feeding
printer (one of those older wide paper - dotmatrix like printers) if I tell
the layout to print landscape when using US STD Fanfold paper , the little
icon in the dialog display portrait and it prints portrait. Then if I tell
it Portrait when using the US STD Fanfold papter with tractor feed printer -
the icon displays landscapte and prints landscape. It is pretty obvious that
someone mixed up the options.
So I am not trying to be mr. picky, but when the end user uses my product,
it needs to work according to the standards. Who can I report this too?
Thanks,
Rich
"Steve MunLeeuw" wrote:
> You are correct, your format looks correct to me.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> > Hello,
> >
> > My question is if a textbox in a report can contain values from multiple
> > fields from the data source:
> >
> > txt1.value
> >
> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> >
> > If this is doable, what is the method/correct method?
> >
> > I can add multiple fields to one textbox in an MS Access Report. Can this
> > be done in a Reporting Services Report? I am thinking I could use a
> > function
> > which would return the concatenated values of these fields as a string.
> > What
> > would the code for that function look like?
> >
> > Thanks,
> > Rich
>
>|||http://connect.microsoft.com/SQLServer/Feedback
Yeah, dealing with different page sizes can be tricky from what I gather.
Luckily I haven't had to deal with that much. Adobe allows you to have
pages in both landscape and portrait in the same document I was asked if I
could do that the other day. I don't think I could.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D2173E9D-E9AF-410D-AFEE-E919AF360951@.microsoft.com...
> Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
> who can I report that too?
> My actual project is using the reportviewer control that comes with VS2005
> (it is almost the same as RS except doesn't require a server - and a few
> other things). It works pretty good, but when I select a tractor feeding
> printer (one of those older wide paper - dotmatrix like printers) if I
> tell
> the layout to print landscape when using US STD Fanfold paper , the little
> icon in the dialog display portrait and it prints portrait. Then if I
> tell
> it Portrait when using the US STD Fanfold papter with tractor feed
> printer -
> the icon displays landscapte and prints landscape. It is pretty obvious
> that
> someone mixed up the options.
> So I am not trying to be mr. picky, but when the end user uses my product,
> it needs to work according to the standards. Who can I report this too?
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> You are correct, your format looks correct to me.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
>> > Hello,
>> >
>> > My question is if a textbox in a report can contain values from
>> > multiple
>> > fields from the data source:
>> >
>> > txt1.value
>> >
>> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
>> >
>> > If this is doable, what is the method/correct method?
>> >
>> > I can add multiple fields to one textbox in an MS Access Report. Can
>> > this
>> > be done in a Reporting Services Report? I am thinking I could use a
>> > function
>> > which would return the concatenated values of these fields as a string.
>> > What
>> > would the code for that function look like?
>> >
>> > Thanks,
>> > Rich
>>

Can a recursive query do this?

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------
PRODUCTID PK
PRODUCTNAME nVarChar(30)

Table: ASSEMBLY
--------
PRODUCTID PK (FK to PRODUCT.PRODUCTID)
COMPONENTID PK (FK to PRODUCT.PRODUCTID)
QTY INT

I can write a query that takes the PRODUCTID, and returns all

PRODUCT
=======
PRODUCTID PRODUCTNAME
--- ----
1 Cage Assembly - Solid Sides
2 Cage Assembly - Split Back
3 Cage Assembly - Split Sides
4 Cage Assembly - Split Top/Bottom
5 Cage Assembly - Split Back and Sides
6 Cage Assembly - Split Back and Top/Bottom
7 Cage Assembly - Split Back and Sides and Top/Bottom
8 33S - Aluminum Divider
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
12 Connector Socket
13 Connector Screws

ASSEMBLY
=========
PRODUCTID COMPONENT QTY
--- --- --
1 9 8
1 10 4
1 11 1
2 1 1
2 8 1
3 1 1
3 8 1
4 1 1
4 8 1
5 1 1
5 8 2
6 1 1
6 8 2
7 1 1
7 8 3
11 12 8
11 13 8

I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:

PRODUCTID PRODUCTNAME
--- ----
2 Cage Assembly - Split Back
1 Cage Assemble - Solid Back
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
8 33S - Aluminum Divider
12 Connector Socket
13 Connector Screws

Is it possible to write such a query or stored procedure?http://www.dbforums.com/t1080526.html|||in a specific case, yes, if you know in advance how many levels down the hierarchy of assemblies/parts you need to go, you would write a left outer join query with as many joins as the maximum number levels you need to traverse to find all component parts for the given part

in the general case, where this number of levels is not known in advance, no, you can't write a query for this

however, you could write a stored proc, but note that the stored proc would be running a query inside a loop and building up its results in a temp table|||Here is a solution that uses a UDF. I thought it was quite slick.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=152361|||yeah, that's what i suggested -- a query inside a loop that builds a temp table

:) :) :)

Calling web services from Query Analyzer

I've made a database change to a table to add home and
operational amounts. In an upgrade path, I need to
populate these columns with values. There is already C#
code that does the currency conversion based on comparing
transactional currency to both the home and operational
currency codes. Right now, our upgrade scripts are run
via Query Analyzer. Is there a way for me to call web
services and execute methods that have already been
written to do the currency conversion for me? I'd hate to
reinvent the wheel and rewrite code for something that's
already been in done C#.
We are running SQL Server 2000.
Thanks in advance,
BettinaSQL Server's T-SQL language doesn't have any in-built features that can
perform any kind of http calls - soap or otherwise.
T-SQL does provide ability to call external libraries and operating system
commands via things such as sp_OACreate (COM) and xp_cmdshell (console
commands), so it's arguable that your could conceivably call a web-service
from within the Query Analyser, consume it with sp_xml_preparedocument etc
but this is really getting into the zen mastery area & not really what Query
Analyser was designed to perform.
You might do better writing a VBScript or JScript & running that as a DTS
package, or perhaps just coding something in a VS.Net project (with
whatever's your favourite language flavour..)
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> I've made a database change to a table to add home and
> operational amounts. In an upgrade path, I need to
> populate these columns with values. There is already C#
> code that does the currency conversion based on comparing
> transactional currency to both the home and operational
> currency codes. Right now, our upgrade scripts are run
> via Query Analyzer. Is there a way for me to call web
> services and execute methods that have already been
> written to do the currency conversion for me? I'd hate to
> reinvent the wheel and rewrite code for something that's
> already been in done C#.
> We are running SQL Server 2000.
> Thanks in advance,
> Bettina
>|||Hi Greg,
Thank you so much for your response. Unfortunately, I'm
not a database programmer (I only have experience as an
Oracle Forms programmer) and I am not knowledgeable in
creating VBScripts, JScript, or writing something up using
VS .NET. I was hoping to find an easier answer.
Thanks again for your response.
Bettina

>--Original Message--
>SQL Server's T-SQL language doesn't have any in-built
features that can
>perform any kind of http calls - soap or otherwise.
>T-SQL does provide ability to call external libraries and
operating system
>commands via things such as sp_OACreate (COM) and
xp_cmdshell (console
>commands), so it's arguable that your could conceivably
call a web-service
>from within the Query Analyser, consume it with
sp_xml_preparedocument etc
>but this is really getting into the zen mastery area &
not really what Query
>Analyser was designed to perform.
>You might do better writing a VBScript or JScript &
running that as a DTS
>package, or perhaps just coding something in a VS.Net
project (with
>whatever's your favourite language flavour..)
>Regards,
>Greg Linwood
>SQL Server MVP
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
comparing
to
>
>.
>|||Sorry there's not an easier answer.. :c/
This (xml, soap etc) is an area where things are improving in SQL Server but
for now it's not a simple task to perform in QA & is better suited to
language development..
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:d76901c3ef5c$3bad51f0$a401280a@.phx.gbl...
> Hi Greg,
> Thank you so much for your response. Unfortunately, I'm
> not a database programmer (I only have experience as an
> Oracle Forms programmer) and I am not knowledgeable in
> creating VBScripts, JScript, or writing something up using
> VS .NET. I was hoping to find an easier answer.
> Thanks again for your response.
> Bettina
>
> features that can
> operating system
> xp_cmdshell (console
> call a web-service
> sp_xml_preparedocument etc
> not really what Query
> running that as a DTS
> project (with
> message
> comparing
> to

Calling web services from Query Analyzer

I've made a database change to a table to add home and
operational amounts. In an upgrade path, I need to
populate these columns with values. There is already C#
code that does the currency conversion based on comparing
transactional currency to both the home and operational
currency codes. Right now, our upgrade scripts are run
via Query Analyzer. Is there a way for me to call web
services and execute methods that have already been
written to do the currency conversion for me? I'd hate to
reinvent the wheel and rewrite code for something that's
already been in done C#.
We are running SQL Server 2000.
Thanks in advance,
BettinaSQL Server's T-SQL language doesn't have any in-built features that can
perform any kind of http calls - soap or otherwise.
T-SQL does provide ability to call external libraries and operating system
commands via things such as sp_OACreate (COM) and xp_cmdshell (console
commands), so it's arguable that your could conceivably call a web-service
from within the Query Analyser, consume it with sp_xml_preparedocument etc
but this is really getting into the zen mastery area & not really what Query
Analyser was designed to perform.
You might do better writing a VBScript or JScript & running that as a DTS
package, or perhaps just coding something in a VS.Net project (with
whatever's your favourite language flavour..)
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> I've made a database change to a table to add home and
> operational amounts. In an upgrade path, I need to
> populate these columns with values. There is already C#
> code that does the currency conversion based on comparing
> transactional currency to both the home and operational
> currency codes. Right now, our upgrade scripts are run
> via Query Analyzer. Is there a way for me to call web
> services and execute methods that have already been
> written to do the currency conversion for me? I'd hate to
> reinvent the wheel and rewrite code for something that's
> already been in done C#.
> We are running SQL Server 2000.
> Thanks in advance,
> Bettina
>|||Hi Greg,
Thank you so much for your response. Unfortunately, I'm
not a database programmer (I only have experience as an
Oracle Forms programmer) and I am not knowledgeable in
creating VBScripts, JScript, or writing something up using
VS .NET. I was hoping to find an easier answer.
Thanks again for your response.
Bettina
>--Original Message--
>SQL Server's T-SQL language doesn't have any in-built
features that can
>perform any kind of http calls - soap or otherwise.
>T-SQL does provide ability to call external libraries and
operating system
>commands via things such as sp_OACreate (COM) and
xp_cmdshell (console
>commands), so it's arguable that your could conceivably
call a web-service
>from within the Query Analyser, consume it with
sp_xml_preparedocument etc
>but this is really getting into the zen mastery area &
not really what Query
>Analyser was designed to perform.
>You might do better writing a VBScript or JScript &
running that as a DTS
>package, or perhaps just coding something in a VS.Net
project (with
>whatever's your favourite language flavour..)
>Regards,
>Greg Linwood
>SQL Server MVP
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
>> I've made a database change to a table to add home and
>> operational amounts. In an upgrade path, I need to
>> populate these columns with values. There is already C#
>> code that does the currency conversion based on
comparing
>> transactional currency to both the home and operational
>> currency codes. Right now, our upgrade scripts are run
>> via Query Analyzer. Is there a way for me to call web
>> services and execute methods that have already been
>> written to do the currency conversion for me? I'd hate
to
>> reinvent the wheel and rewrite code for something that's
>> already been in done C#.
>> We are running SQL Server 2000.
>> Thanks in advance,
>> Bettina
>
>.
>|||Sorry there's not an easier answer.. :c/
This (xml, soap etc) is an area where things are improving in SQL Server but
for now it's not a simple task to perform in QA & is better suited to
language development..
Regards,
Greg Linwood
SQL Server MVP
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:d76901c3ef5c$3bad51f0$a401280a@.phx.gbl...
> Hi Greg,
> Thank you so much for your response. Unfortunately, I'm
> not a database programmer (I only have experience as an
> Oracle Forms programmer) and I am not knowledgeable in
> creating VBScripts, JScript, or writing something up using
> VS .NET. I was hoping to find an easier answer.
> Thanks again for your response.
> Bettina
> >--Original Message--
> >SQL Server's T-SQL language doesn't have any in-built
> features that can
> >perform any kind of http calls - soap or otherwise.
> >
> >T-SQL does provide ability to call external libraries and
> operating system
> >commands via things such as sp_OACreate (COM) and
> xp_cmdshell (console
> >commands), so it's arguable that your could conceivably
> call a web-service
> >from within the Query Analyser, consume it with
> sp_xml_preparedocument etc
> >but this is really getting into the zen mastery area &
> not really what Query
> >Analyser was designed to perform.
> >
> >You might do better writing a VBScript or JScript &
> running that as a DTS
> >package, or perhaps just coding something in a VS.Net
> project (with
> >whatever's your favourite language flavour..)
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:cb4301c3ef43$84912d60$a101280a@.phx.gbl...
> >> I've made a database change to a table to add home and
> >> operational amounts. In an upgrade path, I need to
> >> populate these columns with values. There is already C#
> >> code that does the currency conversion based on
> comparing
> >> transactional currency to both the home and operational
> >> currency codes. Right now, our upgrade scripts are run
> >> via Query Analyzer. Is there a way for me to call web
> >> services and execute methods that have already been
> >> written to do the currency conversion for me? I'd hate
> to
> >> reinvent the wheel and rewrite code for something that's
> >> already been in done C#.
> >>
> >> We are running SQL Server 2000.
> >>
> >> Thanks in advance,
> >> Bettina
> >>
> >
> >
> >.
> >

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
Maury
I do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried..
.
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried
..
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will n
ot
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:
> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury