Sql Advice: sql2005

Showing posts with label sql2005. Show all posts

Thursday, March 29, 2012

Description for fill factor on index rebuild maint plan misleading

Hi.
I found this misleading issue and thought I would share it.
We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
fillfactor of 90%. After the plan ran, the database grew by about 70% and
the fill factor was actually 10%.
We found the following in the maint plan:
'Change free space per page percentage to:' We entered 10%.
In hindsight, it meant FILLFACTOR and should have been 90%.
Do you also find this misleading?
The documentation (BOL) reads:
Change free space per page percentage to
Drop the indexes on the tables in the database and re-create them with a
new, automatically calculated fill factor, thereby reserving the specified
amount of free space on the index pages. The higher the percentage, the more
free space is reserved on the index pages, and the larger the index grows.
Valid values are from 0 through 100.
It says the HIGHER the percentage, the more free space is reserved. This
should read LOWER?
Is this a 'bug' in the documentation?
Could someone please pass this onto the Microsoft guys. Maybe they know
about this already.
Thanks!
WayneWayne
I agree that it is a little bit confusing
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
rebuild indexes is logged operation and needs a free space to rebuild all
indexes
Kalen Delaney said
"The first definition
is correct; fillfactor specifies how full each page should be. 30 means 30%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
I will report this discrepancy in the Books Online definitions. "
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eda35e4b5bedab51/535a1ef3d33f1d88?lnk=st&q=&rnum=3&hl=en#535a1ef3d33f1d88
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
> Thanks!
> Wayne
>|||"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
>
Anyone can submit doc bugs. The feedback link at the bottom of the BOL
topics will generate an email that automatically creates a doc bug.
David

Description for fill factor on index rebuild maint plan misleading

Hi.
I found this misleading issue and thought I would share it.
We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
fillfactor of 90%. After the plan ran, the database grew by about 70% and
the fill factor was actually 10%.
We found the following in the maint plan:
'Change free space per page percentage to:' We entered 10%.
In hindsight, it meant FILLFACTOR and should have been 90%.
Do you also find this misleading?
The documentation (BOL) reads:
Change free space per page percentage to
Drop the indexes on the tables in the database and re-create them with a
new, automatically calculated fill factor, thereby reserving the specified
amount of free space on the index pages. The higher the percentage, the more
free space is reserved on the index pages, and the larger the index grows.
Valid values are from 0 through 100.
It says the HIGHER the percentage, the more free space is reserved. This
should read LOWER?
Is this a 'bug' in the documentation?
Could someone please pass this onto the Microsoft guys. Maybe they know
about this already.
Thanks!
WayneWayne
I agree that it is a little bit confusing

> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
rebuild indexes is logged operation and needs a free space to rebuild all
indexes
Kalen Delaney said
"The first definition
is correct; fillfactor specifies how full each page should be. 30 means 30%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
I will report this discrepancy in the Books Online definitions. "
http://groups.google.com/group/micr...33f1d88

"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
> Thanks!
> Wayne
>|||"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
>
Anyone can submit doc bugs. The feedback link at the bottom of the BOL
topics will generate an email that automatically creates a doc bug.
David

Tuesday, March 27, 2012

Derived Column or script

Dear All,

I am pretty new to SQL2005 technologies.

I have a table like this:

Name

Code

Peter

Eric

I am interested in translation of the “code” to “job role”:

Name

Code

Job role

Peter

Director

Eric

Consultant

I have tried to use Derived Column and also script – but I am unable to get it to work.

Any hints/solution would greatly appreciate.

Best Regards, T

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

|||

Sagestore wrote:

A simple example without NULL checking etc
In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -
[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

Does not seem like the correct syntax, I think it should be:

[Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )

|||

Do you have the translations stored in a table somewhere? If so, the LOOKUP component can be used.

-Jamie

|||

The translation is hardcoded which is fine.

However the number of options are more than 2 "consultan" and "director" - at least up to 5 different options.

So I need someting like:

Case 10 => Director

Case 20 => Consultant

Case 30 => Admin

Etc.

Thanks for input so far - but still im waiting for a soluion.

|||

Michael gave you the solution - you just need to extend it to cover all eventualities:

[Code] == 9999 ? "Something else!" : ([Code] == 30 ? "Admin" : ([Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )))

-Jamie

|||

Thanks all of you - now I finally got it to work.

I am extremely happy with this kind of support.

Thanks again and take care, T

Wednesday, March 7, 2012

deploying App to 2003 server with SQL2005

I devloped this app with vs2005 and SQL 2005 express, everything seems to work fine, when I deploied the app to my web server I got a few different error messages that I worked through but now stuck on this one. i am sure it is something with SQL 2005 server. is there anywhere that has a good check list for doing things right in asp and SQL deployment?

here is the error message i am getting from debugging.

The SELECT permission was denied on the object 'Categories', database 'Blog', schema 'dbo'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Categories', database 'Blog', schema 'dbo'.

Source Error:

Line 33: SqlConnection connection = CreateConnection();
Line 34: SqlCommand command = CreateCommand(connection, query, parameters);
Line 35: SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
Line 36:
Line 37: Type datatype = factory.GetDataType();

Source File: c:\Inetpub\blogplankroad\App_Code\Utilities\SqlDatabase.cs Line: 35

Hi Planker,

Just as the exception message describes, the user account you're using to connect to the database does not have permission to perform a SELECT on the certain table.

In the SQL Server Management Studio, please open the user property window and grant the proper permission for the user.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

Friday, February 24, 2012

DEPLOY packages from Server A to Server B.

I developed some SSIS packages in SQL2005.When developing , The connect is set with Server A.

And i know ,later we are supposed to move to SERVER B.but B's IP and machine name are both unknown for developers.

The deployment guys will be cahrge in that.

what's easiest way for me to tell the deployment guy to add a connection to SERVER B after the SSIS packages have been developed?

any suggestion? Thanks in advance.

Use package configurations to store the data that can change (database connection strings, etc...)|||

HI phil:

yes , i tried this way.

In SERVER B ,imported .dtsx file , then changed the connection value with B server in .dtsconfig file, and ran the package, i found the package still affect the A server.

what' s wrong with my steps?

|||Do you have the ConnectionString property in the .dtsconfig file? This is what you should change.|||

Hi Phil:

Please take a look at the following content in my configure file(sorry for too much lines),could u tell me where to modify?:

Thank u very much

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="ASIAPACIFIC\daij" GeneratedFromPackageName="Package" GeneratedFromPackageID="{B780B7CC-3E98-4F5D-AF9A-E70F262B6FEA}" GeneratedDate="2007-3-4 18:55:47"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Name]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[UserName]" ValueType="String"><ConfiguredValue>sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CertificateContext]" ValueType="Int64"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointFileName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointUsage]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckSignatureOnLoad]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreationDate]" ValueType="DateTime"><ConfiguredValue>3/4/2007 5:32:13 PM</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorComputerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorName]" ValueType="String"><ConfiguredValue>ASIAPACIFIC\daij</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[EncryptCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[InteractiveMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaxConcurrentExecutables]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Name]" ValueType="String"><ConfiguredValue>Package</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[OfflineMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePriorityClass]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackageType]" ValueType="Int32"><ConfiguredValue>5</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SaveCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SQLFolder]" ValueType="String"><ConfiguredValue>\</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SuppressConfigurationWarnings]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdateObjects]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdatePackage]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionBuild]" ValueType="Int32"><ConfiguredValue>7</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionComments]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMajor]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMinor]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[BypassPrepare]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[CodePage]" ValueType="UInt32"><ConfiguredValue>1252</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Connection]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Description]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsStoredProcedure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Name]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ResultSetType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSource]" ValueType="String"><ConfiguredValue>usp_insertSSISLOG</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSourceType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TimeOut]" ValueType="UInt32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration></DTSConfiguration>

|||This is the only line you should have in your configuration file for connections: (The ConnectionString property)

<Configuration

ConfiguredType="Property"

Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]"

ValueType="String"><ConfiguredValue>Data Source=SCN4685;User

ID=sa;Initial

Catalog=DemoDB0;</ConfiguredValue></Configuration>

But, this seems to be using an ODBC connection. So, that means the ODBC connection has to change to point to the new database, OR, you have to change the "Data Source=XXXXX" parameter in the above line to point to the different ODBC connection. (Or Oracle connection, whatever it may be.)|||

Anyway，many many thanks

|||

Hahhahhha~~~~

Sometimes it‘s really weird!
2 whole days, I can’t figure out how to change the connection when deploying SSIS to another Server.
Today, I was so disappointed that I haven’t got the right way to work it round.
Before the shift is off, I fall a snap. And after snap, I used the XML spy to modify the configure file with password adding.
Hah~~it works!
I still wonder why the password is blank when we enabled the configure file option.
Here are my tips to change connections when deploying to Pro environment.

No need to change this:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;Persist Security Info=True;</ConfiguredValue></Configuration>

But we have to change these property’s value:
InitialCatalog:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB1</ConfiguredValue></Configuration>
Password(what is blank at first):
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue>XXXXXX</ConfiguredValue></Configuration>
Servername:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration>

Gosh~~thanks.
It really works.

DEPLOY packages from Server A to Server B.

I developed some SSIS packages in SQL2005.When developing , The connect is set with Server A.

And i know ,later we are supposed to move to SERVER B.but B's IP and machine name are both unknown for developers.

The deployment guys will be cahrge in that.

what's easiest way for me to tell the deployment guy to add a connection to SERVER B after the SSIS packages have been developed?

any suggestion? Thanks in advance.

Use package configurations to store the data that can change (database connection strings, etc...)|||

HI phil:

yes , i tried this way.

In SERVER B ,imported .dtsx file , then changed the connection value with B server in .dtsconfig file, and ran the package, i found the package still affect the A server.

what' s wrong with my steps?

|||Do you have the ConnectionString property in the .dtsconfig file? This is what you should change.|||

Hi Phil:

Please take a look at the following content in my configure file(sorry for too much lines),could u tell me where to modify?:

Thank u very much

|||This is the only line you should have in your configuration file for connections: (The ConnectionString property)

<Configuration

ConfiguredType="Property"

Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]"

ValueType="String"><ConfiguredValue>Data Source=SCN4685;User

ID=sa;Initial

Catalog=DemoDB0;</ConfiguredValue></Configuration>

Anyway，many many thanks

|||

Hahhahhha~~~~

Gosh~~thanks.
It really works.

Sunday, February 19, 2012

deploy a report to report server in SQL 2k5

I have made a report using Business Intelligence Development Studio in
SQL2005. When I deploy that report the report server, I got the error:
****************************************************************************************************************************
A connection cannot not be made to the report server http://servername.
Addition Information:
The server committed a protocol violation. Section=ResponseHeader detail=CR
must be followed by LF (System.Web.Service)
******************************************************************************************************************************************************
Then I use IE to open http://servername/reportserver , I get the following
message:
Server Error in '/ReportServer' Application.
----
Runtime Error
Description: An application error occurred on the server. The current custom
error settings for this application prevent the details of the application
error from being viewed remotely (for security reasons). It could, however,
be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable
on remote machines, please create a <customErrors> tag within a "web.config"
configuration file located in the root directory of the current web
application. This <customErrors> tag should then have its "mode" attribute
set to "Off".

<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>
Notes: The current error page you are seeing can be replaced by a custom
error page by modifying the "defaultRedirect" attribute of the application's
<customErrors> configuration tag to point to a custom error page URL.

<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>Can anyone please help? I am desperate.
" 00 Michael Jordan" <a@.b.com> wrote in message
news:u2kvq7SEIHA.1184@.TK2MSFTNGP04.phx.gbl...
>I have made a report using Business Intelligence Development Studio in
> SQL2005. When I deploy that report the report server, I got the error:
> ****************************************************************************************************************************
> A connection cannot not be made to the report server http://servername.
> Addition Information:
> The server committed a protocol violation. Section=ResponseHeader
> detail=CR
> must be followed by LF (System.Web.Service)
> ******************************************************************************************************************************************************
> Then I use IE to open http://servername/reportserver , I get the
> following
> message:
>
> Server Error in '/ReportServer' Application.
> ----
> Runtime Error
> Description: An application error occurred on the server. The current
> custom
> error settings for this application prevent the details of the application
> error from being viewed remotely (for security reasons). It could,
> however,
> be viewed by browsers running on the local server machine.
> Details: To enable the details of this specific error message to be
> viewable
> on remote machines, please create a <customErrors> tag within a
> "web.config"
> configuration file located in the root directory of the current web
> application. This <customErrors> tag should then have its "mode" attribute
> set to "Off".
> 
> <configuration>
> <system.web>
> <customErrors mode="Off"/>
> </system.web>
> </configuration>
> Notes: The current error page you are seeing can be replaced by a custom
> error page by modifying the "defaultRedirect" attribute of the
> application's
> <customErrors> configuration tag to point to a custom error page URL.
> 
> <configuration>
> <system.web>
> <customErrors mode="RemoteOnly"
> defaultRedirect="mycustompage.htm"/>
> </system.web>
> </configuration>
>|||we have the same problem.we want to give a custom message if any error
occured in Reporting Service.So,pls help.......
"00 Michael Jordan" wrote:
> Can anyone please help? I am desperate.
>
> " 00 Michael Jordan" <a@.b.com> wrote in message
> news:u2kvq7SEIHA.1184@.TK2MSFTNGP04.phx.gbl...
> >I have made a report using Business Intelligence Development Studio in
> > SQL2005. When I deploy that report the report server, I got the error:
> >
> > ****************************************************************************************************************************
> > A connection cannot not be made to the report server http://servername.
> >
> > Addition Information:
> >
> > The server committed a protocol violation. Section=ResponseHeader
> > detail=CR
> > must be followed by LF (System.Web.Service)
> >
> > ******************************************************************************************************************************************************
> >
> > Then I use IE to open http://servername/reportserver , I get the
> > following
> > message:
> >
> >
> >
> > Server Error in '/ReportServer' Application.
> > ----
> >
> > Runtime Error
> > Description: An application error occurred on the server. The current
> > custom
> > error settings for this application prevent the details of the application
> > error from being viewed remotely (for security reasons). It could,
> > however,
> > be viewed by browsers running on the local server machine.
> >
> > Details: To enable the details of this specific error message to be
> > viewable
> > on remote machines, please create a <customErrors> tag within a
> > "web.config"
> > configuration file located in the root directory of the current web
> > application. This <customErrors> tag should then have its "mode" attribute
> > set to "Off".
> >
> > 
> >
> > <configuration>
> > <system.web>
> > <customErrors mode="Off"/>
> > </system.web>
> > </configuration>
> >
> > Notes: The current error page you are seeing can be replaced by a custom
> > error page by modifying the "defaultRedirect" attribute of the
> > application's
> > <customErrors> configuration tag to point to a custom error page URL.
> >
> > 
> >
> > <configuration>
> > <system.web>
> > <customErrors mode="RemoteOnly"
> > defaultRedirect="mycustompage.htm"/>
> > </system.web>
> > </configuration>
> >
>
>

Thursday, March 29, 2012

Description for fill factor on index rebuild maint plan misleading

Description for fill factor on index rebuild maint plan misleading

Tuesday, March 27, 2012

Derived Column or script

Wednesday, March 7, 2012

deploying App to 2003 server with SQL2005

Friday, February 24, 2012

DEPLOY packages from Server A to Server B.

DEPLOY packages from Server A to Server B.

Sunday, February 19, 2012

deploy a report to report server in SQL 2k5

deploy a report to report server in SQL 2k5

Sql Advice

Blog Archive

About Me