how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to insert resultset from SP into a table? or use Select *

Thank you. Yes, I did see that this is the only way to do it. My objective
is that an SP which I did not create and don't want to modify returns a
dataset that I need to order. So I want to read/write that data into a temp
table so that I can order it.
"Alejandro Mesa" wrote:
> Rich,
> Create a temporary or permanent table with the same structure as the resul
t
> of the sp. Use:
> insert into #t1
> exec dbo.usp_p1 ...
>
> AMB
> "Rich" wrote:
>Rich,
If you can order the resultset at the client side, then do it and you will
be liberating some load from your sql server.
AMB
"Rich" wrote:
> Thank you. Yes, I did see that this is the only way to do it. My objecti
ve
> is that an SP which I did not create and don't want to modify returns a
> dataset that I need to order. So I want to read/write that data into a te
mp
> table so that I can order it.
> "Alejandro Mesa" wrote:
>sql

How to Insert Multiple Records into sql 2000 table at once ?

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
--order no --orderno
--issuedate --itemname
--supplier --desccription
--amount --price
--Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!See links below:

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.eggheadcafe.com/articles/20030627c.asp|||hi, thanks a lot,I will browse the web page you give me .

how to insert more than once when processing one row in script component

I am trying use call DirectRowToOutput0 more than once to achieve the goal, but it gives an error of calling COM. I can create more than one destination to achieve it, but I need create 8 destinations for my situation. Is there any easy way?

Thanks

Is this any help to you: http://blogs.conchango.com/jamiethomson/archive/2005/09/05/2113.aspx

-Jamie

|||no, that is not what I am trying to do. In fact, I need insert to the same destination more than once.|||

In that case this will be an asynchronous component. You will then be able to do what you want with the rows.

-Jamie

|||That is it. Thank you so much.

How to insert button control in report manager

Hi
Is it possible to create button in report like we do in VB.net a button
control.
in toolbar their is no button control available ......
Does any one know we can do this thing in report or any alternative way so
we can click on some thing and it do some sort of function
thanksAs a workaround, you can add an image of a button and set its Jump to
URL/Jump to Report properties to make it behave like an actual button.
HTH.
"Amjad" <Amjad@.discussions.microsoft.com> wrote in message
news:C70ABA17-70A2-4DC6-9EFD-B6DE273417BB@.microsoft.com...
> Hi
> Is it possible to create button in report like we do in VB.net a button
> control.
> in toolbar their is no button control available ......
> Does any one know we can do this thing in report or any alternative way so
> we can click on some thing and it do some sort of function
>
> thanks

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to incorporate column-header click-driven sorting, and column header click-driven grou

How to incorporate column-header click-driven sorting, and column
header click-driven grouping in SQL Server Reporting Services:
Create the following report parameters with the following details (you
can change the parameter names to suit your naming convention):
(1) Name=3DprmSortBy
Prompt=3DSort By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: Select non-queried
Default values: none
(2) Name=3DprmDirection
Prompt=3DDirection
Data Type=3Dstring
Allow blank value: checked
Available values: Label: ASC; Value:ASC
Label: DESC; Value: DESC
Default values: Non-queried, with the value "ASC" in the text box next
to it.
(3) Name=3DprmGroupBy
Prompt=3DGroup By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: select non-queried
Default values: None
Scenario:
Suppose you have a report created using the traditional table. Let's
also suppose you have the following columns in your report: ClientID,
ClientName, City and SSN (clientID is numeric and the rest being alpha
numeric data types). You wish to sort by the "ClientName" column and
group by the "City" column.
Task: Sorting by the ClientName column:
=E2=80=A2 In the ClientName column header (that sits in a cell on the table
header), right click and select properties.
=E2=80=A2 Then click the advanced button to go to "Advanced Textbox
properties"
=E2=80=A2 Visibility tab settings:
=E2=80=A2 Initial visibility: visible
=E2=80=A2 Initial appearance of the toggle image...: collapsed
=E2=80=A2 Navigation tab settings:
=E2=80=A2 Hyperlink Action: select Jump to report and enter the name of the
report in the textbox (ex. rptClient)
=E2=80=A2 Click on the "Parameters" button to go to the "Parameters" dialog
box
=E2=80=A2 Parameters Dialog box entries:
=E2=80=A2 Here, you must list ALL of the parameters defined in your report
and assign values to them. Having done that, for our case, we concern
ourselves with the sorting and grouping parameter value settings
parameter name parameter value column
prmSortBy =3D"ClientName"
prmDirection =3DCode.DetermineDirection("ClientName",Parameters!prmSort= By.Value,Parameters!prmDirection.Value)
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D"","None",Parameters!pr= mGroupBy.Value)
Note: "DetermineDirection" is a function we incorporated into our
report to return a sorting direction (ascending or descending) and
below is the function (if you are unsure where to incorporate the
function, highlight the table that holds the report and click on report
menu and go to properties and on the "code" tab, you enter the
function)
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String
If strColumnName =3D strPreviousColumn Then
If strDirection =3D "ASC" Or strDirection =3D "" Then
Return "DESC"
Else
Return "ASC"
End If
Else
Return "ASC"
End If
End Function
Highlight the table that contains the report, right-click and select
properties. On the "Table Properties" dialog, go to the "Sorting" tab
and enter the following:
Expression =3D
iif(Parameters!prmDirection.Value=3D"ASC",Fields(iif(Parameters!prmSortBy.V= alue
Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
Direction:
Ascending
Expression =3D
=3Diif(Parameters!prmDirection.Value=3D"DESC",Fields(iif(Parameters!prmSort= By.Value
Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
Direction:
Descending
That should be it! When you run the repot and click on the ClientName
column header, it will refresh the report with the data sorted by the
ClientName.
Note: Remember that for each column you wish to sort by, you have to go
through the above drill and make sure that you set the values for all
the parameters defined on your report. This is very essential since you
do not wish to lose parameter values between sorting since the report
refreshes the data with each sort. Also you need to set the SortBy
parameter value to the column name you are sorting by and to obtain the
sort direction, you can use the function I have above. The advantage of
using the function above is that it allows you to sort the same column
in ascending or descending mode when you click the column repeatedly
(it alternates). If however you click on a new column for the very
first time, it defaults to ascending sort.
Task: Grouping by a column
In order to group by a certain column we created a small icon that we
embedded in the cell under the column header in a separate row right
below the column header. First off, we inserted a group row by using
the =E2=80=9Cinsert group=E2=80=9D menu. Here are the settings for that gro= up
(under Edit Group menu)
=E2=80=A2 General tab (grouping and sorting properties)
o Name: Gp1
o Group On: Expression=3D
=3Diif(Parameters!prmGroupBy.Value=3DFields!None.Value,"",Parameters!prmGro= upBy.Value
& ": " & Fields(iif(Parameters!prmGroupBy.Value
=3DFields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
o Sorting tab: Sort on expressions:
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DASC=E2=80=9D; Directi= on: Ascending
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DDEC=E2=80=9D; Directi= on: Descending
NOTE: We are using an expression for the groupBy since you can have any
column that you could group your report by. This way you can have
dynamic grouping in your report. In our dataset for the report, we
included a column called =E2=80=9CNone=E2=80=9D that returned an empty stri= ng. We
used that to default the value of the groupBy parameter when no
grouping was selected. Otherwise we would get an error each time the
report is loaded for the first time since the prmGroupBy would not be
populated.
=E2=80=A2 Moving on, for our case we would have an icon under the =E2=80=9C= City=E2=80=9D
column. Right click on that cell and go to properties.
=E2=80=A2 It will then take you to =E2=80=9Cimage properties dialog box=E2= =80=9D.
=E2=80=A2 From here, go to the Navigation tab
o Navigation Tab: Select Jump to Report radio button and enter the
report name (without the .rdl extension of course)
o Click on the Parameters button to go to the Parameters dialog as
before
o Insure that values are assigned to ALL of the remaining parameters on
the report as before.
o Below are the settings specific to the sort and group by parameters
Parameter name parameter value column
prmSortBy =3DParameters!prmSortBy.Value (note the difference between
this and the one before)
prmDirection =3DParameters!prmDirection.Value
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D "City","None","City")
(Replace City with the name of the column you are grouping by)
Documented by raghus, from Kizan Technologies
Edited and checked by Bryan AveryHi Bryan,
I have followed all your steps below, but am getting the following error
message when trying to view the report:
1) The value expression for the textbox â'textbox3â' contains an error:
[BC30057] Too many arguments to 'Public Function
DetermineDirection(strColumnName As String) As Object'.
2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
Do you know what would be causing this?
Thanks
Darren
"Bryan Avery" wrote:
> How to incorporate column-header click-driven sorting, and column
> header click-driven grouping in SQL Server Reporting Services:
> Create the following report parameters with the following details (you
> can change the parameter names to suit your naming convention):
> (1) Name=prmSortBy
> Prompt=Sort By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: Select non-queried
> Default values: none
> (2) Name=prmDirection
> Prompt=Direction
> Data Type=string
> Allow blank value: checked
> Available values: Label: ASC; Value:ASC
> Label: DESC; Value: DESC
> Default values: Non-queried, with the value "ASC" in the text box next
> to it.
> (3) Name=prmGroupBy
> Prompt=Group By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: select non-queried
> Default values: None
> Scenario:
> Suppose you have a report created using the traditional table. Let's
> also suppose you have the following columns in your report: ClientID,
> ClientName, City and SSN (clientID is numeric and the rest being alpha
> numeric data types). You wish to sort by the "ClientName" column and
> group by the "City" column.
> Task: Sorting by the ClientName column:
> â?¢ In the ClientName column header (that sits in a cell on the table
> header), right click and select properties.
> â?¢ Then click the advanced button to go to "Advanced Textbox
> properties"
> â?¢ Visibility tab settings:
> â?¢ Initial visibility: visible
> â?¢ Initial appearance of the toggle image...: collapsed
> â?¢ Navigation tab settings:
> â?¢ Hyperlink Action: select Jump to report and enter the name of the
> report in the textbox (ex. rptClient)
> â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> box
> â?¢ Parameters Dialog box entries:
> â?¢ Here, you must list ALL of the parameters defined in your report
> and assign values to them. Having done that, for our case, we concern
> ourselves with the sorting and grouping parameter value settings
> parameter name parameter value column
> prmSortBy ="ClientName"
> prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> Note: "DetermineDirection" is a function we incorporated into our
> report to return a sorting direction (ascending or descending) and
> below is the function (if you are unsure where to incorporate the
> function, highlight the table that holds the report and click on report
> menu and go to properties and on the "code" tab, you enter the
> function)
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
> If strColumnName = strPreviousColumn Then
> If strDirection = "ASC" Or strDirection = "" Then
> Return "DESC"
> Else
> Return "ASC"
> End If
> Else
> Return "ASC"
> End If
> End Function
> Highlight the table that contains the report, right-click and select
> properties. On the "Table Properties" dialog, go to the "Sorting" tab
> and enter the following:
> Expression => iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Ascending
> Expression => =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Descending
> That should be it! When you run the repot and click on the ClientName
> column header, it will refresh the report with the data sorted by the
> ClientName.
> Note: Remember that for each column you wish to sort by, you have to go
> through the above drill and make sure that you set the values for all
> the parameters defined on your report. This is very essential since you
> do not wish to lose parameter values between sorting since the report
> refreshes the data with each sort. Also you need to set the SortBy
> parameter value to the column name you are sorting by and to obtain the
> sort direction, you can use the function I have above. The advantage of
> using the function above is that it allows you to sort the same column
> in ascending or descending mode when you click the column repeatedly
> (it alternates). If however you click on a new column for the very
> first time, it defaults to ascending sort.
> Task: Grouping by a column
> In order to group by a certain column we created a small icon that we
> embedded in the cell under the column header in a separate row right
> below the column header. First off, we inserted a group row by using
> the â'insert groupâ' menu. Here are the settings for that group
> (under Edit Group menu)
> â?¢ General tab (grouping and sorting properties)
> o Name: Gp1
> o Group On: Expression=> =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> & ": " & Fields(iif(Parameters!prmGroupBy.Value
> =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> o Sorting tab: Sort on expressions:
> ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> NOTE: We are using an expression for the groupBy since you can have any
> column that you could group your report by. This way you can have
> dynamic grouping in your report. In our dataset for the report, we
> included a column called â'Noneâ' that returned an empty string. We
> used that to default the value of the groupBy parameter when no
> grouping was selected. Otherwise we would get an error each time the
> report is loaded for the first time since the prmGroupBy would not be
> populated.
> â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> column. Right click on that cell and go to properties.
> â?¢ It will then take you to â'image properties dialog boxâ'.
> â?¢ From here, go to the Navigation tab
> o Navigation Tab: Select Jump to Report radio button and enter the
> report name (without the .rdl extension of course)
> o Click on the Parameters button to go to the Parameters dialog as
> before
> o Insure that values are assigned to ALL of the remaining parameters on
> the report as before.
> o Below are the settings specific to the sort and group by parameters
> Parameter name parameter value column
> prmSortBy =Parameters!prmSortBy.Value (note the difference between
> this and the one before)
> prmDirection =Parameters!prmDirection.Value
> prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> (Replace City with the name of the column you are grouping by)
>
> Documented by raghus, from Kizan Technologies
> Edited and checked by Bryan Avery
>|||BTW: built-in support for sorting table columns by clicking on the table
headers will be available in the next release (RS 2005).
--
Regards
<<<Bryan Avery>>
"Darren" wrote:
> Hi Bryan,
> I have followed all your steps below, but am getting the following error
> message when trying to view the report:
> 1) The value expression for the textbox â'textbox3â' contains an error:
> [BC30057] Too many arguments to 'Public Function
> DetermineDirection(strColumnName As String) As Object'.
> 2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
> Do you know what would be causing this?
> Thanks
> Darren
> "Bryan Avery" wrote:
> > How to incorporate column-header click-driven sorting, and column
> > header click-driven grouping in SQL Server Reporting Services:
> >
> > Create the following report parameters with the following details (you
> > can change the parameter names to suit your naming convention):
> > (1) Name=prmSortBy
> > Prompt=Sort By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: Select non-queried
> > Default values: none
> >
> > (2) Name=prmDirection
> > Prompt=Direction
> > Data Type=string
> > Allow blank value: checked
> > Available values: Label: ASC; Value:ASC
> > Label: DESC; Value: DESC
> > Default values: Non-queried, with the value "ASC" in the text box next
> > to it.
> >
> > (3) Name=prmGroupBy
> > Prompt=Group By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: select non-queried
> > Default values: None
> >
> > Scenario:
> > Suppose you have a report created using the traditional table. Let's
> > also suppose you have the following columns in your report: ClientID,
> > ClientName, City and SSN (clientID is numeric and the rest being alpha
> > numeric data types). You wish to sort by the "ClientName" column and
> > group by the "City" column.
> >
> > Task: Sorting by the ClientName column:
> >
> > â?¢ In the ClientName column header (that sits in a cell on the table
> > header), right click and select properties.
> > â?¢ Then click the advanced button to go to "Advanced Textbox
> > properties"
> > â?¢ Visibility tab settings:
> > â?¢ Initial visibility: visible
> > â?¢ Initial appearance of the toggle image...: collapsed
> > â?¢ Navigation tab settings:
> > â?¢ Hyperlink Action: select Jump to report and enter the name of the
> > report in the textbox (ex. rptClient)
> > â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> > box
> >
> > â?¢ Parameters Dialog box entries:
> > â?¢ Here, you must list ALL of the parameters defined in your report
> > and assign values to them. Having done that, for our case, we concern
> > ourselves with the sorting and grouping parameter value settings
> > parameter name parameter value column
> > prmSortBy ="ClientName"
> > prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> > prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> > Note: "DetermineDirection" is a function we incorporated into our
> > report to return a sorting direction (ascending or descending) and
> > below is the function (if you are unsure where to incorporate the
> > function, highlight the table that holds the report and click on report
> > menu and go to properties and on the "code" tab, you enter the
> > function)
> >
> > Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> > strPreviousColumn as String, ByVal strDirection as String) As String
> > If strColumnName = strPreviousColumn Then
> > If strDirection = "ASC" Or strDirection = "" Then
> > Return "DESC"
> > Else
> > Return "ASC"
> > End If
> > Else
> > Return "ASC"
> > End If
> > End Function
> >
> > Highlight the table that contains the report, right-click and select
> > properties. On the "Table Properties" dialog, go to the "Sorting" tab
> > and enter the following:
> >
> > Expression => > iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Ascending
> >
> > Expression => > =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Descending
> >
> > That should be it! When you run the repot and click on the ClientName
> > column header, it will refresh the report with the data sorted by the
> > ClientName.
> >
> > Note: Remember that for each column you wish to sort by, you have to go
> > through the above drill and make sure that you set the values for all
> > the parameters defined on your report. This is very essential since you
> > do not wish to lose parameter values between sorting since the report
> > refreshes the data with each sort. Also you need to set the SortBy
> > parameter value to the column name you are sorting by and to obtain the
> > sort direction, you can use the function I have above. The advantage of
> > using the function above is that it allows you to sort the same column
> > in ascending or descending mode when you click the column repeatedly
> > (it alternates). If however you click on a new column for the very
> > first time, it defaults to ascending sort.
> >
> > Task: Grouping by a column
> > In order to group by a certain column we created a small icon that we
> > embedded in the cell under the column header in a separate row right
> > below the column header. First off, we inserted a group row by using
> > the â'insert groupâ' menu. Here are the settings for that group
> > (under Edit Group menu)
> > â?¢ General tab (grouping and sorting properties)
> > o Name: Gp1
> > o Group On: Expression=> > =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> > & ": " & Fields(iif(Parameters!prmGroupBy.Value
> > =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> > o Sorting tab: Sort on expressions:
> > ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> > ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> >
> > NOTE: We are using an expression for the groupBy since you can have any
> > column that you could group your report by. This way you can have
> > dynamic grouping in your report. In our dataset for the report, we
> > included a column called â'Noneâ' that returned an empty string. We
> > used that to default the value of the groupBy parameter when no
> > grouping was selected. Otherwise we would get an error each time the
> > report is loaded for the first time since the prmGroupBy would not be
> > populated.
> >
> > â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> > column. Right click on that cell and go to properties.
> > â?¢ It will then take you to â'image properties dialog boxâ'.
> > â?¢ From here, go to the Navigation tab
> > o Navigation Tab: Select Jump to Report radio button and enter the
> > report name (without the .rdl extension of course)
> > o Click on the Parameters button to go to the Parameters dialog as
> > before
> > o Insure that values are assigned to ALL of the remaining parameters on
> > the report as before.
> > o Below are the settings specific to the sort and group by parameters
> > Parameter name parameter value column
> > prmSortBy =Parameters!prmSortBy.Value (note the difference between
> > this and the one before)
> > prmDirection =Parameters!prmDirection.Value
> > prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> > (Replace City with the name of the column you are grouping by)
> >
> >
> > Documented by raghus, from Kizan Technologies
> > Edited and checked by Bryan Avery
> >
> >|||The following needs to be on one line:
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String|||thanks for resolving this
--
Regards
<<<Bryan Avery>>
"Nick Name" wrote:
> The following needs to be on one line:
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
>

How to include MSDE with Setup of custom applications

I am using VS .NET 2003 Bootstrapper Plug-In to create an
installer that redistribute the .NET Framework 1.1 and
MDAC 2.8 with my application.
How can I include MSDE and provide the switches in order
to install MSDE on clients machine as well?
Thanks
Hi,
Unfortunately, all the auto-install options currently have "issues". I'd
strongly suggest you:
1. Configure the setup.ini file to have your default settings.
2. Get the client to run the setup.exe program to install the MSDE.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"pankaj banga" <pankaj@.discussions.microsoft.com> wrote in message
news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
>I am using VS .NET 2003 Bootstrapper Plug-In to create an
> installer that redistribute the .NET Framework 1.1 and
> MDAC 2.8 with my application.
> How can I include MSDE and provide the switches in order
> to install MSDE on clients machine as well?
> Thanks
|||Thanks HTH,
Then how would I create my tables, stored procs and load
client specific data into tables. Actually I am new to
all this. I read about msde bootstrap installer, are
there issues with it as well?

>--Original Message--
>Hi,
>Unfortunately, all the auto-install options currently
have "issues". I'd
>strongly suggest you:
>1. Configure the setup.ini file to have your default
settings.
>2. Get the client to run the setup.exe program to
install the MSDE.
>HTH,
>--
>Greg Low [MVP]
>MSDE Manager SQL Tools
>www.whitebearconsulting.com
>"pankaj banga" <pankaj@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
an[vbcol=seagreen]
order
>
>.
>
|||hi,
"pankaj banga" <pankaj@.discussions.microsoft.com> ha scritto nel
messaggio news:230901c4a950$1bb0dcb0$a301280a@.phx.gbl
> Thanks HTH,
> Then how would I create my tables, stored procs and load
> client specific data into tables. Actually I am new to
> all this. I read about msde bootstrap installer, are
> there issues with it as well?
>
Microsoft provides a deployment toolkit, in release candidate at the current
time, you can dowload from
http://www.microsoft.com/downloads/d...displaylang=en
I did not installed it, but I think some database deployment feature are
present... and some drawbacks of this toolkit have been posted here... don't
know the current state and/or the final release...
personally I do deploy apps with a companion tool which will read and
execute DDL scripts as long as INSERT INTO scipts, BCP and so on.. other
ways are backup/restore and sp_attach_db
I already discused these 3 methods of mine in http://tinyurl.com/6ux7p and
http://tinyurl.com/4x8pv ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How to improve the query performance on cubes?

Hi,all experts here,

How could we effectively improve the query performance on cubes from a client? Can we create indexes on them? Or if not, what else can we try?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

There are a lot of things to try - partitioning, aggregations.... too much to discuss in a forums answer. Your best bet is to read the AS performance guide:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

Chris

|||

Thanks a lot.

Best regards,

sql

how to improve "update" speed

hi

in asp.net,i used sql server to store records.

in a table has 1 million records,but when i update the record,it is very slowly.

is "create index" helpful for "update" operation?

i need help,thanks a lot.

First ensure that you have properly created index on you table and primary key.

Second ensure that you have data in table only for one business period( on principle one year).

Make shrink on your data table.

Refactor your data index.

This will help you.

|||

Post the update statement you have as well as all the table information. Do an "EXEC sp_Help TableName" in your query analyzer and post the results here.

|||

Hi ndinakar:

mytable has 100,000 records, i use "exec sp_help mytable",its result is :

Name Owner Type Created_datetime

mytable dbo user table 2007-11-30 9:34:21

can i get further information

thanks a lot

|||

Hi yugiant,

Based on my understanding, you are now tring to creating indexes in your sql server database in order to improve your update performance. If I've understood you wrong, please feel free to tell me, thanks.

Yes I think using index is a good idea. Using indexes in your database will save a lot of time for locating the specific record. And since we need to first locate the record before updating it, I think using index is a great help for improving updating performance. exec sp_help will list properties of your table, including all the indexex you have made to your datatable. You can also get your datatable index information from Object Explorer(in the Indexes tree view node).

I strongly suggesting you reading this:http://msdn2.microsoft.com/en-us/library/ms189051.aspx I think it will be much of help to solve your problem. thanks.

Hope my suggestion helps

how to import the database data from backup file ?

Hello,All:

I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.

Now I want to import the backup data(generated by production server),I don't know how to do it.

Is it possible to do it ? anyone can give me a soluation ?

thanks in advanced!

->Open Enterprise manager of SQL Server 2000

-> Select Your Server

-> right click on your database

-> All Tasks -> Restore Database

-> Select from Device

-> click the button Select Devices

-> in the new window select Disk option

-> click add button

-> select your backup file in file name option

-> ok

->ok

->ok

how to import hundreds of CSV files into SQL server?

Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?

If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.

Thanks.Well ... am working on the same myself ... will let you know as soon as i figure it out.|||Really? Someone here was complaining that it's nearly impossible to get your hands on updated info for this stuff!

Anyway, you'll have to be logged on with sysadmin fixed security role to do the following:

declare @.cmd varchar(8000)
create table #files (fname varchar(128) null)
insert #files exec master.dbo.xp_cmdshell [dir <your_directory> /b]
delete #files where fname is null

After this code is run you have a list of all the files that you want to import. If needed, you can incorporate a filter into your DIR command to filter out anything you don't want.

Now you're ready to build your loop to BULK INSERT each file.|||Now you're ready to build your loop to BULK INSERT each file.

Well .. bulk insert requires the table to be present and we both do not have that luxury ... have posted on this before and did not get a satisfactory answer ... so need to think of something else

http://www.dbforums.com/showthread.php?threadid=980175|||I'll send you a VB app to do this...|||I was working on a DTS package using a Dynamic Properties tast to pull file names from a table, and update the path of a Text Input file, but getting it to iterate is the kicker...|||The main problem for me is not to get it to iterate but to get the size of the columns and the column names.

Excel uploads are better coz you can make a linked server to them.|||With a Text Input file in a DTS package, you can set it up to pull the first row as column names. Sizes are, I believe, automatic, but probably defaults to NVARCHAR(255).|||So, how would you like to determine the field size?|||Well, I BULK INSERT into the same table, into either TEXT or IMAGE field. Actually, I have more than 1 table, and I am dealing with archiving EDI transactions coming from/going to the state. IMAGE datatype I use to store all their PDF's and DOC's with their daily ammendments, and TEXT field for the actual transaction files. I also implemented archiving on the tables with TEXT field, so that the database doesn't have to be a tera-byte size for 2 years worth of transactions to be available.|||Dynamically based on the data ...

In case of an excel .. i am using a opendatasource query and then issuing a select * into ... but am not able to do that on csv and flat files|||but if it's a CSV, then the length of a field can change from line to line. I see that working only if it is a fixed-length field file.|||Thats exactly the problem ... and DTS is not an option for me|||Working on it...|||Is it okay if the fields default to some larger size (VARCHAR(255)), then you can shrink them manually?|||How can you load a table that does not exist?

How are you going to know the layout to build it?

I still say that for flat files you either generate a format file and bcp it in, or load it all in to a table with 1 column varchar(8000) and parse the data in to the destination table...

csv is easy if the tables already built...

why aren't the tables built?

How are you going to derive the structure of the file?

on the fly?|||Try this.

Not too flashy, certainly not rock solid, but it should work.

Customize the connection string first, replacing <servername> and <databasename>.

Browse to the folder holding the text files (it will iterate through all files, so be sure you only have CSV files there).

It creates a table named after the text file, adds fields using the first row (all varchar(255)), and runs through the file inserting data.|||I only had two small csv files to test with. Let me know if there are any major bugs.

If you really want, I can go through the file contents first to find the MAX size of each field before creating the table, but I want to go home tonight...|||I was working on the UPS calculator for a while and forget anything else. I will test the zip file that bpdwork created and let you know what happened.

I have the calculator down. The database is kind of big, I don't know how to let everyone share it. send me a mail if you need to take a look.

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp?frame=true
--
David Portas
SQL Server MVP
--|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How to implement Spatial Index in SQL Server?

Any one has an idea?

How should I implement R-Tree spatial index in SQL Server?

My idea now is to create UDT for my spacial coordinate and then I need the index on that UDT. How should I start?

? Start here: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2005-122 -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Pi314159@.discussions.microsoft.com> wrote in message news:1f35e2cf-f9e8-4e69-b2db-cba6da61ed8a@.discussions.microsoft.com... Any one has an idea? How should I implement R-Tree spatial index in SQL Server? My idea now is to create UDT for my spacial coordinate and then I need the index on that UDT. How should I start?|||Thanks for this pointer - We have implimented an R-Tree Bulk loading algorithm for a project we are working on but it is only held in memory. This implimentation lloks perfect for the larger datasets we expect to meet. I cannot find the download sample though - any clues?|||? It's part of this set: http://www.microsoft..com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Paul Dorey@.discussions.microsoft.com> wrote in message news:14d3d799-73aa-455f-96e2-b92bf523c0d5@.discussions.microsoft.com...Thanks for this pointer - We have implimented an R-Tree Bulk loading algorithm for a project we are working on but it is only held in memory. This implimentation lloks perfect for the larger datasets we expect to meet. I cannot find the download sample though - any clues?

How to implement shared member

We are doing a proof of concept to replace Essbase with SSAS. One of the tasks is to create a hierarchy a same member in two places. I know Analysis Services has not supported this feature in the past. Is there anything new in 2005 that would facilitate this?

Example:

Fee Type Dim

All Fee Types

FT1

FT2

FT3

FT4

FT5

FT6

Conversion Fee Types

FT2

FT4

Extension Fee Types

FT5

FT6

Thanks to some good posted hints from Richard Tkachuk, created a work around. So SSAS can't do true shared members well to my understanding neither can Hyerion Essbase in aggregate storeage mode. But what can be done is to create more members in the dimension that have no matching facts. Then write a few calc scripts to associate the measure data with the appropriate real member. Richards example was for a parent child heirarchy. Mine's a little simpler as I only needed a couple of attribute hierarchies. Here's some what I did to help any else that may need it. I'm also not the most experienced MDX coder so there may be a more efficient way to do this.

Changed Dimension Table: added a columns for an associated member, an attribute hierarcy, and a flag in dicating shared member.

CREATE TABLE [dbo].[FEE_TYP](
[FEE_TYP_CD] [char](3) NOT NULL,
[FEE_TYP_DESC] [varchar](62) NULL,
[FEE_TYP_ASSOCIATED] [char](2) NULL, -- Member Key for associated member
[FEE_TYP_SM_FLG] [char](1) NULL, -- Shared Member Flag
[FEE_TYP_HIERARCHY] [varchar](30) NULL) -- Attribute Hierarchy

Sample rows.

insert FEE_TYP values ('96', 'Conversion Refinance /', NULL, NULL, 'All Fee Typ')
insert FEE_TYP values ('A96', '96 - Conversion Refinance /', '96', 'Y', 'All Loan Cnvrt')
insert FEE_TYP values ('B96', '96 - Conversion Refinance /', '96', 'Y', 'Cnvrt Refi')
insert FEE_TYP values ('C96', '96 - Conversion Refinance /', '96', 'Y', 'RT Cnvrt Fee Chng')
insert FEE_TYP values ('D96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Fee Chng')
insert FEE_TYP values ('E96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Move')

Created a hierarchy called All Curr Fee Types

Sample calc:

scope ({[FEE TYP].[All Curr Fee Types].members},[FEE TYP].[FEE TYP SM FLG].&)
this = (StrToMember("[FEE TYP].[FEE TYP].&[" + [FEE TYP].[FEE TYP].currentmember.properties("FEE TYP ASSOCIATED") + "]"))
end scope
scope ({[FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt].children))
end scope
-- repeat for other members of the attribute heirarchy
scope ({[FEE TYP].[All Curr Fee Types].[ALL]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Fee Typ].children))
end scope

How to implement shared member

We are doing a proof of concept to replace Essbase with SSAS. One of the tasks is to create a hierarchy a same member in two places. I know Analysis Services has not supported this feature in the past. Is there anything new in 2005 that would facilitate this?

Example:

Fee Type Dim

All Fee Types

FT1

FT2

FT3

FT4

FT5

FT6

Conversion Fee Types

FT2

FT4

Extension Fee Types

FT5

FT6

Thanks to some good posted hints from Richard Tkachuk, created a work around. So SSAS can't do true shared members well to my understanding neither can Hyerion Essbase in aggregate storeage mode. But what can be done is to create more members in the dimension that have no matching facts. Then write a few calc scripts to associate the measure data with the appropriate real member. Richards example was for a parent child heirarchy. Mine's a little simpler as I only needed a couple of attribute hierarchies. Here's some what I did to help any else that may need it. I'm also not the most experienced MDX coder so there may be a more efficient way to do this.

Changed Dimension Table: added a columns for an associated member, an attribute hierarcy, and a flag in dicating shared member.

CREATE TABLE [dbo].[FEE_TYP](
[FEE_TYP_CD] [char](3) NOT NULL,
[FEE_TYP_DESC] [varchar](62) NULL,
[FEE_TYP_ASSOCIATED] [char](2) NULL, -- Member Key for associated member
[FEE_TYP_SM_FLG] [char](1) NULL, -- Shared Member Flag
[FEE_TYP_HIERARCHY] [varchar](30) NULL) -- Attribute Hierarchy

Sample rows.

insert FEE_TYP values ('96', 'Conversion Refinance /', NULL, NULL, 'All Fee Typ')
insert FEE_TYP values ('A96', '96 - Conversion Refinance /', '96', 'Y', 'All Loan Cnvrt')
insert FEE_TYP values ('B96', '96 - Conversion Refinance /', '96', 'Y', 'Cnvrt Refi')
insert FEE_TYP values ('C96', '96 - Conversion Refinance /', '96', 'Y', 'RT Cnvrt Fee Chng')
insert FEE_TYP values ('D96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Fee Chng')
insert FEE_TYP values ('E96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Move')

Created a hierarchy called All Curr Fee Types

Sample calc:

scope ({[FEE TYP].[All Curr Fee Types].members},[FEE TYP].[FEE TYP SM FLG].&)
this = (StrToMember("[FEE TYP].[FEE TYP].&[" + [FEE TYP].[FEE TYP].currentmember.properties("FEE TYP ASSOCIATED") + "]"))
end scope
scope ({[FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt].children))
end scope
-- repeat for other members of the attribute heirarchy
scope ({[FEE TYP].[All Curr Fee Types].[ALL]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Fee Typ].children))
end scope

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Check the WindowsIdentity class (particularly WindowsIdentity.Impersonate): http://msdn2.microsoft.com/en-us/library/system.security.principal.windowsidentity(VS.80).aspx

-- Robert

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Try one of these articles:

http://www.odetocode.com/Articles/215.aspx

http://www.odetocode.com/Articles/216.aspx|||Hey I STRUGGLED with this for some time and from grabbing other snippets of code and doing some of my own troubleshooting this is what I came up with and it works for me. Please note that in the second line of code you will need the IMPORTS of YOUR web service. I am using Webhost4life as my provider and this code works for me so I hope it helps someone out there to get started. If anyone has enhancements please let me know.

Imports Microsoft.VisualBasicImports com.mysite4now.sql332reportingImports Microsoft.Reporting.WebFormsImports System.Security.PrincipalPartialClass reportviewerInherits System.Web.UI.PagePublic Class ReportViewerCredentialsImplements IReportServerCredentialsPrivate _userNameAs String Private _passwordAs String Private _domainAs String Public Sub New(ByVal userNameAs String,ByVal passwordAs String,ByVal domainAs String) _userName = userName _password = password _domain = domainEnd Sub Public ReadOnly Property ImpersonationUser()As System.Security.Principal.WindowsIdentityImplements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUserGet Return Nothing End Get End Property Public ReadOnly Property NetworkCredentials()As System.Net.ICredentialsImplements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentialsGet Return New Net.NetworkCredential(_userName, _password, _domain)End Get End Property Public Function GetFormsCredentials(ByRef authCookieAs System.Net.Cookie,ByRef userNameAs String,ByRef passwordAs String,ByRef authorityAs String)As Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials userName = _userName password = _password authority = _domainReturn Nothing End Function End Class Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf Not IsPostBackThen Dim rptPathAs String rptPath = Request("Path")Dim rsAs New ReportingService ReportViewer1.ServerReport.ReportServerCredentials =New ReportViewerCredentials("username","password","") ReportViewer1.ServerReport.ReportServerUrl =New Uri("yourservername") ReportViewer1.AsyncRendering =False ReportViewer1.ShowDocumentMapButton =True ReportViewer1.DocumentMapCollapsed =False ReportViewer1.ServerReport.ReportPath = rptPathEnd If End SubEnd Class

|||

Hey, can i ask you for this line:
ReportViewer1.ServerReport.ReportServerCredentials = New ReportViewerCredentials("username", "password", "")

How you get the the password variable? if it from your database or from active directory?

Please advice, thank you.

|||

you can put it in, web.config or from a database. don't know abt active directory.

I have worked with this, and we put it in web.config.

i haven't tried for active directory.

How to ignore sp_runwebtask errors?

Hi,
I have some tables in my database that, upon modification, write a file
to disk with data. I have create the triggers that handle this, using
the sp_makewebtask stored procedure.
For these triggers to work, I needed to login from my application with
integrated security. This is not a problem and works fine.
Sometimes I also need to do some maintenance on the tables, and for
this purpose I cannot use integrated security (because I connect
through vpn an am not on the actual machine). When i then change
something on the tables I get an error: "SQL Web Assistant: Could not
open the output file".
The fact that if I'm doing maintenance, the file is not created, is not
a problem, so I hope that one of the following solutions is possible:
- Build error handling into the trigger that simply ignores the error
- Detect in the trigger that the current user is not a windows user,
and then skip the creation of the file
- something else.
What could be a solution for my problem?
BTW We are using SQL 2000 on Windows 2003 Server.
Regards, FelixFelix,

> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
You can use SUSER_SNAME() to determine this.
IF SUSER_SNAME() = 'MySQLServerLogin' ...
Robert
"felix planjer" <fplanjer@.gmail.com> wrote in message
news:1143541278.508476.286150@.e56g2000cwe.googlegroups.com...
> Hi,
> I have some tables in my database that, upon modification, write a file
> to disk with data. I have create the triggers that handle this, using
> the sp_makewebtask stored procedure.
> For these triggers to work, I needed to login from my application with
> integrated security. This is not a problem and works fine.
> Sometimes I also need to do some maintenance on the tables, and for
> this purpose I cannot use integrated security (because I connect
> through vpn an am not on the actual machine). When i then change
> something on the tables I get an error: "SQL Web Assistant: Could not
> open the output file".
> The fact that if I'm doing maintenance, the file is not created, is not
> a problem, so I hope that one of the following solutions is possible:
> - Build error handling into the trigger that simply ignores the error
> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
> - something else.
> What could be a solution for my problem?
> BTW We are using SQL 2000 on Windows 2003 Server.
> Regards, Felix
>