Problem with SQL Script with Clarion Date format

I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>

problem with sp_addmergearticle

Hey, I've used the following script to add an article to an existing
publication. But now it wants to rerun snapshot agent. Is there anyway where
i could add this table and create a snapshot only for that table only and not
the whole publication? Please tell me what would i have to do. I tried to use
@.force_invalidate_snapshot =0, but it says the snapshot was already run which
is true and i have to use 1 not 0.Thank you very much
exec sp_addmergearticle
@.publication = N'CDIMerge1',
@.article = N'DefReportExportOptions',
@.source_object = N'DefReportExportOptions',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000CFF1,
@.article_resolver = null,
@.source_owner = N'dbo',
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0,
@.force_invalidate_snapshot =1
GO
Tejas,
merge is different in this way to transactional. The snapshot agent will
create a complete snapshot. However the merge agent will just initialize the
new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Problem with SP_addlinkedserver

hi
right now I search a way to set the link Server with a T-Sql SCRIPT.
but the problem is here that I need to set the Server Options also.
is the any way to do this via T-SQL?
thanks for any kind of help
Klaus
How about sp_serveroption?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
> hi
> right now I search a way to set the link Server with a T-Sql SCRIPT.
> but the problem is here that I need to set the Server Options also.
> is the any way to do this via T-SQL?
> thanks for any kind of help
> Klaus
>
|||well good question,
but I think no way with the sp_dboption, that is only for the DB Option like
Read-Only, Offline..
anyway thanks for the idea
klaus
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
> How about sp_serveroption?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
>
|||Please read my response again. I didn't say sp_dboption, I said sp_serveroption. Below is from BOL:
Sets server options for remote servers and linked servers.
In this release, sp_serveroption has been enhanced with two new options, use remote collation and
collation name, that support collations in linked servers.
Syntax
sp_serveroption [@.server =] 'server'
,[@.optname =] 'option_name'
,[@.optvalue =] 'option_value'
Arguments
[@.server =] 'server'
Is the name of the server for which to set the option. server is sysname, with no default.
[@.optname =] 'option_name'
Is the option to set for the specified server. option_name is varchar(35), with no default.
option_name can be any of the following values.
<snip>
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%236haMoGXFHA.3856@.TK2MSFTNGP10.phx.gbl...
> well good question,
> but I think no way with the sp_dboption, that is only for the DB Option like
> Read-Only, Offline..
> anyway thanks for the idea
> klaus
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
>

Problem with SP_addlinkedserver

hi
right now I search a way to set the link Server with a T-Sql SCRIPT.
but the problem is here that I need to set the Server Options also.
is the any way to do this via T-SQL?
thanks for any kind of help
KlausHow about sp_serveroption?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
> hi
> right now I search a way to set the link Server with a T-Sql SCRIPT.
> but the problem is here that I need to set the Server Options also.
> is the any way to do this via T-SQL?
> thanks for any kind of help
> Klaus
>|||well good question,
but I think no way with the sp_dboption, that is only for the DB Option like
Read-Only, Offline..
anyway thanks for the idea
klaus
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
> How about sp_serveroption?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
>|||Please read my response again. I didn't say sp_dboption, I said sp_serveropt
ion. Below is from BOL:
Sets server options for remote servers and linked servers.
In this release, sp_serveroption has been enhanced with two new options, use
remote collation and
collation name, that support collations in linked servers.
Syntax
sp_serveroption [@.server =] 'server'
,[@.optname =] 'option_name'
,[@.optvalue =] 'option_value'
Arguments
[@.server =] 'server'
Is the name of the server for which to set the option. server is sysname, wi
th no default.
[@.optname =] 'option_name'
Is the option to set for the specified server. option_name is varchar(35), w
ith no default.
option_name can be any of the following values.
<snip>
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%236haMoGXFHA.3856@.TK2MSFTNGP10.phx.gbl...
> well good question,
> but I think no way with the sp_dboption, that is only for the DB Option li
ke
> Read-Only, Offline..
> anyway thanks for the idea
> klaus
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
>

Problem with SP_addlinkedserver

hi
right now I search a way to set the link Server with a T-Sql SCRIPT.
but the problem is here that I need to set the Server Options also.
is the any way to do this via T-SQL?
thanks for any kind of help
KlausHow about sp_serveroption?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
> hi
> right now I search a way to set the link Server with a T-Sql SCRIPT.
> but the problem is here that I need to set the Server Options also.
> is the any way to do this via T-SQL?
> thanks for any kind of help
> Klaus
>|||well good question,
but I think no way with the sp_dboption, that is only for the DB Option like
Read-Only, Offline..
anyway thanks for the idea
klaus
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
> How about sp_serveroption?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
>> hi
>> right now I search a way to set the link Server with a T-Sql SCRIPT.
>> but the problem is here that I need to set the Server Options also.
>> is the any way to do this via T-SQL?
>> thanks for any kind of help
>> Klaus
>>
>|||Please read my response again. I didn't say sp_dboption, I said sp_serveroption. Below is from BOL:
Sets server options for remote servers and linked servers.
In this release, sp_serveroption has been enhanced with two new options, use remote collation and
collation name, that support collations in linked servers.
Syntax
sp_serveroption [@.server =] 'server'
,[@.optname =] 'option_name'
,[@.optvalue =] 'option_value'
Arguments
[@.server =] 'server'
Is the name of the server for which to set the option. server is sysname, with no default.
[@.optname =] 'option_name'
Is the option to set for the specified server. option_name is varchar(35), with no default.
option_name can be any of the following values.
<snip>
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:%236haMoGXFHA.3856@.TK2MSFTNGP10.phx.gbl...
> well good question,
> but I think no way with the sp_dboption, that is only for the DB Option like
> Read-Only, Offline..
> anyway thanks for the idea
> klaus
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:uVrqtXGXFHA.2288@.TK2MSFTNGP14.phx.gbl...
>> How about sp_serveroption?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
>> news:%2326D54FXFHA.2692@.TK2MSFTNGP15.phx.gbl...
>> hi
>> right now I search a way to set the link Server with a T-Sql SCRIPT.
>> but the problem is here that I need to set the Server Options also.
>> is the any way to do this via T-SQL?
>> thanks for any kind of help
>> Klaus
>>
>>
>

Problem with Sleep function in SSIS

Hi,

I have a script task in SSIS which uses a sleep command. sleep(600000) which waits for 10 minutes. This works fine when i execute the package through GUI. But when i execute the same with Commandline, it does not work. Is there some thing which I have not set. I have also tried the Windows API call for sleep still it behaves the same way.

Any help?

Thanks in advance

Srividya

I don't know any reason why it would not work in command line. Are you getting any error?|||did you try using the system.timers.timer class?

Problem with scripting in SQL 2005 after SP2

Hello everyone,
We hit a snag after installing SP2 for SQL Server 2005 Standard. Basically,
when we want to script the database the server takes ages to determine
objects in the database and then it takes another age to actually script the
objects.
Before the SP2 scripting was lightning fast. Now we dread any change that
would require scripting.
Any help and/or insight much appreciated.
BR,
GAZ
> Before the SP2 scripting was lightning fast. Now we dread any change that
> would require scripting.
I find Red-Gate's SQL Compare to be very fast at generating change scripts.
Yes, it is not free, but it beats Management Studio's output hands down.
Anyway, here's something from Erland that I posted in the .Tools newsgroup
just yesterday:
[vbcol=seagreen]
I haven't tested scripting with SP2 as far as I can recall, but a tip
is that setting the database in forced parameterisation, can speed up
scripting considerably, since MgmtStudio uses inlined parameter values.
See also
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968[vbcol=seagreen]
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thanks for the tip. Changing the property Parametarisation to Forced has
resolved the issue. Scripting is a pleasurable task once again.
Thanks.
GAZ
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O$UD4VcgHHA.284@.TK2MSFTNGP05.phx.gbl...
> I find Red-Gate's SQL Compare to be very fast at generating change
> scripts. Yes, it is not free, but it beats Management Studio's output
> hands down.
> Anyway, here's something from Erland that I posted in the .Tools newsgroup
> just yesterday:
> I haven't tested scripting with SP2 as far as I can recall, but a tip
> is that setting the database in forced parameterisation, can speed up
> scripting considerably, since MgmtStudio uses inlined parameter values.
> See also
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>

Problem with scripting in SQL 2005 after SP2

Hello everyone,
We hit a snag after installing SP2 for SQL Server 2005 Standard. Basically,
when we want to script the database the server takes ages to determine
objects in the database and then it takes another age to actually script the
objects.
Before the SP2 scripting was lightning fast. Now we dread any change that
would require scripting.
Any help and/or insight much appreciated.
BR,
GAZ
> Before the SP2 scripting was lightning fast. Now we dread any change that
> would require scripting.
I find Red-Gate's SQL Compare to be very fast at generating change scripts.
Yes, it is not free, but it beats Management Studio's output hands down.
Anyway, here's something from Erland that I posted in the .Tools newsgroup
just yesterday:
[vbcol=seagreen]
I haven't tested scripting with SP2 as far as I can recall, but a tip
is that setting the database in forced parameterisation, can speed up
scripting considerably, since MgmtStudio uses inlined parameter values.
See also
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968[vbcol=seagreen]
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thanks for the tip. Changing the property Parametarisation to Forced has
resolved the issue. Scripting is a pleasurable task once again.
Thanks.
GAZ
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O$UD4VcgHHA.284@.TK2MSFTNGP05.phx.gbl...
> I find Red-Gate's SQL Compare to be very fast at generating change
> scripts. Yes, it is not free, but it beats Management Studio's output
> hands down.
> Anyway, here's something from Erland that I posted in the .Tools newsgroup
> just yesterday:
> I haven't tested scripting with SP2 as far as I can recall, but a tip
> is that setting the database in forced parameterisation, can speed up
> scripting considerably, since MgmtStudio uses inlined parameter values.
> See also
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>

Problem with scripting in SQL 2005 after SP2

Hello everyone,
We hit a snag after installing SP2 for SQL Server 2005 Standard. Basically,
when we want to script the database the server takes ages to determine
objects in the database and then it takes another age to actually script the
objects.
Before the SP2 scripting was lightning fast. Now we dread any change that
would require scripting.
Any help and/or insight much appreciated.
BR,
GAZ> Before the SP2 scripting was lightning fast. Now we dread any change that
> would require scripting.
I find Red-Gate's SQL Compare to be very fast at generating change scripts.
Yes, it is not free, but it beats Management Studio's output hands down.
Anyway, here's something from Erland that I posted in the .Tools newsgroup
just yesterday:
[vbcol=seagreen]
I haven't tested scripting with SP2 as far as I can recall, but a tip
is that setting the database in forced parameterisation, can speed up
scripting considerably, since MgmtStudio uses inlined parameter values.
See also
http://connect.microsoft.com/SQLSer...edbackID=247968[vbcol=se
agreen]
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Thanks for the tip. Changing the property Parametarisation to Forced has
resolved the issue. Scripting is a pleasurable task once again.
Thanks.
GAZ
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:O$UD4VcgHHA.284@.TK2MSFTNGP05.phx.gbl...
> I find Red-Gate's SQL Compare to be very fast at generating change
> scripts. Yes, it is not free, but it beats Management Studio's output
> hands down.
> Anyway, here's something from Erland that I posted in the .Tools newsgroup
> just yesterday:
>
> I haven't tested scripting with SP2 as far as I can recall, but a tip
> is that setting the database in forced parameterisation, can speed up
> scripting considerably, since MgmtStudio uses inlined parameter values.
> See also
> http://connect.microsoft.com/SQLSer...=247
968
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>

Problem with scripting in SQL 2005 after SP2

Hello everyone,
We hit a snag after installing SP2 for SQL Server 2005 Standard. Basically,
when we want to script the database the server takes ages to determine
objects in the database and then it takes another age to actually script the
objects.
Before the SP2 scripting was lightning fast. Now we dread any change that
would require scripting.
Any help and/or insight much appreciated.
BR,
GAZ> Before the SP2 scripting was lightning fast. Now we dread any change that
> would require scripting.
I find Red-Gate's SQL Compare to be very fast at generating change scripts.
Yes, it is not free, but it beats Management Studio's output hands down.
Anyway, here's something from Erland that I posted in the .Tools newsgroup
just yesterday:
I haven't tested scripting with SP2 as far as I can recall, but a tip
is that setting the database in forced parameterisation, can speed up
scripting considerably, since MgmtStudio uses inlined parameter values.
See also
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Thanks for the tip. Changing the property Parametarisation to Forced has
resolved the issue. Scripting is a pleasurable task once again.
Thanks.
GAZ
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O$UD4VcgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> Before the SP2 scripting was lightning fast. Now we dread any change that
>> would require scripting.
> I find Red-Gate's SQL Compare to be very fast at generating change
> scripts. Yes, it is not free, but it beats Management Studio's output
> hands down.
> Anyway, here's something from Erland that I posted in the .Tools newsgroup
> just yesterday:
> I haven't tested scripting with SP2 as far as I can recall, but a tip
> is that setting the database in forced parameterisation, can speed up
> scripting considerably, since MgmtStudio uses inlined parameter values.
> See also
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>

Problem with script

We've got a couple of scripts that we routinely run on several SQL
servers, and they usually run fine. But we're trying to run them on one
particular server, and we're running into problems. I'm no expert with
SQL, but two DBA's who I work with have spent hours on this and they're
stumped, so I don't feel so stupid. Maybe someone here can help.

Basically, we've got a .bat file that calls a .sql file. Within the
..bat file, isql is called a couple of times with no problems. Within
the .sql file, there are several lines like:

exec master..xp_cmdshell @.@.cmd

@.@.cmd has previously been defined as a text string that begins with
isql.

Whenever this runs, we get the error:

'isql' is not recognized as an internal or external command,
operable program or batch file.

In other words, it can't find the file isql.exe.

We already made sure that the Windows PATH variable for the user
running the .bat file includes the folder that has isql.exe in it.
Someone said that they once had a problem where that folder had to be
the first folder in the PATH variable in order to recognize isql, so we
even made that change (and logged off and back in, then checked the
path from a DOS prompt to make sure the change took effect).

Anyone know anything else to check that would explain why our sql
script would be unable to find the isql.exe command? Any help would be
greatly appreciated.

--RichardOk, I've got another detail that I was just looking at that seems like
it might be relevant. The initial .bat file uses isql to run the .sql
file. So maybe that has something to do with why the .sql file (run
from within isql) can't access isql? As I said, this works on most
other servers, so it's got to be some sort of security or something.

Would it the .sql file run from within isql use the same user security
as the .bat file that ran it? Or does it have seperate user access?

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> Whenever this runs, we get the error:
> 'isql' is not recognized as an internal or external command,
> operable program or batch file.
> In other words, it can't find the file isql.exe.
> We already made sure that the Windows PATH variable for the user
> running the .bat file includes the folder that has isql.exe in it.

That's does count here. What counts is how the PATH for the user
under which SQL Server itself looks like.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> > Whenever this runs, we get the error:
> > 'isql' is not recognized as an internal or external command,
> > operable program or batch file.
> > In other words, it can't find the file isql.exe.
> > We already made sure that the Windows PATH variable for the user
> > running the .bat file includes the folder that has isql.exe in it.
> That's does count here. What counts is how the PATH for the user
> under which SQL Server itself looks like.

That's the part I'm having trouble figuring out, I think. What user's
PATH would it be using when calling isql from within a .sql script that
was run by isql within a .bat file that was kicked off by a user?
Apparently, it's not using the PATH of the user who ran the .bat file.
I just don't know what user's PATH is being used.

As a temporary solution, I copied isql.exe to C:\Windows\System32,
since I know that'll be in the PATH of all users, and that worked.

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> That's the part I'm having trouble figuring out, I think. What user's
> PATH would it be using when calling isql from within a .sql script that
> was run by isql within a .bat file that was kicked off by a user?
> Apparently, it's not using the PATH of the user who ran the .bat file.
> I just don't know what user's PATH is being used.

Just like any other Windows process, SQL Server logs into to Windows
with a user and a password. Ot it logs in as LocalSystem.

To find out how SQL Server, do this on the server machine: In Control
Panel find Administrative Tools. Go there, and find the Services applet.
Open Services, and find the SQL Server service. Double-click it to see
Properties. Log On information is on the second tab.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> > That's the part I'm having trouble figuring out, I think. What user's
> > PATH would it be using when calling isql from within a .sql script that
> > was run by isql within a .bat file that was kicked off by a user?
> > Apparently, it's not using the PATH of the user who ran the .bat file.
> > I just don't know what user's PATH is being used.
> Just like any other Windows process, SQL Server logs into to Windows
> with a user and a password. Ot it logs in as LocalSystem.
> To find out how SQL Server, do this on the server machine: In Control
> Panel find Administrative Tools. Go there, and find the Services applet.
> Open Services, and find the SQL Server service. Double-click it to see
> Properties. Log On information is on the second tab.

Because I know that the .bat file has the security access of the person
who started it, I thought the sql script run by the bat file would, as
well. But apparently, it always has the security access of the SQL
service, which I didn't realize, but it makes perfect sense. Thanks for
the clarification.

I checked, and the SQL service does log on as LocalSystem. So now for
the next obvious question: Since LocalSystem isn't a real user, how do
I change its PATH variable to include the folder that contains isql.exe
and other necessary SQL commands? I've hunted around a little, and I
haven't been able to find anything on changing the settings of
LocalSystem. I only know how to change the PATH of the username that
I'm logged in as.

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> I checked, and the SQL service does log on as LocalSystem. So now for
> the next obvious question: Since LocalSystem isn't a real user, how do
> I change its PATH variable to include the folder that contains isql.exe
> and other necessary SQL commands? I've hunted around a little, and I
> haven't been able to find anything on changing the settings of
> LocalSystem. I only know how to change the PATH of the username that
> I'm logged in as.

How to change the path for LocalSystem sounds like a question for
a Windows newsgroup. :-)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with Script

Hey all,
Can anyone explain me why this doesnt work:
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
*****declare show_defrag cursor for
exec ('select [name] from ' + @.dbname
+ '..sysobjects where type =''U''') for read only *** how
come i cannot use the variable @.dbname in the second
cursor without getting an error? Any workarround?
Please help,
Thanxs> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
Do the whole lot inside dynamic SQL:
SET @.sql = 'DECLARE show_defrag cursor for SELECT "name" FROM ' + @.dbname +
'..sysobjects WHERE ...'
EXEC(@.sql)
However, you don't have to loop inside the database for DBCC SHOWCONTIG.
Just use DBCC SHOWCONTIG at the database level using the WITH TABLERESULTS
option.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> Hey all,
> Can anyone explain me why this doesnt work:
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
> Thanxs|||It dont seem to work... the process just
stays "pending"... Here is the whole script.. any help
would be greatful:
set nocount on
CREATE TABLE #aux (
[ObjectName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Pages] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MaximumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageRecordSize] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[FowardedRecords] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Extents] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentSwitches] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageFreeBytes] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AveragePageDensity] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDensity] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BestCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
declare @.sql varchar (300)
SET @.sql = 'DECLARE show_defrag cursor for
SELECT [name] FROM ' + @.dbname
+ '..sysobjects WHERE type = ''U'' for read only'
declare @.objectname varchar(200)
declare @.msg varchar(200)
EXEC(@.sql)
--declare show_defrag cursor for
--select [name] from ..sysobjects where type = 'U'
for read only
open show_defrag
fetch next from show_defrag into @.objectname
while @.@.fetch_status = 0
Begin
INSERT INTO #aux
EXEC ('DBCC SHOWCONTIG (' + @.objectname
+ ') WITH TABLERESULTS')
if exists (select Objectname from #aux where
Objectname = @.objectname and
ExtentSwitches > Extents or
ScanDensity < cast (95.0 as Decimal) or
LogicalFragmentation > cast (10.0 as Decimal) or
ExtentFragmentation > cast (10.0 as Decimal))
Begin
set @.msg = 'The table ' + @.objectname + ' in
database' + @.dbname + ' is fragmented'
Print @.msg
--exec master..xp_logevent 51515, @.msg,
informational
End
fetch next from db_defrag into @.dbname
fetch next from show_defrag into @.objectname
End
deallocate db_defrag
deallocate show_defrag
drop table #aux
set nocount off
>--Original Message--
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>Do the whole lot inside dynamic SQL:
>SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
FROM ' + @.dbname +
>'..sysobjects WHERE ...'
>EXEC(@.sql)
>However, you don't have to loop inside the database for
DBCC SHOWCONTIG.
>Just use DBCC SHOWCONTIG at the database level using the
WITH TABLERESULTS
>option.
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
>> Hey all,
>> Can anyone explain me why this doesnt work:
>> declare db_defrag cursor for
>> select [name] from master..sysdatabases where dbid
>> NOT IN (1,2,3,4,5,6) for read only
>> declare @.dbname varchar(50)
>> open db_defrag
>> fetch next from db_defrag into @.dbname
>> while @.@.fetch_status = 0
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>> Thanxs
>
>.
>|||One thing I see that that inside your WHILE, you need BEGIN and END:
WHILE ...
BEGIN
...
...
FETCH NEXT FROM ...
END
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:0b9501c3adb6$987ae5b0$a101280a@.phx.gbl...
> It dont seem to work... the process just
> stays "pending"... Here is the whole script.. any help
> would be greatful:
>
>
> set nocount on
> CREATE TABLE #aux (
> [ObjectName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ObjectId] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexId] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Pages] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Rows] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MinimumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaximumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageRecordSize] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [FowardedRecords] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extents] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentSwitches] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageFreeBytes] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AveragePageDensity] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ScanDensity] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [BestCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActualCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogicalFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> declare @.sql varchar (300)
> SET @.sql = 'DECLARE show_defrag cursor for
> SELECT [name] FROM ' + @.dbname
> + '..sysobjects WHERE type = ''U'' for read only'
> declare @.objectname varchar(200)
> declare @.msg varchar(200)
> EXEC(@.sql)
>
> --declare show_defrag cursor for
> --select [name] from ..sysobjects where type = 'U'
> for read only
>
> open show_defrag
> fetch next from show_defrag into @.objectname
> while @.@.fetch_status = 0
> Begin
> INSERT INTO #aux
> EXEC ('DBCC SHOWCONTIG (' + @.objectname
> + ') WITH TABLERESULTS')
>
> if exists (select Objectname from #aux where
> Objectname = @.objectname and
> ExtentSwitches > Extents or
> ScanDensity < cast (95.0 as Decimal) or
> LogicalFragmentation > cast (10.0 as Decimal) or
> ExtentFragmentation > cast (10.0 as Decimal))
> Begin
> set @.msg = 'The table ' + @.objectname + ' in
> database' + @.dbname + ' is fragmented'
> Print @.msg
> --exec master..xp_logevent 51515, @.msg,
> informational
> End
> fetch next from db_defrag into @.dbname
> fetch next from show_defrag into @.objectname
> End
> deallocate db_defrag
> deallocate show_defrag
> drop table #aux
>
> set nocount off
>
>
>
> >--Original Message--
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >
> >Do the whole lot inside dynamic SQL:
> >
> >SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
> FROM ' + @.dbname +
> >'..sysobjects WHERE ...'
> >EXEC(@.sql)
> >
> >However, you don't have to loop inside the database for
> DBCC SHOWCONTIG.
> >Just use DBCC SHOWCONTIG at the database level using the
> WITH TABLERESULTS
> >option.
> >
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> >> Hey all,
> >>
> >> Can anyone explain me why this doesnt work:
> >>
> >> declare db_defrag cursor for
> >> select [name] from master..sysdatabases where dbid
> >> NOT IN (1,2,3,4,5,6) for read only
> >>
> >> declare @.dbname varchar(50)
> >>
> >> open db_defrag
> >> fetch next from db_defrag into @.dbname
> >>
> >> while @.@.fetch_status = 0
> >>
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >>
> >> Thanxs
> >
> >
> >.
> >

Problem with replication

Dear Friends
I am getting the following problem.
Kindly help
The schema script '\\server2
\f$\sqldatafiles\MSSQL\ReplData\unc\server2_MAINSQ L_MAINSQ
L\20040425110134\ListOTH_8.sch' could not be propagated
to the subscriber.
(Source: Merge Replication Provider (Agent); Error
number: -2147201001)
----
General network error. Check your network documentation.
(Source: AJAY (Data source); Error number: 11)
----
Communication link failure
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
----
general network error means that your network link hiccupped. Rerun your
merge agent.
By any chance are you replicating over the internet?
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:3eaf01c42aa8$d497d890$a001280a@.phx.gbl...
> Dear Friends
> I am getting the following problem.
> Kindly help
> The schema script '\\server2
> \f$\sqldatafiles\MSSQL\ReplData\unc\server2_MAINSQ L_MAINSQ
> L\20040425110134\ListOTH_8.sch' could not be propagated
> to the subscriber.
> (Source: Merge Replication Provider (Agent); Error
> number: -2147201001)
> ----
> ----
> General network error. Check your network documentation.
> (Source: AJAY (Data source); Error number: 11)
> ----
> ----
> Communication link failure
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> ----
> ----

Problem with parameter default when redeploying report

I've written a rss script file to automate publishing of reports to
our server. I've encountered a problem when republishing reports that
have a default value set for a parameter.
If I change the default value of the parameter in the report rdl and
then try to republish, that parameter's default value is not getting
changed on the server. If I add or remove parameters then the server
gets updated correctly. It's only if I change the default value that
the update is not happening.
I've tried with both the CreateReport and SetReportDefinition
functions. The only way I've gotten this to work is to delete the
report and then republish but this is not an acceptable solution
because it also deletes report history and subscriptions.
I'm using RS2005.
Any help is appreciated.Default values for parameters are a little like datasources, in that you
have to explicitly indicate that you want to override a previous definition
of a datasource when you re-publish a report to a server. Basically the idea
is that your testbed, from which you publish, may not be the same as the
server environment, and you want to keep those things separate, and I'm
saying that parameters' defaults are treated like datasources in this
respect.
OK so far?
If you were handling this interactively using the Report Manager interface,
and assuming you have appropriate rights, you know that you can see the Data
Sources and configure them from the Properties tab of a report. Again, the
assumption is not made that the data source information for this report is
re-deployable and automatically written from your test bed.
Similarly, if a report has parameters, when you have selected the Properties
tab, you should see a Parameters item in the left-hand menu along with Data
Sources. Here you can set the default values differently from how they are
currently set -- I don't really understand whether "Override default" works
all the time or not, you will see it in the dialog, though. Never mind.
Here is where you can fix whatever you don't like about how the report got
re-published.
However, you say "I've tried with both the CreateReport and
SetReportDefinition functions", indicating that you are using web services
rather than interactively publishing. I understand this -- just consider
the above explanation a way to conceptualize *why* the parameters work the
way they do and require a separate step, rather than the way you expected.
I think that you may need to use the .SetReportParameters method here,
explicitly providing the new information to indicate that, yes, you want to
change the default values. If not, it may be the .SetProperties method.
I hope this works for you -- if not, you may be able to use the explanation
above to figure out the correct web service approach <s>.
>L<
<bruce42@.gmail.com> wrote in message
news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
> I've written a rss script file to automate publishing of reports to
> our server. I've encountered a problem when republishing reports that
> have a default value set for a parameter.
> If I change the default value of the parameter in the report rdl and
> then try to republish, that parameter's default value is not getting
> changed on the server. If I add or remove parameters then the server
> gets updated correctly. It's only if I change the default value that
> the update is not happening.
> I've tried with both the CreateReport and SetReportDefinition
> functions. The only way I've gotten this to work is to delete the
> report and then republish but this is not an acceptable solution
> because it also deletes report history and subscriptions.
> I'm using RS2005.
> Any help is appreciated.
>|||So, from what you're saying, it's intentional that the parameter
defaults are not being overriden. You mention an "override defaults",
can you be more specific? I can see an "OverwriteDataSources" if I
publish straight from VS, but I've found nothing regarding overwriting
parameter defaults.
I'm familiar with the SetReportParameters method, what I'm trying to
do is avoid writing specific scripts for each report that I need to
publish. The rss script I use to publish now is a generic script that
I can use against any of my reports. Setting the default values
manually via the report manager interface is not really an option for
me. For one reason, it introduces the possibility of me setting
values differently than what was actually used in our test
environment, and two, we are using query based defaults and the report
manager interface doesn't give you enough detail to even be able to
make these changes (i.e. it doesn't show dataset or value field).
Currently the best idea I have for how to resolve this issue is to
publish my report to a temporary location (where it does not already
exist), loop through all of the parameters and capture the defaults, I
can then publish the report to it's normal location and do a
SetReportParameters using the default values I collected. I'm not
especially happy with this approach, it feels a little kludgy to me,
but at least it keeps me from having to write report specific rss
scripts.
Any other ideas?
thanks for your response...
-bruce
On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Defaultvalues for parameters are a little like datasources, in that you
> have to explicitly indicate that you want to override a previous definition
> of a datasource when you re-publish a report to a server. Basically the idea
> is that your testbed, from which you publish, may not be the same as the
> server environment, and you want to keep those things separate, and I'm
> saying that parameters' defaults are treated like datasources in this
> respect.
> OK so far?
> If you were handling this interactively using the Report Manager interface,
> and assuming you have appropriate rights, you know that you can see the Data
> Sources and configure them from the Properties tab of a report. Again, the
> assumption is not made that the data source information for this report is
> re-deployable and automatically written from your test bed.
> Similarly, if a report has parameters, when you have selected the Properties
> tab, you should see a Parameters item in the left-hand menu along with Data
> Sources. Here you can set thedefaultvalues differently from how they are
> currently set -- I don't really understand whether "Overridedefault" works
> all the time or not, you will see it in the dialog, though. Never mind.
> Here is where you can fix whatever you don't like about how the report got
> re-published.
> However, you say "I've tried with both the CreateReport and
> SetReportDefinition functions", indicating that you are using web services
> rather than interactively publishing. I understand this -- just consider
> the above explanation a way to conceptualize *why* the parameters work the
> way they do and require a separate step, rather than the way you expected.
> I think that you may need to use the .SetReportParameters method here,
> explicitly providing the new information to indicate that, yes, you want to
> change thedefaultvalues. If not, it may be the .SetProperties method.
> I hope this works for you -- if not, you may be able to use the explanation
> above to figure out the correct web service approach <s>.
> >L<
> <bruc...@.gmail.com> wrote in message
> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>
> > I've written a rss script file to automate publishing of reports to
> > our server. I've encountered a problem when republishing reports that
> > have adefaultvalue set for aparameter.
> > If I change thedefaultvalue of theparameterin the report rdl and
> > then try to republish, thatparameter'sdefaultvalue is not getting
> > changed on the server. If I add or remove parameters then the server
> > gets updated correctly. It's only if I change thedefaultvalue that
> > the update is not happening.
> > I've tried with both the CreateReport and SetReportDefinition
> > functions. The only way I've gotten this to work is to delete the
> > report and then republish but this is not an acceptable solution
> > because it also deletes report history and subscriptions.
> > I'm using RS2005.
> > Any help is appreciated.- Hide quoted text -
> - Show quoted text -|||Hi Bruce,
>> Any other ideas?
Forgive my lateness of reply (I will CC your e-mail to make sure you see
this) -- I don't get to the forum all that often.
AFAIK you are correct about not having the Overwrite parameter option to
match the Overwrite datasources when you publish straight from VS. When I
mentioned the "override defaults" I was talking only about the interactive
manager interface.
I do agree with you not only that it is confusing but also that, in most
cases, it is not advisable to set values differently in the test environment
than you would set in production. However -- bear with me, I am trying to
envision what was supposed to be the purpose of this "feature" -- we can
imagine that the designers of this system thought it *was* a good idea to
have an "override defaults" so that you could manage the report differently
on different servers, whether for test versus production or deployment of a
generic report to different customers.
For example there might be a sample size used for the test box that would be
different from production, or some sort of customer-specific value that you
wanted to use to brand a generic report.
Now to address your question...
For your generic script purposes, you might need to do something similar to
reflection to "publish" each report.
So far, I'm just restating something that you may have already tried with
your "publish to a temporary location". You could obviously pull the
parameters out of the appropriate Catalog field on the server, or use
GetReportParameters, if you've done that.
However, you don't really need to do that. Remember that the parameters
exist in the RDL, without publication, as a set of XML nodes. So, without
temporarily publishing anywhere, you should be able to read them out of the
RDL and issue the appropriate calls to set them properly on the target.
I hope this makes sense. If you like, you can e-mail me to discuss further
if it doesn't <g>. Again, I don't get here all that much...
>L<
<bruce42@.gmail.com> wrote in message
news:1176117653.014697.204110@.w1g2000hsg.googlegroups.com...
> So, from what you're saying, it's intentional that the parameter
> defaults are not being overriden. You mention an "override defaults",
> can you be more specific? I can see an "OverwriteDataSources" if I
> publish straight from VS, but I've found nothing regarding overwriting
> parameter defaults.
> I'm familiar with the SetReportParameters method, what I'm trying to
> do is avoid writing specific scripts for each report that I need to
> publish. The rss script I use to publish now is a generic script that
> I can use against any of my reports. Setting the default values
> manually via the report manager interface is not really an option for
> me. For one reason, it introduces the possibility of me setting
> values differently than what was actually used in our test
> environment, and two, we are using query based defaults and the report
> manager interface doesn't give you enough detail to even be able to
> make these changes (i.e. it doesn't show dataset or value field).
> Currently the best idea I have for how to resolve this issue is to
> publish my report to a temporary location (where it does not already
> exist), loop through all of the parameters and capture the defaults, I
> can then publish the report to it's normal location and do a
> SetReportParameters using the default values I collected. I'm not
> especially happy with this approach, it feels a little kludgy to me,
> but at least it keeps me from having to write report specific rss
> scripts.
> Any other ideas?
> thanks for your response...
> -bruce
> On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Defaultvalues for parameters are a little like datasources, in that you
>> have to explicitly indicate that you want to override a previous
>> definition
>> of a datasource when you re-publish a report to a server. Basically the
>> idea
>> is that your testbed, from which you publish, may not be the same as the
>> server environment, and you want to keep those things separate, and I'm
>> saying that parameters' defaults are treated like datasources in this
>> respect.
>> OK so far?
>> If you were handling this interactively using the Report Manager
>> interface,
>> and assuming you have appropriate rights, you know that you can see the
>> Data
>> Sources and configure them from the Properties tab of a report. Again,
>> the
>> assumption is not made that the data source information for this report
>> is
>> re-deployable and automatically written from your test bed.
>> Similarly, if a report has parameters, when you have selected the
>> Properties
>> tab, you should see a Parameters item in the left-hand menu along with
>> Data
>> Sources. Here you can set thedefaultvalues differently from how they are
>> currently set -- I don't really understand whether "Overridedefault"
>> works
>> all the time or not, you will see it in the dialog, though. Never mind.
>> Here is where you can fix whatever you don't like about how the report
>> got
>> re-published.
>> However, you say "I've tried with both the CreateReport and
>> SetReportDefinition functions", indicating that you are using web
>> services
>> rather than interactively publishing. I understand this -- just consider
>> the above explanation a way to conceptualize *why* the parameters work
>> the
>> way they do and require a separate step, rather than the way you
>> expected.
>> I think that you may need to use the .SetReportParameters method here,
>> explicitly providing the new information to indicate that, yes, you want
>> to
>> change thedefaultvalues. If not, it may be the .SetProperties method.
>> I hope this works for you -- if not, you may be able to use the
>> explanation
>> above to figure out the correct web service approach <s>.
>> >L<
>> <bruc...@.gmail.com> wrote in message
>> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>>
>> > I've written a rss script file to automate publishing of reports to
>> > our server. I've encountered a problem when republishing reports that
>> > have adefaultvalue set for aparameter.
>> > If I change thedefaultvalue of theparameterin the report rdl and
>> > then try to republish, thatparameter'sdefaultvalue is not getting
>> > changed on the server. If I add or remove parameters then the server
>> > gets updated correctly. It's only if I change thedefaultvalue that
>> > the update is not happening.
>> > I've tried with both the CreateReport and SetReportDefinition
>> > functions. The only way I've gotten this to work is to delete the
>> > report and then republish but this is not an acceptable solution
>> > because it also deletes report history and subscriptions.
>> > I'm using RS2005.
>> > Any help is appreciated.- Hide quoted text -
>> - Show quoted text -
>