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 MSDE Backup

Hi guys,
I have an application that runs on MSDE. I need to back up the database on a
dialy basis. The backup file gets written to this folder (C:\backup) and
then I have found a backup software that will automatically write it to a
DVD+RW, overwriting the previous backup file.
To make the schedule easy I will use 31 DVD+RWs, one for each day of the
month. That way I will always have at least a month worth of backups.
I was hoping that somebody could help me with a small SQL script that will
backup my database from Monday thru Saturday, automatically. Also it'd be
great if the backup file would contain a name and the date of that day.
Thanks a lot for your help.
Hi,
MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below
URL for more info.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Thanks
Hari
SQL Server MVP
"Tom Bombadill" <Genius_poster@.yahoo.com> wrote in message
news:%23D3lQ7zsFHA.2792@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have an application that runs on MSDE. I need to back up the database on
> a dialy basis. The backup file gets written to this folder (C:\backup) and
> then I have found a backup software that will automatically write it to a
> DVD+RW, overwriting the previous backup file.
> To make the schedule easy I will use 31 DVD+RWs, one for each day of the
> month. That way I will always have at least a month worth of backups.
> I was hoping that somebody could help me with a small SQL script that will
> backup my database from Monday thru Saturday, automatically. Also it'd be
> great if the backup file would contain a name and the date of that day.
> Thanks a lot for your help.
>
|||Hi Hari,
Thanks for your attempt to help me. Unfortunately for your suggestion to
work, I have to install SP3a and for some reason I cannot successfully apply
that. I constantly get the "The instance name specified is invalid" error,
even though I did not specify an instance name and there's only one
installaiton of MSDE on my machine.
What do you suggest?
|||hi Tom,
Tom Bombadill wrote:
> Hi Hari,
> Thanks for your attempt to help me. Unfortunately for your suggestion
> to work, I have to install SP3a and for some reason I cannot
> successfully apply that. I constantly get the "The instance name
> specified is invalid" error, even though I did not specify an
> instance name and there's only one installaiton of MSDE on my machine.
> What do you suggest?
you can have a look at a free prj of mine, at the link following my sign,
qhich implements a UI similar to Enterprise Manager where you can define a
SQL Server job to backup your db and of course define your schedules as
desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> you can have a look at a free prj of mine, at the link following my
> sign, qhich implements a UI similar to Enterprise Manager where you
> can define a SQL Server job to backup your db and of course define
> your schedules as desired...
alternatively, you can prepare a cmd file like
<-->
OSQL -Usa -Pyour_pwd -Q"BACKUP DATABASE pubs TO DISK = 'C:\Pubs.bak' WITH
INIT" >c:\err.txt
<-->
and schedule it via standard OS AT or SCHTASKS, the native Win scheduler...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
You're an angel, thanks a lot!
I downloaded your interface and installed it. I scheduled a backup and will
be testing this thoroughly.
A couple of questions re the interface if you don't mind:
1- I don't want to accumulate backups to the same file, since I will be
taking a fresh full backup on a separate DVD+RW everyday. How do I make sure
the backups do not keep appending to the same file day after day? Do I do
that just by leaving the ADD box unchecked, in the backup properties?
2- How can make it so that the backup file contains the day of the backup?
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Andrea,
> You're an angel, thanks a lot!
you are wellcome.. thank you :D

> I downloaded your interface and installed it. I scheduled a backup
> and will be testing this thoroughly.
> A couple of questions re the interface if you don't mind:
> 1- I don't want to accumulate backups to the same file, since I will
> be taking a fresh full backup on a separate DVD+RW everyday. How do I
> make sure the backups do not keep appending to the same file day
> after day? Do I do that just by leaving the ADD box unchecked, in the
> backup properties?
leaving the "add to media" check box unchecked will add the "WITH INIT"
statement to the full backup statement, so that every backup set will be
initialized..

> 2- How can make it so that the backup file contains the day of the
> backup?
can you please expand on this? I'm sorry but I did not understand your
requirement..
and for DbaMgr2k related questions please feel free to contact me directly,
in order not to be OT on this public microsoft NG...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
[vbcol=seagreen]
What I mean by that is to have the backup file name include the date the
backup was performed. I'll give you an example:
For today, it would be called "SJDB 09072005.bak"
For tomorrow, "SJDB 09082005.bak"
For the day after tomorrow, "SJDB 09092005.bak"
And so on...
I hope that makes sense!
Andrea, once again thank you for your service to the community and sharing
your hard work with others.
|||Also Andrea,
Do you know how long it would take for a full backup to run for a full size
MSDE database (2048 MB)?
I ask because I wanted to know how much time to allocate before I schedule
the copy of the backup file to DVD.
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Hi Andrea,
>
> What I mean by that is to have the backup file name include the date
> the backup was performed. I'll give you an example:
> For today, it would be called "SJDB 09072005.bak"
> For tomorrow, "SJDB 09082005.bak"
> For the day after tomorrow, "SJDB 09092005.bak"
> And so on...
> I hope that makes sense!

ok, you have to "edit" the backup statement of the generated job's step ...
assuming you are backing up pubs database to C:\ (replace C:\ with the
existing folder you want to backup to), you can provide the file name to
include the current date casting (
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp )
GETDATE() function result as required..
the final statement will look like:
DECLARE @.FileName nvarchar(25)
SET @.FileName = 'C:\' + 'pubs ' + CONVERT(varchar(10) , GETDATE() , 110 ) +
'.bak'
BACKUP DATABASE [pubs] TO DISK = @.FileName WITH INIT ,
NOUNLOAD ,
NAME = N'pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
as regard your date format, I'd prefer the standard ISO format, thats to say
YYYYMMDD (CONVERT using 112 style), as this data format , 20050908 (for
today) is more readable and sorts better then 09082005..
you can go further, in the "advanced tab", and specify you want to "output"
the result of the execution (like standard messages and/or errors, if any)
to a text file.. if you want to, specify a file name (a text file) in the
"Transact-SQL script command options" ->output file
this will output, in case of success, something like
<-->
Job 'BackUp DB ['pubs'] - #08/09/2005 12.25.12#' : Step 1, 'BackUp DB
['pubs']' : Began Executing 2005-09-08 12:37:16
53 percent backed up. [SQLSTATE 01000]
99 percent backed up. [SQLSTATE 01000]
Processed 224 pages for database 'pubs', file 'pubs' on file 1. [SQLSTATE
01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. [SQLSTATE
01000]
BACKUP DATABASE successfully processed 225 pages in 0.242 seconds (7.586
MB/sec). [SQLSTATE 01000]
<-->
or the problem found, if any, you can inspect...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

sql

How to Schedule and Run a SSIS package

Hi

Does any one know how to run

How to Schedule and Run a SSIS

Package for certain time intervals ?

I have created an application which will do some data transfering from one database

to another databse based on the start

time and end time values(Which is been already configured in some tables).

What I am exactly looking is that my application should do data transffering

Based on only the time intervals(Startime and End Time)

I wanted to execute my package only these time intervals. How to do this

Can any one help please.

Thanks & Regards

Deepu M.I

It sounds like you have a custom application that runs the packages at certain intervals. If that's the case, then just load the packages using the object model and then execute them at the given intervals. If you want to execute the packages using another tool, you can use Agent.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

|||

Yashman wrote:

Hi Kirk,

I have a SQL Server 2005 Advance Express Edition installed and I could not find SQL Server Agent as mentioned.

Yashman,
No need to post your question in multiple threads...|||

Hi Phil,

My question was related to the issue discussed above your message..

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 or retrieve a picture from a table in sql server 2000

hi ,
am wondering how to save or retrieve an image in sql server 2000 table
using vb application or access thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
http://support.microsoft.com/defaul...kb;en-us;153238
http://support.microsoft.com/defaul...kb;en-us;194975
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Saliba" <josephs73@.hotmail.com> wrote in message
news:OWE7fj3HFHA.3472@.TK2MSFTNGP09.phx.gbl...
> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||You just "select " the colname from the table the same way you would any
other column data. On the client side, you need t otreat the output as a
byte array, and "eed" it to whatever it is being used in ... The techniques
for that part are dependant on what and how it is being used, and the
technology you are doing it in (VB6, VB/C#.Net, ASP, HTML, etc. etc. )
"Joe Saliba" wrote:

> hi ,
> am wondering how to save or retrieve an image in sql server 2000 table
> using vb application or access thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

How to save DTS package as an application

Hi,

I want create stand alone application from DTS package. I remember I
did one time ago, I couldnt recall how I did it.

Thanks.If you want to run DTS as an executable then the two main options are
to use the DTSRUN utility (see Books Online) or to invoke the package
using DTS's COM object model (see "DTS Programming" in Books Online)

--
David Portas
SQL Server MVP
--|||more importantly..

you can save a dts package as a vb bas module and then compile it into
a vb6 app right?

hth

aaron|||Yes, I forgot abvout that. Select Package, Save As and select "Visual
Basic File" from the location dropdown.

--
David Portas
SQL Server MVP
--|||that, single-handedly is the reason i haven't gotten into this .NET
crap yet

you can't save it as VB.net can you?

-aaron

How to save a Database project using DTE?

Hi All,

How can we save a Database project (not a SQL Server project) using Application Object Model? I was able to save a SQL Server project using the DTE like this:

Project project = null;
Solution solution = _applicationObject.Solution;
foreach (Project prj in solution.Projects)
{
if (prj.Name.Equals(projectName))
{
project = prj;
break;
}
}
if (project != null)
{
project.Save(project.FileName);
}

But for an Oracle Database project it gives an exception saying that Save() method is not implemented in DTE. A similar kind of problem occured for me when I tried to add an item to a Oracle Database project using DTE.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=495993&SiteID=1

Can anybody give me a hint to find a solution for this?

Best regards,
Imesh

Sorry, generic database projects don't implement DTE.

However, there is a workaround using the base ide. in your macro code, you need to make the solution explorer that active tool window, and then navigate down to the project node and select it. Then run

DTE.ExecuteCommand ("File.SaveSelectedItems")

That should do it.

|||Thanks Kenneth! I will try that out..!

Best regards,
Imesh

How to rollback a transaction in Data Access Layer

Hi,

I am having a application in which from the front end i am saving details of three different things

i.Enquiry Details

ii.Parts Details

iii.Machine details

i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

Do you want to store the data independtly. Even the tail 2 tables failed you want to successfully commit the first insert?

Pls explain the last statement?

|||

You can use a user transaction which can be opend through Sqlconnection.begintransaction()

See the MSDN for more details:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.begintransaction.aspx

The transaction is tied to the connection but can be commited separately.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for Reply,

The answers for your questions are below

1.Do you want to store the data independtly?

yes i am storing the data in three different tables.

2.Even the tail 2 tables failed you want to successfully commit the first insert?

No i want to roll back the entire transaction.

How to revoke write, update access from public role

Hi,
I have an application that gives all the write, update access to public role
, how could I quickly revoke all the Write and Update access from Public, in
stead of uncheck all the checked boxes?
Thanks.Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'revoke all on ' + QUOTENAME(name) + ' from [public]'
from sysobjects where type in('U','P','V','TR','IF','FN')
and objectproperty(id,'IsMSShipped')=0
go
Tahnks
Hari
MCDBA
"Hong" <anonymous@.discussions.microsoft.com> wrote in message
news:41AE885E-A0CB-4542-B6A9-843454B33A66@.microsoft.com...
> Hi,
> I have an application that gives all the write, update access to public
role, how could I quickly revoke all the Write and Update access from
Public, instead of uncheck all the checked boxes?
> Thanks.

How to return an entire table using store procedure

hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

jOriginally posted by nano_electronix
hi all

this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.

I know how parameters can be passed in and out of store procedures, but this returns single value parameters.

when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.

But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?

thanks for taking your time to read.

Cheers

j

Hi, maybe I did not understand your problem entirely, but a stored procedure returning a recordset is as simple as

create procedure P as select * from sales
go

exec p
go

Hope, this helps.|||but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp

besides given the store procedure you shown there, how would you get it into a recordset/dataset using either asp or asp.net.

for example, in asp.net i make use of dataadapter to retrieve a table into a dataset (which can store multiple table) but if i use store procedure i am not sure how that can be done.

cheers

j|||i think i might know why you don't understand my problem

the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.

wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)

when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.

if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.

Please help

J|||Hi

Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.

Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.

Regards

Richard...

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@.CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();|||I had the same problem about a month ago. I think what you're looking for is the datareader. Here's an example of how it works:

Dim connection As New SqlConnection()
Dim cmdSelect As SqlCommand
Dim reader As SqlDataReader
Dim Num As Integer

Num = 12
connection = SqlConnection1

cmdSelect = New SqlCommand("exec sp_getNum @.date = " & Num), connection)
If connection.State <> ConnectionState.Open Then connection.Open()
reader = cmdSelect.ExecuteReader
datalist.DataSource = reader
datalist.DataBind()
reader.Close()
connection.Close()

If you need more tables, just created more readers. Is that what you're looking for?|||Originally posted by nano_electronix
but wat happens when there are multiple select statements

how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
j

I would guess you would want to have one select statement per sproc. and then create a recordset for each executed sproc?|||thanx guys

I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.

Originally posted by nano_electronix

i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).

J|||hi guys

first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.

i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.

i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.

cheers :)

j

how to return a value in a stored procedure?

Hi,
i use a stored procedure for my asp.net application which must return a
value: (number of items). How can i do that?
I tried this but don't know how to giive te found value back.
Thansk
Chris
ALTER PROCEDURE [dbo].[mysp]
AS
declare @.returnValue int
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select COUNT(DISTINCT numberofitems) from items
END
set @.returnValue = ?
return @.returnValueYou need to use an output parameter. Have a look at these:
http://msdn2.microsoft.com/en-us/library/ms971497.aspx
http://www.sommarskog.se/share_data.html
http://www.informit.com/articles/ar...8&seqNum=9&rl=1
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris" <ch@.spam.it> wrote in message
news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i use a stored procedure for my asp.net application which must return a
> value: (number of items). How can i do that?
> I tried this but don't know how to giive te found value back.
> Thansk
> Chris
>
> ALTER PROCEDURE [dbo].[mysp]
> AS
> declare @.returnValue int
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> select COUNT(DISTINCT numberofitems) from items
> END
> set @.returnValue = ?
> return @.returnValue
>|||Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:erDK$IzIIHA.4712@.TK2MSFTNGP04.phx.gbl...
> You need to use an output parameter. Have a look at these:
> http://msdn2.microsoft.com/en-us/library/ms971497.aspx
> http://www.sommarskog.se/share_data.html
> http://www.informit.com/articles/ar...8&seqNum=9&rl=1
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris" <ch@.spam.it> wrote in message
> news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||This is an example I just posted to my book's "Ask Answer" support site. It
calls a SP that returns the Identity value as a Return value. No, you don't
need an output parameter for this--the Return will work fine if you can pass
back an Int.
/****** Object: Stored Procedure dbo.Author_Insert Script Date: 12/7/98
5:08:41 PM ******/
ALTER Procedure Author_Insert @.Author varchar(50), @.Year_Born smallint
As
INSERT INTO Authors
( Author, Year_Born)
VALUES ( @.Author, @.Year_born )
RETURN SCOPE_IDENTITY()
'Copyright (c) 2007 Beta V Corporation. All rights reserved.
' For demonstration purposes only. No warranty of any kind expressed or
implied.
Imports System.Data.SqlClient
Public Class Form1
Dim cn As SqlConnection
Dim cmd As SqlCommand
Sub New()
InitializeComponent()
cn = New SqlConnection(My.Settings.BiblioConnection)
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click
Dim intRa, intIdentity As Integer
Try
cmd = New SqlCommand("Author_Insert", cn)
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@.Author", tbAuthor.Text)
.Parameters.AddWithValue("@.Year_Born", tbYearBorn.Text)
.Parameters.Add("@.ReturnValue", SqlDbType.Int) _
.Direction = ParameterDirection.ReturnValue ' To capture
RETURN value
cn.Open()
intRa = .ExecuteNonQuery
intIdentity = CInt(.Parameters("@.ReturnValue").Value)
End With
If intRa = 1 Then
MsgBox(String.Format("Row inserted. Identity value {0}", _
intIdentity), MsgBoxStyle.Exclamation)
Else
MsgBox("Insert failed")
End If
Catch exsql As SqlException
MessageBox.Show(exsql.ToString)
Catch ex As Exception
Debug.Assert(False, ex.ToString)
Finally
cn.Close()
End Try
End Sub
End Class
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Chris" <ch@.spam.it> wrote in message
news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i use a stored procedure for my asp.net application which must return a
> value: (number of items). How can i do that?
> I tried this but don't know how to giive te found value back.
> Thansk
> Chris
>
> ALTER PROCEDURE [dbo].[mysp]
> AS
> declare @.returnValue int
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> select COUNT(DISTINCT numberofitems) from items
> END
> set @.returnValue = ?
> return @.returnValue
>

how to retrive the names of sql servers running in a network through an vb6 application

I want to retrive the names of sql servers running in a network through an vb6 application .

I found a API which list out the SQL server names in the network , but I am able to connect only to the main

SQL server in the network and for other instances it gives error " DBNETLIB] Connection Open () SQL server doesn?ˉt exist or access denied"

Can anyone help me?

Thanks

Goldie

hy dear

i am also woking in scenario, can you plz tell me which API you are using...

|||You can write a WMI script to read the registry. Instance names can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL/InstalledInstances key

how to retrieve random records from table

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

Quote:

Originally Posted by pritisarode

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

select top 10 * from table order by newid(), mydate

How to retrieve database password

I previously had a web server that was running a application that used a
MSDE database. This server had a motherboard failure and I was not able to
repair it. I did get all the data off the drives so the MSDE SQL databases
did get copied. The problem is the admin password for those database is not
known to me and the previous admin does not remember. I need this password
so that I can reinstall the web application and give it the admin password
for the old databases. The tool the software company gave me to pull the
password does not work, as it pulls the information from the registry (which
is not the original as I have a new boot drive). Does anyone know how to
pull MSDE SQL database passwords without booting from the original drive?
There has to be a way to get all of the databases back since the data is
intact.
If you have the MDF files, just attach the database to a new instance
of MSDE with sp_attachDB.
If you mean a specially created user in the database or server AFAIK
which the application will use for connection and which is not known to
you, this password cannot be recovered. (If you have no access to the
stored password in the source code)
HTH, Jens Suessmeyer.
|||All I have are the old .SQL files no MDF files were created.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136994106.307693.42900@.g43g2000cwa.googlegro ups.com...
> If you have the MDF files, just attach the database to a new instance
> of MSDE with sp_attachDB.
> If you mean a specially created user in the database or server AFAIK
> which the application will use for connection and which is not known to
> you, this password cannot be recovered. (If you have no access to the
> stored password in the source code)
> HTH, Jens Suessmeyer.
>
|||If it is a SQL Server Authenticaion you should look in that Script
files (which they are supposed to be) for sp_addlogin which is used to
create the SQL Server auth. users.
HTH, jens Suessmeyer.
|||1. Go to Start | Run | type in Command.
2. Type the following command in the DOS prompt.
OSQL S <SERVERNAME> -E =
Note Be sure to replace SERVERNAME with the name of your SQL server.
Typically this is the name of your computer.
Note If this statement does not run successfully try entering it again
excluding the '=' sign.
3. Press Enter.
4. Type the line exactly as it is written below where ok is your new
password:
1> EXEC sp_password NULL, 'ok', 'sa'
5. Press Enter.
6. Type the line below:
2> Go
7. Press Enter. This will change the sa password from a value unknown to
ok (this can be changed in step 4).
=
"J Coldwater" <jams@.purdue.edu> wrote in message
news:u5y$dTsFGHA.2896@.TK2MSFTNGP10.phx.gbl...
> I previously had a web server that was running a application that used a
> MSDE database. This server had a motherboard failure and I was not able to
> repair it. I did get all the data off the drives so the MSDE SQL databases
> did get copied. The problem is the admin password for those database is
> not known to me and the previous admin does not remember. I need this
> password so that I can reinstall the web application and give it the admin
> password for the old databases. The tool the software company gave me to
> pull the password does not work, as it pulls the information from the
> registry (which is not the original as I have a new boot drive). Does
> anyone know how to pull MSDE SQL database passwords without booting from
> the original drive? There has to be a way to get all of the databases back
> since the data is intact.
>

how to retreive data depends on another data ...

my application will add and delete and update records in db

my problem is when to update and delete ..

I have two dropdownboxes one to retreive servers and one to retreive dbs in the server ...

SqlDataAdapter daa =

new SqlDataAdapter("SELECT * FROM DB_tbl"+","+"Servers_tbl where Servers_tbl.Server_Name == '"+ DropDownList4.SelectedValue +"' ", sqlConnection1 );

this is the structure of each table >>

servers_tbl : SRV_ID,Server_name

DB_teb : DB_ID,SRV_ID,DB_name

the dropdown box retreive just the names ... so what is the base of where part of the command

hint : the DB_tbl does not have the server name but has the sever id

Hi Adorer,

Based on my understanding, you need to know when to issue a data bind on the dropdownlist which displays db names.

I think you can handle the SelectedIndexChanged event of the Server DropDownList to fill the db names.

You can use 2 SqlDataSources and use a ControlParameter to achieve this. Also, make sure you have set AutoPostback property to true.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

How to restrict un -authorised persons from using database files

hello,

I am using MS SQL db for my web application . I wanna know if there is any way to protect /secure database files from copied or used by any un authorised person.

Right now any one can copy the data base files(.data and .log) files from MS SQL's data folder, and can use it any where without any problem. But i would like to restrict any un authorised person to use it. Please let me if there is any way to avoide this.

Please help me in this problem

All suggesstions are welcome.

thank you.

-Archana

Hi,

you will have to povide additional folder security on your system, or encrypt the data in every column. There is no other way to do this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de