geting value from binary

hi,
is it possible to get value as follows in SQL server
I will have values in 101, 111 like in binary , I want to get its decimal values
I want to get 5 for 101, 7 for 111 ...
please let me know ...
RamSo, you have a string containing a kind of binary number?

Consider to solve this in a user-defined function. Loop through your string, and compute the power of 2 if applicable.

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

geting the UNIQUENAME fom a string

Does anyone have an idea how to get the UNIQUENAME of a member from ther dimesion date given a determined value.

For example in Adventure Works

Val: 2004

UNIQUENAME: [Date].[Calendar].[Calendar Year].&[2004]

Thanks!!

Here is an example showing how to retrieve the unique member name or the key value associated with the current member:

WITH

MEMBER MEASURES.[UniqueName] AS

[Date].Calendar.CurrentMember.UniqueName

MEMBER MEASURES.[KeyValue] AS

[Date].Calendar.CurrentMember.Properties("Key")

SELECT

{[Date].[Calendar].[Calendar Year].&[2004]} ON COLUMNS,

{MEASURES.[UniqueName],MEASURES.[KeyValue]} ON ROWS

FROM [Adventure Works]

HTH,

- Steve

Geting the report url

Hello.

Is there a way in a report to get his url?

I'll explain what i want to do: I want to add an option to export the report to excel format with out useing the menu so i want to add a button (or a table cell) and give him,in the nevigation field, the same url of the rreport with the same parameters but add to him the command "rs:format=excel" and then by pressing the button it will automaticly export my report.

Thanks.I thought it was an easy one :)

If my question is not clear enough, please let me know.

Thanks.|||

Craft a URL like this:

http://localhost/reportserver?/ReportProject1/Report&rs:Command=Render&rs:Format=EXCEL

Documented at http://msdn2.microsoft.com/en-us/library/ms153586.aspx.

Thanks, Donovan

Geting the last inserted row for each CLIENT. How?

Hi,

I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).

Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.

Does anyone knows how to do this? Is it possible with T-SQL only?

Thanks.

Antonio:

Is one of this close to what you are looking for?

Dave

-- --
-- I have used a LEFT JOIN in this case because I think that it
-- is probably important that a client that has not been called
-- be listed.
--
-- I went ahead and added the "recordsToPull" column to provide
-- for the future change when you want to be able to vary the
-- number of conversations returned on a client-by-client
-- basis
-- --
set nocount on

declare @.client table
( client_id integer not null,
recordsToPull integer not null
)

declare @.conversations table
( client_id integer not null,
conv_id integer not null,
conversation_date datetime not null
)

insert into @.client values (1, 4)
insert into @.client values (2, 2)
insert into @.client values (3, 4)
insert into @.client values (4, 4)
--select * from @.client

insert into @.conversations values (1, 1, '1/14/2006')
insert into @.conversations values (1, 2, '10/31/2006')
insert into @.conversations values (1, 3, '3/17/6')

insert into @.conversations values (2, 4, '2/2/6')
insert into @.conversations values (2, 5, '2/2/6')
insert into @.conversations values (2, 6, '2/4/6')
insert into @.conversations values (2, 7, '2/5/6')
insert into @.conversations values (2, 8, '2/8/6')

insert into @.conversations values (3, 9, '7/1/5')
insert into @.conversations values (3, 10, '11/23/5')
insert into @.conversations values (3, 11, '1/17/6')
insert into @.conversations values (3, 12, '4/1/6')
insert into @.conversations values (3, 13, '10/31/6')
--select * from @.conversations

--
-- SQL Server 2005 Version
--
/*
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select client_id,
conv_id,
conversation_date,
row_number ()
over ( partition by client_id
order by conversation_date desc, conv_id desc
)
as seq
from @.conversations
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq
*/

--
-- SQL Server 2000 Version
--
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select x.client_id,
x.conv_id,
x.conversation_date,
count(*) as seq
from @.conversations x
inner join @.conversations y
on x.client_id = y.client_id
and ( x.conversation_date < y.conversation_date or
x.conversation_date = y.conversation_date and
x.conv_id <= y.conv_id
)
group by x.client_id,
x.conversation_date,
x.conv_id
-- order by x.client_id,
-- x.conversation_date desc,
-- x.conv_id desc
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq

--
-- Sample Output:
--

-- client_id recordsToPull conv_id conversation_date
-- -- - -- --
-- 1 4 2 2006-10-31 00:00:00.000
-- 1 4 3 2006-03-17 00:00:00.000
-- 1 4 1 2006-01-14 00:00:00.000
-- 2 2 8 2006-02-08 00:00:00.000
-- 2 2 7 2006-02-05 00:00:00.000
-- 3 4 13 2006-10-31 00:00:00.000
-- 3 4 12 2006-04-01 00:00:00.000
-- 3 4 11 2006-01-17 00:00:00.000
-- 3 4 10 2005-11-23 00:00:00.000
-- 4 4 NULL NULL

|||

It would help if you post the version of SQL Server you are using. In SQL Server 2005, you can do below:

select c.*, t.*

from CLIENTS as c

cross apply (

select top(@.n) *

from CONVERSATIONS as cn

where cn.CLIENT_ID = c.CLIENT_ID

order by cn.CONVERSATION_DATE desc

) as t

|||

Thanks

this is exactly what I needed, worked perfectly 2005 version, didn't try 2000, but I'll need it too...

|||

Hi,

I don't know if you tryed it but didn't work for me. Thanks anyway.

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.
See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.
|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegr oups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>> Hi,
>> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table
>> where
>> CONV_ID and CLIENT_ID form the pk, there is another column
>> CONVERSATION_DATE
>> where the conversation data is registered (and other columns).
>> Now I need to retrieve, for each client, the last N (for some clients,
>> eventually, less then N) conversations with one T-SQL statement.
>> Does anyone knows how to do this? Is it possible with T-SQL only?
>> Thanks.
>sql

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DA
TE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
[url]http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1[/
url]
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Geting report url inside the report

Hello.
Is there a way in a report to get his url?
I'll explain what i want to do: I want to add an option to export the report to excel format with out useing the menu so i want to add a button (or a table cell) and give him,in the nevigation field, the same url of the rreport with the same parameters but add to him the command "rs:format=excel" and then by pressing the button it will automaticly export my report.
Thanks
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comi was sure this is an easy one :)
if my question wasn't clear enough, please let me know.
Thanks in advance/

geting money type from sql

hi

i am retriving value from sql server database like

select cast(round(12345674.8658,2,0) as decimal(20,2))

output is 12345674.87
but i want to get like 12,345,674.87
any function is there?do that in your code. Or cast to Money not decimal.

Geting event from ActiveX into database

Hello everyone,

I was wondering. Is it possible, to recive an event from ActiveX into
database?
I was looking alredy with notification services, but I think that's
the wrong way.

Lets say, that there is a ActiveX which realize some tasks.
The database trigers the ActiveX like below:

================================================== =============

declare @.iRetValint
declare @.iObjectint
declare @.sPropertyvarchar(2560)
declare @.sSource varchar(1000)
declare @.sDescription varchar(1000)
declare @.sLog varchar(1000)
declare @.dDateEVT datetime
declare @.sText1varchar(10)
declare @.sText2varchar(10)
declare @.iProperty int

set @.iObject = 0
set @.dDateEVT = getdate()
set @.iRetVal = 0
set @.sText1 = '0000000000'
set @.sText2 = '0000000000'
set @.iProperty = 7

exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
IF @.iRetVal <> 0
begin
set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
Description: ' + @.sDescription
print @.sLog
end

-- Method
exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
@.textFromA2 = @.sText2
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT

IF @.iRetVal <> 0
begin
set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

PRINT 'Property from method:'
PRINT @.iProperty

================================================== =============

This function works properly. I recive the data into ActiveX.
After the ActiveX process the data, it returns the event, wit a
response for this call of method.

Now ... how do I can get, this event into SQL server?
Is it possible to do that enyhow, without acctions like:
- do, that the ActiveX writes the data in to a interface table, where
from it will be readed.

Any help would be appreciated

Mateuszmarzec@.sauron.xo.pl (Matik) wrote in message news:<8b6e9bf5.0408190717.705a3dec@.posting.google.com>...
> Hello everyone,
> I was wondering. Is it possible, to recive an event from ActiveX into
> database?
> I was looking alredy with notification services, but I think that's
> the wrong way.
> Lets say, that there is a ActiveX which realize some tasks.
> The database trigers the ActiveX like below:
> ================================================== =============
> declare @.iRetValint
> declare @.iObjectint
> declare @.sPropertyvarchar(2560)
> declare @.sSource varchar(1000)
> declare @.sDescription varchar(1000)
> declare @.sLog varchar(1000)
> declare @.dDateEVT datetime
> declare @.sText1varchar(10)
> declare @.sText2varchar(10)
> declare @.iProperty int
> set @.iObject = 0
> set @.dDateEVT = getdate()
> set @.iRetVal = 0
> set @.sText1 = '0000000000'
> set @.sText2 = '0000000000'
> set @.iProperty = 7
> exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
> Description: ' + @.sDescription
> print @.sLog
> end
> -- Method
> exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
> OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
> @.textFromA2 = @.sText2
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
> @.sDescription
> print @.sLog
> end
> PRINT 'Property from method:'
> PRINT @.iProperty
> ================================================== =============
> This function works properly. I recive the data into ActiveX.
> After the ActiveX process the data, it returns the event, wit a
> response for this call of method.
> Now ... how do I can get, this event into SQL server?
> Is it possible to do that enyhow, without acctions like:
> - do, that the ActiveX writes the data in to a interface table, where
> from it will be readed.
> Any help would be appreciated
> Mateusz

I suspect this is not possible, since Books Online has no information
on event handling with the sp_OA% procedures. You should probably
consider using an external program or script to do what you need,
perhaps scheduled to poll a table on the server at intervals. I know
you mentioned that you're trying to avoid this, so if you need an
alternative approach you might want to give some more details of
exactly what you're trying to do, and why an "interface table" isn't a
good solution in your situation.

Simon|||Thank you for reply.

Well... that's the problem. The ActiveX shouldn't be changed any more,
and that's why I have no possibility to implement a function in it,
which writes the data in to interface table. That's why I do not want
such a solution.

I do some algorithm in the database, and in one step, I must 'ask'
ActiveX, for data. This data are the condition for the next steps in the
algorithm from DB.
Now, via sp_OAx procedures, it is possible, to interact with this
ActiveX to give it the data. But the ActiveX do not respond immiedetly,
only via event. It is immposible, to change the ActiveX, that on event,
it writes the data back into DB. I have the solution, that I can have
another program, which catches the event from ActiveX and then writes it
back into DB (or a program which will be called from sp_OAx, and waits
internaly for the ActiveX so long, that as return value can give the
values recived from ActiveX). But these are solutions, which I use
reluctantly.

But anyway ... The solution with table, want be bad, but then I must
have a while loop, til I become answer. I would like not to implement
such a solution.

The best way, as I said, will be, to get the event directly (anyhow) in
to DB, with mechanics and solutions of SQL DB.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Mateusz Marzec (marzec@.sauron.xo.pl) writes:
> Well... that's the problem. The ActiveX shouldn't be changed any more,
> and that's why I have no possibility to implement a function in it,
> which writes the data in to interface table. That's why I do not want
> such a solution.
> I do some algorithm in the database, and in one step, I must 'ask'
> ActiveX, for data. This data are the condition for the next steps in the
> algorithm from DB.
> Now, via sp_OAx procedures, it is possible, to interact with this
> ActiveX to give it the data. But the ActiveX do not respond immiedetly,
> only via event. It is immposible, to change the ActiveX, that on event,
> it writes the data back into DB. I have the solution, that I can have
> another program, which catches the event from ActiveX and then writes it
> back into DB (or a program which will be called from sp_OAx, and waits
> internaly for the ActiveX so long, that as return value can give the
> values recived from ActiveX). But these are solutions, which I use
> reluctantly.

An event is essentially a callback, and T-SQL has no mechanism for this.
So if you can't change the COM module, you would have to write a wrapper
that reacts on the callback.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp