Changing the parameter of a Snapshot

I understand that a history can be created in Reporting Services. Is
it true that when you click on the "New Snapshot" button, you get a
report using the default parameter? If so, then the "history" feature
is pretty much useless as you can only generate one version of the
report. What we want is to be able to take snapshots of a report at a
give point in time with the desired parameters.
Thanks for any input here.Yes, snapshots in the history are taken with default values of parameters.
If you need to store snapshot with different parameter values, you need to
set different defaults and then take snapshot. Also, when you render that
snapshot, you can change values of parameters that are not used in query.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eugene" <primer200@.yahoo.com> wrote in message
news:6290d6d2.0407281559.735524cb@.posting.google.com...
> I understand that a history can be created in Reporting Services. Is
> it true that when you click on the "New Snapshot" button, you get a
> report using the default parameter? If so, then the "history" feature
> is pretty much useless as you can only generate one version of the
> report. What we want is to be able to take snapshots of a report at a
> give point in time with the desired parameters.
> Thanks for any input here.|||Dmitry,
From a user point of view, I changed the parameter and I want to take
a snapshot of what I did. That would be a fair enough request,
wouldn't it? The administrator can't give a user Content Manager right
for him to change the default. May be this can be included in
enhencement for the next release?
Regards
Eugene|||We consider this as a request, however, it is not as straightforward as it
seems.
1. Snapshots were not intended to be a replacement for a good Data
Warehouse. Every missing feature about snapshots (and even snapshots
themselves) can be "worked around" by setting a data warehouse that can
reconstruct result of a query for any given time.
2. History is bound to a report, not to a user. Items in history don't carry
any individual security. Anyone who have access to a report would be able to
render from history. Therefore, personal preferences of a anyone, creating
snapshot would affect all users.
3. When allowing defaults only we identify snapshots by creation time. If we
accept parameters, we would have to introduce other means to identify and
manage snapshots. It would be possible and reasonable to take multiple
snapshots at the same time.
4. In many cases, users can create a linked report, provide different
default values for parameters and take snapshot of that linked report.
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eugene" <primer200@.yahoo.com> wrote in message
news:6290d6d2.0408012040.b062b04@.posting.google.com...
> Dmitry,
> From a user point of view, I changed the parameter and I want to take
> a snapshot of what I did. That would be a fair enough request,
> wouldn't it? The administrator can't give a user Content Manager right
> for him to change the default. May be this can be included in
> enhencement for the next release?
> Regards
> Eugene

Changing the ownership of a object

Hi all,

I have a problem while executing a stored procedure. I have created a database called "cpd" and created some stored procedures. for all my stored procedure the owner is "CPDUSER". when ever i am executing any stored procedures i have to write the user name first else it is not working.

let's say i have a stored procedure called "cp_checklogin". it takes 2 parameters. to execute this i have to write

exec cpduser.cp_checklogin 'admin@.jk.com', 'admin'

but i don't want to write the user name there. and if without username "CPDUSER" i am trying to execute the stored procedure it is throwing me the error that "the stored procedure cp_checklogin is not exist in the database". can anybody suggest me. it's very urgent.

Thanks in advance

Krishna

The cp_checklogin stored procedure has been created in the cpduser schema. The user making the call likely has 'dbo' as their default schema.

To fix this, you can either recreate the stored procedure in the dbo schema or you can change the default schema for the user making the call (for help on this, see http://msdn2.microsoft.com/en-us/library/ms190387.aspx)

|||Another option would be if you use SQL 2000 (you did not mention that) to change the ownership of existing objects using sp_changeobjectowner.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Changing the Language Noise Words

Hi,
I have searched high and low for an answer to this question. Please
tell me that it is a simple process...!
I have created a website in English using SQL 2000 as the database.
Now I have just launched the same website in French using exactly the
same schema (and same server) as the english website. Both use
full-text search. BUT...I notice that the noise word file being used
for both is English so, naturally, I need to change my French language
site to French. How can I do this?
I have already tried changing the database and field collation in the
French database and re-creating and re-populating the full-text index
but the English noise word file is still being used. Please, somebody
help!!
Darren,
You can switch to another language via dropping the existing FT Catalog and
re-creating it via the FT Indexing Wizard and when you pick the column (Text)
to be FT Indexed, click on the drop down box marked "Language for Word
Breaker" and select your language and then run a full population.
(Thanks to John for telling that trick!-)
Regards, Gerald.
"Darren Jensen" wrote:

> Hi,
> I have searched high and low for an answer to this question. Please
> tell me that it is a simple process...!
> I have created a website in English using SQL 2000 as the database.
> Now I have just launched the same website in French using exactly the
> same schema (and same server) as the english website. Both use
> full-text search. BUT...I notice that the noise word file being used
> for both is English so, naturally, I need to change my French language
> site to French. How can I do this?
> I have already tried changing the database and field collation in the
> French database and re-creating and re-populating the full-text index
> but the English noise word file is still being used. Please, somebody
> help!!
>
|||Darren,
Garald is correct. Just some additional info for future reference... You can
find out which noise.* is linked to what language via the Schema.txt file
under \FTDATA\SQLServer\Config, for example for noise.FRA is the noise word
file for French:
<stoplist
language="French_French"
file="noise.fra"
primarylanguage=12
sublanguage=1>
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com...
> Darren,
> You can switch to another language via dropping the existing FT Catalog
and
> re-creating it via the FT Indexing Wizard and when you pick the column
(Text)[vbcol=seagreen]
> to be FT Indexed, click on the drop down box marked "Language for Word
> Breaker" and select your language and then run a full population.
> (Thanks to John for telling that trick!-)
> Regards, Gerald.
> "Darren Jensen" wrote:
|||Thanks, that worked a treat and very easy to do too!
Gerald Baeck <GeraldBaeck@.discussions.microsoft.com> wrote in message news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...
> Darren,
> You can switch to another language via dropping the existing FT Catalog and
> re-creating it via the FT Indexing Wizard and when you pick the column (Text)
> to be FT Indexed, click on the drop down box marked "Language for Word
> Breaker" and select your language and then run a full population.
> (Thanks to John for telling that trick!-)
> Regards, Gerald.
|||I have one follow up question on the subject of fulltext indexing with
French and that is how can I get the search to ignore accents? For
example if I search 'hopital' I would like the results to show all
places where it finds 'hpital' or 'hopital'. Is this possible?
Thanks.
jensendarren@.hotmail.com (Darren Jensen) wrote in message news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...[vbcol=seagreen]
> Thanks, that worked a treat and very easy to do too!
> Gerald Baeck <GeraldBaeck@.discussions.microsoft.com> wrote in message news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...
|||Darren,
Unfortunately, the issue of accent sensitivity vs. accent insensitivity FTS
is more difficult to answer. This is a well known bug that has been around
for a long time (since SQL Server 7.0) and is truly only going to be fixed
in SQL Server 2005 <sigh>. As there are only workarounds for this bug in SQL
Server 2000 that require the use of duplicate data where you store the
non-accented search words, such as 'hopital'. You would then FT Index and FT
Search this column, while returning the accented search data (such as
'hpital') from another column back to the user. You will need to develop
accent removal procedures as well as use triggers (insert & update) to
maintain the currency of the two columns...
Regards,
John
"Darren Jensen" <jensendarren@.hotmail.com> wrote in message
news:c2c1a066.0411222026.3278d8fe@.posting.google.c om...
> I have one follow up question on the subject of fulltext indexing with
> French and that is how can I get the search to ignore accents? For
> example if I search 'hopital' I would like the results to show all
> places where it finds 'hpital' or 'hopital'. Is this possible?
> Thanks.
> jensendarren@.hotmail.com (Darren Jensen) wrote in message
news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...[vbcol=seagreen]
news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...[vbcol=seagreen]
Catalog and[vbcol=seagreen]
(Text)[vbcol=seagreen]
|||Darren,
my workaround for this problem is to replace all special chars before
filling the index. I know it could take a while, but till Yukon is released
its the only way i think.
UPDATE [table] SET [field] = REPLACE([field], '', 'o')
Regards, Gerald.
"Darren Jensen" <jensendarren@.hotmail.com> schrieb im Newsbeitrag
news:c2c1a066.0411222026.3278d8fe@.posting.google.c om...[vbcol=seagreen]
>I have one follow up question on the subject of fulltext indexing with
> French and that is how can I get the search to ignore accents? For
> example if I search 'hopital' I would like the results to show all
> places where it finds 'hpital' or 'hopital'. Is this possible?
> Thanks.
> jensendarren@.hotmail.com (Darren Jensen) wrote in message
> news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...

Changing the file name when sending a report in a subscription

Hello...
When i send a report in a subscription (in excel format - or any other) the
file is created and send as a file with the name of the report. How can i
change the name of this file ?
ThanksOne solution for you might be to create linked reports and to create
subscriptions on those.
However if you are looking to create something where you can configure the
name of the report when the report is executed - say append a date and time
to the report's name - then you are going to either have to create a Custom
Delivery Extension, or you are going to have to build your own component
that you schedule independantly from Reporting Services that extracts the
Report via the SOAP interface, names the output file and delivers it to your
user.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Penker" <Penker@.discussions.microsoft.com> wrote in message
news:89B65C23-7EDF-4B47-B259-7BB370FFB98C@.microsoft.com...
> Hello...
> When i send a report in a subscription (in excel format - or any other)
> the
> file is created and send as a file with the name of the report. How can i
> change the name of this file ?
> Thanks

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

Changing the caption of a member name in a Named Set

I have created a named set that includes a list of five customers. One of the customers is named "XYZ Corp.". For the named set, I want to display the customer with a name of "XYZ Corporation". Could someone supply me with the syntax needed to do this? Thank you.

David

Normally it is not possible to change captions/names of the existing hierarchy members. The usual workaround is to create calculated member which aliases the original member. This, however, has couple of implications, for example Exists rules for the calculation member are different etc.

CREATE Customers.[XYZ Corporation] = Customers.[XYZ Corp];

CREATE SET NamedSet AS { ..., [XYZ Corporation], ... };

|||

Mosha,

If there are implications when creating an alias to be used in a named set, would it be better to handle this issue in the reporting client rather than in Analysis Services. For example, I can rename an item in an Excel 2007 pivot table connected to an AS cube. The renamed item becomes a caption while the MDX still points to the original member name in the cube.

The only problem with this approach is that I would have to repeat this process for each report instead of doing it once as part of the named set.

David

|||This is really up to you which approach to take. I think you recognize now advantages and disadvantages of each approach so you can make informed decision.

Changing table selections

Hello,

I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...

Any help would be appreciated...

Thank you,

David

That should be part of the data flow. when you open the package in BIDS; click in the dataflow tab.

Notice that is the new table has a diffrent structure; you will need to 'refresh' the metadata in the other components in the dataflow.

|||You may have a Transfer Objects task in your package, instead of a data flow. If you click that and hit F4 to bring up the Properties window, one of the properties is Database Objects. You can alter the list of items from there.

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 Security to Windows Authentications from sql login

Hi
I have around 300+ DTS packages, with most of the have been created by users
with SQL Login information.
MY company is now moving to Windows Authentication.
There two places where we can put SQL Login information in DTS Packages.
1. When you create new connection in DTS for SQL Server.
2. When you save DTS packages...
I have posted the same in DTS group also.
It is lots of manual work to change all the dts packages to windows authenti
catiosn.
Is there any simple and automatic way to change all the connections to use w
indows authentications...'?
Appreciate your help.
Thanks
JPUsing SQL DMO you can do anything. Infact the whole SQL Enterprise Manager
uses DMO object to show you what you see.
If you can create a lil application, use SQL DMO, iterate thru DTS packages
and modify their properties.
Read more about SQL DMO !
"JP" <anonymous@.discussions.microsoft.com> wrote in message
news:B8F6BF05-533D-4C8B-8D83-3958B0D24165@.microsoft.com...
> Hi
> I have around 300+ DTS packages, with most of the have been created by
users with SQL Login information.
> MY company is now moving to Windows Authentication.
> There two places where we can put SQL Login information in DTS Packages.
> 1. When you create new connection in DTS for SQL Server.
> 2. When you save DTS packages...
> I have posted the same in DTS group also.
> It is lots of manual work to change all the dts packages to windows
authenticatiosn.
> Is there any simple and automatic way to change all the connections to use
windows authentications...'?
> Appreciate your help.
> Thanks
> JP
>

Changing PDF Output Orientation

I have created report subscriptions for my end users which are emailed
PDF format reports. However, the default report orientation is PORTRAIT
but this format causes the reports to be generated in a very unreadable
way.
Is there a solution to changing the orientation to LANDSCAPE?
Thanks.You can't change the orientation directly - you need to change the
height and width values for the report page (from 11 x 8 1/2 to 8 1/2 x
11).
You also need to ensure the size of the body and the margins are
consistent with these settings.
The Acrobat reader will automatically rotate the page if you have these
settings correct - check the Acrobat settings, too, as it is possible
to have the auto rotate feature turned off.

Changing password of the Owner of the job

Hi,
I have created a job with specifying the owner as <SomeSQLLogin>.
Later I have changed the password of that login.
Do Impact of change of password will affect excution of job?
I have tried it 2-3 times but job is executing successfully.
But I am not fully convinced whether change of password affect the job execu
tion or not.
Can some give greater detail on this?
One more issue:
When executing the job I tried to capture login logout event in profiler. I
find that:
Login Event occurs under login name of Agent Service account.
Logout Event occurs for login name of owner of the job.
In between SQL Server somehow chaged the logged in user. I don't know how?
Please clear this issue also.
Thanks in advance
PushkarHi
This is from BOL regarding sp_start_job, that the owner is not who the
account under which the job is executed, but will restrict who can run the
job.
"Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the symin fixed role ca
n
start any job. A user who is not a member of the symin role can use
sp_start_job to start only the jobs he/she owns.
When sp_start_job is invoked by a user who is a member of the symin fixed
server role, sp_start_job will be executed under the security context in
which the SQL Server service is running. When the user is not a member of th
e
symin fixed server role, sp_start_job will impersonate the SQL Server
Agent proxy account, which is specified using xp_sqlagent_proxy_account. If
the proxy account is not available, sp_start_job will fail. This is only tru
e
for Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is
no
impersonation and sp_start_job is always executed under the security context
of the Windows 9.x user who started SQL Server."
John
"Pushkar" wrote:

> Hi,
> I have created a job with specifying the owner as <SomeSQLLogin>.
> Later I have changed the password of that login.
> Do Impact of change of password will affect excution of job?
> I have tried it 2-3 times but job is executing successfully.
> But I am not fully convinced whether change of password affect the job exe
cution or not.
> Can some give greater detail on this?
> One more issue:
> When executing the job I tried to capture login logout event in profiler.
I find that:
> Login Event occurs under login name of Agent Service account.
> Logout Event occurs for login name of owner of the job.
> In between SQL Server somehow chaged the logged in user. I don't know how?
> Please clear this issue also.
> Thanks in advance
> Pushkar
>|||Hi,
I have not specified any account to be used as proxy account.
I have verified this by executing xp_sqlagent_proxy_account N'GET' and it
returns null.
But still job is running successfully.
I am not accessing any external resource through this job, then it does the
impersonation?
Is SQL Server is using 'sp_setuserbylogin' to change the logged on user from
Agent Service account to owner of the job.
Please clear my doubts.
Thanks
Pushkar
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A43E89C0-DE85-44D0-BBF9-867F5D822A38@.microsoft.com...
> Hi
> This is from BOL regarding sp_start_job, that the owner is not who the
> account under which the job is executed, but will restrict who can run the
> job.
> "Permissions
> Execute permissions default to the public role in the msdb database. A
> user
> who can execute this procedure and is a member of the symin fixed role
> can
> start any job. A user who is not a member of the symin role can use
> sp_start_job to start only the jobs he/she owns.
> When sp_start_job is invoked by a user who is a member of the symin
> fixed
> server role, sp_start_job will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the
> symin fixed server role, sp_start_job will impersonate the SQL Server
> Agent proxy account, which is specified using xp_sqlagent_proxy_account.
> If
> the proxy account is not available, sp_start_job will fail. This is only
> true
> for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is
> no
> impersonation and sp_start_job is always executed under the security
> context
> of the Windows 9.x user who started SQL Server."
> John
>
> "Pushkar" wrote:
>

changing owner of stored procedure

Is there a way to change the owner of a stored procedure?
I used Enterprise Manager to create a stored procedure. But since it was
created under my login name, other users are denied permission. Rather than
explicitly giving permission to everyone, I would like to change the owner
to 'dbo'.
How?
Lisasp_changeobjectowner
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:ubuJHdw7DHA.2924@.tk2msftngp13.phx.gbl...
> Is there a way to change the owner of a stored procedure?
> I used Enterprise Manager to create a stored procedure. But since it was
> created under my login name, other users are denied permission. Rather
than
> explicitly giving permission to everyone, I would like to change the owner
> to 'dbo'.
> How?
> Lisa
>

changing filename of PDF-Report

Hello,
we use the Report Services to generate bills from our orders. Therefor a
url with all the report parameters is created and send to the internet
explorer which displays the report with the pdf plugin (standard).
Our problem is that all reports have the same name (for example bill.pdf).
Is it possible to send a paramter (maybe a system one) which the report
server is using and named the file like it?
For example we give in the url &reportname=bill001 and then the file
generated names bill001.pdf?
Thanks for all answers.Hi Thomas,
We are using RS to generate pdf files, but we are just streaming directly
the resulting pdf to the client browser.
So, the result of the Render method of the ReportingServices object (which
is of type "array of bytes" ) gets passed to the browser using
Response.BinaryWrite(result).
I think you could use something to write the array of bytes to a pdf file -
and at this step you can choose the name you want for this pdf file.
Example:
Dim fs As New System.IO.FileStream("c:\customers\BILL001.pdf",
System.IO.FileMode.CreateNew)
' Create the writer for data.
Dim w As New System.io.BinaryWriter(fs)
' Write data to Test.data.
Dim b() As Byte
b = rs.Render(...parameters for report rendering)
w.Write(b)
w.Close()
fs.Close()
Hope this helps,
Andrei.
"Thomas Weiler" <Thomas.Weiler@.bigfoot.de> wrote in message
news:ec8HJOGPFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hello,
> we use the Report Services to generate bills from our orders. Therefor a
> url with all the report parameters is created and send to the internet
> explorer which displays the report with the pdf plugin (standard).
> Our problem is that all reports have the same name (for example bill.pdf).
> Is it possible to send a paramter (maybe a system one) which the report
> server is using and named the file like it?
> For example we give in the url &reportname=bill001 and then the file
> generated names bill001.pdf?
> Thanks for all answers.

Changing Filegroup for Text/Image Data SQL 2005

I have created a new filegroup to store text/image data for a couple of
tables, but when I try to change the filegroup in management studio it always
changes back to the default filegroup. Any suggestions?
Thanks,
AdrewCan you be more specific on what you mean by try to change it? In order for
the LOBs to be stored on a specific Filegroup you need to specify that when
you create the table, not afterwards. And if you are in SQL2005 you should
be looking at using the new MAX datatypes and not Text & Image.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"chabotwvu" <chabotwvu@.discussions.microsoft.com> wrote in message
news:2688797F-1B5D-447D-AC5E-E79AF175ADF5@.microsoft.com...
>I have created a new filegroup to store text/image data for a couple of
> tables, but when I try to change the filegroup in management studio it
> always
> changes back to the default filegroup. Any suggestions?
> Thanks,
> Adrew

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan