Configuration problems

Hi,
I am trying to configure my serwer (local computer) as a distributor and
publisher to see how it works. I have created sample database with one table
and few filelds. One of them is unique primary key.
During Configuring and Selecting Distributor or Creating Publication I get
an error:
Error 14114: '(null)' is not configured as a Distributor.
What can be the problem? I was trying to configure transactional
replication, but I think that it has no influence, because error occures
during configuring distributor. Also I have used fo snapshot folder admin
share C$ ans normal share with full rights for all users C.
Software versions are:
Windows XP Professional SP1
SQL 2000 Developer SP3 version 8.00.760
Thank you
Przemo
Have a look at http://support.microsoft.com/kb/q302223/
You may have changed the name of the machine since installing replication
and there is no entry in sysservers with the new machine name
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3473CCB4-6C0B-4ED4-833E-73CEC5E2E1DB@.microsoft.com...
> Hi,
> I am trying to configure my serwer (local computer) as a distributor and
> publisher to see how it works. I have created sample database with one
> table
> and few filelds. One of them is unique primary key.
> During Configuring and Selecting Distributor or Creating Publication I get
> an error:
> Error 14114: '(null)' is not configured as a Distributor.
> What can be the problem? I was trying to configure transactional
> replication, but I think that it has no influence, because error occures
> during configuring distributor. Also I have used fo snapshot folder admin
> share C$ ans normal share with full rights for all users C.
> Software versions are:
> Windows XP Professional SP1
> SQL 2000 Developer SP3 version 8.00.760
> Thank you
> Przemo

Configuration files do not work in CmdExec mode

I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig (on my dev machine).

Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.

However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:\....\myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig cannot be found.

What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.

Is there a way around this?

Does anyone know if this is the correct behaviour of a SSIS package? Shouldn't the config file in the command line overrule any command-line inside the package?|||Can't help with an answer, but curious as to why you need to use CmdExec instead of Integration Services Package?

Greg.|||

Hi GregAbd!

The reason to why I need CmdExec is that running it as an Integration Services Package doesn't give any good output if something goes wrong. And, I also have the problem that my package takes twice as long time if I run it inside a job as if I run it "manually". I'm curious to know if this changes if I run it in the job, but as a cmdexec task.

Regards Andreas

|||Can't help with the run time issue, but you can enable logging in your package - you can literally log everything that happens. Just right-click on the package (i.e. a blank area of the Control Flow) and choose Logging. You can add a Log Provider for Text Files, create a File Connection Manager, and choose which events you want logged.

You can then configure the Log file location from the Data Sources tab on the SQL Agent Job Step.

Greg.|||

You have set a config in your package, the command line allows you to add configurations in addition to the one in the package, it does not change the package structure.

There are similar issues covered in the answers here-

Re: Implementing IDTSLogging - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=816241&SiteID=1)

Config files get lost...

I created a project with multiple packages. I created a global

config file that all packages reference. I also created a config file

for each package that contains the package specific settings. I

checked it all into TFS. I went to a different machine and checked it

all out. Upon loading a package into the IDE I got the following 3

warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The

configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found.

Check the directory and file name.

e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning

loading MT_LSE_PROD_StageLoad.dtsx: The configuration file

"Environment.dtsConfig" cannot be found. Check the directory and file

name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning

loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the

configuration entries for the package. Check configurations entries and

previous warnings to see descriptions of which configuration failed.

e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any

ideas? And yes, the files *ARE* there! I tried building the project

and it builds. I also tried doing a deployment build and that too

built. I am so confused!!!

StarPilot wrote:

I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any ideas? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!

Could it be a permissions issue? I assume the account running the packages has access to e:\contentloader\sprint1a\

-Jamie

|||Not a permission issue. I've granted every account access to the file system (one of the "joys" of being in a dev environment). Also, I see this behavior in VS.NET not SQL. I do a get latest or check out of the project on a brand new dev machine and it is VS.NET giving me the warnings. I compile (ignoring the warnings) and it works just fine. I turn deployment on and build and the deployment directory has the packages and config files. So, they are there and being used - it's VS.NET that's winpering over it...

config creating havoc

I have created two package configurations

Each identical apart from the name (dev/prod) [They appear in that order in the 'configurations..' dialog]

I then manually edited the prod config (in notepad) to the correct values (connection string etc)

I then opened the config file in the config edit tool (some time later) to note that my changes had somehow been overwritten. I changed them back to the correct values and saved the prod config.

I then tried building the package.

It returned with numerous validation errors despite 'delay validation' being set to true on each of the tasks that were causing problems (they create temp tables which it cannot validate against)

I then tried running the package.

It errored.

It was trying to connect to production!!

How on earth is this possible?
According to the configuration dialog, the 'dev' configuration is the first one loaded.
I haven't told it anywhere to use 'prod'.
I haven't even (within ssis) managed to even change the values (connection strings) to production.

What is going on here?It would appear that subsequent entries in the 'package configurations organizer' overwrite initials ones.

e.g.
config1 value1=abc
config2 value1=def

the package would have value1=def

so, what exactly is the point of being able to load multiple configurations if it is just going to pick up the last one regardless?|||

The point of superceding configured properties, so far as I use it, to go from generalization to specialization.

A given configuration might be shared by 50 packages. However, for one or two of those packages, the configured value is not appropriate, must be specifialized, and hence, an "subclassed" configuration is added, which overrides the base, or precediing configuration.

|||ok, I can see how that could work but...

If I have a package and want to store the connection string for dev/test/prod in the same package, how can I then test this in VS.net?

Other than manually adding and deleting them each time?

also am getting the following error when trying to build:
Creating deployment utility...
Error : System.ApplicationException: Could not copy file "xxxx.dtsConfig" to the deployment utility output directory "yyyyyyy". > System.IO.IOException: The file 'xxxxxx' already exists.

I cannot see any kind of 'overwrite' flag in the project - deploy properties (like you get in reporting services)|||do not add config files into the misc folder in the project - this was causing the 'already exists' error

It obviously decides to deploy all the misc files. How stupid of me to include config files in this folder. They are only completely 100% relevant to the project after all.|||

You don't need to build anything, though. For that matter, deploying is a bit of a stretch as well. The .dtsx and .dtsconfig files are all you need. You can simply copy them to the server via a mapped drive, or use the import (or for that matter, Save Copy As... in BIDS) in Management Studio when connected to Integration Services if you prefer to store packages in MSDB.

As to your question as to dev, test, and prod, simply create three config files, name them slightly differently than each other. Then point your package to an environment-neutral named config file. To test, simply copy one of your dev, test, or prod config files (probably starting with dev) to the name that you have the package pointing to. When you want to switch environments, repeat the process using a different config file this time (perhaps test's file)

Conditionally Formating Subtotal Output

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.

I have also found that the autogenerated subtotal feature has much to be desired.

Conditional Split - DateTime Condition

Hi,

I'm trying to check if a row was created yesterday?

This does not seem to work?

(MyId == "10") && DATEPART("dd",GETDATE()) == (DATEPART("dd",MyDateTimeColumn) - 1)

Does anybody know how I can accomplish this?

Many thanks.

Two things...

First, you should surround your DATEPART equality checks in parenthesis.

(MyID == "10") && ( datepart stuff here )

Second, you probably shouldn't subtract one from the result of datepart("dd",MyDateTimeColumn), instead, you should use datepart("dd",dateadd("dd",-1,MyDateColumn))|||

Hi Mr. Hat,

I would try something similar to

(MyId == "10") && MyDateTimeColumn == DATEADD("dd", -1, GetDate())

Hope this helps,

Andy

|||

Andy Leonard wrote:

Hi Mr. Hat,

I would try something similar to

(MyId == "10") && MyDateTimeColumn == DATEADD("dd", -1, GetDate())

Hope this helps,

Andy

HAHAHAHA! (Sorry, but I blindly overlooked the part where the user was comparing day of months.) Yeah, do what Andy suggests. There's no need to compare "days" to each other. Just compare the dates. However, cast them to dt_dbdate first, to eliminate time.

(MyId == "10) && ((DT_DBDATE)[MyDateColumn] == (DT_DBDATE)DATEADD("dd",-1",GetDate()))|||

Brilliant! It worked.

Thank you so much!!

Conditional formatting for missing numeric data

I created a matrix report which includes numeric data to be formatted as
currency. In cases where the data is missing, the empty cell displays "$".
If I use the iif() function for conditional formatting, it inserts the entire
expression into all of the cells. Does anyone know how I can format the
cells that do not contain any data so that nothing will be displayed?
thanks!It sounds like you need Nothing:
=iif(Fields!MyValue.Value Is Nothing,"","C0")
Put the expression in the Custom field in the Textbox Properties window.
I think you can use "". If not, choose something else.
"Anna" wrote:
> I created a matrix report which includes numeric data to be formatted as
> currency. In cases where the data is missing, the empty cell displays "$".
> If I use the iif() function for conditional formatting, it inserts the entire
> expression into all of the cells. Does anyone know how I can format the
> cells that do not contain any data so that nothing will be displayed?
> thanks!

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Conception of my cube

Hello,
During my internship, I had to create a cube for sales based on only one dB (of an ERP).
So I've created a view grouping information I need.
So I've only one "table" (a view actually) which is my fact table and my only dimension table.
Actually, the dimension are the attributes of my sole dimesion.

The problem is that I've to write my report and I don't know if I've made the good choice. I can also create a dimension table with Customer, for countries ... but the result would be the same.

Thanks in advance for your advices.

Hey there,

If you are happy with the performance, the SQL is maintainable and you will only ever have one cube for your company, then your solution will be adequate.

However, you may find that you run into performance, scalability and maintenance issues as your data grows and if you need to start working with other business processes (e.g. order processing or invoicing).

By having a single view you are bypassing some of the automated work a cube / Analysis Services can give you. Also with the view, you may end up replicating some logic for bringing back dimension values (e.g. country). If you were to have another view for a new cube, you would have to replicate all the SQL for extracting the country information to return in your flattened view.

By using dedicated dimension tables, you will gain re-use of dimension content (conformed dimensions), requiring only a single extract from the source system.

There are numerous other benefits of having a more structured data warehouse/data extract process, such as change tracking, referential integrity checking, introduction of surrogate keys (non source system dependent keys if you are dealing with multiple data sources).

Hope that made sense,

Jonathon

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.