copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
Staffan
Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan
|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
StaffanHi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx
.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx
.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
StaffanHi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

Converts the 5 character string duration (ie hh:nn) to minutes

Dear All,

I wanted to convert 5 characters string duration (ie hh:mm - 10:30) to minutes and convert back the resulting minutes to 5 character string duration using a scalar UDFs in Sqlserver 2000. How do i write the script to obtain the desired output.

I badly need some from someone.

Give few set of example. do you want result 10 *60 + 30 for 10:30..|||

For example, if duration is 10 hrs. 30 mins. written in 10:30 format should be convert to minutes, means convert 10 hours into minutes and add 30 mins to it will be the resulting output = 630 minutes this should be convert back into the previous (10:30) format.

Thanks

|||

here you go...

Code Snippet

Create Table #times (

[Time] Varchar(100)

);

Insert Into #times Values('10:30');

Insert Into #times Values('12:34');

Insert Into #times Values('15:45');

Select

Datediff(Mi,Cast('00:00' as datetime) ,Cast([Time] as Datetime))

From

#times

--or

Select

Substring([Time],1,Charindex(':',[Time])-1) * 60

+ Substring([Time],Charindex(':',[Time]) + 1,10)

From

#Times

|||

Thanks alot for the help. And how about the reverse of that, ie, Converts the resulting minutes to 5 character string duration

|||

Yes.. here it is...

Code Snippet

Create Table #mindata (

[Mint] int

);

Insert Into #mindata Values('630');

Insert Into #mindata Values('754');

Insert Into #mindata Values('945');

Select

Cast(Mint/60 as Varchar) + ':' + Cast(Mint%60 as Varchar)as [Time]

From

#mindata

|||

Dear Manivannan.D.Sekaran,

Thank you very very very much.

Converting Word Document into RDL Format

Hi,
how can I convert a word document into the RDL Format for Reporting Services.
Is there a tool or an workaround ?
Thanks
StefanOn Jul 19, 11:46 pm, Stefan <Ste...@.discussions.microsoft.com> wrote:
> Hi,
> how can I convert a word document into the RDL Format for Reporting Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan
As far as I know, there is not really anything available. You would
need to investigate into using a custom .NET application to read in
the word document (most likely using System.IO and Streamreader) and
then output the file in the RDL format (using Streamwriter, XMLDoc,
etc). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Is the issue that you want a word document that comes up similar to click on
a report. I do this now. In the designer just do a right mouse click on
reports, add and existing item, then select your word document. Deploy like
a normal report. The only caveat is when you redeploy first go into report
manager and delete it and then deploy. I have found a redeploy does not
stick.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan" <Stefan@.discussions.microsoft.com> wrote in message
news:E80FD6D3-A511-4C37-B018-5C872946555D@.microsoft.com...
> Hi,
> how can I convert a word document into the RDL Format for Reporting
> Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan

Converting visual foxpro to sql 2000

I am trying to convert a foxpro database to sql 2000 using the wizard within sql 2000 but it keeps giving me an odbc error when accessing the foxpro database? I am stuck and don't know where to turn next. Any ideas? Thanks
rick,
Please share your error message and the version of Visual FoxPro you are
using. It sounds like a problem in defining your
ODBC data source, but need more info.
Russell Fields
"rick" <anonymous@.discussions.microsoft.com> wrote in message
news:DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com...
> I am trying to convert a foxpro database to sql 2000 using the wizard
within sql 2000 but it keeps giving me an odbc error when accessing the
foxpro database? I am stuck and don't know where to turn next. Any ideas?
Thanks
|||In news: DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com,
rick <anonymous@.discussions.microsoft.com> wrote:
> I am trying to convert a foxpro database to sql 2000 using the wizard
> within sql 2000 but it keeps giving me an odbc error when accessing
> the foxpro database?
Hi Rick,
FoxPro tables can come in either of two formats. They can be "free" tables,
meaning that each table is independent of the others, or they can be part of
a "database container" which holds metadata and allows additional features
for the tables. If you see a file with a DBC extension then you've got a
database.
Also, tables using any of the new features that were introduced in VFP7 and
VFP8 can only be accessed via the FoxPro and Visual FoxPro OLE DB data
provider.
Both the ODBC driver and OLE DB data provider are downloadable from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx .
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@.mvps.org www.cindywinegarden.com

Converting Visual FoxPro reports to RDL

I need to convert Microsoft Visual FoxPro reports (FRX files) to RDL format.
FoxPro report has groups.
I think I must create nested List elements to simulate grouping and add
report fields to those list elements.
I have analyzed some possibilities:
1. Generate RDL XML from VFP FRX file using XmlDocument or XML stream.
2. Using dynamic RDL generation class from http://www.gotreportviewer.com
3. Transform XML report defintion generated by FoxPro XMLListener
(http://msdn2.microsoft.com/en-us/library/ms994713(VS.80).aspx) to RDL
4. Use intermediate format like described in
http://www.vfpconversion.com/Blog.aspx?blogid=1b208937-2232-4b48-9a12-8c89f98ce08f&blog=us:Mike&messageid=b842330d-c2e2-4b5e-9d04-3415c8c0a4ad
Any idea or sample of FRX to RDL conversion ?
Andrus.Hi Andrus,
I see there is a VFP to RDL conversion tool at
http://www.vfpconversion.com/Vfp2NetReports.aspx .
--
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
"Andrus" <kobruleht2@.hot.ee> wrote in message
news:%23SyWacFVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>I need to convert Microsoft Visual FoxPro reports (FRX files) to RDL
>format.
> FoxPro report has groups.
> I think I must create nested List elements to simulate grouping and add
> report fields to those list elements.
> I have analyzed some possibilities:
> 1. Generate RDL XML from VFP FRX file using XmlDocument or XML stream.
> 2. Using dynamic RDL generation class from http://www.gotreportviewer.com
> 3. Transform XML report defintion generated by FoxPro XMLListener
> (http://msdn2.microsoft.com/en-us/library/ms994713(VS.80).aspx) to RDL
> 4. Use intermediate format like described in
> http://www.vfpconversion.com/Blog.aspx?blogid=1b208937-2232-4b48-9a12-8c89f98ce08f&blog=us:Mike&messageid=b842330d-c2e2-4b5e-9d04-3415c8c0a4ad
>
> Any idea or sample of FRX to RDL conversion ?
>
> Andrus.
>
>

Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>

Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.

Converting varchar to datetime

I have a sql server 2000 db that has a carchar field that is currently storying date data in the following format:

June 16

Can I convert that from varchar to datetime or smalldatetime without loss of data? And, if so, what adjustments do I have to make in my ado code to continue to allow my clients to add data. Currently they add date data by selecting a month from one dropdown and the day from another.

Thanks!Create another column and use CONVERT function to conver the date and store. Then delete the old column.

Converting US Date To UK Date in Reporting Services

I am using thise function in the Code Properties of my Report to convert US Date formats to UK Date format :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As Object) As String
Dim strDay As String = strUSDate.Substring(3, 2)
Dim strMonth As String = strUSDate.Substring(0, 2)
Dim strYear As String = strUSDate.Substring(6, 4)
Dim strUKDate As String = strDay + "/" + strMonth + "/" + strYear + strUSDate.Substring(10, 12)
Return strUKDate
End Function

I keep getting an error with it, I'm not sure whether it is a VB.NET code error, or a Reporting Services error. Can anybody help?

Cheers,

Mike

Sorted this out :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As String) As String
Dim strUKDate As String = ""
If Not (strUSDate = "") Then
Dim arrRateArray As String()
Dim arrSeparator As Char() = {"/"C}
arrRateArray = strUSDate.Split(arrSeparator)
Dim strMonth As String = arrRateArray(0)
Dim strDay As String = arrRateArray(1)
Dim strYear As String = arrRateArray(2).Substring(0, 4)
Dim strTime As String = arrRateArray(2).Substring(5, arrRateArray(2).Length - 5)
strUKDate = strDay + "/" + strMonth + "/" + strYear + " " + strTime
Else
strUKDate = ""
End If

Converting to number: convert, cast ??

HI,
Converting to number: convert, cast '
what should I use and how'
I have looked at sql server 2000 help and until now I couldn't get sucess1
to string there is the function Str and for numbers'
Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
vbscript'
see the code below,thanks
vilmar
spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
Drop Procedure spInserirImagem
Create Procedure spInserirImagem
@.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
As
begin
Set @.IdImagem = Cast(@.IdImagem as int)
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
print RTRIM(LTRIM(@.strSQL))
Exec(@.strSQL)
end> Set @.IdImagem = Cast(@.IdImagem as int)
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
The problem here is that you are injecting an integer into a string. In
order for @.strSQL to work, you need to convert the INT to a string. This
should work fine:
Set @.IdImagem = Cast(@.IdImagem as VARCHAR(12))
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor) values('
+ @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
However, I'm not sure why you think you have to make a string out of this.
Can't you just do:
INSERT ImagemTopo(IdImagem,Descricao,PadraoCor)
VALUES(@.IdImagem, @.Descricao, @.PadraoCor)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi
I am not sure why think you need to cast this way, as you appending to a
string. The following should work:
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
values(' + CONVERT(varchar,@.IdImagem) + ',''' + @.Descricao + ''',''' +
@.PadraoCor + ''')'
John
"news.microsoft.com" <suporte@.hitecnet.com.br> wrote in message
news:uvtN3$zuDHA.2060@.TK2MSFTNGP10.phx.gbl...
> HI,
> Converting to number: convert, cast '
> what should I use and how'
> I have looked at sql server 2000 help and until now I couldn't get sucess1
> to string there is the function Str and for numbers'
> Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
> vbscript'
> see the code below,thanks
> vilmar
> spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
> Drop Procedure spInserirImagem
> Create Procedure spInserirImagem
> @.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
> As
> begin
> Set @.IdImagem = Cast(@.IdImagem as int)
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
> print RTRIM(LTRIM(@.strSQL))
> Exec(@.strSQL)
> end
>|||Thank you John Bell and Aaron Bertrand a lot!!
I was too forgetting about double single quotes in stored procedure besides
putting convert or cast in the way which
you showed me!!
Regards,
Vilmar
From Brazil
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:AK1Ab.20423$_y4.206566615@.news-text.cableinet.net...
> Hi
> I am not sure why think you need to cast this way, as you appending to a
> string. The following should work:
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + CONVERT(varchar,@.IdImagem) + ',''' + @.Descricao + ''',''' +
> @.PadraoCor + ''')'
> John
> "news.microsoft.com" <suporte@.hitecnet.com.br> wrote in message
> news:uvtN3$zuDHA.2060@.TK2MSFTNGP10.phx.gbl...
> > HI,
> > Converting to number: convert, cast '
> > what should I use and how'
> > I have looked at sql server 2000 help and until now I couldn't get
sucess1
> > to string there is the function Str and for numbers'
> >
> > Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
> > vbscript'
> > see the code below,thanks
> > vilmar
> > spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
> > Drop Procedure spInserirImagem
> > Create Procedure spInserirImagem
> > @.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
> > As
> > begin
> > Set @.IdImagem = Cast(@.IdImagem as int)
> > Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> > values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
> > print RTRIM(LTRIM(@.strSQL))
> > Exec(@.strSQL)
> > end
> >
> >
>

Converting to Hex

Hi All,

I'm needing to take a value inputted by a user via html form and convert it to a hex value upon inserting into SQL2000 db. I only need to store the 8 chars after "0x". Is there any T-SQL that can pull this off? CAST or CONVERT? Sorry if this is a silly question and hope I supplied enough info...

Thx,
Mike
Check this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1) for Umachandar's solution (which are always great!)

Converting to datetime...

Hello..
I am having a difficult time trying to get SQL Server to convert the
following date:
22-08-2004 00:00:00
I have tried to convert and cast and I get the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Any help would be most appreciative.
Thank you,
Brett
P.S.
I am using SQL Server 2000How are you doing this convert ? This works for me:
Select convert(varchar(20),'22-08-2004 00:00:00',102)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||You need to tell SQL Server more about the pattern used for the datetime. Tr
y
this:
Select Convert(DateTime, '22-08-2004 00:00:00', 105)
Without specifying the date format, I believe that SQL uses the format from
the
database's collation or perhaps the server's regional settings.
HTH
Thomas
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the follo
wing
> date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
an
> out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||Use styles 112 or 126. See CONVERT in BOL.
Example:
select cast('20040822' as datetime)
select cast('2004-08-22T00:00:00.000' as datetime)
go
AMB
"Brett Davis" wrote:

> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>
>|||Thomas,
You use the style parameter when converting from datetime to varchar / char
and not the opposite. All these statements will give same result.
select convert(datetime, '20050427', 105)
select convert(datetime, '20050427', 112)
select convert(datetime, '20050427', 126)
select convert(datetime, '20050427')
AMB
"Thomas" wrote:

> You need to tell SQL Server more about the pattern used for the datetime.
Try
> this:
> Select Convert(DateTime, '22-08-2004 00:00:00', 105)
> Without specifying the date format, I believe that SQL uses the format fro
m the
> database's collation or perhaps the server's regional settings.
>
> HTH
>
> Thomas
>
> "Brett Davis" <bdavis123@.cox.net> wrote in message
> news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
>
>|||I think we might be saying the same thing. By passing the style parameter, y
ou
are giving SQL information about the format of the string.

>All these statements will give same result.
> select convert(datetime, '20050427', 105)
> select convert(datetime, '20050427', 112)
> select convert(datetime, '20050427', 126)
> select convert(datetime, '20050427')
But these do not:
1. select convert(datetime, '22-08-2004 00:00:00', 105)
2. select convert(datetime, '22-08-2004 00:00:00', 112)
3. select convert(datetime, '22-08-2004 00:00:00', 126)
4. select convert(datetime, '22-08-2004 00:00:00',)
Only the first one successfully parses the string into a datetime. The other
s
fail with a conversion error because the system thinks that the first digits
are
the month instead of the day.
I'll grant you that passing the ISO format (yyyymmdd) would be the best way
to
avoid all of these problems.
Thomas|||You are right.
AMB
"Thomas" wrote:

> I think we might be saying the same thing. By passing the style parameter,
you
> are giving SQL information about the format of the string.
>
> But these do not:
> 1. select convert(datetime, '22-08-2004 00:00:00', 105)
> 2. select convert(datetime, '22-08-2004 00:00:00', 112)
> 3. select convert(datetime, '22-08-2004 00:00:00', 126)
> 4. select convert(datetime, '22-08-2004 00:00:00',)
> Only the first one successfully parses the string into a datetime. The oth
ers
> fail with a conversion error because the system thinks that the first digi
ts are
> the month instead of the day.
> I'll grant you that passing the ISO format (yyyymmdd) would be the best wa
y to
> avoid all of these problems.
>
> Thomas
>
>

Converting to ANSI joins

Is it possible to convert the following query to use ANSI join syntax?
It may be already but I'm suspecting the "WHERE t2.table_id =
t1.table_id" means it isn't. I tried things like the bottom query but
the number of records returned doesn't match. Any help would be
appreciated. I can't give the DDL or test date cause this is a
simplification of the actual query. Thanks a lot!
SELECT DISTINCT t1.table_id
FROM mytable t1
WHERE (t1.username = 'john')
AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
t1.table_id AND t2.username <> 'john'))
-- my attempt --
SELECT DISTINCT t1.table_id
FROM mytable t1
INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
'john'
WHERE (t1.username = 'john')It's not a JOIN but a correlated subquery. It is possible to replace this
clause with a LEFT JOIN and testing for the presence of the null value at
the right side; something like:
SELECT DISTINCT t1.table_id
FROM mytable t1 LEFT JOIN mytable t2 on (t2.table_id = t1.table_id AND
t2.username <> 'john')
WHERE (t1.username = 'john')
And t2.table_id is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149545664.941792.259700@.h76g2000cwa.googlegroups.com...
> Is it possible to convert the following query to use ANSI join syntax?
> It may be already but I'm suspecting the "WHERE t2.table_id =
> t1.table_id" means it isn't. I tried things like the bottom query but
> the number of records returned doesn't match. Any help would be
> appreciated. I can't give the DDL or test date cause this is a
> simplification of the actual query. Thanks a lot!
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> WHERE (t1.username = 'john')
> AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
> t1.table_id AND t2.username <> 'john'))
>
> -- my attempt --
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
> 'john'
> WHERE (t1.username = 'john')
>|||Thank you Sylvain I will try that.
Phil|||It works! Thank you Sylvain !!!|||The advantage of the LEFT JOIN is that on many occasions it will be faster
then the use of an Exists statement (not sure if it's still true but it was
some years ago).
However, it's much more easier to write some complex filterings with an
Exists() function than with the LEFT JOIN.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149603901.621194.200240@.h76g2000cwa.googlegroups.com...
> It works! Thank you Sylvain !!!
>

Converting To ADP, Reference Recommendations

I have been creating Access applications since version 2.0 and may soon
become involved with a project that will convert an existing database
into an ADP. I would consider myself verbose in VBA, but inexperienced
using a SQL server back end. Typically when faced with a new challenge
like this one, I will read technical articles & books plus start
experimenting with code. I am sure there are many of you out there
that understand the issues I will have to confront (it worked in
'just' Access, but now...). I would appreciate recommendations
regarding books, web page links, etc., that will be helpful in my next
project.
Access version 2000 - possibly moving to 2003 next year.
Regards,
Bob<boborta@.hotmail.com> wrote in message
news:1162223068.352845.97040@.i42g2000cwa.googlegroups.com...
> I have been creating Access applications since version 2.0 and may soon
> become involved with a project that will convert an existing database
> into an ADP. I would consider myself verbose in VBA, but inexperienced
> using a SQL server back end. Typically when faced with a new challenge
> like this one, I will read technical articles & books plus start
> experimenting with code. I am sure there are many of you out there
> that understand the issues I will have to confront (it worked in
> 'just' Access, but now...). I would appreciate recommendations
> regarding books, web page links, etc., that will be helpful in my next
> project.
> Access version 2000 - possibly moving to 2003 next year.
> Regards,
Keep in mind that you can use SQL Server as a back-end and continue to use
an access mdb as the front-end. Many developers use this model. You can
still use SQL Server functionality like views, stored procedures, etc.. I
have had difficulties with ADPs and no longer attempt to use them. I also
have questions about the future of ADPs. Others have different views. Google
groups for "access adp pros cons" and you'll see there has been a lot of
discussion on this. Perhaps it has already been decided that this project
will be ADP. If so then good luck.
If the requirement is to move the data to SQL Server but the format of the
front-end is still open I would generally recommend using mdb with linked
tables to SQL Server. A lot would depend on why you are upgrading, the
application itself, the experience of the developers, etc. This is all of
course in my opinion.|||Try the book:
Microsoft Access Developers Guide to SQL Server
by Mary Chipman and Andy Baron. They address a lot of the
issues you will face in the conversion from an Access
developers perspective.
-Sue
On 30 Oct 2006 07:44:28 -0800, "boborta@.hotmail.com"
<boborta@.hotmail.com> wrote:

>I have been creating Access applications since version 2.0 and may soon
>become involved with a project that will convert an existing database
>into an ADP. I would consider myself verbose in VBA, but inexperienced
>using a SQL server back end. Typically when faced with a new challenge
>like this one, I will read technical articles & books plus start
>experimenting with code. I am sure there are many of you out there
>that understand the issues I will have to confront (it worked in
>'just' Access, but now...). I would appreciate recommendations
>regarding books, web page links, etc., that will be helpful in my next
>project.
>Access version 2000 - possibly moving to 2003 next year.
>Regards,
>Bob

Converting To ADP, Reference Recommendations

I have been creating Access applications since version 2.0 and may soon
become involved with a project that will convert an existing database
into an ADP. I would consider myself verbose in VBA, but inexperienced
using a SQL server back end. Typically when faced with a new challenge
like this one, I will read technical articles & books plus start
experimenting with code. I am sure there are many of you out there
that understand the issues I will have to confront (it worked in
'just' Access, but now...). I would appreciate recommendations
regarding books, web page links, etc., that will be helpful in my next
project.
Access version 2000 - possibly moving to 2003 next year.
Regards,
Bob<boborta@.hotmail.com> wrote in message
news:1162223068.352845.97040@.i42g2000cwa.googlegroups.com...
> I have been creating Access applications since version 2.0 and may soon
> become involved with a project that will convert an existing database
> into an ADP. I would consider myself verbose in VBA, but inexperienced
> using a SQL server back end. Typically when faced with a new challenge
> like this one, I will read technical articles & books plus start
> experimenting with code. I am sure there are many of you out there
> that understand the issues I will have to confront (it worked in
> 'just' Access, but now...). I would appreciate recommendations
> regarding books, web page links, etc., that will be helpful in my next
> project.
> Access version 2000 - possibly moving to 2003 next year.
> Regards,
Keep in mind that you can use SQL Server as a back-end and continue to use
an access mdb as the front-end. Many developers use this model. You can
still use SQL Server functionality like views, stored procedures, etc.. I
have had difficulties with ADPs and no longer attempt to use them. I also
have questions about the future of ADPs. Others have different views. Google
groups for "access adp pros cons" and you'll see there has been a lot of
discussion on this. Perhaps it has already been decided that this project
will be ADP. If so then good luck.
If the requirement is to move the data to SQL Server but the format of the
front-end is still open I would generally recommend using mdb with linked
tables to SQL Server. A lot would depend on why you are upgrading, the
application itself, the experience of the developers, etc. This is all of
course in my opinion.|||Try the book:
Microsoft Access Developers Guide to SQL Server
by Mary Chipman and Andy Baron. They address a lot of the
issues you will face in the conversion from an Access
developers perspective.
-Sue
On 30 Oct 2006 07:44:28 -0800, "boborta@.hotmail.com"
<boborta@.hotmail.com> wrote:
>I have been creating Access applications since version 2.0 and may soon
>become involved with a project that will convert an existing database
>into an ADP. I would consider myself verbose in VBA, but inexperienced
>using a SQL server back end. Typically when faced with a new challenge
>like this one, I will read technical articles & books plus start
>experimenting with code. I am sure there are many of you out there
>that understand the issues I will have to confront (it worked in
>'just' Access, but now...). I would appreciate recommendations
>regarding books, web page links, etc., that will be helpful in my next
>project.
>Access version 2000 - possibly moving to 2003 next year.
>Regards,
>Bob

Converting Text to Proper Text in SQL

Given a string it should convert it to a proper text.
Example: if you passed a string 'Cat in the hat', I want 'Cat In The
Hat'

Curious about few things, Does sql have Instr OR Split(like VB)
functionality

Anybody can help??(m.ramana@.gmail.com) writes:
> Given a string it should convert it to a proper text.
> Example: if you passed a string 'Cat in the hat', I want 'Cat In The
> Hat'

I though "Cat in the Hat" was the proper title text in English,
and "Cat In The Hat" is what you get when you use a computer?

SQL Server is not strong on text maninpulation. You would have to loop
over the string, either one by one, or possibly piece by piece with
charindex(). There is no built-in for this.

> Curious about few things, Does sql have Instr OR Split(like VB)
> functionality

I would encourage you to look at Functions->String Functions in the
T-SQL References in Books Online. There you can learn about all functions
to manipulate strings in T-SQL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
m.ramana@.gmail.com wrote:
> Given a string it should convert it to a proper text.
> Example: if you passed a string 'Cat in the hat', I want 'Cat In The
> Hat'
> Curious about few things, Does sql have Instr OR Split(like VB)
> functionality
> Anybody can help??

CREATE function TitleCase
(
@.my_str as varchar(8000)
)
Returns varchar (8000)
AS
Begin
Declare @.this_str as varchar(8000)
Declare @.word_str as varchar(5000)
Declare @.spc int
Declare @.patindx1 as varchar(100)
Declare @.patindx2 as varchar(100)
Declare @.patindx3 as varchar(100)
Declare @.patindx4 as varchar(100)
select @.patindx1 = '%[ -"/().]%'
select @.patindx2 = '%[''][^s]%'
select @.patindx4 = '%[''][s][a-z]%'
select @.patindx3 = '%[0123456789][^snrt][^tdh]%'
Select @.this_str = ''

Select
@.my_str = LTrim(RTrim(@.my_str))

While Len(@.my_str) > 0
Begin
if (PatIndex(@.patindx1, @.my_str) + PatIndex(@.patindx2, @.my_str) +
PatIndex(@.patindx3, @.my_str) + PatIndex(@.patindx4, @.my_str) = 0)
Begin
Select
@.spc = Len(@.my_str)
End
Else
Begin
Select @.spc = PatIndex(@.patindx1, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx2, @.my_str) < @.spc
And PatIndex(@.patindx2, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx2, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx3, @.my_str) < @.spc
And PatIndex(@.patindx3, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx3, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx4, @.my_str) < @.spc
And PatIndex(@.patindx4, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx4, @.my_str)

End

Select
@.word_str = Left(@.my_str, @.spc)

Select
@.this_str = @.this_str + Upper(Left(@.word_str,1)) +
Lower(SubString(@.word_str,2,@.spc))
select @.my_str = Right(@.my_str, (Len(@.my_str)-(@.spc)))
End
Return (@.this_str)
End

--
David Rowland
NEW DBMonitor Out Now! New Email Alert Feature!
http://dbmonitor.tripod.com|||Great, I was about to create one, appreciate your time.

Converting text to date

I have a field that stores a date as text (121205). I need to convert this field to a date, but since it is text, I cannot figure out how to do it. Any ideas? Thanks! :Dwhich database? informix? sybase? db2? access? firebird? oracle? mysql? sql server? postgresql?|||SQL Server|||Moving this thread to Microsoft SQL Server forum, but I'd use:SELECT t, Convert(DATETIME, Stuff(Stuff(t, 5, 0, '/'), 3, 0, '/'))
FROM (SELECT '121205' AS t UNION SELECT '111105' UNION SELECT '101005') AS z
-PatP|||As you can tell, Pat likes to STUFF things...

CREATE PROC mySproc99 @.x text
AS
SELECT CONVERT(datetime, CONVERT(varchar(25),@.x))
GO

EXEC mySproc99 '121205'
GO

DROP PROC mySproc99
GO

So who's to say it's not 2012?|||Brett is correct, you don't need the slashes in the US, and maybe not in the UK either... They assume a string of digits are MMDDYY. As far as I know, the slashes work in any locale.

-PatP

Converting text to bit issues

I need to convert a text column to a bit in a select query.
Backing up, in case there's a better way to do this:
I have a varchar column that contains one of six letters. There are two
that interest me (Q and A). I want my vb.net application to update changes
back to this column.
For my application, Q=0 and A=1
The name of the column is 'Status'
I tried:
Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
.. didn't work. Any help?
UsarianTry,
Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
also:
convert(bit, ascii(upper(colA)) - ascii('Q'))
AMB
"Usarian Skiff" wrote:

> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> ... didn't work. Any help?
> Usarian
>
>|||Usarian Skiff wrote:
> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> .. didn't work. Any help?
> Usarian
>
cast((case when status = 'q' then 0 else 1 end) as bit)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Stinkin Yeah!
Thanks!
Usarian Skiff
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87BB3703-E7B8-4946-B0BF-681FBF548A61@.microsoft.com...
> Try,
> Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
> also:
> convert(bit, ascii(upper(colA)) - ascii('Q'))
>
> AMB
>
> "Usarian Skiff" wrote:
>
changes