network + sql backup file copy to another server

Trying to understand this from a n/w perspective.
We have multiple backup files that we try and copy(copy,xcopy or robocopy)
to a secondary server thats Gig attached on the same LAN. When we issue one
DOS Session to start copying an individual file, why doesnt it use the
entire throughput of the NIC ...as an example, the bytes sent/sec might be
say 2000000, when we invoke another session and start copying another file,
the bytes sent/sec now jumps to say around 3500000. So my question is why
with one DOS Session, it does not push at 3500000 . Is there a DOS
Limitation ? Trying to understand the n/w concepts here. The numbers I have
listed are not accurate but always noticed a major increase when multiple
sessions are opened for the file copying..
I know this is not a SQL Server related question but any help here would be
appreciated from the experts hereHassan,
There may be other limiting factors than the network bandwith. For instance
you have the Disk I/O, the controller's channel and bus bandwidth, cpu etc.
If the file being read from a single thread or in parallel? I would check
perfmon to ensure you don't have other bottlenecks affecting the tests.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> Trying to understand this from a n/w perspective.
> We have multiple backup files that we try and copy(copy,xcopy or robocopy)
> to a secondary server thats Gig attached on the same LAN. When we issue
one
> DOS Session to start copying an individual file, why doesnt it use the
> entire throughput of the NIC ...as an example, the bytes sent/sec might be
> say 2000000, when we invoke another session and start copying another
file,
> the bytes sent/sec now jumps to say around 3500000. So my question is why
> with one DOS Session, it does not push at 3500000 . Is there a DOS
> Limitation ? Trying to understand the n/w concepts here. The numbers I
have
> listed are not accurate but always noticed a major increase when multiple
> sessions are opened for the file copying..
> I know this is not a SQL Server related question but any help here would
be
> appreciated from the experts here
>|||With one DOS session, it looks like it can peak at a certain rate and then
when i open more and start copying other files, it increases and if i
cancel/close some sessions, i can see it decrease and was wondering why one
session cant take it all at first and if I open more DOS sessions, the rate
should remain the same ...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uAfiPvPhDHA.1340@.tk2msftngp13.phx.gbl...
> Hassan,
> There may be other limiting factors than the network bandwith. For
instance
> you have the Disk I/O, the controller's channel and bus bandwidth, cpu
etc.
> If the file being read from a single thread or in parallel? I would check
> perfmon to ensure you don't have other bottlenecks affecting the tests.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> > Trying to understand this from a n/w perspective.
> > We have multiple backup files that we try and copy(copy,xcopy or
robocopy)
> > to a secondary server thats Gig attached on the same LAN. When we issue
> one
> > DOS Session to start copying an individual file, why doesnt it use the
> > entire throughput of the NIC ...as an example, the bytes sent/sec might
be
> > say 2000000, when we invoke another session and start copying another
> file,
> > the bytes sent/sec now jumps to say around 3500000. So my question is
why
> > with one DOS Session, it does not push at 3500000 . Is there a DOS
> > Limitation ? Trying to understand the n/w concepts here. The numbers I
> have
> > listed are not accurate but always noticed a major increase when
multiple
> > sessions are opened for the file copying..
> >
> > I know this is not a SQL Server related question but any help here would
> be
> > appreciated from the experts here
> >
> >
>|||I see now. Not sure why that is unless each DOS session is just single
threaded and can't read as fast as the network can handle.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23wqnmaRhDHA.3272@.tk2msftngp13.phx.gbl...
> With one DOS session, it looks like it can peak at a certain rate and then
> when i open more and start copying other files, it increases and if i
> cancel/close some sessions, i can see it decrease and was wondering why
one
> session cant take it all at first and if I open more DOS sessions, the
rate
> should remain the same ...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uAfiPvPhDHA.1340@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > There may be other limiting factors than the network bandwith. For
> instance
> > you have the Disk I/O, the controller's channel and bus bandwidth, cpu
> etc.
> > If the file being read from a single thread or in parallel? I would
check
> > perfmon to ensure you don't have other bottlenecks affecting the tests.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> > > Trying to understand this from a n/w perspective.
> > > We have multiple backup files that we try and copy(copy,xcopy or
> robocopy)
> > > to a secondary server thats Gig attached on the same LAN. When we
issue
> > one
> > > DOS Session to start copying an individual file, why doesnt it use the
> > > entire throughput of the NIC ...as an example, the bytes sent/sec
might
> be
> > > say 2000000, when we invoke another session and start copying another
> > file,
> > > the bytes sent/sec now jumps to say around 3500000. So my question is
> why
> > > with one DOS Session, it does not push at 3500000 . Is there a DOS
> > > Limitation ? Trying to understand the n/w concepts here. The numbers I
> > have
> > > listed are not accurate but always noticed a major increase when
> multiple
> > > sessions are opened for the file copying..
> > >
> > > I know this is not a SQL Server related question but any help here
would
> > be
> > > appreciated from the experts here
> > >
> > >
> >
> >
>

Net start mssqlserver with counters?

Hello,
I need to restart a SQL server every morning. This is done by a local
scheduled task in control panel which runs at .bat file. The contents of the
.bat file is as below:
net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent
The restarting of the services works great...but...the sql performance
counters aren't there anymore. The same thing happens if I stop and start
the services through the control panel. The only way of restarting the
services and still have the performance counters is by restart the services
either by Enterprise Manager or the SQL Server Service Manager. We really
need those counters...
Please give me some ideas!
Regards,
/RogerSQL Server (even non clustered) loses its performance
counters when you stop the SQL server while running
perfmon (or monitor tool that uses perfcounters).
This feature is very badly reported (I 'am experiencing
simmular problems on all SQL Servers).
Stop your performance logging (or the services/tools that
are monitoring your sql server) prior to stopping
sqlserver. Start it up again after sqlserver is started.
Your counters will not dissapear.
Unfortionaly, this is not an option for me. I'm working in
a 365/365 environement and have + 20 SQL servers. I need a
monitor tool to check my servers and can't stop it just
like that).
I think there are some locking issues with perfmon.
I already tried to unload/load SQL server performance
counters (unlodctr/lodctr) but this doesn't help.
Hope this helps.
If sombody finds the cause of this info, please share
knowledge.

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

K

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Needs to put quotes around numeric data in export text file

Hi
- I'm trying to export the data from crystal report to a disk file in a CS/ tab seperated format.
-The export file contains quotes around text fileds
- Is there any way to get quotes around numerical data?
Thanks in Advance :)
Bashayou need to have that in report itself
Create formula and write code
""""+Cstr({Field})+""""

needed sql script to convert binary content

Hi all,

how r u everyone, guys i need a help, i have a interface in my application, adding attachments to issues, ie users can attach a file for a particular issue and if anyone have to view they ve click a link and it will download.

iam using filestream and binaryreader concept in that, ie read the file using filestream and then use binaryreader.readbytes method to convert it into byte array and store it in the database in a column with a image datatype as a binary content.

FileStream oImg;
BinaryReader oBinaryReader;
byte[] oImgByteArray;

oImg = new FileStream(sFilePath,FileMode.Open,FileAccess.Read);
oBinaryReader = new BinaryReader(oImg);
oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length);
oBinaryReader.Close();
oImg.Close();

this is the code iam using.

when i use this its taking so much of time to get uploaded. so what i thought of doing is save the file in a specific folder using some postedfile.saveas(not sure of syntax) and when the user wants to view the file they can just download the file. i can do this by googling but what i want is wat abt the existing attachments in the database. so i need a help to do this......

is it possible to create a query to read the files and convert it into original file and save it in the specific folder or do i ve to create a simple interface to create do it manually , pls someone help me what to do .....

thanks in advance.

waiting for a reply soon

Note: any unclear statement in my question kindly reply me

Wishes n Regards

Venkat.

Hi,

Based on my understanding, I understand that you're trying to pass the files in a specific folder to the client.

Yes, I think it is possible to do this. But we cannot save the binary content of the file in database. In your database, you can only store the path of the file, then just use the following code to push to client.

String filePath = Server.MapPath(PathOfFileFromDatabase);
System.IO.FileInfo fileInfo = new System.IO.FileInfo(filePath);
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", String.Format(@."attachment;filename=\""{0}\""", filePath));
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(filePath);
Response.End();

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

|||

Hi,Thanks for the reply, it was useful for me , but still wat iam looking for is , is it possible to do the same as a sql query , i mean i shld run a query or a stored procedure in query analyser that shld do same without using an interface , is it possible ?

is it possible to read a image from a db table using a sql query ?

Thanks in advance

Wishes n Regards

Need tools to Convert Access to SQL environment

I got a big Access file (1 400 tables) to convert to SQL and I would
like to be provided with some automated tools, except upsizing wizard
and DTS, to convert it on my own.

I got a lot of forms and query to convert too.

Can someday provide me with at least one tool name ?

Thanks,

GuyHi
AFAIK the upsizing wizard is about your only option. SQL Server does not
have forms, these will remain in the access database/project.

Have you tried the wizard? If yes does it produce any errors?

John

"Guy" <guy@.graphic-designer.com> wrote in message
news:e04bda98.0311280614.523730ea@.posting.google.c om...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy|||Also...

You may want to post to the access news group!

John

"Guy" <guy@.graphic-designer.com> wrote in message
news:e04bda98.0311280614.523730ea@.posting.google.c om...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy|||John,

The wizard did a little bit of the job (a lot to complete), but nothing
is available to help me finish conversion, moreover with forms and
query.

What' the Access newsgroup URL ?

Thanks,

Guy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

Either:

comp.databases.ms-access
microsoft.public.access.*

Your forms will need to remain in the access database or an access project,
they is no equivalent in SQL server.

John

"Guy Yug" <guy@.graphic-designer.com> wrote in message
news:3fc76a10$0$88386$75868355@.news.frii.net...
> John,
> The wizard did a little bit of the job (a lot to complete), but nothing
> is available to help me finish conversion, moreover with forms and
> query.
> What' the Access newsgroup URL ?
> Thanks,
> Guy
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 28 Nov 2003 17:10:42 GMT, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>Either:
>comp.databases.ms-access
>microsoft.public.access.*
>Your forms will need to remain in the access database or an access project,
>they is no equivalent in SQL server.
>John
>
>"Guy Yug" <guy@.graphic-designer.com> wrote in message
>news:3fc76a10$0$88386$75868355@.news.frii.net...
>>
>> John,
>>
>> The wizard did a little bit of the job (a lot to complete), but nothing
>> is available to help me finish conversion, moreover with forms and
>> query.
>>
>> What' the Access newsgroup URL ?
>>
>> Thanks,
>>
>> Guy
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!
Do you mean you want the forms / queries to remain in access but use
sql server as a source of tables, or do you want to convert them to
something else? Either way you might find this link helpful

http://www.microtools.us/

It's a tool that purports to convert an access application to an
asp.net one.|||Guy,

www.upsizewizard.com has the tool to convert Access Queries(even with
form parameters) to SQL stored procedures.

wizard|||Thanks for your time and info.,

I'll have a tour of this tool soon.

Guy

wizard@.upsizewizard.com (upsizewizard) wrote in message news:<c63ed6cf.0311281820.7f97e654@.posting.google.com>...
> Guy,
> www.upsizewizard.com has the tool to convert Access Queries(even with
> form parameters) to SQL stored procedures.
> wizard|||On 30 Nov 2003 08:57:53 -0800, guy@.graphic-designer.com (Guy) wrote:

>Thanks for your time and info.,
>I'll have a tour of this tool soon.
>Guy
>wizard@.upsizewizard.com (upsizewizard) wrote in message news:<c63ed6cf.0311281820.7f97e654@.posting.google.com>...
>> Guy,
>>
>> www.upsizewizard.com has the tool to convert Access Queries(even with
>> form parameters) to SQL stored procedures.
>>
>> wizard

Hmmmm $10.000 dollars to convert a Access Database to SQL kinda pricey
isnt it?|||Lyndon Hills <lyndon@.nospam.tenegi.com> wrote in message news:<66dfsvsdabl8u0k4baobg71df0p4fhurjs@.4ax.com>...
> Do you mean you want the forms / queries to remain in access but use
> sql server as a source of tables, or do you want to convert them to
> something else? Either way you might find this link helpful
> http://www.microtools.us/
> It's a tool that purports to convert an access application to an
> asp.net one.
Has anyone actually use this Microtools converter? Is it good?

Regards,
Michael Freidgeim|||Hi Guy,

My application generator for MS SQL Server can do the entire job for
you
The benefits of this system are as follows:
1. The system generates true Client Server and Multi Tier
applications.
2. There is zero coding for common functionality, i.e. Adds, Updates,
Deletes, Audit trail, Security, Rolling Down Data, Cascading Deletes,
Posting to General ledger etc.
3. There is zero work of any kind for generation of data entry
screens and their lookups.
4. The back end is completely independent from the front end. You can
hit the database with any application or user interface and still be
sure that you have complete security and valid data.
5. Easy navigation through out the application. The generated user
interface is a familiar modern metaphor with a navigation tree on top
or at the side and data entry screens at the bottom. Also, the
generated user interface remembers customizations to each data entry
screen. This allows you to make sweeping changes to the interface, and
regenerate all data entry screens, without loosing your
customizations.
6. Consistent look and feel via OOP Inheritance and code generators.
7. Major changes in look and functionality are made in one place
only, and ripple down to all affected parts of the system without
programmer intervention. Again, this was accomplished with OOP
inheritance and also with code generators.
8. Users to have the ability to create queries and reports on the
fly. And the ability to save and reload those queries and reports in
many formats including Excel and HTML.
9. Users to have complete flexibility in customizing the look and
feel of the system. The extent to which each user can customize the
interface must be seen to be believed. This high level of
customizability creates a high degree of user acceptance.
10. Logical use of hot keys and local popup menus allow for easy mouse
free operation, permitting the user to keep his or her hands on the
keyboard, if the user so desires.
11. All custom code added to generated data entry screens and
generated backend code persists after regeneration.
12. Comes with a business rule generator
13. Comes with a data import utility
14. Comes with a data revalidation utility to use when you change your
business rules.
Get all this without programming.
If you need to quickly build feature rich, bug free business
applications for MS SQL Server, then please call (201 665 8906) or
write to johnshearing@.aol.com|||guy@.graphic-designer.com (Guy) wrote in message news:<e04bda98.0311280614.523730ea@.posting.google.com>...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy

1400 tables? Yikes. Is this thing set up properly? I'd make sure it
was before converting anything... Then you could use access as the
front and and SQL server as the back... but you might want to read
some of Chipman & Baron's book on the subject...|||Yo JohnShear -

You still out there?

Still offering this software?
If so please advise

BobAlston9 AT aol D O T com

Need to View some type of LOG file

Hi
I need to view a log of all SQL scripts that were RUN in Sequel Server.
Is It possible to view some type of a log, which will show me the script
as well as when it was run.
Many Thanks
AQ
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Retroactively, no. Going forward, you can use SQL Server Profiler to catch
scripts as they run.
Look up SQL Profiler in Books Online for information about how to use the
tool.
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
You can get the object creation date from sysobjects system table.
use dbname
go
select substring(name,1,35) as Object_name,type as Object_type,crdate from
sysobjects
Description for Object_type displayed in the above query
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Thanks
Hari
MCDBA
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
Thanks
You will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith
|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>
|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith
|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith
|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>
|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
|||Hi
You could always restore the database with a given name and then change it
after this process using sp_renamedb. You may want to use something like the
following that assumes the database is called TESTUSERS, but you could make
it dynamic:
USE TEMPDB
GO
CREATE TABLE #users ( UserName sysname, UserSID varbinary(85) )
/* You may want to look at TESTUSERS..sysusers, although that would need to
exclude some entries */
INSERT INTO #users ( UserName, UserSID )
EXEC TESTUSERS..sp_change_users_login 'report'
/* Assumes user names and logins are the same */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_change_users_login
''update_one'', ' + QUOTENAME(UserName) + ', ' + QUOTENAME(UserName) FROM
#users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
GO
/* Alternatively dropping users */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_dropuser ' +
QUOTENAME(UserName) FROM #users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
John
"shub" wrote:

> No the databases do not contain the stored proc to drop or create
> users, I complie the stored proc after I restore the database and I
> was wondering if there was some way from my stored proc to restore the
> database if I could do all of that in one step. I think from the input
> I have got, it sounds like I pretty much have to build a large dynamic
> SQL string or do the way I am doing. (Two Step Process)
> I need to drop the databases because every once in a while we will pull
> in databases from different networks so when it is restored on ours
> those users no longer exist so to keep it clean I prefer to delete the
> users.
> John Bell wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:

>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:

>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manne
r,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then y
ou
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
--
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:
>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:
>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
--
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> >> Since the database name changes, that would require dynamic SQL.
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:
> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
> > >> Since the database name changes, that would require dynamic SQL.
> >
> > That should be fine, if written properly, right? Since it is a
> > administrative task, it has little to do with injection risks or
> > recompilation issues.
> >
> > Otherwise, he can consider a few alternatives:
> >
> > 1. Create individual RESTORE statements within the proc & use IF clause to
> > control the execution flow
> > 2. Create separate stored procedures in each database & call it from the
> > main proc with the database as the parameter.
> >
> > EXEC @.r = @.db.dbo.restore_proc ;
> >
> > --
> > Anith
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
> > Anith,
> > I am not sure I understand what are you suggesting. You are right in
> > assuming that this is a administrative task and there is no concern or
> > SQL injection however I do not uderstand the solution.
> >
> > Currently the Restore Stored proc is complied in the master database
> > and after the individual database is restored I am then compilibg the
> > other stored proc to drop all the users.
> >
> > You may be into something, but I just need to understand to apply it
> > Thanks
> > Anith Sen wrote:
> > > >> Since the database name changes, that would require dynamic SQL.
> > >
> > > That should be fine, if written properly, right? Since it is a
> > > administrative task, it has little to do with injection risks or
> > > recompilation issues.
> > >
> > > Otherwise, he can consider a few alternatives:
> > >
> > > 1. Create individual RESTORE statements within the proc & use IF clause to
> > > control the execution flow
> > > 2. Create separate stored procedures in each database & call it from the
> > > main proc with the database as the parameter.
> > >
> > > EXEC @.r = @.db.dbo.restore_proc ;
> > >
> > > --
> > > Anith
> >
> >