changing the default value

Hi,
I am facing a problem. Our current database aontains more than 50 tables
having a datefield, whose default value is set as 'GetUTCDate()'. Now what i
need is a Query to replace those default value with GetDate() without any
dataloss. We dont care abt the time differrence.
Thanking u in advance
regards
LaraLara
Drop constraint and create a new one
"Lara" <lara@.hotmail.com> wrote in message
news:OoiUym$lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am facing a problem. Our current database aontains more than 50 tables
> having a datefield, whose default value is set as 'GetUTCDate()'. Now what
> i
> need is a Query to replace those default value with GetDate() without any
> dataloss. We dont care abt the time differrence.
> Thanking u in advance
> regards
> Lara
>

Changing the current database inside a stored procedure

Hi all,

I need to change the current database from within a stored procedure or something that can be called by a stored procedure. I know the use statement is ineffective in these circumstances. Just need to know if there's a method out there I can employ.

Any help would be really appreciated.

Richyou don't need to change DBs while in a stored procedure just reference the object(s) using the three part object name: <DATABASE>.<OWNER>.<OBJECT NAME>.|||Thanks Paul...one of those times when you miss the blind obvious.

Cheers

Rich

Changing sql dbo user password (will this effect current replication)

We have a subscriber that want's to change the dbo password for the
replication user. Will this effect current publications to that
subscriber?
tia
-comb
Comb,
dbo is a database user rather than a login, so whose password is being
changed? Also, are you using SQL or trusted authentication for the
subscription?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, this account that you are using will have to be updated at the
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1d53fb2b4158876c9896af@.news.newsreader.co m...
> We have a subscriber that want's to change the dbo password for the
> replication user. Will this effect current publications to that
> subscriber?
> tia
> -comb

Changing parameters causes refresh...

Hey Guys,

I have two parameters on a report, which are independent of one another, yet when I change one, the current report refreshes, and I'm left with a blank screen. What is causing the refresh behavior on this report? It appears as though it is treating them as cascading parameters, but I don’t use one to filter the other. Since the report has default values, when first navigating to the report, it displays just fine. I would like the user to be able to change the first parameter without the report refreshing to a blank screen. Once it refreshes, they can click on the “View Report” button and see the report, but I would like for the current report to stay there, which is the case on my other reports, until they click the button. Any ideas?

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

Data is retrieved from OLAP cube, some parameters are also retrieved from olap cube and I have also some non-queried parameters and all parameters have non- queried defaults with expressions and custom codes.

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

--

-

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

-

My Main Data Set :

"WITH SET [Period1] AS '{"+ Parameters!px_Time_Hier1.Value +"}' MEMBER [Measures].[SalesP1] AS 'SUM([Period1] ,[Measures].Sales)' MEMBER [Measures].[SalesP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].Sales)' MEMBER [Measures].[SalesP3] AS 'SUM({ParallelPeriod(Year)} ,[Measures].Sales)' MEMBER [Measures].[SalesP4] AS 'SUM({PeriodsToDate("+Parameters!CompareBy.Label+",ParallelPeriod(Year))} ,[Measures].Sales)' MEMBER [Measures].[CIPP1] AS 'SUM([Period1] ,[Measures].CIP)' MEMBER [Measures].[CIPP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].CIP)' MEMBER [Measures].[Open Orders] AS 'SUM({[Time].[All Time]},[Measures].[Open Order])' MEMBER [Sales Category].[SalesCat] AS 'Aggregate({"+ Parameters!SalesCat.Value +"})' SELECT NONEMPTYCROSSJOIN({" + Parameters!px_Subsidiary_Hier.Value +"},{[Country].[Purc Country].Members} ,{Descendants([Item].[All Item],[Item].[Commodity],LEAVES)} ) ON ROWS, {[Measures].[SalesP1], [Measures].[CIPP1],[Measures].[SalesP2], [Measures].[CIPP2],[Measures].[SalesP3],[Measures].[SalesP4],[Measures].[Open Orders]} ON COLUMNS , [Period1] ON PAGES FROM Cost WHERE ([Sales Category].[SalesCat], " + Parameters!px_Currency_Hier1.Value + ")"

|||

It sounds like the dataset queries used as valid value / default value of the second parameter use complex expressions (e.g. with string concatenation) and could potentially call custom assemblies etc. which could indirectly reference the value of the first report parameter - this is why the parameters are treated as cascading parameters.

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

|||Actually, there were no complex expressions involved. The datasets only took one parameter, which was a hard-coded number. I tried switching the the order of the parameters, and it didn't help. What ended up helping was to create a report parameter specifically for the dataset to pass as its parameter, instead of using the hard-coded parameter directly in the dataset. Not sure why that helped, but it did.|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

There is no referenses between my parameters and non of them has 'hardcoded' default-values (except for some that defaults to null). However, some datasets uses the parameters as input to stored procedures.

When the exact same report is viewed from a reportViewer in a winform the report is left intact after the parameters are changed and the report only updates if the 'view report' or 'refresh' button is pressed. So, here I'm not left with a blanc (white) report.

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

I have two parameters on a report, which are independent of one another, yet when I change one, the current report refreshes, and I'm left with a blank screen. What is causing the refresh behavior on this report? It appears as though it is treating them as cascading parameters, but I don’t use one to filter the other. Since the report has default values, when first navigating to the report, it displays just fine. I would like the user to be able to change the first parameter without the report refreshing to a blank screen. Once it refreshes, they can click on the “View Report” button and see the report, but I would like for the current report to stay there, which is the case on my other reports, until they click the button. Any ideas?

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

Data is retrieved from OLAP cube, some parameters are also retrieved from olap cube and I have also some non-queried parameters and all parameters have non- queried defaults with expressions and custom codes.

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

--

-

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

-

My Main Data Set :

"WITH SET [Period1] AS '{"+ Parameters!px_Time_Hier1.Value +"}' MEMBER [Measures].[SalesP1] AS 'SUM([Period1] ,[Measures].Sales)' MEMBER [Measures].[SalesP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].Sales)' MEMBER [Measures].[SalesP3] AS 'SUM({ParallelPeriod(Year)} ,[Measures].Sales)' MEMBER [Measures].[SalesP4] AS 'SUM({PeriodsToDate("+Parameters!CompareBy.Label+",ParallelPeriod(Year))} ,[Measures].Sales)' MEMBER [Measures].[CIPP1] AS 'SUM([Period1] ,[Measures].CIP)' MEMBER [Measures].[CIPP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].CIP)' MEMBER [Measures].[Open Orders] AS 'SUM({[Time].[All Time]},[Measures].[Open Order])' MEMBER [Sales Category].[SalesCat] AS 'Aggregate({"+ Parameters!SalesCat.Value +"})' SELECT NONEMPTYCROSSJOIN({" + Parameters!px_Subsidiary_Hier.Value +"},{[Country].[Purc Country].Members} ,{Descendants([Item].[All Item],[Item].[Commodity],LEAVES)} ) ON ROWS, {[Measures].[SalesP1], [Measures].[CIPP1],[Measures].[SalesP2], [Measures].[CIPP2],[Measures].[SalesP3],[Measures].[SalesP4],[Measures].[Open Orders]} ON COLUMNS , [Period1] ON PAGES FROM Cost WHERE ([Sales Category].[SalesCat], " + Parameters!px_Currency_Hier1.Value + ")"

|||

It sounds like the dataset queries used as valid value / default value of the second parameter use complex expressions (e.g. with string concatenation) and could potentially call custom assemblies etc. which could indirectly reference the value of the first report parameter - this is why the parameters are treated as cascading parameters.

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

|||Actually, there were no complex expressions involved. The datasets only took one parameter, which was a hard-coded number. I tried switching the the order of the parameters, and it didn't help. What ended up helping was to create a report parameter specifically for the dataset to pass as its parameter, instead of using the hard-coded parameter directly in the dataset. Not sure why that helped, but it did.|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

There is no referenses between my parameters and non of them has 'hardcoded' default-values (except for some that defaults to null). However, some datasets uses the parameters as input to stored procedures.

When the exact same report is viewed from a reportViewer in a winform the report is left intact after the parameters are changed and the report only updates if the 'view report' or 'refresh' button is pressed. So, here I'm not left with a blanc (white) report.

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

I have two parameters on a report, which are independent of one another, yet when I change one, the current report refreshes, and I'm left with a blank screen. What is causing the refresh behavior on this report? It appears as though it is treating them as cascading parameters, but I don’t use one to filter the other. Since the report has default values, when first navigating to the report, it displays just fine. I would like the user to be able to change the first parameter without the report refreshing to a blank screen. Once it refreshes, they can click on the “View Report” button and see the report, but I would like for the current report to stay there, which is the case on my other reports, until they click the button. Any ideas?

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

Data is retrieved from OLAP cube, some parameters are also retrieved from olap cube and I have also some non-queried parameters and all parameters have non- queried defaults with expressions and custom codes.

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

--

-

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

-

My Main Data Set :

"WITH SET [Period1] AS '{"+ Parameters!px_Time_Hier1.Value +"}' MEMBER [Measures].[SalesP1] AS 'SUM([Period1] ,[Measures].Sales)' MEMBER [Measures].[SalesP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].Sales)' MEMBER [Measures].[SalesP3] AS 'SUM({ParallelPeriod(Year)} ,[Measures].Sales)' MEMBER [Measures].[SalesP4] AS 'SUM({PeriodsToDate("+Parameters!CompareBy.Label+",ParallelPeriod(Year))} ,[Measures].Sales)' MEMBER [Measures].[CIPP1] AS 'SUM([Period1] ,[Measures].CIP)' MEMBER [Measures].[CIPP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].CIP)' MEMBER [Measures].[Open Orders] AS 'SUM({[Time].[All Time]},[Measures].[Open Order])' MEMBER [Sales Category].[SalesCat] AS 'Aggregate({"+ Parameters!SalesCat.Value +"})' SELECT NONEMPTYCROSSJOIN({" + Parameters!px_Subsidiary_Hier.Value +"},{[Country].[Purc Country].Members} ,{Descendants([Item].[All Item],[Item].[Commodity],LEAVES)} ) ON ROWS, {[Measures].[SalesP1], [Measures].[CIPP1],[Measures].[SalesP2], [Measures].[CIPP2],[Measures].[SalesP3],[Measures].[SalesP4],[Measures].[Open Orders]} ON COLUMNS , [Period1] ON PAGES FROM Cost WHERE ([Sales Category].[SalesCat], " + Parameters!px_Currency_Hier1.Value + ")"

|||

It sounds like the dataset queries used as valid value / default value of the second parameter use complex expressions (e.g. with string concatenation) and could potentially call custom assemblies etc. which could indirectly reference the value of the first report parameter - this is why the parameters are treated as cascading parameters.

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

|||Actually, there were no complex expressions involved. The datasets only took one parameter, which was a hard-coded number. I tried switching the the order of the parameters, and it didn't help. What ended up helping was to create a report parameter specifically for the dataset to pass as its parameter, instead of using the hard-coded parameter directly in the dataset. Not sure why that helped, but it did.|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

There is no referenses between my parameters and non of them has 'hardcoded' default-values (except for some that defaults to null). However, some datasets uses the parameters as input to stored procedures.

When the exact same report is viewed from a reportViewer in a winform the report is left intact after the parameters are changed and the report only updates if the 'view report' or 'refresh' button is pressed. So, here I'm not left with a blanc (white) report.

Can I solve this problem in some way?

Regards Andreas

Changing name of SQL Server

I need to change the server name of my SQL Server to one of a current SQl
server that is being decomissioned since the newer server is faster, more
disk space, etc. Do I need to do anything with the named instances on the
server I am renaming, as i want to make sure that System DSN's and such stil
l
work when looking at the renamed server.
Thanks,
SeanHi
Check out http://support.microsoft.com/defaul...kb;en-us;303774
John
"Sean Donnelly" wrote:

> I need to change the server name of my SQL Server to one of a current SQl
> server that is being decomissioned since the newer server is faster, more
> disk space, etc. Do I need to do anything with the named instances on the
> server I am renaming, as i want to make sure that System DSN's and such st
ill
> work when looking at the renamed server.
> Thanks,
> Sean|||John,
Thanks for taking the time, this is perfect!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out http://support.microsoft.com/defaul...kb;en-us;303774
> John
> "Sean Donnelly" wrote:
>

Changing name of SQL Server

I need to change the server name of my SQL Server to one of a current SQl
server that is being decomissioned since the newer server is faster, more
disk space, etc. Do I need to do anything with the named instances on the
server I am renaming, as i want to make sure that System DSN's and such still
work when looking at the renamed server.
Thanks,
Sean
Hi
Check out http://support.microsoft.com/default...b;en-us;303774
John
"Sean Donnelly" wrote:

> I need to change the server name of my SQL Server to one of a current SQl
> server that is being decomissioned since the newer server is faster, more
> disk space, etc. Do I need to do anything with the named instances on the
> server I am renaming, as i want to make sure that System DSN's and such still
> work when looking at the renamed server.
> Thanks,
> Sean
|||John,
Thanks for taking the time, this is perfect!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out http://support.microsoft.com/default...b;en-us;303774
> John
> "Sean Donnelly" wrote:

Changing name of SQL Server

I need to change the server name of my SQL Server to one of a current SQl
server that is being decomissioned since the newer server is faster, more
disk space, etc. Do I need to do anything with the named instances on the
server I am renaming, as i want to make sure that System DSN's and such still
work when looking at the renamed server.
Thanks,
SeanHi
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
John
"Sean Donnelly" wrote:
> I need to change the server name of my SQL Server to one of a current SQl
> server that is being decomissioned since the newer server is faster, more
> disk space, etc. Do I need to do anything with the named instances on the
> server I am renaming, as i want to make sure that System DSN's and such still
> work when looking at the renamed server.
> Thanks,
> Sean|||John,
Thanks for taking the time, this is perfect!
"John Bell" wrote:
> Hi
> Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
> John
> "Sean Donnelly" wrote:
> > I need to change the server name of my SQL Server to one of a current SQl
> > server that is being decomissioned since the newer server is faster, more
> > disk space, etc. Do I need to do anything with the named instances on the
> > server I am renaming, as i want to make sure that System DSN's and such still
> > work when looking at the renamed server.
> >
> > Thanks,
> > Sean

Changing file grouth automaticly

Hello there
Is there a way to build sql script that changes all the file growth of all
the databases in the current server?Roy Goldhammer a crit :
> Hello there
> Is there a way to build sql script that changes all the file growth of all
> the databases in the current server?
>
use the hidden procedure sp_MSforeachdb and inside the string use the
character ? in place of db name.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Whell SQLPro
I can't find any help on this store procedure.
Can you give me the exact syntax for using sp_MSforeachdb?
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
> Roy Goldhammer a crit :
> use the hidden procedure sp_MSforeachdb and inside the string use the
> character ? in place of db name.
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||It's actually not hidden, and can be found in the master database. However,
it is not documented (at least not officially).
http://www.dbazine.com/sql/sql-articles/larsen5
http://groups.google.com/groups?q=sp_MSforeachdb&hl=en
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eXRt70DTGHA.2088@.TK2MSFTNGP14.phx.gbl...
> Whell SQLPro
> I can't find any help on this store procedure.
> Can you give me the exact syntax for using sp_MSforeachdb?
> "SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
> news:%23tHAQnDTGHA.5172@.TK2MSFTNGP12.phx.gbl...
>

Changing current database during local tx or xa tx

What are the consequences of changing the current database (using "use dbnam
e")
during a transaction?
If I modify multiple databases (on the same Sql Server instance) during a
transaction, will all changes be done in the same transaction (and commited/
rollbacked
atomically)?
Does using XA tx change the answer to the question?Taras Tielkes (taras.tielkes@.gmail.com) writes:
> What are the consequences of changing the current database (using "use
> dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance) during
> a transaction, will all changes be done in the same transaction (and
> commited/rollbacked atomically)?
Yes.

> Does using XA tx change the answer to the question?
XA?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Taras Tielkes wrote:
> What are the consequences of changing the current database (using
> "use dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance)
> during a transaction, will all changes be done in the same
> transaction (and commited/rollbacked atomically)?
> Does using XA tx change the answer to the question?
Yes. Same transaction. But you cannot use USE statements in stored
procedures or triggers. There's a better way, however. USE, which
physically changes a user's context, is an expensive operation. You're
just better off fully-qualifying the objects you are modifying. Then you
don't have to switch databases:
For example:
Update MyDB.dbo.MyTable SET ...
David Gugick
Quest Software
www.imceda.com
www.quest.com