How to script permissions?

I'm using SQL Server 2000. I have an application that uses three
databases. Currently, when a new user is added (Windows Integration),
I go to each database, create the login then go to each spoc and give
that user rights.
Is there a faster more efficient way to do this? For example, I have
maybe two or three scripts (for various permission levels) that I can
drop in a user name and it does everything?
Thanks,
BrettYou could probably use the GRANT statement and dynamic SQL to do the trick.
"brett" <account@.cygen.com> wrote in message
news:1146001887.339002.206390@.g10g2000cwb.googlegroups.com...
> I'm using SQL Server 2000. I have an application that uses three
> databases. Currently, when a new user is added (Windows Integration),
> I go to each database, create the login then go to each spoc and give
> that user rights.
> Is there a faster more efficient way to do this? For example, I have
> maybe two or three scripts (for various permission levels) that I can
> drop in a user name and it does everything?
> Thanks,
> Brett
>|||Or, add the user to a Windows Group, and give that Group permissions.|||The Windows Group sounds like a good solution.
To be more automated, I'd like to build this process into application
deployment. Are there any spocs I can run to setup a new user login
and then start assigned specific permissions to objects in a database?
Thanks,
Brett|||If you want the user to have execute permission to every proc you could
use this
--Grab all the procedures for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME
INTO #Procedurelist
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),
'IsMSShipped') =0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY SPECIFIC_NAME
DECLARE
@.Loopid INT,
@.MaxId INT,
@.UserName VARCHAR(50)
--This is the user that will get the execute permissions
SELECT @.UserName = 'SomeUser'
--Grab start and end values for the loop
SELECT @.Loopid = 1,
@.MaxId = MAX(ID)
FROM #Procedurelist
DECLARE
@.SQL VARCHAR(500),
@.ProcName VARCHAR(400)
--This is where the loop starts
WHILE @.Loopid <= @.MaxId BEGIN
--grab the procedure name
SELECT @.ProcName = SPECIFIC_NAME
FROM #Procedurelist
WHERE ID = @.Loopid
--construct the statement
SELECT @.SQL = 'GRANT EXECUTE ON ' + @.ProcName + ' TO ' + @.UserName
PRINT (@.SQL) --change PRINT to EXECUTE if you want it to run
automatically
--increment counter
SET @.Loopid = @.Loopid + 1
END
--clean up
DROP TABLE #Procedurelist
if you need user defined functions also use this
http://sqlservercode.blogspot.com/2...or.html

Denis the SQL Menace
http://sqlservercode.blogspot.com/

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

How to Save Report Parameters

I am trying to design a C# .NET web application that allows the user to run
a report, then save the report parameters so that when they open it again,
they get the same parameters with refreshed data.
I think my design should go something like this:
1) Render the report using the web service
2) make a button or something for the user to save the report
3) When the user clicks the save report button, call a web service method to
retrieve the report ParameterValue[] collection of parameter values that
were last used to run the report.
4) Save the ParameterValue[] collection into a DB table
5) when the user goes back to the report, load the ParameterValue[]
collection back from the DB table and render the report with the saved
parameters
How do I accomplish step (3)'
I looked at the ReportingService.GetReportParameters Method, but it does not
return the parameter values! It just returns a collection of ReportParameter
objects, which have no properties for the current values of a report. This
is just a listing of report parameters defined for a report, not what their
current values are in the current session.
The only methods that return a ParameterValue[] collection are the
GetDataDrivenSubscriptionProperties and GetSubscriptionProperties methods.
Does this mean I have to create a subscription before I can retrieve the
values?
I also see that the Render method does return a ParameterValue[] collection
as an output parameter, but only if the report is being rendered as a Report
History Snapshot! Do I have to create a snapshot every time just to store
the report parameters?
The only other thing I can think of is to write my own code to display the
parameters and code my own "view report" button, so that I can manually
store the parameter values... I don't want to resort to this!! :-P
Please point me in the right direction, and I will follow up.
MalikTry this (I'm using VB.Net syntax)
Dim ReportParameters(Number of Parameters defined in your report) as
ParameterValue
Dim iCount as Integer
'Retrieve your parameter values from the database.
'Assume two parameters from this example.
For iCount = 1 to ReportParameters.Length
ReportParameters(iCount - 1) = New ParameterValue
Select Case iCount
Case 1
ReportParameters(iCount - 1).Name = "The name of your report parameter
defined in your report"
ReportParameters(iCount - 1).value = "Value from your database"
Case 2
ReportParameters(iCount - 1).Name = "The name of your report parameter
defined in your report"
ReportParameters(iCount - 1).value = "Value from your database"
End Select
Next
Once you have defined all of your report parameters, you can use
ReportParameters in the render method, and your report will have the last
values that were used in the report you are viewing.
"Abdul Malik Said" wrote:
> I am trying to design a C# .NET web application that allows the user to run
> a report, then save the report parameters so that when they open it again,
> they get the same parameters with refreshed data.
> I think my design should go something like this:
> 1) Render the report using the web service
> 2) make a button or something for the user to save the report
> 3) When the user clicks the save report button, call a web service method to
> retrieve the report ParameterValue[] collection of parameter values that
> were last used to run the report.
> 4) Save the ParameterValue[] collection into a DB table
> 5) when the user goes back to the report, load the ParameterValue[]
> collection back from the DB table and render the report with the saved
> parameters
> How do I accomplish step (3)'
> I looked at the ReportingService.GetReportParameters Method, but it does not
> return the parameter values! It just returns a collection of ReportParameter
> objects, which have no properties for the current values of a report. This
> is just a listing of report parameters defined for a report, not what their
> current values are in the current session.
> The only methods that return a ParameterValue[] collection are the
> GetDataDrivenSubscriptionProperties and GetSubscriptionProperties methods.
> Does this mean I have to create a subscription before I can retrieve the
> values?
> I also see that the Render method does return a ParameterValue[] collection
> as an output parameter, but only if the report is being rendered as a Report
> History Snapshot! Do I have to create a snapshot every time just to store
> the report parameters?
> The only other thing I can think of is to write my own code to display the
> parameters and code my own "view report" button, so that I can manually
> store the parameter values... I don't want to resort to this!! :-P
> Please point me in the right direction, and I will follow up.
> Malik
>
>|||Mike,
Thanks a lot for your help.
I guess I was not clear in my stating of "Step 3", which was to determine
which parameters the user last used to run the report. By this, I meant to
say:
How can I programmatically get from the report object model which parameters
were last used to run the report? I have to get these first before I can
store them in the database. Retrieving them and reconstituting them is not
difficult after they are saved. The problem is, how do I find the values in
the first place?
Here is a better description:
The user clicks "View Report" from the parameters toolbar
The user clicks "Save Report" button which I have made elsewhere on the page
In the code for the "Save Report" button, how do I reference the parameter
values that were last chosen? I need to get these values from the report
first, before I can store them in the database. This is what I don't know
how to do.
Any more help would be greatly appreciated.
Malik
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:B8C4E6E5-5AA7-4D8F-8CB3-2B01387392B4@.microsoft.com...
> Try this (I'm using VB.Net syntax)
> Dim ReportParameters(Number of Parameters defined in your report) as
> ParameterValue
> Dim iCount as Integer
> 'Retrieve your parameter values from the database.
> 'Assume two parameters from this example.
> For iCount = 1 to ReportParameters.Length
> ReportParameters(iCount - 1) = New ParameterValue
> Select Case iCount
> Case 1
> ReportParameters(iCount - 1).Name = "The name of your report
parameter
> defined in your report"
> ReportParameters(iCount - 1).value = "Value from your database"
> Case 2
> ReportParameters(iCount - 1).Name = "The name of your report
parameter
> defined in your report"
> ReportParameters(iCount - 1).value = "Value from your database"
> End Select
> Next
> Once you have defined all of your report parameters, you can use
> ReportParameters in the render method, and your report will have the last
> values that were used in the report you are viewing.
> "Abdul Malik Said" wrote:
> > I am trying to design a C# .NET web application that allows the user to
run
> > a report, then save the report parameters so that when they open it
again,
> > they get the same parameters with refreshed data.
> >
> > I think my design should go something like this:
> >
> > 1) Render the report using the web service
> > 2) make a button or something for the user to save the report
> > 3) When the user clicks the save report button, call a web service
method to
> > retrieve the report ParameterValue[] collection of parameter values that
> > were last used to run the report.
> > 4) Save the ParameterValue[] collection into a DB table
> > 5) when the user goes back to the report, load the ParameterValue[]
> > collection back from the DB table and render the report with the saved
> > parameters
> >
> > How do I accomplish step (3)'
> >
> > I looked at the ReportingService.GetReportParameters Method, but it does
not
> > return the parameter values! It just returns a collection of
ReportParameter
> > objects, which have no properties for the current values of a report.
This
> > is just a listing of report parameters defined for a report, not what
their
> > current values are in the current session.
> >
> > The only methods that return a ParameterValue[] collection are the
> > GetDataDrivenSubscriptionProperties and GetSubscriptionProperties
methods.
> > Does this mean I have to create a subscription before I can retrieve the
> > values?
> >
> > I also see that the Render method does return a ParameterValue[]
collection
> > as an output parameter, but only if the report is being rendered as a
Report
> > History Snapshot! Do I have to create a snapshot every time just to
store
> > the report parameters?
> >
> > The only other thing I can think of is to write my own code to display
the
> > parameters and code my own "view report" button, so that I can manually
> > store the parameter values... I don't want to resort to this!! :-P
> >
> > Please point me in the right direction, and I will follow up.
> >
> > Malik
> >
> >
> >|||Hi Malik,
I've just completed an application which does exactly what you are
trying to do :) Mine does a little more like managing subscriptions,
schedules etc but the viewing and saving of the report/params is the
core of my app. I can't share the code as it's a commercial app but
will gladly help out with snippets etc :)
I did it the following way:
1/ Reports are already on the server so I loop through the selected
report and obtain all the params for that report. I then
enable/disable webusercontrols for each of the params on screen.
see code below to get the params from a report (formatting screwed by
newsreader :( )
private void GetSpecificReportParameters(string report)
{
if(rs == null) rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ReportParameter[] parameters parameters =rs.GetReportParameters(report, null, false, null, null);
if (parameters != null)
{
string []nameArray = new string[50];
int i = 0;
foreach (ReportParameter rp in parameters)
{
// this is each of the names banged into an array
nameArray[i] = rp.Name;
i++;
}
// now loop through em and switch on/off the correct params
foreach (string str in nameArray)
{
if(str == null) // nothing so carry on
continue;
if(str.ToString() == "SurnameFrom")
{
SurnameWebUserControl.Visible = true;
}
// etc etc for all params found
}
}
}
2/ now i've got all the params for the report and the relevant
controls to fill in these params, my user can now set their params and
click on either View or Save report.
View builds up a string from the webusercontrols, appending the &
after each param & then passing this into the ReportViewer control on
the form.
Save does the same building of the string but instead of rendering to
the ReportViewer control, it simply saves to a database field along
with the path to the report i.e. heres one I've just created:
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&SurnameFrom=SMITH&SurnameTo=SMITH
When I recall the saved report, I simply pass that line in as the URL
prefixing the server address to it - then it's passed to the
ReportViewer control to render to screen.
Very simple and works very well - hope it makes sense to you. The
major advantage of this mechanism is that all we need to do is upload
a new report with params, the application will not need recompilation
in order to deal with the params (as long as no new params have been
added that the previous app knows nothing of!)
Si
On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Mike,
>Thanks a lot for your help.
>I guess I was not clear in my stating of "Step 3", which was to determine
>which parameters the user last used to run the report. By this, I meant to
>say:
>How can I programmatically get from the report object model which parameters
>were last used to run the report? I have to get these first before I can
>store them in the database. Retrieving them and reconstituting them is not
>difficult after they are saved. The problem is, how do I find the values in
>the first place?
>Here is a better description:
>The user clicks "View Report" from the parameters toolbar
>The user clicks "Save Report" button which I have made elsewhere on the page
>In the code for the "Save Report" button, how do I reference the parameter
>values that were last chosen? I need to get these values from the report
>first, before I can store them in the database. This is what I don't know
>how to do.
>Any more help would be greatly appreciated.
>Malik
>|||Hi Si,
That is a good way to solve this problem. If I understand what you are
doing, then it seems like you are basically writing your own code to replace
the parameter toolbar. This way, you know exactly what the values of the
controls are when the user clicks "save".
I could write a solution like this, but I was hoping there was a way of
doing it without developing code to replace the parameter selection toolbar.
Since there is already a parameter toolbar, I would like to use it. This
way, any report could be added to my application, since I wouldn't have to
assume anything about parameters.
I am currently trying to see if there is anything in the client-side
javascript generated for the report that will help me. I can see the current
report parameters there, but I am still trying to figure out how to retrieve
them properly...
Anyway, all of this has helped me think through my design, so many thanks
for your ideas.
Malik
"Si" <no@.spam.thanks> wrote in message
news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
> Hi Malik,
> I've just completed an application which does exactly what you are
> trying to do :) Mine does a little more like managing subscriptions,
> schedules etc but the viewing and saving of the report/params is the
> core of my app. I can't share the code as it's a commercial app but
> will gladly help out with snippets etc :)
> I did it the following way:
> 1/ Reports are already on the server so I loop through the selected
> report and obtain all the params for that report. I then
> enable/disable webusercontrols for each of the params on screen.
> see code below to get the params from a report (formatting screwed by
> newsreader :( )
> private void GetSpecificReportParameters(string report)
> {
> if(rs == null) rs = new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> ReportParameter[] parameters parameters => rs.GetReportParameters(report, null, false, null, null);
> if (parameters != null)
> {
> string []nameArray = new string[50];
> int i = 0;
> foreach (ReportParameter rp in parameters)
> {
> // this is each of the names banged into an array
> nameArray[i] = rp.Name;
> i++;
> }
> // now loop through em and switch on/off the correct params
> foreach (string str in nameArray)
> {
> if(str == null) // nothing so carry on
> continue;
> if(str.ToString() == "SurnameFrom")
> {
> SurnameWebUserControl.Visible = true;
> }
> // etc etc for all params found
> }
> }
> }
>
> 2/ now i've got all the params for the report and the relevant
> controls to fill in these params, my user can now set their params and
> click on either View or Save report.
> View builds up a string from the webusercontrols, appending the &
> after each param & then passing this into the ReportViewer control on
> the form.
> Save does the same building of the string but instead of rendering to
> the ReportViewer control, it simply saves to a database field along
> with the path to the report i.e. heres one I've just created:
>
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
rnameFrom=SMITH&SurnameTo=SMITH
> When I recall the saved report, I simply pass that line in as the URL
> prefixing the server address to it - then it's passed to the
> ReportViewer control to render to screen.
> Very simple and works very well - hope it makes sense to you. The
> major advantage of this mechanism is that all we need to do is upload
> a new report with params, the application will not need recompilation
> in order to deal with the params (as long as no new params have been
> added that the previous app knows nothing of!)
> Si
>
>
>
> On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
> <diplacusis@.hotmNOSPAMail.com> wrote:
> >Mike,
> >
> >Thanks a lot for your help.
> >
> >I guess I was not clear in my stating of "Step 3", which was to determine
> >which parameters the user last used to run the report. By this, I meant
to
> >say:
> >
> >How can I programmatically get from the report object model which
parameters
> >were last used to run the report? I have to get these first before I can
> >store them in the database. Retrieving them and reconstituting them is
not
> >difficult after they are saved. The problem is, how do I find the values
in
> >the first place?
> >
> >Here is a better description:
> >
> >The user clicks "View Report" from the parameters toolbar
> >The user clicks "Save Report" button which I have made elsewhere on the
page
> >
> >In the code for the "Save Report" button, how do I reference the
parameter
> >values that were last chosen? I need to get these values from the report
> >first, before I can store them in the database. This is what I don't know
> >how to do.
> >
> >Any more help would be greatly appreciated.
> >
> >Malik
> >|||No probs,
I still have the param toolbar there, it's just hidden using the
rc:Parameters=false tag in the url.
I understand what you are trying to achive but from my limited
understanding, it's currently not possible as there is no statefull
save of the params once the report is generated, only beforehand.
Si
On Fri, 20 Aug 2004 12:52:22 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Hi Si,
>That is a good way to solve this problem. If I understand what you are
>doing, then it seems like you are basically writing your own code to replace
>the parameter toolbar. This way, you know exactly what the values of the
>controls are when the user clicks "save".
>I could write a solution like this, but I was hoping there was a way of
>doing it without developing code to replace the parameter selection toolbar.
>Since there is already a parameter toolbar, I would like to use it. This
>way, any report could be added to my application, since I wouldn't have to
>assume anything about parameters.
>I am currently trying to see if there is anything in the client-side
>javascript generated for the report that will help me. I can see the current
>report parameters there, but I am still trying to figure out how to retrieve
>them properly...
>Anyway, all of this has helped me think through my design, so many thanks
>for your ideas.
>Malik
>|||If you come up with an elegant solution to this problem, I'd love to
hear about it (i.e. please share it with the group). We would also
very much like to be able to save and restore parameters without
reinventing the wheel (a.k.a. the parameter selection toolbar :-)
Brad.
On Fri, 20 Aug 2004 12:52:22 +0100, "Abdul Malik Said"
<diplacusis@.hotmNOSPAMail.com> wrote:
>Hi Si,
>That is a good way to solve this problem. If I understand what you are
>doing, then it seems like you are basically writing your own code to replace
>the parameter toolbar. This way, you know exactly what the values of the
>controls are when the user clicks "save".
>I could write a solution like this, but I was hoping there was a way of
>doing it without developing code to replace the parameter selection toolbar.
>Since there is already a parameter toolbar, I would like to use it. This
>way, any report could be added to my application, since I wouldn't have to
>assume anything about parameters.
>I am currently trying to see if there is anything in the client-side
>javascript generated for the report that will help me. I can see the current
>report parameters there, but I am still trying to figure out how to retrieve
>them properly...
>Anyway, all of this has helped me think through my design, so many thanks
>for your ideas.
>Malik
>"Si" <no@.spam.thanks> wrote in message
>news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
>> Hi Malik,
>> I've just completed an application which does exactly what you are
>> trying to do :) Mine does a little more like managing subscriptions,
>> schedules etc but the viewing and saving of the report/params is the
>> core of my app. I can't share the code as it's a commercial app but
>> will gladly help out with snippets etc :)
>> I did it the following way:
>> 1/ Reports are already on the server so I loop through the selected
>> report and obtain all the params for that report. I then
>> enable/disable webusercontrols for each of the params on screen.
>> see code below to get the params from a report (formatting screwed by
>> newsreader :( )
>> private void GetSpecificReportParameters(string report)
>> {
>> if(rs == null) rs = new ReportingService();
>> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
>> ReportParameter[] parameters parameters =>> rs.GetReportParameters(report, null, false, null, null);
>> if (parameters != null)
>> {
>> string []nameArray = new string[50];
>> int i = 0;
>> foreach (ReportParameter rp in parameters)
>> {
>> // this is each of the names banged into an array
>> nameArray[i] = rp.Name;
>> i++;
>> }
>> // now loop through em and switch on/off the correct params
>> foreach (string str in nameArray)
>> {
>> if(str == null) // nothing so carry on
>> continue;
>> if(str.ToString() == "SurnameFrom")
>> {
>> SurnameWebUserControl.Visible = true;
>> }
>> // etc etc for all params found
>> }
>> }
>> }
>>
>> 2/ now i've got all the params for the report and the relevant
>> controls to fill in these params, my user can now set their params and
>> click on either View or Save report.
>> View builds up a string from the webusercontrols, appending the &
>> after each param & then passing this into the ReportViewer control on
>> the form.
>> Save does the same building of the string but instead of rendering to
>> the ReportViewer control, it simply saves to a database field along
>> with the path to the report i.e. heres one I've just created:
>>
>/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
>rnameFrom=SMITH&SurnameTo=SMITH
>> When I recall the saved report, I simply pass that line in as the URL
>> prefixing the server address to it - then it's passed to the
>> ReportViewer control to render to screen.
>> Very simple and works very well - hope it makes sense to you. The
>> major advantage of this mechanism is that all we need to do is upload
>> a new report with params, the application will not need recompilation
>> in order to deal with the params (as long as no new params have been
>> added that the previous app knows nothing of!)
>> Si
>>
>>
>>
>> On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
>> <diplacusis@.hotmNOSPAMail.com> wrote:
>> >Mike,
>> >
>> >Thanks a lot for your help.
>> >
>> >I guess I was not clear in my stating of "Step 3", which was to determine
>> >which parameters the user last used to run the report. By this, I meant
>to
>> >say:
>> >
>> >How can I programmatically get from the report object model which
>parameters
>> >were last used to run the report? I have to get these first before I can
>> >store them in the database. Retrieving them and reconstituting them is
>not
>> >difficult after they are saved. The problem is, how do I find the values
>in
>> >the first place?
>> >
>> >Here is a better description:
>> >
>> >The user clicks "View Report" from the parameters toolbar
>> >The user clicks "Save Report" button which I have made elsewhere on the
>page
>> >
>> >In the code for the "Save Report" button, how do I reference the
>parameter
>> >values that were last chosen? I need to get these values from the report
>> >first, before I can store them in the database. This is what I don't know
>> >how to do.
>> >
>> >Any more help would be greatly appreciated.
>> >
>> >Malik
>> >
>|||Unfortunately, I have hit the wall with this one. The last hope was to use
the current URL that can be found in the client-side javascript generated
by the report server. However, I hear from microsoft that these variables
are for their internal use only, and could change in the future.
Since I don't want my reports to stop working when v2 is released, I can't
do that! :-@. I have given up hope for an "elegant solution".
I have looked at the SOAP method of integration as well as the URL method,
and it is just not possible to save report parameters as selected by the
user! The only way you can save them is if you re-invent your own parameter
inputs! And keep in mind that a complete integration solution should be able
to accept any possible combination of report parameters!
I think this is another oversight in the design of this product from
microsoft. They have specially designed ways to cache report data, saving
history, etc... But what about the more common case of wanting fresh data
for the same parameters? I am amazed that they did not think of this...
Malik
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:uMytSxqhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi Si,
> That is a good way to solve this problem. If I understand what you are
> doing, then it seems like you are basically writing your own code to
replace
> the parameter toolbar. This way, you know exactly what the values of the
> controls are when the user clicks "save".
> I could write a solution like this, but I was hoping there was a way of
> doing it without developing code to replace the parameter selection
toolbar.
> Since there is already a parameter toolbar, I would like to use it. This
> way, any report could be added to my application, since I wouldn't have to
> assume anything about parameters.
> I am currently trying to see if there is anything in the client-side
> javascript generated for the report that will help me. I can see the
current
> report parameters there, but I am still trying to figure out how to
retrieve
> them properly...
> Anyway, all of this has helped me think through my design, so many thanks
> for your ideas.
> Malik
> "Si" <no@.spam.thanks> wrote in message
> news:l9nbi052eoggh9ucpro71t1t72lbnou72i@.4ax.com...
> > Hi Malik,
> >
> > I've just completed an application which does exactly what you are
> > trying to do :) Mine does a little more like managing subscriptions,
> > schedules etc but the viewing and saving of the report/params is the
> > core of my app. I can't share the code as it's a commercial app but
> > will gladly help out with snippets etc :)
> >
> > I did it the following way:
> >
> > 1/ Reports are already on the server so I loop through the selected
> > report and obtain all the params for that report. I then
> > enable/disable webusercontrols for each of the params on screen.
> > see code below to get the params from a report (formatting screwed by
> > newsreader :( )
> >
> > private void GetSpecificReportParameters(string report)
> > {
> > if(rs == null) rs = new ReportingService();
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> >
> > ReportParameter[] parameters parameters => > rs.GetReportParameters(report, null, false, null, null);
> >
> > if (parameters != null)
> > {
> > string []nameArray = new string[50];
> > int i = 0;
> > foreach (ReportParameter rp in parameters)
> > {
> > // this is each of the names banged into an array
> > nameArray[i] = rp.Name;
> > i++;
> > }
> >
> > // now loop through em and switch on/off the correct params
> > foreach (string str in nameArray)
> > {
> > if(str == null) // nothing so carry on
> > continue;
> >
> > if(str.ToString() == "SurnameFrom")
> > {
> > SurnameWebUserControl.Visible = true;
> > }
> > // etc etc for all params found
> > }
> > }
> > }
> >
> >
> > 2/ now i've got all the params for the report and the relevant
> > controls to fill in these params, my user can now set their params and
> > click on either View or Save report.
> >
> > View builds up a string from the webusercontrols, appending the &
> > after each param & then passing this into the ReportViewer control on
> > the form.
> >
> > Save does the same building of the string but instead of rendering to
> > the ReportViewer control, it simply saves to a database field along
> > with the path to the report i.e. heres one I've just created:
> >
> >
>
/Reports/MyReport&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&Su
> rnameFrom=SMITH&SurnameTo=SMITH
> >
> > When I recall the saved report, I simply pass that line in as the URL
> > prefixing the server address to it - then it's passed to the
> > ReportViewer control to render to screen.
> >
> > Very simple and works very well - hope it makes sense to you. The
> > major advantage of this mechanism is that all we need to do is upload
> > a new report with params, the application will not need recompilation
> > in order to deal with the params (as long as no new params have been
> > added that the previous app knows nothing of!)
> >
> > Si
> >
> >
> >
> >
> >
> >
> >
> > On Fri, 20 Aug 2004 10:30:45 +0100, "Abdul Malik Said"
> > <diplacusis@.hotmNOSPAMail.com> wrote:
> >
> > >Mike,
> > >
> > >Thanks a lot for your help.
> > >
> > >I guess I was not clear in my stating of "Step 3", which was to
determine
> > >which parameters the user last used to run the report. By this, I meant
> to
> > >say:
> > >
> > >How can I programmatically get from the report object model which
> parameters
> > >were last used to run the report? I have to get these first before I
can
> > >store them in the database. Retrieving them and reconstituting them is
> not
> > >difficult after they are saved. The problem is, how do I find the
values
> in
> > >the first place?
> > >
> > >Here is a better description:
> > >
> > >The user clicks "View Report" from the parameters toolbar
> > >The user clicks "Save Report" button which I have made elsewhere on the
> page
> > >
> > >In the code for the "Save Report" button, how do I reference the
> parameter
> > >values that were last chosen? I need to get these values from the
report
> > >first, before I can store them in the database. This is what I don't
know
> > >how to do.
> > >
> > >Any more help would be greatly appreciated.
> > >
> > >Malik
> > >
>sql

How to save a file in SQL Server

I have the need to allow a user to upload a file via the web and then save it into a database. THen I also need to be able to retrieve the file for download.

Can someone give me guidance as to code or a sample on how to do this.

Thanks.the sql column is usually set to a type of "image" as this holds binary data.
the file gets uploaded to the webserver and saved to a temp folder.
iv seen samples in the forum where the file is read into a byte array and then from the byte array, is put into the db via a stored proc.|||Look at BLOB, although it's normally better not to store binary data in your DB.

How to run user defined function on sql server 2005

Dear all

I wants to run sql server user defined function when linked two server.

I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?

Regards

Jerminxxx

You can call the function with [ServerName] .[DatabaseName].[User/Schema Name].[FunctionName].

|||

Just use the select ServerName.DatabaseName.OwnerName.Functionname

i.e Like select IFoundd10.Employee.dbo.getenc()

__________________________________________________________

Don't forget to click "Mark as Answer" on the post that helped you.

|||

Thanks for ur reply.

when i try to use this technique i got some error like .

"contains more than the maximum number of prefixes."

Can u help me

|||

Which version of SQL Server are you using ? Can you post some of the details of the 2 servers that you've linked, the function and from where are you trying to execute the function ?

|||

I'm using sql server 2005.

For Example:

We have two sql server named as server1 and server2. These two servers are already linked.

On server1 we have some user defined function.Amoung the function one function called getencf().

What i want .I wants to run function getencf() on server 2 environment.

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

CAn you help me

Regards

Jerminxxx

|||

jerminxxx:

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

Are you getting the same error ("contains more than the maximum number of prefixes.") which you posted in one of your posts ?

Are you able to run the function from server1 ?

|||

it will e hard due to prefixes number, if your function returns single value try to replace it( or add extra ) stored procedure on the server which will call you function. If your function returns results set try to use OPENQuery to execute it on remote server.

How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> ¦b¶l¥ó
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl ¤¤¼¶¼g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
>

How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi
With SQL 2005 there is an ALTER TRACE permission that allows you create
traces.
John
"unknown" wrote:

>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> bl
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>

How to run Reporting service in MS-Access environment?

Hi

Please someone tell me, is it possbile to run Reporting service in MS-Access environment?
I mean to say i have to use MS-Access as user interface to design and generate report
(instead of Visual studio IDE) and the Sql server 2005 should be Report Server.
If it is possible then how can i implement it.

Thanks and Regards

Altaf Nizamuddin

Hi Altaf,

It is possible to design reports in Access, and you can import it with BIDS with some restrictions. Well, i never tried, but if you can import the report viewer control in Access and you write a custom code behind, then you should be able to render the report. Again, I never tried it, just an idea.

Regards,

Janos

ps.: maybe I'll try it

|||http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx|||

Hi Adolf,

Thanks a lot but it will never work in my sceneraio,

The article explains "How to Migrate Access reports to reporting services", but i want to run Reporting service in MS-Access environment, I mean to say i have to use MS-Access as user interface to design and generate report (instead of Visual studio IDE) and the Sql server 2005 should be Report Server.

Thanks and Regards

Altaf Nizamuddin

|||So just design it in Access and migrate it.

Personally I'd just take the hit on learning how to create reports in RS.sql

How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to run DTS from stored proc

I am running a DTS package from stored proc as,

Exec [master].[dbo].[xp_cmdshell] "dtsrun /S Server /U User /P Pass /N Package Name"

But i am getting an error :

The system can not find the path specified.

But i am able to run the same from DTS Design Wizard.

Please anyone of you help me out.set @.str varchar(8000)
set @.dtsname varchar(8000)
set @.dtsname=physical path and name of your dts package
SET @.str='DtsRun '+ '/F ' + @.DTSNAME

EXEC master..xp_Cmdshell @.str


I believe it will help you

Subhasish Ray

subhasishray@.sify.com

How to run a DTS as a trusted user in a stored proc

I get an error when i try to run a dts
but when i create a new dts and select the right server and user
everything went well.
Can someone help me?What is the error you receive ?

This is a cut from a microsoft article which may address your problem:
"Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run. "

Here is the article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;q269074)sql

How to return the first row of each group of rows

I have a group of rows that grouped by host and user. I want to return the first row of each group of hosts. I have struggled with this all day and need some help. With this T-SQL I have built the table:

USE Test

GO

IF OBJECT_ID(N'[dbo].Sample') IS NOT NULL

DROP TABLE [dbo].Sample

GO

-- Create the Sample table

CREATE TABLE [dbo].Sample(

ID [int] IDENTITY NOT NULL,

Host [varchar](128) NOT NULL,

Username [varchar](128) NOT NULL,

Seconds [int] NOT NULL)

GO

SET NOCOUNT ON

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Smith',120012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Jone',100012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Cole',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Lee',70012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Lee',8012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Cole',5012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Smith',4012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Lee',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Cole',7012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Smith',60012);

SET NOCOUNT OFF

I want to write a select statement that will return the first row of each host group:

C001 Smith 120012

C002 Lee 8012

C003 Doe 90012

Thanks for pointing me in the right direction.

Ken

Give a look to the ROW_NUMBER() function in books online; partition by host and order by seconds.|||

Try:

select

a.*

from

dbo.Sample as a

where

not exists(

select

*

from

dbo.Sample as b

where

b.Host = a.Host

and b.[ID] < a.[ID]

)

go

-- 2005

;with cte

as

(

select

Host, Username, Seconds,

row_number() over(partition by Host order by [ID]) as rn

from

dbo.Sample

)

select

*

from

cte

where

rn = 1

go

AMB

|||

Also,

select

a.*

from

dbo.Sample as a

where

a.[ID] = (

select

min(b.[ID])

from

dbo.Sample as b

where

b.Host = a.Host

)

go

AMB

How to retrive a value using a query string?

Hello,

I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.

This is what I have:

<script runat="server"> Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @.Password) AND (users.username = @.Username)" conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@.Username", SqlDbType.VarChar, 50) cmd.Parameters("@.Username").Value = Me.Login1.UserName cmd.Parameters.Add("@.Password", SqlDbType.VarChar, 50) cmd.Parameters("@.Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else 'Response.Write("Invalid credentials") End If myReader.Close() End Sub</script>

I would like to know how can I get now the "user.FirstName" and pass it to a session variable?
how should I code it?

thanks,

Inside your read have

Session["FirstName"]=myReader.getString(2)

Is there any reason that you're getting 7(I think) fields from the database and throwing them away?

Also, it's poor form to store unencrypted passwords in the database. At least use xor or something.

How to retrieve system oledb Provider list

In my app I have to list of "oledb provider" to let the user choose the connection method. As follow:
1.Jet 4.0 OLE DB Provider
2.OLE DB Provider for DTS Packages
3.OLEDB Provider for Indexing Service
4.OLEDB Provider for ODBC Drivers
5.OLEDB Provider for OLAP Services
6.OLEDB Provider for Olap Services 8.0
7.OLEDB Provider for Oracle
8.OLEDB Provider for SQL Server
9.OLEDB Simple Provide
Which doesn't not support must be disabled.
Use which funciton can retrieve the Provider list of system or something like that.
Thanks.
--Master..xp_enum_oledb_providers

If you want to keep the resule,Create a table with the column returned by the above query.And

insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||en ... I still do not know how ..
where is the master..xp_enum_oledb_providers
is that a function?
can show me a sample ?
many thanks.
Originally posted by ClaireHsu
Master..xp_enum_oledb_providers

If you want to keep the resule,Create a table with the column returned by the above query.And

insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||Can show me a sample code to list the oledb providers of current system.
Thanks.|||I really want to know does the system support:

1.SQLOLEDB
2.DTSPackageDSO
3.SQLReplication.OLEDB
4.MSDataShape
5.VSEE Versioning Enlistment Manager Proxy Data Source
6.ADsDSOObject
7.MSOLAP
8.MSDAIPP.DSO
9.MSDASQL
10.MSDASQL Enumerator
11.Microsoft.Jet.OLEDB.4.0
12.Microsoft.Jet.OLEDB.3.51
13.SQLOLEDB Enumerator
14.MSDAOSP
15.MSDAORA
...
How to perform this check/text of the user's system in my app.
In my app the unsupport oledb provider must be visable.
--
Thanks.

how to retrieve one record

I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Thanks.No can do, unless the application was set up to archive changes.

Your only option is to restore the database to a new database from a backup and then transfer that record back into your production database.|||Thanks. I think so. If i want to restore the database to new database, i have to create a new database first, then restore, is that right? I tried, it failed.|||No, but it is the method I usually use.

You can restore the database backup to a new database all in one step, if you wish.

Make sure on the second page of the Restore Database Dialog box that you have correct file paths for the new database and log files. That is probably what generated your error.

How to restrict user to tab (no mouse) selection -question transferred to VB Forms

How to restrict user to tab (no mouse) selection -question transferred to VB FormsIt seems like the best source of help for a forms (or webform) based application is by posting in one of the variuos Visual Studion forums.

How to restrict user to tab (no mouse) selection

How to restrict user to tab (no mouse) selectionIt seems like the best source of help for a forms (or webform) based application is by posting in one of the variuos Visual Studion forums.

How to restrict evil create scripts?

Hi,

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

Regards,

WardWard,

I would say as long as your permissions are set right you don't have to
worry. If your user only have rights in their own databases they
shouldn't be able to accessother user databases. Xp_Cmdshell which
would be able to delete files or run other OS commands is (by default)
only available to members of the sysadmin role.
So I would say create a empty datbase and grant the user only db_owner
or ddl_admin rights within the database.

Markus|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
>
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
>
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

Ward

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

>For a service I'm working on I need to ask the user for their database
>create script. It's used to re-create the users database schema in a
>temporary database on a in-house server in an automated fashion.
>>
>For security reasons, I need to be sure that the create script can only
>create tables, columns etc and not things like snooping in other
>databases and/or formatting the server.
>>
>Can you give me pointers about what the minimum grants are to let good
>script execute successfully and evil scripts fail?

>
First of all, which version of SQL Server including service pack do you
have?
>
As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.
>
>

|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

Hi Erland,
>
Thanks for the tips!
>
We are running SQL Server 2005.

In that case you should grant CONTROL on the dbo schema for the database
user. Maybe they are creating other scheams, but then they need to include
GRANTs for that.

You should also use

select name, is_trustworthy_on, is_db_chaining_on
from sys.databases

to make sure that the databases are not marked as trustworthy, or available
for DB-chaning.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx