How to remove a Primary Key you don't know?

Hello Guys,

I need some help with a question I've been struggling to solve all day. I'm new to Transact-SQL and thus tried to solve my little problem by reading the language reference, but I didn't find what I was looking for.

Here's the scenario: I want to remove a table's primary key and replace it with a new one. Removing the pk is usually done with:

1) ALTER TABLE tablename DROP CONSTRAINT constraint_name; GO

Unfortunatley, I don't know constraint_name and the simple MySQL command

ALTER TABLE tablename DROP PRIMARY KEY

doesn't seem to work. To get constraint_name I made a query:

2) SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename')

Now, here's the core of my problem: I need to link statements 1) and 2) and tried

ALTER TABLE tablename DROP CONSTRAINT (SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename'))

but that doesn't, I get a syntax error.

Do you have a clue? I'm sure its a simple problem for an experienced programmer, but I don't get it. Or do you know another way of removing the primary key without having to state constraint_name explicitely?

Any help is appreciated!

Thanks a bunch,

adunak

You need to use dynamic SQL to execute the ALTER TABLE statement if you want to parameterize the constraint name. Most of the DDL statement parameters in TSQL cannot be parameterized by using variables or expressions. So do below instead:

declare @.const nvarchar(200)

set @.const = QUOTENAME((

select kc.name

from sys.key_constraints as kc

where kc.parent_object_id = object_id('tablename') and kc.type = 'PK'

))

exec('ALTER TABLE tablename DROP CONSTRAINT ' + @.const)

|||

Hi Umachandar,

your hint worked well - thanks a lot for helping me out :-)

Adunak

How to reference the primary key of a newly added record in trigger?

Please help me somebody solve my problem with my first :o trigger:

ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
INSERT INTO partner (name) SELECT name FROM deleted
UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


*** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?I'd use:ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
BEGIN
INSERT INTO partner (name)
SELECT name FROM deleted

UPDATE o
SET id_partner= i.id_partner
FROM dbo.invoice AS o
INNER JOIN deleted AS d
ON (d.id_partner = o.id_partner)
LEFT OUTER JOIN inserted AS i
ON (i.id_partner = o.id_partner)
ENDThis trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

-PatP

How to read just inserted auto incremented primary key to use it as parameter?

Hi. After inserting data (new row) by using DetailsView control, how to read auto incremented primary key (identity) of this new row from sql database to use it as parameter passed to stored procedure?

Or in other way. I need to insert new row (let's call them parent row) in one table by using DetailsView control. This is quite easy. But after inserting I need to read primary key of this new row to insert new child row in other table. Both tables are in relation. How to do this? Any suggestions? Pawel.

Hi Pawelek,

Since the ID (primary key) is configured as an auto incremented field, the last row inserted will always have the maximum number.

You could write a stored procedure which selects the maximum ID number ( SELECT max(ID) from ParentRowsTable ) and returns it.
Then in your code you can execute the SP after you insert a row and you get the max. ID of the parent rows.

Greets,

Wim

|||Hi. I'm afraid that this is a risky business. What if in the same time some other people will insert new row which becomes row with maximum number? Is it possible such situation? Regards. Pawel.|||

Hi. you can return the@.@.IDENTITY

INSERT INTO [dbo].[table] (name, phone)VALUES ("test", "tttt")Return@.@.IDENTITY

|||I would recommend using SCOPE_IDENTITY() instead of @.@.IDENTITY. The former maintains the scope. Check out BOL for more info.

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<