Problems instaling IIS & Notification Service

I am trying to install SQL server 2005 Developer Edition on a stand alone PC running XP (service pack 2). I have the below error messages preventing me installing Notification Services and Intergration Services ( looks like the same problem for both ). I followed the advice to check the 'Distributed TransactionCoordinator' in services, all setting were correct. I stopped amd restarted the service and selected "retry' to no avail. Any ideas anyone?

Messages:

TITLE: Microsoft SQL Server 2005 Setup

Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2147024894

Error message: The system cannot find the file specified.

Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80070002

BUTTONS:

&Abort

&Retry

&Ignore

TITLE: Microsoft SQL Server 2005 Setup

Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894

Error message: The system cannot find the file specified.

Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80070002

BUTTONS:

&Abort

&Retry

&Ignore

Thanks in advance

ADG

can you try to reinstall .NET Framework 2.0 and then try the setup again?

You can find x86 vesrion of .NET 2.0 redistributable at: http://www.microsoft.com/downloads/details.aspx?FamilyID=0856EACB-4362-4B0D-8EDD-AAB15C5E04F5&displaylang=en

The page also contains links to x64 and ia64 versions at the bottom.

|||

Many thanks Jin

I downloaded .net 2.0 as suggested, and selected repair. Service loaded fine afterwards

Problems inserting into table

I am trying to write an insert statement. I insert into 1 table test with th
e
statements below and what I want is I want to use this table to insert value
s
in
table test1 but I want be able to insert everything from the keyword product
so the new table description is always beginning from Product......
Since the length of month varies before the keyword I cannot use a static
substring or trim functions.
My insert will be something like this in test1.
id desc
-- --
1 Product dispatch to website...(individual number)
2 Product dispatch to website...(individual number)
Can anyone help me .
create table test(
Sno Int,
description varchar(2000),
cyats datetime default getdate() )
create table test1(
id int,
description varchar(2000),
cyats datetime default getdate())
insert into test(sno,description)
values(1,'Integrated Release August 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is :...')
insert into test(sno,description)
values(1,'Integrated Release December 2004. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')
insert into test(sno,description)
values(1,'Integrated Release June 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')First suggestion clean this data before getting into SQL Server using
whatever tool you have (like DTS or whatever.) as it will be far more
natural. However, a possible way is to reformat as a select in whatever
code you are using to build this data:
SELECT 1,substring('Integrated Release August 2005. Product despatched to
website.
Website address for tracking consignment is http://www.track.com,your
number
is :...', charindex('product', 'Integrated Release August 2005. Product
despatched to website.
Website address for tracking consignment is http://www.track.com,your
number
is :...'), 4000)
You could change the string to a variable, but I wouldn't unless you are
hand typing this stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8B17F575-0318-4317-A318-2E7F543D1A31@.microsoft.com...
>I am trying to write an insert statement. I insert into 1 table test with
>the
> statements below and what I want is I want to use this table to insert
> values
> in
> table test1 but I want be able to insert everything from the keyword
> product
> so the new table description is always beginning from Product......
> Since the length of month varies before the keyword I cannot use a static
> substring or trim functions.
> My insert will be something like this in test1.
> id desc
> -- --
> 1 Product dispatch to website...(individual number)
> 2 Product dispatch to website...(individual number)
> Can anyone help me .
> create table test(
> Sno Int,
> description varchar(2000),
> cyats datetime default getdate() )
> create table test1(
> id int,
> description varchar(2000),
> cyats datetime default getdate())
> insert into test(sno,description)
> values(1,'Integrated Release August 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is :...')
> insert into test(sno,description)
> values(1,'Integrated Release December 2004. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')
> insert into test(sno,description)
> values(1,'Integrated Release June 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')

Problems Inserting DateTimeStamp into database

Here's the error I get trying to run the code posted below..."Prepared statement '(@.datestamp datetime,@.PrevValue real,@.NewValue real,@.IPaddress r' expects parameter @.datestamp, which was not supplied."

I've tried it without the quotes around the DateTime.Now and also adding the # around them. Without the quotes, I do get a different error because it doesn't like the spaces in the DateTime text. Also, the datestamp data in the database is formatted as a datetime (MS SQL Server 2005).

Here's my code:

PrivateSub UpdateRefresh()

UpdateConnection.Open()

UpdateDataAdapter.InsertCommand.CommandText = "INSERT INTO dbo.tbDeploy(datestamp," & _

"PrevValue, NewValue, IPaddress, HighCapability, LowCapability, Share) VALUES" & _

"('" & DateTime.Now & "', '" & lblCurrent.Text & "', '" & lblCurrent.Text & _

"', '" & lblIP.Text & "', '" & lblHigh.Text & "', '" & lblLow.Text & "', '" & lblShare.Text & "')"

UpdateDataAdapter.InsertCommand.ExecuteNonQuery()

UpdateConnection.Close()

EndSub

Use parameterized queries. Your problem will be solved. You can also prevent SQL Injection attacks.

Problems Inserting Data- Am I doing something wrong?

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)

Insert

into customer(CustomerID, LastName, FirstName, BillingAddress, City,State, Country, Zipcode, PhoneNumber, EmailAddress)

select

CustomerID,(left(ContactName,charindex(' ', ContactName)-1))as LastName,

(right(

ContactName,charindex(' ',reverse(ContactName))-1))as FirstName,

Address

, City,Region,PostalCode,Country,Phonefrom Northwind.dbo.Customers

I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my application

Any help would be of great

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

|||

Hi Stew,

I corrected it in the actual query (when I executed) and it gives me this error message....any pointers??

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

Thank you

|||Look at the size constraints of the columns you are inserting too and see if they make sense against what is being inserted. You may have a string field going into a char field, or something of the sort.|||

Stew312:

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

Looks like the problem is this:

This is what my query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber

There is only one FROM COLUMN (ie., Contactname) but is repeated twice...I mean to say in

the Northwind customer database both the last name and first name are put in a single column

and I am trying to separate it into two colums (Lastname , Firstname).....Is it a wrong way of doing things.....I had been breaking my on this for couple of hours

your help is greatly appreciated

|||Too embrassing and a costly mistake (had to waste most of my time).........the order of columns I was trying to insert was wrong....though embrassing, I am sharing because some people (including me) tend to forget minor details while focusing on other major problems.....so moral of story....Pay attention to detail and don't forget basics.

Problems in Loading configuration file

Hi,

We have configured package to load configurations from XML file.

But whenever we try to run the package, it throws below warning message

and continues to download the configuration file and finally ending up in low memory exception

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

I have made sure the file exists at the above location.

Please let me know if anyone have any solution on this.

Regards,

sachin

What exactly is the file name you are trying to supply? What are the machine specs where the package is running? What exactly is configured in the xml file?
|||

SachinS wrote:

The package is attempting to configure from the XML file "E:\Cybage\Deployment\MSCERT\MSCERT_MainPackgeConfig.dtsConfig".

That's an informational message. Where's the error/warning message? That message will occur every single time (and it is valid) that the package runs.|||

We are storing connection strings in this config file.

'Loading configuration settings from file' message gets repeated n number of times and it does not stop until it gets some memory exception

|||Do you have the same configuration file listed multiple times in the package configuration window?sql

Problems converting varchar to smallmoney

Hi

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

t.

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)

Problems backing up SQL Express Database...

Hello,

I found some code below that works fine when I run it in my vb.net code in Design mode. When I publish the application, run the app and then execute the sub MyDbBackup() I get the following error: Cannot open backup device 'C:\dbBackup'. Operating system error 5(Access is denied). BACKUP DATABASE is terminating abnormally.

Here is the code that runs:

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

2 Questions: Why does it backup fine in design mode, but not after it is published? Also, in design mode, my database is called TAP_Master.mdf but when I look under C:\Program Files\Microsoft\ SQL Server\MSSQL.1\MSSQL\Data, I do not see that database name... all I see is master.mdf, model, msdbdata etc... Is that database stored anywhere else?

I have set the MyConnectionStringdb variable to = "Data Source =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TAP_Master.mdf;Integrated Security=True;User Instance=True"

Thanks in advance.

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.

I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.

Mike

|||

Mike Wachal - MSFT wrote:

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.

I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.

Mike

Thanks for your reply. You are correct... My published application is on a different computer than my Dev box. If you can find out any additional info as to why I'm getting that error that would be great.

Also, I'll ask this question as well, Once the bak up file is created, what would be the best method to restore the .bak file to the machine that runs the published application.? I thought I could use the following code, but I receive the message, "Restore cannot process the database C"\Visual Studio\Projects\Prog\BIN\DEBUG\TAP_Master.MDF beause it is in use by this session. It is recommended that the master database be used when performing this operation":

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("RESTORE DATABASE [" & sqlconn.Database.ToString & "] FROM DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

Thanks in advance.

|||

Hi,

I guess that the user instance feature is not made for Backup and restore (with the emphasize on restore) because a restore will do a server attaching after the restoring which is not the sense of a user attached instance. Why don′t you take the MDF as a backup only ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Hi,

... Why don′t you take the MDF as a backup only ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hello, can you elaborate on that quote? How can I take only the MDF as a backup? Basically I want to be able to back up any saved data, and then be able to restore it in case of a system failure or a corrupted operating system.

Thanks for your replies all.

|||Just do a normal file copy when the database connection is closed, then you can easily attach it to a user instance again when the data is corrupt.

HTH, Jens K. Suessmeyer.

htttp://www.sqlserver2005.de

|||

Ok. This is what I have done. I opened my published app on my laptop, and then entered in some data. I closed my app, and then attempted to copy the master.mdf file from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory. At first I couldn't copy because SQL server express was still running. So I killed the services and copied the data.

I reinstalled my app, copied back the master.mdf into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory, and then launched the app. Now I do not see the data that I previously entered.

Am I understanding the File Copy / Attach to user instance concept correctly?

Thanks again for all your help.

|||No actually not, the database which has to be backup is your own database not the SQL Server internal ones. YOu will have to backup the MDF file of the database with the reference TAP_master.mdf you mentioned above.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The reason you're seeing the backup error is that the user context running your app on the deployed system doesn't have Windows permissions to create a file in the root of the local C: drive. That's why it's different from your dev environment: Different machine, different permissions.

So, either point the backup to a known directory that you're guaranteed to have permission to write to on machines where you're deployed, or find a way to make sure that you have appropriate permissions to the root of C:.

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

|||

Kevin Farlee wrote:

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?

Thanks.

|||

Rashar wrote:

Kevin Farlee wrote:

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?

Thanks.

The context information is session based, you can change the context from within your script (if you are using one, I am not sure from your explnation using the USE <DatabaseName> command. If you are in any GUI of SQL Management Studio, make sure that you are not residing on a database node in the treeview that you want to restore.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem writing XML files.

Hi guys,

I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.

The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.

Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.

STORED PROC:
****************************

CREATE PROCEDURE usrp_sp_makexmlfile
@.str varchar(50),
@.templatefile varchar(255),
@.ReturnValue as image OUT

AS
SET NOCOUNT ON
DECLARE @.strVar as varchar(8000)
/*DECLARE @.ReturnValue as varchar(255)*/

Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
Set @.templatefile = 'c:\template.tpl'

EXEC (@.strVar)

SELECT @.ReturnValue
GO

THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
the property Size has an invalid size: 0

Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.

Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.

Pi.Hi

You may want to check out http://sqlxml.org/faqs.aspx?faq=29 but there may
be issues with line breaks for sp_makewebtask or look at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp

John

"Pi" <3.something@.gmail.com> wrote in message
news:1126041751.328093.220490@.f14g2000cwb.googlegr oups.com...
> Hi guys,
> I have a stored procedure that I will subsequently post below this
> message. What the stored procedure does is, it reads some specific
> tables in a database and created XML off it.
> The problem comes in when the data is bigger than a few hundred/
> thousand characters. The data is truncated and the XML file is not
> fully made.
> Now i have been reading some posts by some people and tried using TEXT/
> NTEXT/ IMAGE type to write the files but they give me errors which,
> again, I am adding to this email.
> STORED PROC:
> ****************************
> CREATE PROCEDURE usrp_sp_makexmlfile
> @.str varchar(50),
> @.templatefile varchar(255),
> @.ReturnValue as image OUT
> AS
> SET NOCOUNT ON
> DECLARE @.strVar as varchar(8000)
> /*DECLARE @.ReturnValue as varchar(255)*/
> Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
> Set @.templatefile = 'c:\template.tpl'
> EXEC (@.strVar)
> SELECT @.ReturnValue
> GO
>
>
> THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
> ************************************************** *******
> An unhandled exception of type 'System.InvalidOperationException'
> occurred in system.data.dll
> Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
> the property Size has an invalid size: 0
>
> Also, if i try to add an integer value to the image/text/ntext like
> 8000 or something less than that, it tells me that the result has to be
> discarded because the current process has had some error.
>
> Can someone give me a suggestion on how to resolve this or an example
> that would illustrate the solution? Thank you all in advance.
> Pi.|||According to the Sybase ASE manual at :

<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X
<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip|||Hi

With Microsoft SQL Server text is a valid datatype for parameter.

John
"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1126057842.315787.150600@.g14g2000cwa.googlegr oups.com...
> According to the Sybase ASE manual at :
> <http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X>
> <snip>
> Restrictions on text and image columns
> text and image columns cannot be used:
> *As parameters to stored procedures or as values passed to
> these parameters
> *As local variables
> </snip

Problem with varchar(max) in SQL Server 2005

Hi All,
I am having a small problem with varchar(max) type variable. Please look at
the code below.
DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 10000
lengthy string
SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 leng
thy
string
When I run the a code like;
SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
SELECT LEN(@.varcharMAX) AS Length
The result is 18000
But when I take the varcharmax front;
SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
SELECT LEN(@.varcharMAX) AS Length
The result is 26000
What is the problem with my code? I made a small experiment
(http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3lW0
UC8uA!217.entry) on this, if want have a look please. I am using April CTP.
Highly appreciate your reply.
Dinesh Priyankarahttp://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dinesh Priyankara" wrote:

> Hi All,
> I am having a small problem with varchar(max) type variable. Please look a
t
> the code below.
> DECLARE @.varcharmax varchar(max), @.varchar varchar(8000)
> SET @.varcharmax = REPLICATE(‘D’, 10000) – this will end up with 1000
0
> lengthy string
> SET @.varchar = REPLICATE(‘D’, 10000) – this will end up with 8000 le
ngthy
> string
> When I run the a code like;
> SET @.varcharmax = @.varchar + @.varchar + @.varcharmax
> SELECT LEN(@.varcharMAX) AS Length
> The result is 18000
> But when I take the varcharmax front;
> SET @.varcharmax = @.varcharmax + @.varchar + @.varchar
> SELECT LEN(@.varcharMAX) AS Length
> The result is 26000
> What is the problem with my code? I made a small experiment
> (http://spaces.msn.com/members/dineshpriyankara/Blog/cns!1p-RgJ7SV2D2-6Y3l
W0UC8uA!217.entry) on this, if want have a look please. I am using April CTP
.
> Highly appreciate your reply.
> Dinesh Priyankara
>
>
>|||Posted same at
http://communities.microsoft.com/ne...p=sqlserver2005
thanks
"Mike Epprecht (SQL MVP)" wrote:
> http://communities.microsoft.com/ne...p=sqlserver2005
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Dinesh Priyankara" wrote:
>

Problem with using BETWEEN for date range...

Hi all -

This problem has been driving me crazy, and I'm hoping the answer is
something stupid I am neglecting to see...

The procedure posted below is part of an Access/SQL database I have
developed. Basically, the user would input a beginning and ending date,
and the query goes and pulls records that meet the following criteria:

1. TradeDate is between beginning date and ending date
2. TradeTime is between beginning date and ending date
3. Trade's Match ID is equal to match IDs returned by the second select
statement - this is part of a ticket processing system, and tickets are
grouped using their match id. So, if one ticket has been updated and
now meets criteria #1 or #2 above, this is supposed to also return any
of the other tickets with the same match ID - so if one ticket in a
group changes, our acct. dept can look at the whole group on their
reports.

Anyway, the query below seems to work, but I am not happy with it. The
problem was I was using the BETWEEN function, and not converting all
the dates to varchar. This worked fine, unless the beginning date and
ending date were the same. For example, if I had a ticket with a
tradedate of 5/3/06 and I ran a beginning date of 5/3/06 and an ending
date of 5/3/06, the ticket should be returned. However, with the
BETWEEN statement, it would return no rows.

I changed the BETWEEN statements to statements like:

tradedate >= beginning date and tradedate <= ending date

but this also returned no rows.

It was only upon converting all the dates to varchar and using the <=
and >= operators that I started getting the results I need.

Can someone tell me why the heck BETWEEN wouldn't work? Tradedate and
Tradetime are both datetime values, and I was bringing in the beginning
and ending date variables in datetime form...is there a problem using
BETWEEN when the first and second variables used are the same?

Any light you can shed on this would be great, because having all these
convert statements and such makes me nervous...I'd rather get between
to work, but I have not been able to in my testing...

Thanks! -Jim

CREATE PROCEDURE dbo.spAcctExport(@.begindate datetime,
@.enddate datetime)
AS SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode AS
TradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,
dbo.tblOrders.SettleDate, NULL AS
ProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,
dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,
dbo.TBLCUSIP.Factor, dbo.tblOrders.BuySell,
dbo.tblOrders.CUSIP, dbo.TBLCUSIP.Issuer, dbo.TBLCUSIP.PoolNum,
dbo.TBLCUSIP.Coupon,
dbo.tblOrders.FixAdj, dbo.tblOrders.Price,
dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' AS
ProdType, dbo.tblOrders.DeskSC,
dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' AS
DeskMarkup, dbo.tblOrders.MatchID,

'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS
TotalPrincipal, dbo.tblOrders.CancelCorrect,
dbo.tblOrders.OriginalTrade,
dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,
dbo.tblOrders.Rep2SC
FROM dbo.tblOrders INNER JOIN
dbo.TBLCUSIP ON dbo.tblOrders.CUSIP =
dbo.TBLCUSIP.CUSIP INNER JOIN
dbo.tblAccounts ON dbo.tblOrders.AcctNum =
dbo.tblAccounts.AcctNum INNER JOIN
dbo.tblTradeAccount ON dbo.tblOrders.TradeAccount
= dbo.tblTradeAccount.TradeAccount
WHERE ((

(dbo.tblOrders.MatchID IS NOT NULL) AND (dbo.tblOrders.MatchID IN
(SELECT MatchID FROM dbo.tblOrders WHERE (

CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
CONVERT(VARCHAR(10), @.begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
CONVERT(VARCHAR(10), @.enddate,101))

OR (

CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=
CONVERT(VARCHAR(10), @.begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
CONVERT(VARCHAR(10), @.enddate,101)))))

OR ((

CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
CONVERT(VARCHAR(10), @.begindate,101) and
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
CONVERT(VARCHAR(10), @.enddate,101))

OR (

CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=
CONVERT(VARCHAR(10), @.begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
CONVERT(VARCHAR(10), @.enddate,101))))

ORDER BY dbo.tblOrders.CancelCorrect,
dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,
dbo.tblOrders.BuySell, dbo.tblOrders.TicketNum
GOIn SQL Server, there is not DATE data type or TIME data type -- but
there is a single DATETIME. Quit doing all that converting and work
with temporal expressions instead of COBOL-style strings. Think in
abstract terms, not how it is displayed on a piece of paper or a
screen.

WHERE trade_time BETWEEN start_time AND end_time

will work fine, if you bother to add constraints to start_time (i.e
00:00:00) and end_time (23:59:59.9999) columns. Another trick is to
allow end_time to be NULL when an event is still in progress and then
use COALESCE (end_time, CURRENT_TIMESTAMP)

If you look up Rick Snodgrass at University of Arizona, you can
download his out-of-print book on temporal SQL queries.|||OK - my post mentioned I originally used

WHERE tradedate BETWEEN begindate AND enddate

Begindate and enddate are not columns - they are parameters the user
supplies in the format mm/dd/yyyy.

My whole post was explaining that I would *prefer* to use the BETWEEN
AND statement - the converting was not my choice, but it is the only
way this query works when the user specifices the same date as
begindate and enddate.

So once again - can someone explain why this query returns no records
when begindate and enddate are specified as 5/3/2006 and there is
clearly trade data with that tradetime?|||My assumption is that the tradedate is a datetime datatype that
includes a time, while the begin and end dates are just dates. The
comparison is will be done on datetimes, which include both date and
time.

>WHERE tradedate BETWEEN begindate AND enddate

Consider what this can look like when we substitute actual values.

WHERE
'2006-05-03 17:12:51.420' BETWEEN
'2006-05-03 00:00:00.000' AND
'2006-05-03 00:00:00.000'

The time that is part of tradedate is putting it outside of the
"range" of the BETWEEN; in this case of course the BETWEEN has no
range, being two identical points in time. To match it needs a 24
hour range, but that is not what it has.

To use BETWEEN you would need toto reduce tradedate to a simple date
with no time for the test.

Roy Harvey
Beacon Falls, CT

On 3 May 2006 13:40:16 -0700, "Jim Armstrong"
<armstrongjc@.hotmail.com> wrote:

>OK - my post mentioned I originally used
>WHERE tradedate BETWEEN begindate AND enddate
>Begindate and enddate are not columns - they are parameters the user
>supplies in the format mm/dd/yyyy.
>My whole post was explaining that I would *prefer* to use the BETWEEN
>AND statement - the converting was not my choice, but it is the only
>way this query works when the user specifices the same date as
>begindate and enddate.
>So once again - can someone explain why this query returns no records
>when begindate and enddate are specified as 5/3/2006 and there is
>clearly trade data with that tradetime?|||Jim Armstrong (armstrongjc@.hotmail.com) writes:
> CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
> CONVERT(VARCHAR(10), @.begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
> CONVERT(VARCHAR(10), @.enddate,101))

But this does not make any sense. When I run

select convert(varchar(10), getdate(), 101)

I get

05/03/2006

So if the user enters 2006-05-03, you will also give him hits from
2005?

If you feel compelled to use string format, use format 112 which is
YYYYMMDD, which is possible to compare, and which also is unambiguosly
convertible back to date.

But it would make more sense to simple say:

O.TradeDate BETWEEN @.begindate AND @.enddate

Provided that is, that TradeDate always have a timeportion of midnight.
And the same provision applies to @.begindate and @.endate, but I got
the impression that the user enters date only.

If TradeDate also includes hours and seconds, you need to write:

O.TradeDate >= @.begindate AND
O.TradeDate < dateadd(DAY, 1, @.enddate)

There is one more important thing to observer here: you should avoid
putting TradeDate in an expression. This is because if there is an
index on TradeDate, that index cannot be used if you put TradeDate in
an expression, and performance will suffer.

> CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=
> CONVERT(VARCHAR(10), @.begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
> CONVERT(VARCHAR(10), @.enddate,101)))))

The same applies here, although I'm not really clear what is in TradeTime.
Is TradeDate 2006-05-03 00:00:00 and TradeTime is 2006-05-03 12:23:23?

In such case, I'm not sure why you need to check both.

> CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
> CONVERT(VARCHAR(10), @.begindate,101) and
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
> CONVERT(VARCHAR(10), @.enddate,101))
> OR (
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=
> CONVERT(VARCHAR(10), @.begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
> CONVERT(VARCHAR(10), @.enddate,101))))

And then you have it once more?

--
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|||>> So once again - can someone explain why this query returns no records [sic] when begindate and enddate are specified as 5/3/2006 and there is clearly trade data with that tradetime? <<

Once again, because there is a DATETIME column which has a time on it.
If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
a single point in time. What you wanted to use was

WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
23:59:59.99'

You might also want to learn ISO standards and use 'yyyy--mm-dd' for
date values.

And do not forget to add constraints to temporal columns in your base
tables.|||Yes but hang on a minute, what if you don't want a time, holding the time
component is misleading that indicates the data value may contain a time
between 00:00:00 and 23:59:59, being 00:00:00 means the trade was done at
midnight which may well not be true, consider settment date where the trade
needs to settle on a particular day, not by midnight but sometime on that
day.

In SQL Server because we don't have a DATE data type we need to store dates
as an integer in the form yyyymmdd which is unfortunate but the reality.

> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.

Rich coming from the guy who doesn't use the standard format himself.

2006-05-03 00:00:00.000 should be written 2006-05-03T00:00:00.000

OR

20060503 if you are only specifying the date.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146749926.099160.51660@.i40g2000cwc.googlegro ups.com...
>>> So once again - can someone explain why this query returns no records
>>> [sic] when begindate and enddate are specified as 5/3/2006 and there is
>>> clearly trade data with that tradetime? <<
> Once again, because there is a DATETIME column which has a time on it.
> If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
> a single point in time. What you wanted to use was
> WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
> 23:59:59.99'
> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.
> And do not forget to add constraints to temporal columns in your base
> tables.|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Yes but hang on a minute, what if you don't want a time, holding the
> time component is misleading that indicates the data value may contain a
> time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
> done at midnight which may well not be true, consider settment date
> where the trade needs to settle on a particular day, not by midnight but
> sometime on that day.
> In SQL Server because we don't have a DATE data type we need to store
> dates as an integer in the form yyyymmdd which is unfortunate but the
> reality.

Only if you like to throw out babies and bathtubs simultaneously.

All our settledate columns are datetime, or more precisely aba_date,
which is a datetime with a rule bound to that screams blue murder if
you try to use anything else than 00:00:00.000 for the time porttion.

And, no, our customers do not settle at midnight.

--
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|||> All our settledate columns are datetime, or more precisely aba_date,
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
> And, no, our customers do not settle at midnight.

Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?

To quote celko, using datetime to hold a pure date is really a kludge ;),
seriously though, even though you have a check constraint to prevent times
over than midnight, it shouldn't have a time at all and any query result
should not contain a time component, it ensinuates that settment date is not
a date but actually a date with a time, so to the application developer or
third party vendor accessing your webservice etc... it might well appear
that trades should be settled by midnight.

All the trading systems I've worked with ALL use the integer data type to
hold pure dates.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B9F36F1FE35Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
>> Yes but hang on a minute, what if you don't want a time, holding the
>> time component is misleading that indicates the data value may contain a
>> time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
>> done at midnight which may well not be true, consider settment date
>> where the trade needs to settle on a particular day, not by midnight but
>> sometime on that day.
>>
>> In SQL Server because we don't have a DATE data type we need to store
>> dates as an integer in the form yyyymmdd which is unfortunate but the
>> reality.
> Only if you like to throw out babies and bathtubs simultaneously.
> All our settledate columns are datetime, or more precisely aba_date,
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
> And, no, our customers do not settle at midnight.
>
> --
> 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|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Presumeably you also have a computed column that strips away the time
> component so only the date gets passed back to the client?

Computed column? Hey, this system started its life in SQL Server 4.x. And
you cannot accuse for being too quick to leave SQL 6.5. I think we have
two computed columns in the system.

Yes, the time portion is a bit of noice in Query Analyzer. For the client,
it is a non-issue.

> All the trading systems I've worked with ALL use the integer data type to
> hold pure dates.

One of our competitors seems to go by char(8). (They're not using SQL
Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
over several of their customers and converted their data to our system.
I've seen some completely hilarous dates there.

Using integer or char(8) for dates is in my opinion an even worse solution,
as you run the risk of getting dates like 20060230 and that in the system.
--
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|||> Using integer or char(8) for dates is in my opinion an even worse
> solution,
> as you run the risk of getting dates like 20060230 and that in the system.

Well not really, its the same argument i'd have with a time component of the
datetime, both are fixed with a check constraint.

I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount to
a lot more IO for larger trading systems....

check( ISDATE( 20060230 ) = 1 ) which is simpler then the check constraint
needed for slicing off / checking for the time component!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BA66AF76B25Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
>> Presumeably you also have a computed column that strips away the time
>> component so only the date gets passed back to the client?
> Computed column? Hey, this system started its life in SQL Server 4.x. And
> you cannot accuse for being too quick to leave SQL 6.5. I think we have
> two computed columns in the system.
> Yes, the time portion is a bit of noice in Query Analyzer. For the client,
> it is a non-issue.
>> All the trading systems I've worked with ALL use the integer data type to
>> hold pure dates.
> One of our competitors seems to go by char(8). (They're not using SQL
> Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
> over several of their customers and converted their data to our system.
> I've seen some completely hilarous dates there.
> Using integer or char(8) for dates is in my opinion an even worse
> solution,
> as you run the risk of getting dates like 20060230 and that in the system.
> --
> 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|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
> to a lot more IO for larger trading systems....

So that is an argument that carries some weight.

But integer data for dates is so much messier to deal with, not the
least for the client.

> check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
> constraint needed for slicing off / checking for the time component!

Nah, I would write it as

CREATE RULE aba_date_rule (isdate(ltrim(str(@.x))) = 1

I hate to rely on implicit conversions from integer to string.

(And it is a rule, as I only want it one place.)

--
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|||Erland,

There is another approach, you can number days sequentially, such as
datediff(day, '20000101', your_original_date). In many cases it allows
to use a 2-byte integer, and that may be quite a difference. I use
dateadd to get dates from those integers, but but dates are needed for
display purposes only, not frequently. I also use a calendar table with
the the same integer as PK.|||Make sure @.x is nvarchar then, because thats what ISDATE takes.

> But integer data for dates is so much messier to deal with, not the
> least for the client.

I totally agree, but its more messy have the time component on queries in my
experience.

Having said that i always convert the integer to a smalldatetime when
creating a fact table purely because its easier for the time dimension on my
dimensional model....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BAED6B51AAAYazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
>> I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
>> to a lot more IO for larger trading systems....
> So that is an argument that carries some weight.
> But integer data for dates is so much messier to deal with, not the
> least for the client.
>> check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
>> constraint needed for slicing off / checking for the time component!
> Nah, I would write it as
> CREATE RULE aba_date_rule (isdate(ltrim(str(@.x))) = 1
> I hate to rely on implicit conversions from integer to string.
> (And it is a rule, as I only want it one place.)
> --
> 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

Problem with using bcp

Hello all,
I try to execute the code below. Whatever I try I get the error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dc_temp_text'.
This also happens when I put the owner and the database name in from of the
table name.
Can anybody tell me what it is that I'm missing?
TIA
Ronald
CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
begin
dbcc checkident (dc_temp_text, reseed, 0)
declare @.BcpCommand varchar(5000)
declare @.TableName varchar(100)
set @.Tablename = QuoteName('dc_temp_text')
/*
Schrijf het text bestand
*/
delete from dbo.dc_temp_text
insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
set @.BcpCommand = 'bcp "select * from '
set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
set @.bcpCommand = @.BcpCommand + '" queryout
c:\testuser1\text.txt -Uuser -Ppassword -c'
exec master..xp_cmdshell @.BcpCommand
endI assume that you have created the stored procedure in the same database
that you have the table dc_temp_text.
If you comment out the BCP portion I am guessing that the proc works
correctly.
I do not see where you specify the database or the server within the BCP
string.
Instead of
exec master..xp_cmdshell @.BcpCommand
Try
SELECT @.BcpCommand AS HereIsTheBCPstatement
If the command is correct you should be able to run it within DOS from your
PC. Use the output of the query to test your BCP statement.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>|||Keith,

> If you comment out the BCP portion I am guessing that the proc works
> correctly.
Yes, ther is no problem then. The table is filled and everything checks out.

> I do not see where you specify the database or the server within the BCP
> string.
I added that a few minutes later with no effect. I also tried to run the
statement in de dos prompt with the same result.
Any other ideas?
Greetings
Ronald
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>I assume that you have created the stored procedure in the same database
>that you have the table dc_temp_text.
> If you comment out the BCP portion I am guessing that the proc works
> correctly.
> I do not see where you specify the database or the server within the BCP
> string.
> Instead of
> exec master..xp_cmdshell @.BcpCommand
> Try
> SELECT @.BcpCommand AS HereIsTheBCPstatement
> If the command is correct you should be able to run it within DOS from
> your PC. Use the output of the query to test your BCP statement.
> --
> Keith Kratochvil
>
> "Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
> news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||>I added that a few minutes later with no effect. I also tried to run the
>statement in de dos prompt with the same result.
Ok, now we have isolated the problem to the BCP statement.
Type BCP into Query Analyzer
Highlight the string BCP
Hit Shift_F1
Read up on BCP overview within Books Online. You will see that you are be
missing a few params.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:%23U3ffCEdGHA.380@.TK2MSFTNGP04.phx.gbl...
> Keith,
>
> Yes, ther is no problem then. The table is filled and everything checks
> out.
>
> I added that a few minutes later with no effect. I also tried to run the
> statement in de dos prompt with the same result.
> Any other ideas?
> Greetings
> Ronald
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
> news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>|||On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:

>Hello all,
>I try to execute the code below. Whatever I try I get the error:
>Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>'dc_temp_text'.
>This also happens when I put the owner and the database name in from of the
>table name.
>Can anybody tell me what it is that I'm missing?
Hi Ronald,
Where do you put the owner and database name in front of the table name?
The execution of bcp will open a new connection to the server that will
connect to the user's default database. It is really important that you
always database-qualify the tablename on the bcp command.
Also, try what happens if yoou replace
exec master..xp_cmdshell @.BcpCommand
with
PRINT @.BcpCommand
Maybe this helps you see the problem in the generated bcp command. And
if not, you can post the output of the PRINT command here and see if
someone else sees a problem with it.
Hugo Kornelis, SQL Server MVP|||Ronald Hermans (rhermans@.datamedicare.nl) writes:
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
Is the server you are running on a default instance? Or is it a named
instance?
Since you do not specify -S the BCP command will connect to the default
instance. You get can the server name from @.@.servername.
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|||Erland,
Yes It is a Default instance, but just to make sure I entered the the
servername with the -S parameter. Unfortunatly it had no effect.
Ronald
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns97C06E3A3171Yazorman@.127.0.0.1...
> Ronald Hermans (rhermans@.datamedicare.nl) writes:
> Is the server you are running on a default instance? Or is it a named
> instance?
> Since you do not specify -S the BCP command will connect to the default
> instance. You get can the server name from @.@.servername.
>
>
> --
> 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|||Hugo,
bcp "select * from dc_temp_text" queryout
c:\\text.txt -Sservername -Uuser -Ppassword -c
I tried to put the db name and the owner name in front of the table name
with no effect. I still get the same error.
Ronald
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:ulo462drdh8s5rv72pjm9bd4l9v12afjd8@.
4ax.com...
> On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:
>
> Hi Ronald,
> Where do you put the owner and database name in front of the table name?
> The execution of bcp will open a new connection to the server that will
> connect to the user's default database. It is really important that you
> always database-qualify the tablename on the bcp command.
> Also, try what happens if yoou replace
> exec master..xp_cmdshell @.BcpCommand
> with
> PRINT @.BcpCommand
> Maybe this helps you see the problem in the generated bcp command. And
> if not, you can post the output of the PRINT command here and see if
> someone else sees a problem with it.
> --
> Hugo Kornelis, SQL Server MVP|||bcp "select * from [your_db_name_goes_here].[dbo].dc_temp_text" queryout
"c:\text.txt" -Uuser -Ppassword -c
-oj
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OaINpgMdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> bcp "select * from dc_temp_text" queryout
> c:\\text.txt -Sservername -Uuser -Ppassword -c
> I tried to put the db name and the owner name in front of the table name
> with no effect. I still get the same error.
> Ronald
>|||Hello all,
I think I may have found the problem (and if so, I'm going to cry). The
databasename starts with a 1 (one digit) and I think the bcp tool has a
problem with that.
Can anybody confirm this?
TIA
Ronald
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>