Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:

> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the ￡ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=
und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.[vbcol=seagreen]
> Linchi
> "nomad" wrote:
>
>
>|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:[vbcol=seagreen]
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
Pound[vbcol=seagreen]
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
>
>
>
>
>
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the £ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.
> Linchi
> "nomad" wrote:
> > Hi,
> > I am creating a text file using bcp in SQL2000. When I open the text
> > file which was created the =A3 sign has been replaced with something
> > else. WHen I look at the results in a query, the pound sign is there,
> > it's only when bcp'd to a text file.
> > It is running on Windows 2003 Server.
> > ANy help would be appreciated.|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> > This is a char, varchar, or text code page issue. The ascii value for p=Pound
> > sterling is 163m, which is greater than 127. If you don't want any
> > conversation, try to use -C RAW on the bcp command line.
> > Hi,
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
> > Linchi
> > "nomad" wrote:
> > > Hi,
> > > I am creating a text file using bcp in SQL2000. When I open the text
> > > file which was created the =A3 sign has been replaced with something
> > > else. WHen I look at the results in a query, the pound sign is there,
> > > it's only when bcp'd to a text file.
> > > It is running on Windows 2003 Server.
> > > ANy help would be appreciated.
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Help with a substring query

I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.

Things you will need to keep in mind are;

The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.

Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Regards,
Ciarnchudson007@.hotmail.com wrote:
> I need help capturing information from a free text field.
> The 10 examples below contain examples of the information I am trying
> to capture.
> In each cell I am trying to capture the number between 'TranID=' and
> the next '&'.
> So in the fisrt cell I would like to capture 14078800.
> Things you will need to keep in mind are;
> The number is not of fixed length.
> 'TranID' will always precede the number
> The number will always be followed by an '&'
> The '&' sign can occur multiple times in the text.
>
> Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Hint: use LIKE.

Kind regards

robert|||--something like this:
declare @.tranid_position int, @.amp_position int,@.string varchar(8000),
@.rest_of_string varchar(8000), @.Result_string varchar(8000)
set
@.string='Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.P*ostCSDelivery&OtherFlag0par=deliv
'
set @.tranid_position=charindex('&TranID=',@.string)
set @.rest_of_string= substring(@.string,@.tranid_position+8,8000)
set @.amp_position=charindex('&',@.rest_of_string)
set @.Result_string=left(@.rest_of_string,@.amp_position-1)
select @.Result_string|||Here you go...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'

SELECT
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest

You could also look into regular expressions.|||That worked perfectly.
Much appreciated.|||I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example

OtherID=1638256785230&TranID=12345

How do I edit the script to capture these records?

Regards,
Ciarn|||There may be a better way but this should work...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'OtherID=1638256785230&TranID=12345'

SELECT
CASE WHEN
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
) = 0
THEN
RIGHT(QueryString,
LEN(QueryString)-
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-6
)
ELSE
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
END AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest|||(chudson007@.hotmail.com) writes:
> I've just encountered a little problam.
> Contrary to the criteria I provided earlier there are cells which end
> with the TranID, like for example
> OtherID=1638256785230&TranID=12345
> How do I edit the script to capture these records?

Here is a query, a little different from figitals:

SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('TranID=', str) + len('TranId='),
len(str))
FROM QueryStringTest) AS a) AS b

By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)

--
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|||Perfect!
Thanks

Help when renaming server

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5198B.423A4230
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi.
Due to corporate guidelines we are "forced" to change the name of our = Windows 2000 server which runs our precious SQL 7.0 server. I guess = there are some issues I have to deal with afterwards to get the database = engine up and about. Do any of you have good resources (URL's) or other = information which you may share with me, to enable us rename our server = without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0008_01C5198B.423A4230
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi.
Due to corporate guidelines we are = "forced" to change the name of our Windows 2000 server which runs our precious SQL = 7.0 server. I guess there are some issues I have to deal with afterwards to = get the database engine up and about. Do any of you have good resources (URL's) = or other information which you may share with me, to enable us rename our server = without to much hassle. Please.

Bst Regards
Mr. Smith
--=_NextPart_000_0008_01C5198B.423A4230--This is a multi-part message in MIME format.
--=_NextPart_000_0178_01C5199A.A55BC500
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
Look at sp_dropserver and sp_addserver in the BOL.
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message =news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our =Windows 2000 server which runs our precious SQL 7.0 server. I guess =there are some issues I have to deal with afterwards to get the database =engine up and about. Do any of you have good resources (URL's) or other =information which you may share with me, to enable us rename our server =without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0178_01C5199A.A55BC500
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1491" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi</FONT></DIV>
<DIV><FONT size=3D2>Look at sp_dropserver and sp_addserver in the BOL.</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Mr. Smith" <<A =href=3D"mailto:nospam@.blindfolded.gone">nospam@.blindfolded.gone</A>> =wrote in message <A =href=3D"news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl">news:uB9V6KYGFHA.444@.T=K2MSFTNGP15.phx.gbl</A>...</DIV>
<DIV>Hi.</DIV>
<DIV>
<P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN style=3D"mso-ansi-language: EN-US">Due to corporate guidelines we are ="forced" to change the name of our Windows 2000 server which runs our precious =SQL 7.0 server. I guess there are some issues I have to deal with afterwards =to get the database engine up and about. Do any of you have good resources =(URL's) or other information which you may share with me, to enable us rename our =server without to much hassle. Please.<o:p></o:p></SPAN></P></DIV>
<DIV> </DIV>
<DIV>Bst Regards</DIV>
<DIV>Mr. Smith</DIV></BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0178_01C5199A.A55BC500--|||This is a multi-part message in MIME format.
--=_NextPart_000_0140_01C51993.260B9F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
After having renamed the server, you just need to run sp_dropserver and =then sp_addserver. You can look up the syntax in BOL.
Regards
Steen "Mr. Smith" <nospam@.blindfolded.gone> skrev i en meddelelse =news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our =Windows 2000 server which runs our precious SQL 7.0 server. I guess =there are some issues I have to deal with afterwards to get the database =engine up and about. Do any of you have good resources (URL's) or other =information which you may share with me, to enable us rename our server =without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0140_01C51993.260B9F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>After having renamed the server, you =just need to run sp_dropserver and then sp_addserver. You can look up the syntax in BOL.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Steen</FONT> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Mr. Smith" <<A =href=3D"mailto:nospam@.blindfolded.gone">nospam@.blindfolded.gone</A>> =skrev i en meddelelse <A =href=3D"news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl">news:uB9V6KYGFHA.444@.T=K2MSFTNGP15.phx.gbl</A>...</DIV>
<DIV>Hi.</DIV>
<DIV>
<P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN style=3D"mso-ansi-language: EN-US">Due to corporate guidelines we are ="forced" to change the name of our Windows 2000 server which runs our precious =SQL 7.0 server. I guess there are some issues I have to deal with afterwards =to get the database engine up and about. Do any of you have good resources =(URL's) or other information which you may share with me, to enable us rename our =server without to much hassle. Please.<o:p></o:p></SPAN></P></DIV>
<DIV> </DIV>
<DIV>Bst Regards</DIV>
<DIV>Mr. Smith</DIV></BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0140_01C51993.260B9F70--|||There's more to it than only sp_dropserver and sp_addserver. Especially for a 7.0 SQL Server. Check
out http://www.karaszi.com/SQLServer/info_change_server_name.asp for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our Windows 2000 server which runs
our precious SQL 7.0 server. I guess there are some issues I have to deal with afterwards to get the
database engine up and about. Do any of you have good resources (URL's) or other information which
you may share with me, to enable us rename our server without to much hassle. Please.
Bst Regards
Mr. Smith|||Thanks Tibor.
The URL whas just what I needed.
Mr. Smith
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eYs%235vYGFHA.3732@.TK2MSFTNGP14.phx.gbl...
> There's more to it than only sp_dropserver and sp_addserver. Especially
for a 7.0 SQL Server. Check
> out http://www.karaszi.com/SQLServer/info_change_server_name.asp for more
information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
> Hi.
> Due to corporate guidelines we are "forced" to change the name of our
Windows 2000 server which runs
> our precious SQL 7.0 server. I guess there are some issues I have to deal
with afterwards to get the
> database engine up and about. Do any of you have good resources (URL's) or
other information which
> you may share with me, to enable us rename our server without to much
hassle. Please.
>
> Bst Regards
> Mr. Smith
>

Help w/ Not enough storage is available to complete this operation.

I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
************************************************** **************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
************************************************** **************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
************************************************** **************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
************************************************** **************************************************
I really need to get this data loaded... Someone PLEASE HELP!!
> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP
|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.

Help w/ Not enough storage is available to complete this operation.

I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************************************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************************************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************************************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************************************************************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
--
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > BTW, it's not a limitation on the size of my DB.. I've still got over
> > 20 GB of unused space allocated to me on the server farm.
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.

Help w/ Not enough storage is available to complete this operation.

I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************
************************************
************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************
************************************
************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************
************************************
************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_
trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************
************************************
************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive
?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hoste
d
> SQL Server provided by someone. If so, have you brought the error to thei
r
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.

Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David

Help Urgent

Hi everyone,
I'm getting this error from a production SQL Server 2000 +
SP3
Error: 7105, Severity: 22, State: 6
Page (1:433348), slot 7 for text, ntext, or image node
does not exist.
I found this article about thte error:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;304847&Product=sql2k
It says that this issue is solved with SP2. I have SP3,
what can i do ' I'm receiving this error right now and
after reading the article i'm afraid that the server
shutdown. Is a critical server to my enterprise. Can
anyone give me any tips for see what's going on and how to
solve it ?
Thanks in advance
MiguelDid you try some diagnosis...mybe try and run DBCC CHECKDB'
-- Miguel wrote: --
Hi everyone,
I'm getting this error from a production SQL Server 2000 +
SP3
Error: 7105, Severity: 22, State: 6
Page (1:433348), slot 7 for text, ntext, or image node
does not exist.
I found this article about thte error:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;304847&Product=sql2k
It says that this issue is solved with SP2. I have SP3,
what can i do ' I'm receiving this error right now and
after reading the article i'm afraid that the server
shutdown. Is a critical server to my enterprise. Can
anyone give me any tips for see what's going on and how to
solve it ?
Thanks in advance
Miguel|||Was the first thing i done, but i did not have the
database name, soo a run dbcc for all databases with no
errors returned. You can see witch database is causing the
error by the message ?

>--Original Message--
>Did you try some diagnosis...mybe try and run DBCC
CHECKDB'
> -- Miguel wrote: --
> Hi everyone,
> I'm getting this error from a production SQL Server
2000 +
> SP3
> Error: 7105, Severity: 22, State: 6
> Page (1:433348), slot 7 for text, ntext, or image
node
> does not exist.
> I found this article about thte error:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304847&Product=sql2k
> It says that this issue is solved with SP2. I have
SP3,
> what can i do ' I'm receiving this error right now
and
> after reading the article i'm afraid that the server
> shutdown. Is a critical server to my enterprise. Can
> anyone give me any tips for see what's going on and
how to
> solve it ?
> Thanks in advance
> Miguel
>.
>

Help requred

Hi, folks. The software seems very slow to open the forms. When i run trace on the application, i get the TEXT in the profiler like this:
[quote]
declare @.P1 int
set @.P1=1
exec sp_prepare @.P1 output, NULL, N'SELECT * FROM orders_tab', 1
select @.P1
go
/[quote]
SELECT * FROM orders_tab, is it really hapenning?
orders_tab contains 2 lac records.
I couldn't find sp_prepare in BOL.
Any ideas?

Howdy!Howdy

Sp_prepare are SQL system commands. You wont find them documented.