API to create table, stored procedure, etc.

In SQL Manager I can right click on a stored procedure, table, etc. and I am presented with a list of options one of which is "Create >". Clicking this I can get a script that will create the respective stored procedure or table and write the create script to the clipboard, a file, or an SQL query window. I want to automate this to essentially selectively "back up" our data base by creating one large script that will create the tables and stored procedures from the database. Is there an API, tool, or library call that I can get access to the code that implements these menu selections?

Thank you.

Kevin

Yes. Look up the SMO reference in BOL. It has all of the capabilities and the full class library to do what you need.

API support on SQL Server ?

Is it true that MS is pulling support of API calls to SQL
Server for the upcoming and future release(s) ?It depends on exactly what you men by API calls. All DBMS's need some API,
otherwise you couldn't write applications against the DBMS. So, there is
definitely supported API's for Yukon. The only thing I know is that The old
dead DbLibrary is in the "danger zone", but I don't know the exact official
words about Db Library.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Michael" <mykliv@.htomail.com> wrote in message
news:c33c01c4085f$08ce2720$a101280a@.phx.gbl...
> Is it true that MS is pulling support of API calls to SQL
> Server for the upcoming and future release(s) ?

API Reference for SQLDMO?

Ive been googling for a while now and i cant seem to find the API reference for SQLDMO.dll. Can any kind person paste an url? :)

The only thing I want to use it for is to know what kind of exceptions SQLServer.Connet() and SQLServer.attachDB() throws, but an url would be nice for futher use...

There are a couple of places you can find it. If you installed SQL Server, then you can find it in Books Online.

Otherwise, go here: http://msdn2.microsoft.com/en-us/library/ms133993.aspx

API Information

Can someone direct me to the APi information that would allow me to retrieve
the "Last Run date" of a report as well as the schedule information that
belongs to that report?>From Reporting Services BOL 2005:
Report.ExecutionTime
Gets the date and time that the report was generated.
Namespace: Microsoft.ReportingServices.ReportRendering
Assembly: Microsoft.ReportingServices.Processing (in
microsoft.reportingservices.processing.dll)
C#
public DateTime ExecutionTime { get; }
Regards,
Thiago Silva

API functions

hi
How can I use an API function in a stored procedure in sql server 2000?
tanxHi
Not directly! You may call a COM object using the sp_OA* procedures or write
an extended stored procedure. Depending on what you may want to do there may
be a procedure that already provides the functionality or an alternative
method of untaking the task.
John
"mtgoli" wrote:

> hi
> How can I use an API function in a stored procedure in sql server 2000?
> tanx|||
"John Bell" wrote:
> Hi
> Not directly! You may call a COM object using the sp_OA* procedures or wri
te
> an extended stored procedure. Depending on what you may want to do there m
ay
> be a procedure that already provides the functionality or an alternative
> method of untaking the task.
> John
>
> "mtgoli" wrote:
>
Dear John
Can u please introduce som references about using COM object in sql? I've
never worked with COM object.
tanx|||Hi
This is an example:
http://msdn.microsoft.com/library/d...r />
_2ktw.asp
Searching for sp_OACreate on msdn.microsoft.com or google should turn
up more!
John

API function

Hi
How can I use an API function in a stored procedure in sql server 2000?2 ways. First you can write a wrapper in C and compile it into a DLL and bring it into SQL as an extended stored procedure (see examples in BOL). Second, if an API belongs to an ActiveX/COM object, - you can use it directly through sp_OAxxx. If it doesn't, - write a wrapper in C++ or VB and compile it into an ActiveX component.

API for Synonyms

Hi Experts:

I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).

I am using GetSchema method on DBConnection object and it works fine for
oracle.

SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.

Thanks

AK

bets thing would be to use the SMO libraries, a sample script would be:

Code Snippet

foreach (Synonym s in s.Databases["Somedb"].Synonyms)

{

Console.WriteLine(s.Name);

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de

API for Roles

Hi,

Do we have an API for creating Roles? I want to build a UI instead for filling in the roles I will set for my cell security depending on who accesses the cube.

cherriesh

You can use the AMO library for creating roles from .Net or you could build the raw XMLA commands and send them using something like the ascmd sample.

API for notificationEnumeration in notification

hi all
i have found for function as function:SubscriptionEnumeration in API of notification service, i want display myAlert(notifications) in(for example: one day).
the Function SubscriptionEnumerationdisplay Subscription of (subscriberId,subscriptionType,nsApplication)
it can be made this ?

thanks
HungN

i don't think SQL notification services isn't support for this? isn't it? any advice?

API for creating user or group

What's the SOAP API call to create a user or group? It seems that when someone successfully logs into Report Server, a new user is created for that user. However, is it possible to create a list of users on demand?There are no explicit APIs for managing groups or users. In fact, the report server doesn't even know anything about groups - group membership is handled by the Windows APIs. Assuming that users are authenticated and authorized to access resources, the user is created automatically.|||I am using Forms Authentication. Report Manager has the ability to add user/group so there must be an API available. Report Server must know about users/groups because there's a database table that stores user info with relationships to policies and roles.|||

If you are using Forms auth then somebody has written a custom security extension that manages users and groups for you. (Perhaps you are using the sample?) In that case you can see if the security extension provides an API for adding users. If you are using the sample security extension, it does not provide a mechanism (outside of the logon page), you would have to add directly to the table it uses.

You are correct that RS has a table of users and policies, but it does not technically control this list. It always defers to the security extension. When a user comes in RS ask the security extension for the user name and then stores it so that it can keep track of owners. It also stores information on policies, but only to pass back to the security extension to allow the security extension to evaluate if a user has permission to perform the current operation.

|||I wrote the Forms Auth security extension and everything is working fine. I am just confused as to when RS create users and assign roles to them. For example, I want to initially create 5 users and give them permissions to certain folders. Those users are already created in the my own database but not in RS database. So if I use the API to set permissions for them, does RS automatically create the users in its own database? I am also curious as to what actually happens when a new user/group is created via Report Manager. Since Report Manager is built using the same exposed ReportServices2005 SOAP API, there must be a way to create users.|||

RS only adds the users to it's database when it needs to reference the user. If a user creates an item in the catalog or subscribes to a report, the user will be added to our database.

As you said, Report Manager uses the Soap API so it doesn't create any users, it only makes soap calls which may result in a new user row in the RS database.

Is there a reason you feel that the RS database needs to have the user table populated? As long as your security extension knows the users, what RS stores shouldn't be an issue. Perhaps if you explain the issue you are facing we could address that.

|||I want to use Reporting Services to manage reports with several clients. When a new client is added, I will create a folder under the Home folder and create user accounts for their administrators. Then I will assign roles for each user to the folder. So I guess that as soon as I assign roles, that user will be created in the RS database.|||Yep, that is correct.|||

Has anyone tried using the ASP.NET 2.0 Application Services (Membership and Role Management) together with custom extension to authenticate users?

I'm in the process of trying to configure this for a client. We are trying to use as much "out of the box" stuff as possible. Any samples would be usefull.

API for controlling replication?

Rather than having merge replication performed on a schedule, I need to
programmatically invoke it. Is this possible, and where can I find
documentation about how to do it?
David
That depends on what you are trying to accomplish.
Some people use Windows Synchronization Manager which can be run on users
laptops. It is a little difficult to deploy and doesn't really support SQL
Authentication (it does, but the user will be prompted to enter the password
each time, which makes it impractical). It has limited scheduling.
Then there is the replication activeX controls, which is great, however you
will have to code your own scheduler here. Another problem with it and WSM
is that you can't set many of the properties on the agents.
WSM and ActiveX controls do support Interactive Conflict resolution.
Finally you can use ado to run sp_startjob, which may be your best option as
it is feature rich..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David" <David@.discussions.microsoft.com> wrote in message
news:4E8C6EAB-D8E2-4A30-A393-49D9E8786AAE@.microsoft.com...
> Rather than having merge replication performed on a schedule, I need to
> programmatically invoke it. Is this possible, and where can I find
> documentation about how to do it?
> David
>

API cursors?

Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?
Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:

> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>
|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it? what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com
|||CharlesT via droptable.com wrote:

> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it? what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

API cursors?

Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API curso
r
the same as a server cursor? does an API cursor/ server cursor hold any lock
s
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch
n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor
'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes an
d
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:

> Im really confused about cursor stuff that isnt quite explained in the BOL
.
> What exactly is the difference between a Tsql cursor and an API cursor? Wh
en
> I access a db from a .NET application is this an API cursor? is an API cur
sor
> the same as a server cursor? does an API cursor/ server cursor hold any lo
cks
> on the db? does a .NET application? BOL says to use a 'server' cursor as t
he
> prefered methos to give a web app user a result set from an aribtrary quer
y
> defined by the user which might be unpredicably large so that you can fetc
h n
> rows at a time without bringing the entire result set in and without holdi
ng
> locks. But I cant find anywhere else in BOL what exactly this 'server curs
or'
> is how and if it differs from a Tsql cursor called by a stored procedure o
r
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NE
T
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but thi
s
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteri
a
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database
.
I suppose that since this is using ADO it is not an API cursor, but then wha
t
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com|||CharlesT via droptable.com wrote:

> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .
NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but t
his
> takes time and resources and it is likely that the user might want to chan
ge
> their search after seeing that hundreds of properties have met their crite
ria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if t
he
> user decides to keep looking fetches the next 10 rows etc. I understand ho
w
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the use
r
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the databa
se.
> I suppose that since this is using ADO it is not an API cursor, but then w
hat
> is it' what is going on in SQL when an outside application runs a cursor
in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isn
t
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

API cursors?

Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

API calls from within a Stored Procedure?

Folks, I would like to convert some macros into a stored procedure. The ide
a
is to read file names out of a database and copy them from their current
location to specified directories. Can an API call be made from a SP to
prompt for a root directior (via file dialog), create a directory, and copy
a
file as its steping through a record set?
Suggestions will be greatly appreciated on how to do this.You can use extended stored procedures, although I believe you're not
supposed to have any UI on an XP. You could write a stored proc to read the
filenames from your database, and take advantage of the xp_cmdshell stored
procedure to move them to a destination directory. UI implementation is
really a whole 'nother layer from SQL Server, and you'd probably do better
to write a little .NET or other program to select the directory; although
you could create a stored proc to do the actual copy for you.
"Crisp" <Crisp@.discussions.microsoft.com> wrote in message
news:5E816E07-01D6-43F6-8E6C-E54E02BDB943@.microsoft.com...
> Folks, I would like to convert some macros into a stored procedure. The
> idea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and
> copy a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||You want to use SQL Server to move files around ?
including prompting a user for input?
This would be a gross misuse of the tool, it would be more appropriate and
much easier to do this in any ordinary programming language. You can still
put the file list in SQL if you want, and have application code get the list
from the SQL database, but having SQL do the File operations, or prompt user
s
for input, would be much too difficult, and inappropriate.
In fact, the only solution I can think of, is really a combination of having
SQL kick off an external process that does the actual "prompting and gather
user input" function, and then does the FIle IO... to do that you need to
write a COM-Component tool in a COM-Capable language, and call it from SQL
using built-in specially designed System procesdures. (Investigate the set
of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
you to create, use, and destroy COM components from inside SQL.)
But don't use these for any application where scaleability is a concern,
because they are notoriously NOT scaleable.
"Crisp" wrote:

> Folks, I would like to convert some macros into a stored procedure. The i
dea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and cop
y a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||I agree with you that the user input function and prompting would be a
misuse of SQL Server. The OP didn't specify this, but I was thinking along
the lines of administering the file move from a client machine? I.e., on my
desktop computer I want to move files on my SQL Server from C:\test_data to
D:\new_data. SQL Server makes it relatively easy to get a directory listing
and perform command shell operations via extended proc's, without setting up
and administering shares all over your network, or creating your own
specialized client/server app.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
> You want to use SQL Server to move files around ?
> including prompting a user for input?
> This would be a gross misuse of the tool, it would be more appropriate and
> much easier to do this in any ordinary programming language. You can
> still
> put the file list in SQL if you want, and have application code get the
> list
> from the SQL database, but having SQL do the File operations, or prompt
> users
> for input, would be much too difficult, and inappropriate.
> In fact, the only solution I can think of, is really a combination of
> having
> SQL kick off an external process that does the actual "prompting and
> gather
> user input" function, and then does the FIle IO... to do that you need to
> write a COM-Component tool in a COM-Capable language, and call it from SQL
> using built-in specially designed System procesdures. (Investigate the
> set
> of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
> you to create, use, and destroy COM components from inside SQL.)
> But don't use these for any application where scaleability is a concern,
> because they are notoriously NOT scaleable.
>
> "Crisp" wrote:
>|||Well then it is certainly possible to write a SP that you would call from
your desktop, passing in acomplete source FileSpec, and destination FileSpec
,
as Local (Local to SQL Serevr) File Specfications, that would copy the file
from one folder \FileName to aspecified Folder\Filename... S(Sounds like yo
u
already know about xp_cmdShell)
You would still need to ensure that the Process ID the SQL Server was
running under had the appropriate permissions on the local file system.
The only thing this approach saves you is creating of user access
permissions and controlled network shares on the SQL Server across the
network...
"Michael C#" wrote:

> I agree with you that the user input function and prompting would be a
> misuse of SQL Server. The OP didn't specify this, but I was thinking alon
g
> the lines of administering the file move from a client machine? I.e., on
my
> desktop computer I want to move files on my SQL Server from C:\test_data t
o
> D:\new_data. SQL Server makes it relatively easy to get a directory listi
ng
> and perform command shell operations via extended proc's, without setting
up
> and administering shares all over your network, or creating your own
> specialized client/server app.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:12A44395-01F8-44C7-996E-EDAC39490BCF@.microsoft.com...
> Well then it is certainly possible to write a SP that you would call from
> your desktop, passing in acomplete source FileSpec, and destination
> FileSpec,
> as Local (Local to SQL Serevr) File Specfications, that would copy the
> file
> from one folder \FileName to aspecified Folder\Filename... S(Sounds like
> you
> already know about xp_cmdShell)
> You would still need to ensure that the Process ID the SQL Server was
> running under had the appropriate permissions on the local file system.
> The only thing this approach saves you is creating of user access
> permissions and controlled network shares on the SQL Server across the
> network...
Designated network shares restrict the OP's solution to copying files from
pre-defined Point A's to pre-defined Point B's, and he seemed to hint that
he wanted to be able to have a choice of source and destination; although
I'm not sure exactly how many choices he wants. For only one or two
destinations, the network shares solution would definitely be the way to go.
But if you know there are going to be a lot of sources and destinations,
like each user has his/her own set of source and destination folders, a more
dynamic approach might be needed. A front-end file selection utility like
the File Attach interface in EM is not that hard to create in VB.NET or
C#.NET using SQL Server xp's. I guess it all really depends on a more
specific set of requirements from the OP to decide what solution would be
best for his/her needs. I think we both agree that trying to use SQL Server
as a UI engine is a horrible idea.