Conversion between data types

Lets say I execute: SELECT hashbytes('MD5','IMTIAZ')

Which returns: 0x60D164C6B64EE81C7E7395C01D838FEE

How do I get a varchar: 60D164C6B64EE81C7E7395C01D838FEE

Not converted.

How do I get the 0x removed from the string ?

Regards

Imtiaz

I have been googling to find a solution to this question.....

I have acome across a few places to use the xp_varbintohexstr undocumented procedure. But in SQL 2005 what is the equivalent and any pointers in this direction will be of great help.

Regards

Imtiaz

|||

Ok...here's the answer...

SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

|||You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.

Controlling the cursor position

Let's say I run a query that returns a 100k records. Is there a way to
control where the cursor is in the result set. For example, can I get it
to jump to row 50k and return the next 500 rows?Taken from Transact SQL help:
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor
Hope that helps
"preston" wrote:

> Let's say I run a query that returns a 100k records. Is there a way to
> control where the cursor is in the result set. For example, can I get it
> to jump to row 50k and return the next 500 rows?
>|||Hi, preston
See: http://www.aspfaq.com/show.asp?id=2120
Razvan

Control names of fields when exporting to CSV

My query returns fields that have spaces in the names, for example:

select o.OrderID as 'INVOICE NUMBER', etc..

When I created the DataSet for this query in report designer, it changed all spaces in the field names to underscores, i.e. INVOICE_NUMBER.

This report must be exported to CSV, and the names of the fields (first row in csv) must have the spaces, not underscores. But it appears RS uses the field names (not the names in the column headers of the report table control) as the field names. Is there any way for me to force the field names in the first row of the csv to something other than the field names in the DataSet?

Thanks!

I think I you should be able to rename the column headers to change the results in the CSV file.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

ALFKI,

You can go to the column properties data output tab and enter the "Element Name" and select output as "Yes" this is how I can my export to csv files.

Ham

|||

should read

"how I change my export column names in the csv files"

|||Thanks - the only problem is, Element Name cannot contain a space , hence the underscores. Oh, well...|||

ALFKI,

Sorry, Yes, you are still bounded by the textbox property field names. You could not for instance name a textbox "My Textbox Value" is must be My_TextBox_Value. I mistaken thought you were just trying to rename the field.

Ham

continuous dates

Hi Guys,
I have a sql query that returns a count of a certain column say the
number of bugs per w on a w by w basis. there are some ws
which have no bugs and these ws are not returned. How can I write a
sql query that has all the ws whether there were bugs or not and
return 0 for the ws where there were no bugs.
for example:
at the moment:
w no. of bugs
22/1/06 10
15/1/06 5
1/1/06 2
(w 8/1/06 is missing)
what I expect :
w no. of bugs
22/1/06 10
15/1/06 5
8/1/06 3
1/1/06 2
infact I want all the ws from 1970 to present
Thanking you,
Regards,
NJYou need to provide more information to get an accurate response. It would
greatly help to see the table involved and some sample data. What is the
criteria for determining if there is a bug or not? Are you summing rows
with similar dates or is there a BUG column that has a value in it. Your
example does not make sense in that you state 8/1/06 is missing yet you show
it as having 3 bugs.
Andrew J. Kelly SQL MVP
"NJ" <npaulus@.hotmail.com> wrote in message
news:1137886146.073541.14890@.g43g2000cwa.googlegroups.com...
> Hi Guys,
> I have a sql query that returns a count of a certain column say the
> number of bugs per w on a w by w basis. there are some ws
> which have no bugs and these ws are not returned. How can I write a
> sql query that has all the ws whether there were bugs or not and
> return 0 for the ws where there were no bugs.
> for example:
> at the moment:
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 1/1/06 2
> (w 8/1/06 is missing)
> what I expect :
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 8/1/06 3
> 1/1/06 2
> infact I want all the ws from 1970 to present
> Thanking you,
> Regards,
> NJ
>|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||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.
Guessing at what you have, build a report range table:
CREATE TABLE WRanges
(w_nbr INTEGER NOT NULL PRIMARY KEY,
w_start DATETIME NOT NULL,
w_finish DATETIME NOT NULL,
CHECK (w_start, w_finish));
then do a query like this:
SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
FROM WRanges AS W
LEFT OUT JOIN
BugReport AS B
ON B.bug_date BETWEEN W.w_start AND W, w_finish
GROUP BY W.w_nbr, W.w_start;|||
--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.
>
True. Unfortunately, not letting us see this code of yours
would have been much kinder. Slop, slop, slop.

>Guessing at what you have, build a report range table:
>CREATE TABLE WRanges
>(w_nbr INTEGER NOT NULL PRIMARY KEY,
> w_start DATETIME NOT NULL,
> w_finish DATETIME NOT NULL,
> CHECK (w_start, w_finish));
>
CHECK what?

>then do a query like this:
>SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
> FROM WRanges AS W
> LEFT OUT JOIN
>
LEFT OUT JOIN?

> BugReport AS B
> ON B.bug_date BETWEEN W.w_start AND W, w_finish
>
W, w_finish? And for that matter, how is BETWEEN going
to work at all?. Do Sunday bugs get counted in two separate
ws, or do they not get counted at all? Who knows?

>GROUP BY W.w_nbr, W.w_start;
>
>
Steve Kass
Drew University|||On 21 Jan 2006 15:29:06 -0800, NJ wrote:

>Hi Guys,
>I have a sql query that returns a count of a certain column say the
>number of bugs per w on a w by w basis. there are some ws
>which have no bugs and these ws are not returned. How can I write a
>sql query that has all the ws whether there were bugs or not and
>return 0 for the ws where there were no bugs.
Hi NJ,
Use a calendar table (www.aspfaq.com/2519).
SELECT c.dt, COUNT(y.BugDate)
FROM Calendar AS c
LEFT OUTER JOIN YourTable AS y
ON y.BugDate >= c.dt
AND y.BugDate < DATEADD(day, 7, c.dt)
WHERE c.dayname = 'Sunday'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP