Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Friday, March 23, 2012

Export Access Memo field to SQL Text field

Hi,

Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.

Thanks.

You can use the TEXTCOPY utility in SQL Server 2000. You can find it in the BINN directory of the server instance. This is a DBLIB utility to import a single text/ntext/image value into a row. You can also use BCP to import the data.

Monday, March 12, 2012

Expirting a copy of DB

Hi,

I am trying to send a copy of my database to a 3rd party.

I initally thought that a simple CSV woult be the perfect solution. However, their database columns are labeled differently to mine, and also in a different order.

Is it possible to create a custom header to a csv file (so I can rename the columns), and also read from the columns in a certain order?

I waould also like this CSV to be generated automatically, at the end of every day. Is this possible?

Thanks,

Glyn.

The best solution is to backup the database and zip the .bak file and email it or put it in a CD and send it to the person with the instructions to place the .bak file in the backup sub folder in Microsoft SQL Server under programs and use the backup and restore wizard to recreate it with the restore from a device option. If you need to send data to the person daily you can use a DTS package or manual insert into. Hope this helps.|||

Thanks for the quick response, I'm not sure that this would work. Pleease correct me if I'm wrong.

The third party requires the file to have a .blm extension (I think it's a custom extension; .txt renamed)
The .blm file needs to contain a header (to show the DB columns) followed by the data, and should look something like this:

#Header#
DB_Field1^DB_Field2^DB_Field3^DB_Field4^DB_Field5^~
#Data
Row1Field1Data^Row1Field2Data^Row1Field3Data^Row1Field4Data^Row1Field5Data^~
Row2Field1Data^Row2Field2Data^Row2Field3Data^Row2Field4Data^Row2Field5Data^~
Row3Field1Data^Row3Field2Data^Row3Field3Data^Row3Field4Data^Row2Field5Data^~
...
...
...
#END#

I do not know much about backing up a databases... Which is the best way to go? CSV or BAK?

Thanks

Glyn

|||If the third party is running the same version of SQL Server all you need is the .bak file I know I have restored a .bak file from CD and emailed. And I cannot help you with CSV files because it is not RDBMS (relational database management systems) file. Hope this helps.|||

I have no idea what sort of database the third party are using. Their support people say they need a 'Data Stream' (as they call it), in the format I showd in my previous post. This format is very close to that of a CSV, which is why I was going down the CSV route.

Can anyone help with custom CSV's, or post some links to good articals??

Thanks.

Friday, February 24, 2012

EXISTS

Well, finally I lost one day to find a solution for a simple problem. I

wanted to derive two tables... that seemed to be simple... but it isn't.

My last question for today:

Why does my sql query return the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

The Query:

SELECT

Configurations.AlarmId, Configurations.Mode, Configurations.Activated,

Configurations.Empty, Configurations.UserName, Configurations.datetime,

(SELECT ConfigurationId, Setpoint1, Alarm

FROM

dbo.ExtendedValues(Configurations.ConfigurationId) AS ExtendedValues)

AS Expr1

FROM Configurations INNER JOIN

Controllers ON Configurations.ConfigurationId =

Controllers.ActiveConfig INNER JOIN

Tanques ON Controllers.ControllerId = Tanques.ControllerId

WHERE (Tanques.TanqueId = 1)

Where do I have to enter EXISTS?

Thanks for every hint!!

P.D.: The ExtendedValues Function:

CREATE FUNCTION dbo.ExtendedValues

(

@.ConfigurationId int = -1

)

RETURNS TABLE

AS

RETURN SELECT *

FROM ConfigurationsTanques

WHERE ConfigurationsTanques.ConfigurationId = @.ConfigurationId

You have put a sub-query into your Select list. Such a sub-query can only return one value, not three the way you have. The Exists error message is misleading in this case.

I think I see how you got into this position. You are trying to call the ExtendedValues() function for each value of Configurations, but that is not legal either. Functions that return tables can only be placed in the From clause and must take constant parameters.

Unless the function that you included is just a stub and the real one is more complex, you do not need a function in this case. You only need to add the ConfigurationTanques table into your From clause.

Sunday, February 19, 2012

Execution plans differ

I have a stored procedure that is taking too long to execute. In trying to
diagnose the problem and come up with a solution, I executed the query the
stored procedure runs in query analyzer and it ran very fast. I compared th
e
two execution plans and they are vastly different.
to clarify I took the text of the stored procedure and copied it into query
analyzer. The stored procedure takes 2 paramaters. I declared the 2
parameters as variables and set them to the values that were being passed
into the stored procedure.
When executed in the stored procedure the query takes 3 minutes. When
executed in query analyzer it takes 7 seconds. Why would the execution plan
s
be so different?
The reason the stored procedure plan takes so long is it does an index scan
and then a bookmark lookup of more than 6 million rows. The ultimate result
set is only a few rows so this is definately not needed. When run in query
analyzer only the rows needed are retrieved.
Thanks,
DerrickParameter sniffing... your stored procedure is likely cached with a bad plan
due to atypical or non-optimal parameter values.
http://tinyurl.com/rkgbm
"Derrick Pope" <DerrickPope@.discussions.microsoft.com> wrote in message
news:8692616D-CF3E-411E-B17C-C95732A59BEE@.microsoft.com...
>I have a stored procedure that is taking too long to execute. In trying to
> diagnose the problem and come up with a solution, I executed the query the
> stored procedure runs in query analyzer and it ran very fast. I compared
> the
> two execution plans and they are vastly different.
> to clarify I took the text of the stored procedure and copied it into
> query
> analyzer. The stored procedure takes 2 paramaters. I declared the 2
> parameters as variables and set them to the values that were being passed
> into the stored procedure.
> When executed in the stored procedure the query takes 3 minutes. When
> executed in query analyzer it takes 7 seconds. Why would the execution
> plans
> be so different?
> The reason the stored procedure plan takes so long is it does an index
> scan
> and then a bookmark lookup of more than 6 million rows. The ultimate
> result
> set is only a few rows so this is definately not needed. When run in
> query
> analyzer only the rows needed are retrieved.
> --
> Thanks,
> Derrick