Creating an All option for parameter value in GUID format

I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.

select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname

The issue I am running into is implementing logic in another dataset referencing my parameter.

All is fine in the where clause if it is structured "where ownerid in (@.Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@.Users) or @.Users = '00000000-0000-0000-0000-000000000000') it errors out.

How do you impement "All" when you're dealing with a GUID type field? Thanks.

Have you looked at SSRS SP2? It puts the Select All option back. You can get it HERE

R

|||Thanks. We will be installing SP2.

creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

sql

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

Creating a new variable with SQL Server Analysis Services

Hi!

I have just started working with SQL Server Analysis Services and I have already expierenced some problems:
I am working with the Adventure Works data and I want to create a new variable (Customer Value) out of the following data:

"Customer ID" and "Order Number"

I.e. a customer with the "customer ID" 00001 has ordered two products (so two order numbers are linked to this customer id) --> the new variable should identify the customer as a "C" customer.

therefore -->

"D" customer value: 0 orders
"C" customer value: 1-2 orders
"B" customer value: 3-4 orders
"A" customer value: >5 orders

Do you have any ideas to solve this problem?

Thanks

Cemens

Hello Cemens,

Do you want to create another attribute for the dimension Customers? Then I would suggest to create a named calculation in the DataSourceView (DSV) of your project with SQL

Creating a New Measure Under Existing Measure Group?

Hello,

When working with SSAS cubes, is there a way to add a new Measure to to the existing Measure Group without creating a new Measure Group? For instance, I have a Measure Group called Account with one measure in it but when I try to add a new measure to Account but it gets added to a new Measure Group called Account 1.

Thanks

You are probably trying to add new measure based on a column that is coming from another table in relational database.

Analysis Services tools dont consider this situation a good practice and suggest you create a new measure group for data coming from another table.

If you want the measure to appear in the same measure group, you can replace your original table with named query in DSV where you join 2 tables together. And then you should be able to add new measure to you measure group.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward.

I am new to Analysis Services and I have been struggling with the following issue for the last two days. Here is the deal: I have two dimensions such as Gender and Semester and one measure called # Of Students. What I would like to have differences between # Of Students for 06FS and 05FS semesters. I used a [Calculated Member] measure but without much success:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

AS [Measures].[Distinct # Of Students]-[Measures].[Distinct # Of Students],

VISIBLE = 1 ;

The desired column is in blue

Semester

05FS

06FS

Gender

# Of Students

Difference

Females

6000

6200

200

Males

6800

690

100

Could you give me a hand with this?

Thanks for your help!

|||

Hi I have the same problem despite my measures are coming from the same table. I have created a Measure with the Sum aggregation on one column and another one with the DistinctCount Aggregation on a second colum of the same table and SSAS creates a different Measure Group for the second measure. I don't understand why. I have exactly the same structure for another table and two measures are stored under the same Measure Group.

Any idea why I can't have my two measures in the same Measure Group ?

|||

This is Stupid, when I create a new Measure with an aggregation on a column from the same table than another measure it automatically creates a new measure group for this measure. But If I create a new Measure Group it automatically creates different measures depending of the types of the column from the selected table. I have just replaced the properties I needed from the different measures within the measure group. That's the only way I've found to have multiple measures in an existing measure group.

Creating a New Measure Under Existing Measure Group?

Hello,

When working with SSAS cubes, is there a way to add a new Measure to to the existing Measure Group without creating a new Measure Group? For instance, I have a Measure Group called Account with one measure in it but when I try to add a new measure to Account but it gets added to a new Measure Group called Account 1.

Thanks

You are probably trying to add new measure based on a column that is coming from another table in relational database.

Analysis Services tools dont consider this situation a good practice and suggest you create a new measure group for data coming from another table.

If you want the measure to appear in the same measure group, you can replace your original table with named query in DSV where you join 2 tables together. And then you should be able to add new measure to you measure group.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward.

I am new to Analysis Services and I have been struggling with the following issue for the last two days. Here is the deal: I have two dimensions such as Gender and Semester and one measure called # Of Students. What I would like to have differences between # Of Students for 06FS and 05FS semesters. I used a [Calculated Member] measure but without much success:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

AS [Measures].[Distinct # Of Students]-[Measures].[Distinct # Of Students],

VISIBLE = 1 ;

The desired column is in blue

Semester

05FS

06FS

Gender

# Of Students

Difference

Females

6000

6200

200

Males

6800

690

100

Could you give me a hand with this?

Thanks for your help!

|||

Hi I have the same problem despite my measures are coming from the same table. I have created a Measure with the Sum aggregation on one column and another one with the DistinctCount Aggregation on a second colum of the same table and SSAS creates a different Measure Group for the second measure. I don't understand why. I have exactly the same structure for another table and two measures are stored under the same Measure Group.

Any idea why I can't have my two measures in the same Measure Group ?

|||

This is Stupid, when I create a new Measure with an aggregation on a column from the same table than another measure it automatically creates a new measure group for this measure. But If I create a new Measure Group it automatically creates different measures depending of the types of the column from the selected table. I have just replaced the properties I needed from the different measures within the measure group. That's the only way I've found to have multiple measures in an existing measure group.

creating a FK on existing tables

hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?

product :

product_id
report_id
name

report :

report_id
dateR

i want to create a FK on product.report_id, and ON DELETE CASCADE

thank you--Creating table with same structure (Primary key)]
CREATE TABLE [A] (
[report_id] [varchar] (10) ,
[dateR] [Datetime],
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[report_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Inserting data from report table
INSERT INTO A
SELECT * FROM report

--Dropping table report
DROP TABLE report
GO
--Renaming A table as report table
EXEC sp_rename 'A','report'
GO
--Caution: Changing any part of an object name
--could break scripts and stored procedures.

--This will create a FK in product table

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[report_id]
) REFERENCES [report] (
[report_id]
) ON DELETE CASCADE|||genial !

thanks a lot

Creating a custom resolver with VB.NET

Someone posted the question "Can anyone point me towards a source code
listing for a working replication custom resolver written in .Net?" Here is
an example that I have written.
Custom resolvers are created by adding a reference to the Microsoft SQL
Replication Conflict Resolver Library, replrec.dll. Unfortunately, the .NET
type library importer incorrectly defines the buffer parameter of methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to be the
address of an Object. For COM interfaces, which is what replrec.dll is
supposed to be, that implies a COM-VARIANT parameter passing mechanism, but
this is not what Replrec passes back. Replrec is simply passing back the
address of a buffer containing the column value which you have to decode.
So the solution involves correcting the method definitions so that a buffer
address can be passed and using the .NET Marshal routines to create the
buffer and move bytes out of the buffer.
Here are the steps involved, followed by a code example that resolves
conflicts by using the column values from the row with the latest date in
user defined column ModifyDate.
1. Open a Visual Studio .NET 2003 Command Prompt
2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
/OUT:SQLResolver_import.dll
3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
int32 pvBuffer"
5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
6. Create a new .NET Windows Control Library project
7. Remove the wizard generated control
8. Use "Add New Item..." to add a new COM class
9. Add reference to SQLResolver.dll created in step #5
Imports System.Text
Imports SQLResolver
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.MarshalAsAttribute
Imports SQLResolver.REPOLE_CHANGE_TYPE
Imports SQLResolver.REPOLE_COLSTATUS_TYPE
<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1
Implements SQLResolver.IVBCustomResolver
Private Const MAX_BUFFER_SIZE As Integer = 1048576
Private Const MAX_NAME_LENGTH As Integer = 128
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
Public Const InterfaceId As String =
"CECFBB8F-584F-4733-9373-B69AFA6F117F"
Public Const EventsId As String = "5D55BA40-A438-4FD4-BA8B-05095DC89948"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
IVBCustomResolver.GetHandledStates
ResolverBm = REPOLEUpdateConflicts
End Sub
Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
Integer, ByVal pvReserved As IReplRowChange) Implements
IVBCustomResolver.Reconcile
Dim cntColumns As Integer
Dim intColumn As Integer
Dim strColumnName As String
Dim strLogMessage As String
Dim WinningData As Object
Dim blnSourceIsWinner As Boolean
Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
Dim intBufferLenActual As Integer
Dim intBufferLen As Integer
Dim strDestinationDateTime As String
Dim strSourceDateTime As String
Dim strDestinationUser As String
Dim strSourceUser As String
Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
Dim strMsg As String
'If Not Debugger.IsAttached Then
' Debugger.Launch()
'Else
' Debugger.Break()
'End If
Call pRowChange.GetNumColumns(cntColumns)
For intColumn = 1 To cntColumns
strColumnName = " ".PadRight(MAX_NAME_LENGTH)
' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
Call pRowChange.GetColumnName(intColumn, strColumnName, MAX_NAME_LENGTH)
' strColumnName.TrimEnd(Chr(vbNull))
strColumnName = strColumnName.TrimEnd()
If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationDateTime = ConvertBufferToDateString(myBuffer)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceDateTime = ConvertBufferToDateString(myBuffer)
If strSourceDateTime > strDestinationDateTime Then
blnSourceIsWinner = True
Else
blnSourceIsWinner = False
End If
End If
If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
End If
Next intColumn
For intColumn = 1 To cntColumns
'Get the column status of each column
pRowChange.GetColumnStatus(intColumn, ColStatus)
' If the column has been updated at both the Publisher and Subscriber
If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
If blnSourceIsWinner Then
pRowChange.CopyColumnFromSource(intColumn)
End If
' For columns that have been updated without any changes, copy column
values from source
ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
pRowChange.CopyColumnFromSource(intColumn)
' For columns that have not been updated - do nothing.
ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
End If
Next intColumn
' Log conflict and call the UpdateRow method to commit all the column value
changes.
'
If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0 Then
If blnSourceIsWinner Then
strMsg = "Losing update(s) made by " & strDestinationUser
Else
strMsg = "Losing update(s) made by " & strSourceUser
End If
End If
pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
Call pRowChange.UpdateRow()
Marshal.FreeHGlobal(myBuffer)
End Sub
Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
Dim s As String = String.Empty
Dim i(7) As Short
Dim j As Integer
Marshal.Copy(p, i, 0, i.Length)
s = i(0).ToString '4 digit Year
For j = 1 To i.GetUpperBound(0)
s &= i(j).ToString.PadLeft(2, "0"c)
Next
ConvertBufferToDateString = s
End Function
Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
intBufferLenActual As Integer) As String
Dim i As Integer
Dim s As String = String.Empty
For i = 0 To intBufferLenActual - 1
s &= Chr(Marshal.ReadByte(p, i))
Next
ConvertBufferToString = s
End Function
Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
String, ByVal inMsg As String)
s = s & ";" & inMsg
If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
End Sub
End Class
u rock man!
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
"Douglas Arterburn" <darterburn@.precisdev.com> wrote in message
news:uzhwyd7iFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Someone posted the question "Can anyone point me towards a source code
> listing for a working replication custom resolver written in .Net?" Here
> is an example that I have written.
> Custom resolvers are created by adding a reference to the Microsoft SQL
> Replication Conflict Resolver Library, replrec.dll. Unfortunately, the
> .NET type library importer incorrectly defines the buffer parameter of
> methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to
> be the address of an Object. For COM interfaces, which is what
> replrec.dll is supposed to be, that implies a COM-VARIANT parameter
> passing mechanism, but this is not what Replrec passes back. Replrec is
> simply passing back the address of a buffer containing the column value
> which you have to decode.
> So the solution involves correcting the method definitions so that a
> buffer address can be passed and using the .NET Marshal routines to create
> the buffer and move bytes out of the buffer.
> Here are the steps involved, followed by a code example that resolves
> conflicts by using the column values from the row with the latest date in
> user defined column ModifyDate.
> 1. Open a Visual Studio .NET 2003 Command Prompt
> 2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
> /OUT:SQLResolver_import.dll
> 3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
> 4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
> GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
> int32 pvBuffer"
> 5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
> 6. Create a new .NET Windows Control Library project
> 7. Remove the wizard generated control
> 8. Use "Add New Item..." to add a new COM class
> 9. Add reference to SQLResolver.dll created in step #5
> Imports System.Text
> Imports SQLResolver
> Imports System.Runtime.InteropServices
> Imports System.Runtime.InteropServices.MarshalAsAttribute
> Imports SQLResolver.REPOLE_CHANGE_TYPE
> Imports SQLResolver.REPOLE_COLSTATUS_TYPE
> <ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
> Public Class ComClass1
> Implements SQLResolver.IVBCustomResolver
> Private Const MAX_BUFFER_SIZE As Integer = 1048576
> Private Const MAX_NAME_LENGTH As Integer = 128
> #Region "COM GUIDs"
> ' These GUIDs provide the COM identity for this class
> ' and its COM interfaces. If you change them, existing
> ' clients will no longer be able to access the class.
> Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
> Public Const InterfaceId As String =
> "CECFBB8F-584F-4733-9373-B69AFA6F117F"
> Public Const EventsId As String =
> "5D55BA40-A438-4FD4-BA8B-05095DC89948"
> #End Region
> ' A creatable COM class must have a Public Sub New()
> ' with no parameters, otherwise, the class will not be
> ' registered in the COM registry and cannot be created
> ' via CreateObject.
> Public Sub New()
> MyBase.New()
> End Sub
> Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
> IVBCustomResolver.GetHandledStates
> ResolverBm = REPOLEUpdateConflicts
> End Sub
> Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
> Integer, ByVal pvReserved As IReplRowChange) Implements
> IVBCustomResolver.Reconcile
> Dim cntColumns As Integer
> Dim intColumn As Integer
> Dim strColumnName As String
> Dim strLogMessage As String
> Dim WinningData As Object
> Dim blnSourceIsWinner As Boolean
> Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
> Dim intBufferLenActual As Integer
> Dim intBufferLen As Integer
> Dim strDestinationDateTime As String
> Dim strSourceDateTime As String
> Dim strDestinationUser As String
> Dim strSourceUser As String
> Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
> Dim strMsg As String
> 'If Not Debugger.IsAttached Then
> ' Debugger.Launch()
> 'Else
> ' Debugger.Break()
> 'End If
> Call pRowChange.GetNumColumns(cntColumns)
> For intColumn = 1 To cntColumns
> strColumnName = " ".PadRight(MAX_NAME_LENGTH)
> ' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
> Call pRowChange.GetColumnName(intColumn, strColumnName,
> MAX_NAME_LENGTH)
> ' strColumnName.TrimEnd(Chr(vbNull))
> strColumnName = strColumnName.TrimEnd()
> If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationDateTime = ConvertBufferToDateString(myBuffer)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceDateTime = ConvertBufferToDateString(myBuffer)
> If strSourceDateTime > strDestinationDateTime Then
> blnSourceIsWinner = True
> Else
> blnSourceIsWinner = False
> End If
> End If
> If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> End If
> Next intColumn
> For intColumn = 1 To cntColumns
> 'Get the column status of each column
> pRowChange.GetColumnStatus(intColumn, ColStatus)
> ' If the column has been updated at both the Publisher and Subscriber
> If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
> If blnSourceIsWinner Then
> pRowChange.CopyColumnFromSource(intColumn)
> End If
> ' For columns that have been updated without any changes, copy column
> values from source
> ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
> pRowChange.CopyColumnFromSource(intColumn)
> ' For columns that have not been updated - do nothing.
> ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
> End If
> Next intColumn
> ' Log conflict and call the UpdateRow method to commit all the column
> value changes.
> '
> If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0
> Then
> If blnSourceIsWinner Then
> strMsg = "Losing update(s) made by " & strDestinationUser
> Else
> strMsg = "Losing update(s) made by " & strSourceUser
> End If
> End If
> pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
> REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
> REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
> Call pRowChange.UpdateRow()
> Marshal.FreeHGlobal(myBuffer)
>
> End Sub
> Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
> Dim s As String = String.Empty
> Dim i(7) As Short
> Dim j As Integer
> Marshal.Copy(p, i, 0, i.Length)
> s = i(0).ToString '4 digit Year
> For j = 1 To i.GetUpperBound(0)
> s &= i(j).ToString.PadLeft(2, "0"c)
> Next
> ConvertBufferToDateString = s
> End Function
> Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
> intBufferLenActual As Integer) As String
> Dim i As Integer
> Dim s As String = String.Empty
> For i = 0 To intBufferLenActual - 1
> s &= Chr(Marshal.ReadByte(p, i))
> Next
> ConvertBufferToString = s
> End Function
> Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
> String, ByVal inMsg As String)
> s = s & ";" & inMsg
> If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
> End Sub
> End Class
>

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.

CREATEing a new db

I am trying to create a new database with T SQL in the query window.

I am working from a book and have doubled checked my spelling, path, and all other things that seem obvious. There is something I am missing. I get an error message when I run my script.

Here is my script.....

ON

(NAME = 'Accounting',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingData.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5)

LOG ON

(NAME = 'AccountingLog',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingLog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB)

GO

This is the error that I got...

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL Data\AccountingData.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

Msg 1802, Level 16, State 1, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I am running SQL Server 2005 Express. Please help.

Thanks,

stuck

It appears that your path is incorrect. (Though NOT your fault.)

There is an issue with ending a line with a backslash. It is ignored.

Note the path in the error message. There is NOT a backslash between MSSQL and Data.

It probably should be '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AccountingData.mdf"

Don't break the line like on the backslash.

Code Snippet

CREATE DATABASE AccountingData
ON ( NAME = 'Accounting',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AccountingData.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON ( NAME = 'AccountingLog',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AccountingLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

GO

Created database at school but can't open it at home

Please help me.

I'm a college student working on a database project using MS SQL Server 2005 Express Edition.

The program (SQL SMSEE) is installed on both the computers in class and on two of my computers at home. The first installation resulted in the "remote connections" error. No matter what I do, I can't get the program to fully load. So I tried installing on another computer at home. The second installation went well. Didn't do anything different from the first installation, but any hoo....................

My 2-week old problem is this - I save my database that I do in class on my thumbdrive. Last class, I saved all the files that had my database's name on it on my thumbdrive--.mdf, .log, .bak, etc. On the second home computer, I cannot open the databases that I work on at school. Even my professor is stumped on this one.

Here is what I'm doing......

After I connect to the server, I right-click "Databases" then left-click "Restore Database".

In the "To database" box, I enter the name of the database, as I saved it at school.

In the "To a point in time" box, I leave the default "Most recent possible" entry.

I select "From device" as the location of backup and click the "..." button. The file is on my thumbdrive, so I click "Add" and select the location on my usb (with the .bak extension) and click OK.

I pick the most recent file checkbox and click "OK".

The green progress circle goes to 50% and then gets stuck. The database never opens and the Object Explorer shows the database name followed by "(Restoring...)". So if I try to do anything else with it, I get an error message stating the database in the middle of a restore and that I have to wait until its done. Well, of course it never finishes.

Please help me. Right now I am stuck doing duplicate work at home and at school and am making little to no progress. The final project is due on 26 March and right now I only have my tables, a few attributes and a couple of relationships. And I have a LOT more work to do.

Thanks.

i have more luck using the attach task than with restore --

not sure why -- but sounds like you have a need for speed so you might want to try using the attach task.

When you copied the files to your thumb drive was this a database backup or just a copy?

if its just a copy did you stop the sql engine before you made the copy (this is a "best practice")

then copy the .mdf and .ldf files

try using the attach task in smsee instead of restoring

1. copy the .mdf and .ldf files from your thumb drive to your computer.

2. open smsee and connect to a server --probably <yourcomputername>\SQLEXPRESS

3. in the object browser:

select the database node and right click

select the attach task on the context menu

4. an ATTACH DATABASE dialog will open -- click the add button

5. a LOCATE FILE... dialog will open -- drill down to the database.mdf file location and select it

and click OK on the LOCATE FILE dialog

6. the database details should be in the bottom of the ATTACH DATABASE dialog -- click OK

you should now have a node under the databases node for your attached database.

good luck

|||

I LOVE YOU!!!!!!!!!!!!

(in context, of course )

You have just saved me from days of mental anguish. I'm telling you, I only had a few strands of hair left.

Now I don't have to do double work any more and I actually have a shot at completing this project on time.

Thank you so VERY much. I wish I would have visited this forum sooner.

Gotta go.........plenty work to be done!

.........did I say THANK YOU?

Create View using Stored procedure

I would like to drop and re-create a view using stored procedure. I try with the following codes for creating view. but some its not working.
How can I drop the existing view befor creating it?

Declare @.sql_command nvarchar(4000)
Declare @.ParmDef nvarchar(4000)
Declare @.Branch int

Set @.sql_command =N'Create view [vwacchart] as ( Select * from ah where ah1=' + @.Pamvar + ')'
set @.parmdef =N'@.Pamvar int'
set @.branch=1

EXEC sp_executesql @.sql_command,@.parmdef,@.Pamvar= @.branchN'Create view [vwacchart] as ( Select * from ah where ah1=' + @.Pamvar + ')'

Or something like it. The text as shown in your post will not substitute in the @.variable, but be parsed as meaning =@.variable.

Create View Only

Good afternoon,

I have a user that needs read only access to all tables in a particular database - Which is working fine.

He also needs to create views, as well as the above, but nothing else.

Is this possible?

Many thanks.

This can be done, but there are a couple of caveats; hang on. Look at this example:

Code Snippet

create view dbo.v_doWhat
as
select what from doWhat
go

/*

Server: Msg 2760, Level 16, State 1, Procedure v_doWhat, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

*/

alter view kawTest.v_doWhat
as
select what from doWhat

go

delete from v_doWhat

/*
Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'doWhat', database 'kawTest', owner 'dbo'.
*/

Note in the first create that because you do not have DBO privilege that you cannot create DBO views. Secondly, the views that you create will be for a specific schema and in SQL 2000 they will be owned by a specific user.

I am probably leaving out more; hopefully, somebody will pick me up.

Another thing to think about: If the developer or user has permission to create views, should they also have the ability to create functions?

create view

CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;

its working with oracle

how to chenge in t-sql

thanx

Hi,

you have to ALIAS Your SubQuery.

CREATE VIEW getUsedColumns
AS
SELECT * FROM
(
SELECT DISTINCT attr
FROM iba
UNION
SELECT DISTINCT attr
FROM new_iba
) SomeSubQuery
WHERE attr != 0;

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

You need to specify an alias for the derived table. And you don't really need the DISTINCT in each of the SELECT statements since the UNION will eliminate the duplicates anyway. You will get better performance by writing it as:

SELECT * FROM (SELECT Dattr FROM iba UNION SELECT attr FROM new_iba) as t

WHERE attr != 0;