Changing the connection string on the fly....

Now that I have figured out how to connect to a foxpro database...

Could anyone tell me how to change the connection string on the fly?

I would like to make it possible, from the command line (using dtexec) to specify a portion of the connection string.... incorportate this portion into the connection string and then instruct the connection manager to use this connection...

For example I have a number of foxpro databases to import, they will be place in a directory structure like this:

TopLevel
TopLevel\NewYork\transactions.dbf
TopLevel\London\transactions.dbf
TopLevel\Sydney\transactions.dbf

I'd like the user to be able to specify which city's files to load each time the package is run..

dtexec /f MyPackage /set \Package.Variables[User::THECITY].Properties[Value];"London"

And when the package starts the THECITY variable would take the value "London"

But I'm not sure how to then affect the connection string property of the connection manager... there seems to be no way to specify that it take its connection string property from an expression...

So I'm lookin for some way which would cause the Connection String property of the coneection manager to become:
Driver={Microsoft Visual FoxPro Driver};sourcedb=d:\TopLevel\London;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine;

Jsut to recap, the provider for this connection manager
is a ".Net Providers\Odbc Data Provider" which I've been forced to use becasue the OLEDb provider for Foxpro has been rendered useless by service pack 1

Thanks in advance
PJ

PJFINTRAX wrote:

Now that I have figured out how to connect to a foxpro database...

Could anyone tell me how to change the connection string on the fly?

I would like to make it possible, from the command line (using dtexec) to specify a portion of the connection string.... incorportate this portion into the connection string and then instruct the connection manager to use this connection...

For example I have a number of foxpro databases to import, they will be place in a directory structure like this:

TopLevel
TopLevel\NewYork\transactions.dbf
TopLevel\London\transactions.dbf
TopLevel\Sydney\transactions.dbf

I'd like the user to be able to specify which city's files to load each time the package is run..

dtexec /f MyPackage /set \Package.Variables[User::THECITY].Properties[Value];"London"

And when the package starts the THECITY variable would take the value "London"

But I'm not sure how to then affect the connection string property of the connection manager... there seems to be no way to specify that it take its connection string property from an expression...

So I'm lookin for some way which would cause the Connection String property of the coneection manager to become:
Driver={Microsoft Visual FoxPro Driver};sourcedb=d:\TopLevel\London;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine;

Jsut to recap, the provider for this connection manager
is a ".Net Providers\Odbc Data Provider" which I've been forced to use becasue the OLEDb provider for Foxpro has been rendered useless by service pack 1

Thanks in advance
PJ

PJ,

It is eminently possible to put an expression onto the ConnectionString property of a connection manager. You need to access the Expressions UI via the proeprties window.

In this example you can also use the /CONN option of dtexec. Check that out as well.

-Jamie

|||Hi Jamie,
thanks for the reply...
Mustn't have had enough caffeine in my system...

I was right clicking on the connection manager and selecting 'Edit'.. and there's no way there to set up an expression to populate the connection string....
Of course I completely forgot about the properties window... with the ..sigh.. Expressions option ... right there... sigh...

Apologies...

PJ

Changing the 6th character of a string?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

TIA,
-CSscholzie (scholzie@.gmail.com) writes:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?

Have you looked at substring()?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE VIEW Parts (.., generic-part, ..)
AS
SELECT .. SUBSTRING part_id,1,5) + '_'[ + SUBSTRING part_id, 7, 1) ,
...
FROM Inventory;|||Thanks. I thought substring() was for finding the instance of a
character, but I guess I read wrong.

Appreciate the help!

Changing FormatString for attribute of a dimension

i can't change the value of a format string for attributes of dimensions.

For Cubes is easy, right-click on the measure -> Proprieties -> FormatString

but for the attributes of a dimension I cannot find where I can change this proprieties..

Someone can help me?

thank you

Riccardo

You cannot find it, because there isn't FormatString for attributes. FormatString is a property which applies to cell values, so specifying different formatting for different attributes doesn't make much sense, as they will simply conflict with each other when applied to the cell. You can specify different formatting for different attribute members, however. Check the CustomRollupOptions property, which allows you to do so.|||

i can't do what i want to do :)

my problem is: I have an attribute in a dimension which assumes values from 0.0001 to 0.0099

I don't know why but this attribute when I browse the dimension is visualized as ".0001" (without the zero)

I want this zero!! :)

ho can I to make it appear with CustomRollupOptions property!?!

|||

Is it not possible to do a named calculation in the data source view and use it in the dimension?

You use TSQL like:

Case When MyColumn < = 0.001 Then 0

Else MyColumn

HTH

Thomas Ivarsson

|||

I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

Anyway, this is formatting of attribute members, not formatting of cell values, therefore FormatString is not applicable here. You should instead, as Thomas suggests, put a named calculation in DSV which will do formatting for you, using appropriate SQL functions.

|||

>I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

yes!!

thank you for your answers.

but now i have a new question..

how can I make a named calculation for obtained what I want?

In DSV if I do "Explore Data" on the table of my dimension I can see my attribute correctly (with the leading zero), how create a named calculation to see this zero also when I browse the dimension?

thank you again!

Riccardo

|||

If you can accept a string in the dimension you could try

Named Calculation = MyColumnText

Case When MyColumn between 0.0001 and 0.0099 Then '0' + Cast(MyColumn as Char(4))

Else Cast(MyColumn as char(4))

End

I am not sure about what you would like to do with the other values that are outside of the TSQL-Case here.

This is from memory only so you might have to try:

'0.' + Cast(MyColumn as Char(4))

HTH

Thomas Ivarsson

|||

>If you can accept a string in the dimension you could try

your solution works :)

thank you very much!

Changing FormatString for attribute of a dimension

i can't change the value of a format string for attributes of dimensions.

For Cubes is easy, right-click on the measure -> Proprieties -> FormatString

but for the attributes of a dimension I cannot find where I can change this proprieties..

Someone can help me?

thank you

Riccardo

You cannot find it, because there isn't FormatString for attributes. FormatString is a property which applies to cell values, so specifying different formatting for different attributes doesn't make much sense, as they will simply conflict with each other when applied to the cell. You can specify different formatting for different attribute members, however. Check the CustomRollupOptions property, which allows you to do so.|||

i can't do what i want to do :)

my problem is: I have an attribute in a dimension which assumes values from 0.0001 to 0.0099

I don't know why but this attribute when I browse the dimension is visualized as ".0001" (without the zero)

I want this zero!! :)

ho can I to make it appear with CustomRollupOptions property!?!

|||

Is it not possible to do a named calculation in the data source view and use it in the dimension?

You use TSQL like:

Case When MyColumn < = 0.001 Then 0

Else MyColumn

HTH

Thomas Ivarsson

|||

I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

Anyway, this is formatting of attribute members, not formatting of cell values, therefore FormatString is not applicable here. You should instead, as Thomas suggests, put a named calculation in DSV which will do formatting for you, using appropriate SQL functions.

|||

>I think wildthink wants a leading zero, i.e. 0.0001 instead of .0001

yes!!

thank you for your answers.

but now i have a new question..

how can I make a named calculation for obtained what I want?

In DSV if I do "Explore Data" on the table of my dimension I can see my attribute correctly (with the leading zero), how create a named calculation to see this zero also when I browse the dimension?

thank you again!

Riccardo

|||

If you can accept a string in the dimension you could try

Named Calculation = MyColumnText

Case When MyColumn between 0.0001 and 0.0099 Then '0' + Cast(MyColumn as Char(4))

Else Cast(MyColumn as char(4))

End

I am not sure about what you would like to do with the other values that are outside of the TSQL-Case here.

This is from memory only so you might have to try:

'0.' + Cast(MyColumn as Char(4))

HTH

Thomas Ivarsson

|||

>If you can accept a string in the dimension you could try

your solution works :)

thank you very much!

Changing Fiirst Letter to capital

Hello there
Is there a simple way to change first letter on string to capital letter?[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Roy Goldhammer (roy@.hotmail.com) writes:
> Is there a simple way to change first letter on string to capital letter?
SELECT upper(substring(strcol, 1, 1)) + substring(strcol, 2, len(strcol))
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Changing Fiirst Letter to capital

Hello there
Is there a simple way to change first letter on string to capital letter?"Roy Goldhammer" <roy@.hotmail.com> wrote

> Is there a simple way to change first letter on string to capital letter?
write user-defined function.
CREATE FUNCTION dbo.FirstCapital (@.str VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.FirstLetter CHAR(1)
SET @.FirstLetter = LEFT(@.str,1)
IF @.FirstLetter between 'a' and 'z'
SET @.FirstLetter = CHAR(ASCII(@.FirstLetter) + (ASCII('A') - ASCII('a')))
SET @.str = @.FirstLetter +RIGHT(@.str, LEN(@.str)-1)
RETURN @.str
END
Then
SELECT dbo.FirstCapital ('test string')|||didn't wake up yet
CREATE FUNCTION dbo.FirstCapital (@.str VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
RETURN UPPER(LEFT(@.str,1))+RIGHT(@.str, LEN(@.str)-1)
END
"Anatoli Dontsov" <Anatoli@.dontsov.com> wrote in message
news:eJ9pjtOUGHA.5808@.TK2MSFTNGP12.phx.gbl...
> "Roy Goldhammer" <roy@.hotmail.com> wrote
>
> write user-defined function.
> CREATE FUNCTION dbo.FirstCapital (@.str VARCHAR(255))
> RETURNS VARCHAR(255)
> AS
> BEGIN
> DECLARE @.FirstLetter CHAR(1)
> SET @.FirstLetter = LEFT(@.str,1)
> IF @.FirstLetter between 'a' and 'z'
> SET @.FirstLetter = CHAR(ASCII(@.FirstLetter) + (ASCII('A') - ASCII('a')))
> SET @.str = @.FirstLetter +RIGHT(@.str, LEN(@.str)-1)
> RETURN @.str
> END
>
> Then
> SELECT dbo.FirstCapital ('test string')
>

changing datetime to string?

As in the database, i made a few columns in the forum table.
date(datetime) 15/09/2004 3.35PM
author(char) John

Select datetime + '<br>' + author from forum

it claimed there is an error on this datetime.

By right, the result should be

15/09/2004 3.35PM
John

can anyone help me how i could get the result out without having to change date's properties in the sql database?

Will be greatly appreciated if help gets ard.It looks like that select statement is trying to perform a math function. You should select the fields individually, then format them appropriately in your vb/cs code.

select datetime, author from forum

In your code, you will now have two fields exposed, and you can concatenate them if you wish.|||The TSQL CONVERT function can do that for you or Google for using string.format and use a date format code to convert it to the type of string you want it to be from within your VB code.

Changing date to string of numbers

I basic question but can someone help.

I have a SELECT statement, the result of which populates adatagrid. The first column has consecutive dates in it and I wantto hyperlink each date to a seperate Javascript function (theJavascript is created on the fly and is unique for each date). Ineed a different function name for each function and so tried the datebut "/" is not allowed in the Javasript function name. I thinkthe easiest way will be to produce a new column with the date expressesddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-

"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]

but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.

I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!

Many thanks

Mike

Hi Mike,

You can use the ISO format in this place:

CONVERT(NCHAR(8), [Date], 112)AS newDate

the date format will be yyyymmdd.

You can always check CONVERT DATE function from Books Online to convert your date.

Hope this helps.

|||Hi Limno

Many thanks. Your reply is just what I need. It works great.

I am sorry that could not figure it out for myself. I do usebooks on line and I have "Microsoft SQL Server 2005 A Beginner'sGuide" (which I got before I realise my server uses 2000!) and "SAMSTeach Yourself SQL 24 Hours". I started off learning VB.net butas my project goes on, rather than feeling that I am becoming competentat producing the web pages I want, I seem require more and moreknowledge (like SQL, JavaScript & CSS). Sometimes I feel I amgetting there, the next minute feel totally inadequate!! I strive tolearn, and in the meantime I really do appreciate the help of peoplelike yourself.

Many many thanks for your time and patience.

Regards

Mike

Changing data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie