Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default name for domain

Hello, I'm creating a script that will create logins for groups of a
windows machine, and I want to know if there's a way for me not to have to
specify the machine domain name.
Something like the '.\sqlexpress' name to identify the sqlexpress instance
in running in the local machine:
CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
Does something like this exist?
Regards,
Pablo MontillaOne option is to use variables and dynamically build the
SQL statements to create the logins. You can get the server
name using
select @.@.servername
You can use that to build the login names specific for a
machine.
-Sue
On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
<melkor@.odyssey.com.uy> wrote:

>Hello, I'm creating a script that will create logins for groups of a
>windows machine, and I want to know if there's a way for me not to have to
>specify the machine domain name.
>Something like the '.\sqlexpress' name to identify the sqlexpress instance
>in running in the local machine:
>CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
>Does something like this exist?
>Regards,
>Pablo Montilla
>|||Many thanks, I'll try that.
Pablo
On Wed, 21 Mar 2007 22:00:19 -0300, Sue Hoegemeier <Sue_H@.nomail.please>
wrote:

> One option is to use variables and dynamically build the
> SQL statements to create the logins. You can get the server
> name using
> select @.@.servername
> You can use that to build the login names specific for a
> machine.
> -Sue
> On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
> <melkor@.odyssey.com.uy> wrote:
>
>

Default field to another field value on db level

Hi All,

I need to create a new field on a table and have that field default to another field value in that same table. Is there a way to do this w/ a default constraint rather than adding a trigger to the table? If i can't use a default constraint does anyone have a template trigger i could use? Below is an example of what i'm trying to do (Field_C is the new field and i want it to use Field_A value if no other value is specified on insert). Any help would be greatly appreciated.

alter table FOO add Field_C varchar(50) not null constraint FOO_default DEFAULT Field_A

thanks,
Davethis worked for me.....

CREATE Trigger TRG_FOO_default_INS
on dbo.FOO
for Insert
as

Declare @.default_FieldC varchar(50)

select @.default_FieldC = Field_C from inserted

If (@.default_FieldC is null) or (@.default_FieldC = '')
BEGIN
Update FOO set Field_C = Field_A where PK_ID in (select PK_ID from inserted)
END -- update externalname

default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
Mark
Mark,
I assume you mean they have been assigned the db_owner role? Only one login can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner, then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is supported or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTNGP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>
|||Mark wrote:

> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then have
that group be the dbowner of the db?

default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
MarkMark,
I assume you mean they have been assigned the db_owner role? Only one login
can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner,
then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this
will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is sup
ported or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTN
GP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way t
o
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>|||Mark wrote:

> Several developers have been assigned dbo of a database. Is there a way t
o
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then ha
ve
that group be the dbowner of the db?sql

default dbo to object names instead of user name

Several developers have been assigned dbo of a database. Is there a way to
allow all the objects that we create to all have the dbo ownership by
default, rather than our user name?
I'm aware that we could explicitly type ...
create proc dbo.myprocedurename as
but was hoping this could be the default behavior.
Thanks in advance.
MarkMark,
I assume you mean they have been assigned the db_owner role? Only one login can be the dbo user. There is a
difference between dbo and db_owner.
Assuming that the logins have their own user names and are indeed db_owner, then you have to qualify the owner
when you create the object. Only way around is to use sp_addalias, but this will disappear in future and
there's no GUI support for this. Be careful to read in BOL whether it is supported or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message news:%23GgYl4dMEHA.1392@.TK2MSFTNGP09.phx.gbl...
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
> I'm aware that we could explicitly type ...
> create proc dbo.myprocedurename as
> but was hoping this could be the default behavior.
> Thanks in advance.
> Mark
>|||Mark wrote:
> Several developers have been assigned dbo of a database. Is there a way to
> allow all the objects that we create to all have the dbo ownership by
> default, rather than our user name?
could you put all those developers into a group on the db server and then have
that group be the dbowner of the db?

default database directory

Is it possible like when I create the database so it creates the database in
C:\Data folder and log file at D:\log folder by default. I think we can do
some thing in model database, if yes then can any one let me know how can I
do this ?
thanks
See a post which was open a few days ago:
http://groups.google.de/groups?hl=de...le.com%26rnum%
3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:%23lYGo9QRFHA.600@.TK2MSFTNGP10.phx.gbl...
> Is it possible like when I create the database so it creates the database
> in
> C:\Data folder and log file at D:\log folder by default. I think we can do
> some thing in model database, if yes then can any one let me know how can
> I
> do this ?
> thanks
>
>
|||Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OrLOTBRRFHA.356@.TK2MSFTNGP14.phx.gbl...
> See a post which was open a few days ago:
>
http://groups.google.de/groups?hl=de...0042.191fe 23
8%40posting.google.com&rnum=1&prev=/groups%3Fq%3Ddefault%2Bdirectory%2Bchang
e%2Bsql%2Bserver%26hl%3Dde%26lr%3D%26selm%3D8ef90b 6e.0504130042.191fe238%254
0posting.google.com%26rnum%3D1[vbcol=seagreen]
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:%23lYGo9QRFHA.600@.TK2MSFTNGP10.phx.gbl...
database[vbcol=seagreen]
do[vbcol=seagreen]
can
>

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

Default access for a login

Hi guys,
I create a new login; let's say "aaa" with authentication "SQL Server
Authentication" and no database access specified.
Now... I'm able to connect to my SQL Server 2000 using Query Analyzer with
this "aaa" login and read data/execute stored procedure even I didn't specif
y
any level access. For me looks strange, I was expecting to access a database
after I define a user and permisions using this login. Is this normal?
Thanks!Yes they can connect. In terms of what they can execute
where, that depends.
If a login isn't assigned a database, they will access
databases under the guest account. If guest is in the
database, they have access to the database and whatever
public permissions have been set as well as whatever has
been explicitly granted to the guest user in that database.
Guest always exists in master and tempdb - you can't delete
the account from those databases.
-Sue
On Tue, 27 Sep 2005 14:19:01 -0700, "Radu"
<Radu@.discussions.microsoft.com> wrote:

>Hi guys,
>I create a new login; let's say "aaa" with authentication "SQL Server
>Authentication" and no database access specified.
>Now... I'm able to connect to my SQL Server 2000 using Query Analyzer with
>this "aaa" login and read data/execute stored procedure even I didn't speci
fy
>any level access. For me looks strange, I was expecting to access a databas
e
>after I define a user and permisions using this login. Is this normal?
>
>Thanks!

Dedupe Query

Below, I have some T-SQL to create a table, add some sample records and do a dedupe query. This all works but performance is poor on large data sets and I was wondering if someone had any optimization tips.

Thanks to blindman for helping me develop this version.

Some background: the staging table has undeduped records about users. I want to dedupe and get the record with the most fields. I don't want to mix fields from different records. The staging table is currently unindexed but that can be changed.

CREATE TABLE StagingRecords
(
EmailAddress VARCHAR(75) NULL,
FirstName VARCHAR(255) NULL,
LastName VARCHAR(255) NULL,
StreetAddress VARCHAR(255) NULL,
City VARCHAR(255) NULL,
State VARCHAR(255) NULL,
ZipCode VARCHAR(255) NULL,
RecordID INT IDENTITY NOT NULL
)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'John', 'Doe', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Abe', 'Abelman', NULL, NULL, 'MI', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Zach', 'Zedcynsky', NULL, NULL, 'TX', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Mary', 'Jane', NULL, NULL, NULL, NULL)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Cletus', 'Van Damme', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Alfonse', 'Ackbar', NULL, NULL, 'AL', '12345')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Zoom', 'Zuckerman', NULL, NULL, 'NJ', '54321')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Mary', 'Jane', NULL, 'Springfield', NULL, NULL)

SELECT
NULLCountTable.MinNULLCount, IDTable.TargetRecordID,
StagingRecords.EmailAddress, StagingRecords.FirstName, StagingRecords.LastName, StagingRecords.StreetAddress, StagingRecords.City, StagingRecords.State, StagingRecords.ZipCode
FROM
(SELECT EmailAddress
, MIN(CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS MinNULLCount
FROM StagingRecords
GROUP BY EmailAddress) AS NULLCountTable
INNER JOIN
(SELECT Min(RecordID) AS TargetRecordID, EmailAddress
, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS NULLCount
FROM StagingRecords
GROUP BY EmailAddress, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END)) AS IDTable
ON (NULLCountTable.EmailAddress = IDTable.EmailAddress AND NULLCountTable.MinNULLCount = IDTable.NULLCount)
INNER JOIN StagingRecords ON (StagingRecords.EmailAddress = IDTable.EmailAddress AND StagingRecords.RecordID = IDTable.TargetRecordID)This has been running on an undeduped table of 170 million records for over 2.5 hours and has yet to output a single row.

Recently, people in this forum said I should use set based solutions such as this over cursors. I should get dramatic/exponential performance gains. And if I wasn't seeing that, then I wasn't doing it right. Well, I must not be doing this right so I'm asking for help.

This seems like it must do extra logic and sorting that the cursor based code doesn't have to do. For example, the cursor code doesn't need unique staging record IDs and never has to join on them or perform a fraction of the joining of this query.

The provided SQL should be say to run and experiment with on a tempdb.

Any help is much appreciated.|||There are probably more efficient solutions, but this approach is about 4 times faster than blindmans more complex query
if you build the view and indexes (which may make the whole thing a wash in the end)

Also, you suggested that your cursor solution appeared faster. It may return some rows faster than blindmans query, but
his suggestion is still fairly efficient. It will read the table 2-3 times but iterating through each row with a cursor,
even if you read each row only once, will still be MUCH SLOWER to complete the entire process.

SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON

--Create a view with a calculated colum for count of null records
CREATE VIEW v_StagingRecords WITH SCHEMABINDING AS
SELECT EmailAddress,
CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN ZipCode IS NULL THEN 1 ELSE 0 END NullCount, RecordID
FROM dbo.StagingRecords

--3 seconds. 1700 reads
CREATE UNIQUE CLUSTERED INDEX vSR_IDX on v_StagingRecords (RecordID)
CREATE INDEX v_EN_IDX on v_StagingRecords (EmailAddress, NullCount)

--100,000 row table. 0.6 seconds, 800 reads vs 2.8 seconds, 2200 reads for original query.
SELECT * FROM v_StagingRecords WHERE RecordID IN
(
SELECT (SELECT TOP 1 RecordID FROM v_StagingRecords WITH (NOEXPAND)
WHERE EmailAddress = o.EmailAddress Order BY NullCount ASC) RecordID
FROM v_StagingRecords o WITH (NOEXPAND)
GROUP BY EmailAddress
)
ORDER BY EmailAddress|||I loaded 17 million rows into a test table and built the view, indexes, and ran the query. It took 16 minutes in total, 101 seconds for the query itself. I tried blindmans query on the same data it it took 113, seconds so scrap my suggestion. I might play with it a little more and see if I can come up with anything better.

Decrypting and Encrypted Stored Procedure

Hello,
In SQL 2000,
I have created a Stored Procedure as follows,

Code Snippet

CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer

Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.

Code Snippet

sp_helptext mytest

But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?

You won’t retrieve back the source using sp_helptext /SMO, when you say WITH ENCRYPT.

You have to maintain your procedure source (like in File system or VSS). The encryption is very useful when you launch a product along with your database to public. So they can see the table schema but they can’t change or edit or view your programmability source code.

Decoding a binary base64 inside a XML using transact SQL

Yes i just can't find a place to help me out on something.

Everything went well to create the XML using transact SQL command

SELECT TOP 10 * FROM TblEvenement for xml auto, binary base64

But how do decode my image field in my XML so i can save the data into a new table ..

all this as to be done in a store procedure inside SQL-Server 2000 !!

thanx guys !!

***Any good sites or example i can use for !!What i did is a store proc in SQL-Server 2000 that build an XML document so i can transfert data between my two server.

But i have image fields and i would like to transfert that as well. I can encode it no problem ( for xml auto, binary base64 ), but how can i DECODE my TEXT value (int the XML document) using Transact SQL to store my image to the new server !!

thanx|||Did you find any solution to this. I'm really interested.|||¸Yes i build a function in my SQL DataBase that convert my image but works only with images that have less or equal to binary(8000)..

but im looking for something else.. its not working as i would like so.. im keep working on it.

still need help guys !!!

not a single site in the web that have an example..

there must be a way !! or its to easy so no buddy wants to help me out..|||Does this post help you?OPENXML and image binary base64.

The approach here is to create a UDF which will convert base64 to binary, and use this function with OPENXML.

Terri|||I try the function but the problem is that it return a varbinary(8000) .. its to short.. all my images are over 10K

so im trying something else.. must be a way

thanx

Decode

I am new to SQL+ and Oracle. I am trying to create a cross tabular file. It sums all the ded_code Totals, but does not sum ded_code ANN.
My code looks like this: HELP!!
column EMPLOYEE format 9999999999 heading 'Employee'
COLUMN ANN FORMAT 99999999.99 heading 'Ann'
COLUMN FOUR format 99999999.99 heading 'FOUR'
COLUMN TOTALS format 99999999.99 heading 'Totals'
compute sum of FOUR on employee
compute sum of ANN on employee
compute sum of TOTALS on employee
SPOOL bc.TXT
SELECT employee,
SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
SUM(DED_AMT)TOTALS
FROM PAYDEDUCTN
WHERE check_id IN (SELECT CHECK_ID
FROM PAYMASTR
WHERE CHECK_DATE = '22-Mar-02')
and ded_code in ('4', '5','403F', '403X', 'ANN')
GROUP by employee
/

SPOOL OUT

The output looks like this:


5639 .00 .00 267.05
:confused:The query seems to work fine without the WHERE clause for check_id.

SELECT employee,
SUM(DECODE(ded_code, 'ANN', ded_amt,0)) ANN,
SUM(DECODE(ded_code, '4', ded_amt,0)) FOUR,
SUM(DED_AMT) TOTALS
FROM PAYDEDUCTN
WHERE
ded_code in ('4', '5','403F', '403X', 'ANN')

A you sure you have records in table PAYDEDUCTN having check_id at 22-Mar-02 for the ded_code 'ANN' ?

Declaring DataTable in script causes error?

As discovered when trying to create a custom transformation for this question (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=899895&SiteID=1&mode=1) I tried creating an object of type DataTable and I get this error

dim myTable as DataTable

Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. dts://Scripts/ScriptComponent_3851bc3613714d2d904d79bc006234f9/ScriptMain 19 24 ScriptComponent_3851bc3613714d2d904d79bc006234f9

Even if I add "Imports System.XML" I get the same error. Isn't DataTable part of System.Data (which is imported by default in Script Components)? Everyone else get the same behavior?

Chris,

The Imports directive brings namespaces from referenced assemblies. please see the link below for more details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vastmimports.asp

You need to add a reference to System.Xml.dll through the project pane in order to be able to use any types from System.Xml namespace.

|||Thanks that works!... so even though its listed as part of System.Data it uses something from System.Xml?

DECLARE Table

Can anyone give me an example on how to use DECLARE to create a table
variable?
Thanks in advance
DimitrisDECLARE @.T TABLE(COL1 INT, COL2 CHAR(3))
"Dimitris Milonas" <gnout@.hotmail.com> wrote in message
news:u29$Yx3sFHA.2348@.tk2msftngp13.phx.gbl...
> Can anyone give me an example on how to use DECLARE to create a table
> variable?
> Thanks in advance
> Dimitris
>|||Start here:
http://msdn.microsoft.com/library/d...br />
66w5.asp
Continue here:
http://msdn.microsoft.com/library/d...br />
7ysl.asp
declare @.tableVar table (
Col1 int identity (1, 1)
,Col2 varchar(32)
,Col3 ntext
..etc, etc...
)
ML|||Dimitris,
See follwoing example
========================================
==========
DECLARE @.Student TABLE
(
Student_Name varchar(50),
Student_ID int
)
SELECT * FROM @.Student
========================================
==========
I am sure this will definite will work... pls do let me know... in
case of any issue..
Regards
Hari Sharma

declare syntax in a UDF

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in
the wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @.set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)

Cheers for any help,
Chris"Not Me" <Noone.is.home@.here.com> wrote in message
news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
> Hi, I'm trying to create a function that returns a table, however I want
> to use a local variable in there and enterprise manager ain't liking it!
> The error I get is number 156 'incorrect syntax near the keyword
> 'declare'.. hopefully this is just a simple thing where I've put it in the
> wrong place.
> The code follows:
> CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
> smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
> nvarchar(20))
> RETURNS TABLE AS
> RETURN
> declare @.set nvarchar(150)
> select "Total Pledged" as info, sum(total) as tot
> FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
>
> Cheers for any help,
> Chris

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

In your function, you haven't defined the structure of the returned table,
so the only thing you can have in the body of the function is a single
SELECT.

Simon|||Simon Hayes wrote:
> "Not Me" <Noone.is.home@.here.com> wrote in message
> news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
>>The error I get is number 156 'incorrect syntax near the keyword
>>'declare'.. hopefully this is just a simple thing where I've put it in the
>>wrong place.
>>
>>The code follows:
>>
>>CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
>>smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
>>nvarchar(20))
>>RETURNS TABLE AS
>>RETURN
>>declare @.set nvarchar(150)
>>select "Total Pledged" as info, sum(total) as tot
>>FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
> You seem to be mixing inline and multi-statement syntax. If you just say
> RETURN TABLE, then the rest of the function can only be a single SELECT
> statement; if you want to use multiple statements in the function, then you
> must define the structure of the table you're returning. See the examples in
> Books Online under CREATE FUNCTION.

Aha! sounds about right, just needed a little shunt in the right
direction.. gonna have nightmares about BOL :p

cheers,
Chris

Declare or Create cursor

Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?
Is Declare Cursor same as Create Cursor and if not what is the major difference?Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

DECLARE CURSOR is part of the T-SQL Language dfinition. CREATE CURSOR is not.|||I do not think there is "Create Cursor". Does anyone know that?

Declare dynamic Cursor from String

Hi,
is it possible to create a cursor from a dynamic string?
Like:

DECLARE @.cursor nvarchar(1000)
SET @.cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @.cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
PeppiNot within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoropen" and a few others with "sp_cursor*" which might
be abloe to do the job for you.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<peppi911@.hotmail.com> wrote in message
news:1146129244.595060.254470@.v46g2000cwv.googlegr oups.com...
> Hi,
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
> = 'aktiv'
> Havn't found anything in the net...
> Thanks,
> Peppi|||(peppi911@.hotmail.com) writes:
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
>= 'aktiv'

Yes, this is possible, but the question remains: why?

See here for details: http://www.sommarskog.se/dynamic_sql.html.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.

Thanks,
mike|||peppi911@.hotmail.com wrote:
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That doesn't explain why you are using a cursor. It also doesn't
explain the need for dynamic SQL. Both are things you should avoid when
you can, I think that was what Erland was trying to get at.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(peppi911@.hotmail.com) writes:
> Thanks for your answers.
> I'll have a look at the link.
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That does not explain the cursor - but could be that there is some
calculations are too complex to be carried out set-based. But there is
all reason to avoid the iteration if possible and handle all rows at
once. If there are many products this could mean serious reduction in
execution time.

On the other hand, there is enough information for me to tell that you
don't need any dynamic SQL. There are two possible solutions:

DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND (@.runforall = 1 OR fund_amt > 0)

If there is an index on the selection column for active products, it's
better to do:

IF @.runforall = 1
BEGIN
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
END
ELSE
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND fund_amt > 0
END

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx