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 SQL Server Agent settings on the fly?

Hello all,

I have created a simple package that imports data from a flat file into a database. To run the package I'm using a SQL Server Agent Job. The location of the file is stored as a connection string in the Connection Managers tab in the SQL Server Agent Job.

Is there a way to change this connection string programmatically? If not programmatically, is there a way to change this setting right before I execute the package. I want to change the location of this file based on user input. Also, I'm executing the package using the sp_start_job stored procedure to run the job.

Thanks in advance for any advice!

-Dwayne

You could just create the entire job on the fly, they can be scripted in T-SQL.

If you want to keep the job, then you could use sp_update_jobstep to update the step command. Now I have not looked at the command for the SSIS subsystem, but I'd recommend use the CmdExec subsystem and calling DTEXEC myself, the logging is much better. Even MS recommend that approach in one of their KB's now. BTW could read it here first, illustrates why - http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Finally leave the job alone, and store the value in a table. You could manually use the Exec SQL Task to query the value, or perhaps better still use a SQL server based configuration. Have a look in Books Online about configurations, they are very useful, and I suggest you use them anyway to help manage connection details.

Changing Server Report on the fly

I would like to modify some of the properties of a ServerReport entity from a desktop app using the ReportViewer control. In particular, I would like to have the document map displayed as expanded, and make some changes to the group toggle items based on the user's parameter selection.

Can someone please point me in the right direction? How do I get access programmatically to the ServerReport layout?

Thx

In the event I'm way off base just disregard this post.

Create a parameter of your choice and name it for example "ExpandDetails" and apply the Data Type as Boolean. In the default values select “Non-Queried” and type “False”

Select the table in question, open up the Properties of the table, select the group that controls the expanded element, Edit the group you selected, change from the General tab to the Visibility tab, select Expression, add this code"=Iif(Parameters!ExpandDetails.Value = True, False, True)"

Select the textbox where the expanded element will occur, open up the Properties of the textbox. Change from the General tab to the Visibility tab. Select Expression at the bottom of the dialog box where you see “Expanded” and “Collapsed”, add this code “=Iif(Parameters!ExpandDetails.Value = True, True, False)”

This will create a parameter named ExpandDetails with a radio box selection. Click on the appropriate value and select “View Report”

Tweak the logic to work in the manner you need. by that I mean the true or false of the IIF statements.

|||

Thanks for the reply.

I've actually tried what you suggest. My situation is that I allow my users to choose an outer group, and an inner group, with the option of choosing "None" (I.e. no inner grouping) for the inner group. I've managed to get it all working (except for the +/- icons being reversed in some cases) but I have not been able to get the Expand/Collapse to work, mainly because I have no way (that I can see) of changing a ToggleItem based on an expression.

So I need to get down and dirty with the rdl before rendering the report, and am hunting around for how to proceed. I suspect I need to dump the report using LoadReport into a memory stream and cast it somehow, but some sample code with be helpful.

Cheers,

Changing path of data & log files on the fly

We have a SQL Server setup as a publisher to 15 subscribers. We need to change the path of the data & log files to a new drive (added a new harddisk). We plan to take a cold backup of the database and shift the data & log files to the new drive. Then we just attach the data & log files from the new path.

Will this disturb my existing replication Setup?
Is the the correct procedure for changing the path of the existing data & log files?
What is the appropriate method for shifting data & log file of a live database to a different location (directory/drive) ?

thanks in advancedisable replication;
sp_detach_db;
move files to new location;
sp_attach_db;
enable replication.|||Thanks for the suggestion. I'll try this out and confirm back. Thanks again anyways.

changing images on the fly

I am building an app to print Recipes and I need to display a different picture for each dish

The Database is Access 97 which is fine and I want to keep my images in a seperate folder on the users PC

like "C:\RecipePictures\Soup.jpg"

I have tried using an embedded, External and Database sources and cannot get any to do what I want has anybody got any ideas please

I guess I realy want to use external but it won't accept C:\RecipePictures\Soup.jpg it is looking for a URL

any help gratefully received

You need to make sure the path to your images is located under your web root. Assume your web root is c:\inetpub\wwwroot, then you need to put the RecipePictures folder in wwwroot (or a subfolder to wwwroot). Or you could create a virtual directory under wwwroot that points to C:\RecipePictures. You create a virtual directory in the IIS manager (MMC). Good luck!

|||

if the DB has the path to the image, you can have the picture in the report as expression "file://" & {the path to the imge}

that may bring the picture in.

Best solution is to do everything in SQLserver express its free! and you can have pictures in the DB

|||

thanks I will have a play with that

the problem is that my users are strictly non technical and the picures will be saved where they are saved, I have struggled to get them to save them with english names

|||

Thanks, sounds good

the access Database is a legacy thing and I may move to SqlServer but these guys are working on the road so we currently use replication through their company VPN if we added images, most are over 1meg, the syncro would be horrendous (they use 3G when they don't have wireless)

|||

OK FYI

I set the source to External

the Value to +"Fill://"+Fields!Photolocation.Value //Text field in Access DB holding the path and name of the image like C:\Recpe pictures\Soup.jpg

the MimeType was empty

this means that each record can have it's own image

|||

sorry an error should be ="File://" NOT +"Fill://"

I set the source to External
the Value to ="File://"+Fields!Photolocation.Value //Text field in Access DB holding the path and name of the image like C:\Recpe pictures\Soup.jpg
the MimeType was empty

this means that each record can have it's own image

Changing ftcatid on the fly?

Hi all
Quick question, I have a set of full text catalog files that we would
like to use to re-create a set of catalogs on a new build database, as
part of a build script. I have copied a set of files to a devstore on
out SAN with the intention of moving these to each local file system
before creating the catalogs.
I have the script and the bcp's writtten to create and populate both
the CRUD and the Search Databases. The search DB is the one with the FT
enabled.
My plan is to script the catalogs, then move the files to the local
file system, initially i wanted to create the new files with a dynamic
rename ( to follow the SQLxxxxx(dbid)yyyyy(ftcatid) naming convention)
however as all the hash files use the same name I am thinking it would
be easier to align the dbid and ftcatid to the ones specified on the
devstore files,
So this gives me a hardcoded dbid of 7, which i can recreate in the
build script by attaching and detaching dummy db's till i get the dbid
i need.
My question (finally!) is can i create the catalogs on the new machine,
then edit the ftcatid values in the sysfulltextcatalogs table to give
the catalkogs the desired ftcatid's (which would be 5,6,7)
Is this going to cause me a massive headache, or is there a better way
to create A number of prepoulated FT catalogs from a 'master' set?
regards
Barri Martin
SQL Bod - DWP.gov.uk
You can do this, but I would consult this article for a complete guide on
how to do it.
http://support.microsoft.com/kb/240867/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<barri_j_martin@.yahoo.co.uk> wrote in message
news:1159372232.664003.16300@.i42g2000cwa.googlegro ups.com...
> Hi all
> Quick question, I have a set of full text catalog files that we would
> like to use to re-create a set of catalogs on a new build database, as
> part of a build script. I have copied a set of files to a devstore on
> out SAN with the intention of moving these to each local file system
> before creating the catalogs.
> I have the script and the bcp's writtten to create and populate both
> the CRUD and the Search Databases. The search DB is the one with the FT
> enabled.
> My plan is to script the catalogs, then move the files to the local
> file system, initially i wanted to create the new files with a dynamic
> rename ( to follow the SQLxxxxx(dbid)yyyyy(ftcatid) naming convention)
> however as all the hash files use the same name I am thinking it would
> be easier to align the dbid and ftcatid to the ones specified on the
> devstore files,
> So this gives me a hardcoded dbid of 7, which i can recreate in the
> build script by attaching and detaching dummy db's till i get the dbid
> i need.
> My question (finally!) is can i create the catalogs on the new machine,
> then edit the ftcatid values in the sysfulltextcatalogs table to give
> the catalkogs the desired ftcatid's (which would be 5,6,7)
> Is this going to cause me a massive headache, or is there a better way
> to create A number of prepoulated FT catalogs from a 'master' set?
> regards
> Barri Martin
> SQL Bod - DWP.gov.uk
>