Extract number from a string

Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.
Can anybody tell me how to extract a number from a string?...
TIA!!!
Hello, snigs
If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable
If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable
Razvan
PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest ;)
|||On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
>PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
>-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

>PS. With this occasion, I would like to submit my two entries for the
>"most unreadable query of the month" contest ;)
Hi Razvann,
Month, year, century - you win them all! ;-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Extended Stored Procedure

Good morning,
We are porting a legacy VB6 user interface application that stores data in
binary text files to SQL Server 2000 and C#.
The VB6 user interface hooks into backend C/C++ dll's to pass VB6 "Type"
data into the C code that writes to the binary files.
We have to preserve this strategy of writing data to binary files because a
massive C dll library uses them to analyze the data.
What I would like to do is store the data in a normalized SQL database and
then use Extended Stored Procedures (ESP) to hook into the C functions that
write the binary files.
This would require selecting a row of data from a SQL table and then passing
it to the ESP C function in a way that mimics the VB6 Type datatype. Really
the C is looking for a pointer to the beginning of the Type data.
Any thoughts/advice greatly appreciated!
Thank you
jmattSQL Server is not an application development tool. Processing data one row
at a time and calling C functions would be best implemented on the
application side rather than from the database.
"jmatt" <jmatt@.discussions.microsoft.com> wrote in message
news:8E82603C-AA1F-462A-AEA4-7EDC103E5F45@.microsoft.com...
> Good morning,
> We are porting a legacy VB6 user interface application that stores data in
> binary text files to SQL Server 2000 and C#.
> The VB6 user interface hooks into backend C/C++ dll's to pass VB6 "Type"
> data into the C code that writes to the binary files.
> We have to preserve this strategy of writing data to binary files because
> a
> massive C dll library uses them to analyze the data.
> What I would like to do is store the data in a normalized SQL database and
> then use Extended Stored Procedures (ESP) to hook into the C functions
> that
> write the binary files.
> This would require selecting a row of data from a SQL table and then
> passing
> it to the ESP C function in a way that mimics the VB6 Type datatype.
> Really
> the C is looking for a pointer to the beginning of the Type data.
> Any thoughts/advice greatly appreciated!
>
> Thank you
> jmatt|||I understand your point. It just seems so direct and efficient to go from
table to file.
Thanks!
jmatt
"JT" wrote:

> SQL Server is not an application development tool. Processing data one row
> at a time and calling C functions would be best implemented on the
> application side rather than from the database.
> "jmatt" <jmatt@.discussions.microsoft.com> wrote in message
> news:8E82603C-AA1F-462A-AEA4-7EDC103E5F45@.microsoft.com...
>
>|||> SQL Server is not an application development tool.
Not sure I totally agree with that. SQL2005 is great application server
environment. The lines are not dark any more, they are shades of grey.
This kind of thing would be ~easy to do in sql2005 clr.
William|||William,
Any ideas on how I would pass data to the C function as a parameter in ESP
so that it would mimic VB6 Type data? Would the result of a simple SELECT b
e
a start?
jmatt
"William Stacey [MVP]" wrote:

> Not sure I totally agree with that. SQL2005 is great application server
> environment. The lines are not dark any more, they are shades of grey.
> This kind of thing would be ~easy to do in sql2005 clr.
> --
> William
>
>|||You have SQL2005? If not, then I can't help.
If so, I would rewrite the function in C# and just call from a SqlUDF then
you can use the power of .Net and IO classes.
You could probably also call c dll from SqlUDF like you would call a win32
function, by defining it first.
William Stacey [MVP]
"jmatt" <jmatt@.discussions.microsoft.com> wrote in message
news:371399B9-B337-4F6F-AECD-89CFBB626DA0@.microsoft.com...
> William,
> Any ideas on how I would pass data to the C function as a parameter in ESP
> so that it would mimic VB6 Type data? Would the result of a simple SELECT
> be
> a start?
> jmatt
> "William Stacey [MVP]" wrote:
>

Extended SP Questions

I would like to know if I can determine the calling user from within
an extended stored procedure. I assume it's accessible in the
SRV_PROC structure somewhere.

Also, does anyone know of a comprehensive list of what is included in
the SRV_PROC structure?

This is for SQL Server 2000.

Thanks"Bruce" wrote:
> I would like to know if I can determine the calling user from within
> an extended stored procedure. I assume it's accessible in the
> SRV_PROC structure somewhere.
> Also, does anyone know of a comprehensive list of what is included in
> the SRV_PROC structure?
> This is for SQL Server 2000.
> Thanks

My understanding is that SRV_PROC is intended to be an opaque structure and
as such you shouldn't hack it for production purposes (I mean, running my
own C/C++ in the process space of a production SQL Server gives me the
heebie-geebies anyway).

That being said, what about srv_pfield? It seems to provide quite a bit of
information...

Craig

Extended SP Questions

I would like to know if I can determine the calling user from within
an extended stored procedure. I assume it's accessible in the
SRV_PROC structure somewhere.

Also, does anyone know of a comprehensive list of what is included in
the SRV_PROC structure?

This is for SQL Server 2000.

Thanks"Bruce" wrote:
> I would like to know if I can determine the calling user from within
> an extended stored procedure. I assume it's accessible in the
> SRV_PROC structure somewhere.
> Also, does anyone know of a comprehensive list of what is included in
> the SRV_PROC structure?
> This is for SQL Server 2000.
> Thanks

My understanding is that SRV_PROC is intended to be an opaque structure and
as such you shouldn't hack it for production purposes (I mean, running my
own C/C++ in the process space of a production SQL Server gives me the
heebie-geebies anyway).

That being said, what about srv_pfield? It seems to provide quite a bit of
information...

Craig|||Thanks, srv_pfield worked great.

Expression Problem Using a Multi-Select Parameter

I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.

My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.

The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.

How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?

I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

If you're on SP0 or SP2 or later, the Select All option is always there. It's not really a checkbox you can detect. It's just a shortcut way for selecting/deselecting all options.

I've reported this as an enhancement. You should be able to tell whether they've selected all possible options:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124515

At that link I describe a couple of possible workarounds. Hope that helps.

|||

There is currently no built-in functionality, but here are some ideas to achieve what you are looking for:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values (e.g. =Parameters!P1.Count).

-- Robert

Expression for calculating the different between two dates

i am currently doing a system where user can submit their suggestions to us
and part of my system is using Reporting Services and i need some guide on my
problem.
When user submit their suggestions, the date of submission will be auto
input into SQL Server database and if the suggestions is being evaluated by a
evaluator, the date of evaluation will also be input into the SQL Server
database and all this part i had done it all in aspx.
So now when user want to view their suggestions, they will go to a page
which i had generate using Reporting Services. In this page, there is a
column call Turnaround Time(the datediff from the day of submission to now
till the suggestions is being evaluated). That means In this column i wan to
display the datediff from the date of submission till the date today if it
still had not been evaluated(that means the SQL Server database that contains
the field "Date of Evaluation" is still null). And if the suggestions had
been evaluated(that means the SQL Server database that contains the field
"Date of Evaluation" contains a date value now), it will display the datediff
from the date of submission to the date of evaluation.
For now i can only display the datediff from the date of submission to now
using the following expression : =DateDiff(DateInterval.Day,
Fields!DateSubmitted.Value, Now()) and thus i need help on how to display the
datediff from the date of submission to now if the suggestions had not been
evaluated and display the datediff from the date of submission to the date of
evaluation if the suggestions had been evaluated.
I appreciate for the help and guide. ThanksYou can try this :
IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
SubmissionDate.Value)
>--Original Message--
>i am currently doing a system where user can submit their
suggestions to us
>and part of my system is using Reporting Services and i
need some guide on my
>problem.
>When user submit their suggestions, the date of
submission will be auto
>input into SQL Server database and if the suggestions is
being evaluated by a
>evaluator, the date of evaluation will also be input into
the SQL Server
>database and all this part i had done it all in aspx.
>So now when user want to view their suggestions, they
will go to a page
>which i had generate using Reporting Services. In this
page, there is a
>column call Turnaround Time(the datediff from the day of
submission to now
>till the suggestions is being evaluated). That means In
this column i wan to
>display the datediff from the date of submission till the
date today if it
>still had not been evaluated(that means the SQL Server
database that contains
>the field "Date of Evaluation" is still null). And if the
suggestions had
>been evaluated(that means the SQL Server database that
contains the field
>"Date of Evaluation" contains a date value now), it will
display the datediff
>from the date of submission to the date of evaluation.
>For now i can only display the datediff from the date of
submission to now
>using the following expression : =DateDiff
(DateInterval.Day,
>Fields!DateSubmitted.Value, Now()) and thus i need help
on how to display the
>datediff from the date of submission to now if the
suggestions had not been
>evaluated and display the datediff from the date of
submission to the date of
>evaluation if the suggestions had been evaluated.
>I appreciate for the help and guide. Thanks
>.
>|||you can directly get the Turnaround Time from database.
In your query get this column...
DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
TurnaroundTime
and use Fields!TurnaroundTime.Value in your report.
hth
"JiaN" <JiaN@.discussions.microsoft.com> wrote in message
news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
>i am currently doing a system where user can submit their suggestions to us
> and part of my system is using Reporting Services and i need some guide on
> my
> problem.
> When user submit their suggestions, the date of submission will be auto
> input into SQL Server database and if the suggestions is being evaluated
> by a
> evaluator, the date of evaluation will also be input into the SQL Server
> database and all this part i had done it all in aspx.
> So now when user want to view their suggestions, they will go to a page
> which i had generate using Reporting Services. In this page, there is a
> column call Turnaround Time(the datediff from the day of submission to now
> till the suggestions is being evaluated). That means In this column i wan
> to
> display the datediff from the date of submission till the date today if it
> still had not been evaluated(that means the SQL Server database that
> contains
> the field "Date of Evaluation" is still null). And if the suggestions had
> been evaluated(that means the SQL Server database that contains the field
> "Date of Evaluation" contains a date value now), it will display the
> datediff
> from the date of submission to the date of evaluation.
> For now i can only display the datediff from the date of submission to now
> using the following expression : =DateDiff(DateInterval.Day,
> Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> the
> datediff from the date of submission to now if the suggestions had not
> been
> evaluated and display the datediff from the date of submission to the date
> of
> evaluation if the suggestions had been evaluated.
> I appreciate for the help and guide. Thanks|||ok thanks for your help ravi but my datatype for EvaluationDate and
SubmissionDate is Date/Time and it shows a error when i preview using the
expression you had given me.
The error is : "The value expression for the textbox 'Turnaround Time'
contains and error: [BC30452] Operator '-' is not defined for types 'Date'
and Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields"
"Ravi" wrote:
> You can try this :
> IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
> SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
> SubmissionDate.Value)
>
> >--Original Message--
> >i am currently doing a system where user can submit their
> suggestions to us
> >and part of my system is using Reporting Services and i
> need some guide on my
> >problem.
> >
> >When user submit their suggestions, the date of
> submission will be auto
> >input into SQL Server database and if the suggestions is
> being evaluated by a
> >evaluator, the date of evaluation will also be input into
> the SQL Server
> >database and all this part i had done it all in aspx.
> >So now when user want to view their suggestions, they
> will go to a page
> >which i had generate using Reporting Services. In this
> page, there is a
> >column call Turnaround Time(the datediff from the day of
> submission to now
> >till the suggestions is being evaluated). That means In
> this column i wan to
> >display the datediff from the date of submission till the
> date today if it
> >still had not been evaluated(that means the SQL Server
> database that contains
> >the field "Date of Evaluation" is still null). And if the
> suggestions had
> >been evaluated(that means the SQL Server database that
> contains the field
> >"Date of Evaluation" contains a date value now), it will
> display the datediff
> >from the date of submission to the date of evaluation.
> >
> >For now i can only display the datediff from the date of
> submission to now
> >using the following expression : =DateDiff
> (DateInterval.Day,
> >Fields!DateSubmitted.Value, Now()) and thus i need help
> on how to display the
> >datediff from the date of submission to now if the
> suggestions had not been
> >evaluated and display the datediff from the date of
> submission to the date of
> >evaluation if the suggestions had been evaluated.
> >
> >I appreciate for the help and guide. Thanks
> >.
> >
>|||Its working!! Thanks avnrao!!
Ravi, thanks too! for spending the time in helping me. =)
"avnrao" wrote:
> you can directly get the Turnaround Time from database.
> In your query get this column...
> DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
> TurnaroundTime
> and use Fields!TurnaroundTime.Value in your report.
> hth
> "JiaN" <JiaN@.discussions.microsoft.com> wrote in message
> news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
> >i am currently doing a system where user can submit their suggestions to us
> > and part of my system is using Reporting Services and i need some guide on
> > my
> > problem.
> >
> > When user submit their suggestions, the date of submission will be auto
> > input into SQL Server database and if the suggestions is being evaluated
> > by a
> > evaluator, the date of evaluation will also be input into the SQL Server
> > database and all this part i had done it all in aspx.
> > So now when user want to view their suggestions, they will go to a page
> > which i had generate using Reporting Services. In this page, there is a
> > column call Turnaround Time(the datediff from the day of submission to now
> > till the suggestions is being evaluated). That means In this column i wan
> > to
> > display the datediff from the date of submission till the date today if it
> > still had not been evaluated(that means the SQL Server database that
> > contains
> > the field "Date of Evaluation" is still null). And if the suggestions had
> > been evaluated(that means the SQL Server database that contains the field
> > "Date of Evaluation" contains a date value now), it will display the
> > datediff
> > from the date of submission to the date of evaluation.
> >
> > For now i can only display the datediff from the date of submission to now
> > using the following expression : =DateDiff(DateInterval.Day,
> > Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> > the
> > datediff from the date of submission to now if the suggestions had not
> > been
> > evaluated and display the datediff from the date of submission to the date
> > of
> > evaluation if the suggestions had been evaluated.
> >
> > I appreciate for the help and guide. Thanks
>
>

Expression editor on Custom Properties on Custom Data Flow Component

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:

Code Snippet

IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";

But it doesn't work, if I enter @.[System:ackageName] in the field. It comes out "@.[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

The expression for a component's property is held at the task level. If a property is marked as CPET_NOTIFY, it notifies the task (the data flow which parent's the component), which tells it to generate a new property on which can be set an expression. So to see the expression in the designer look at the properties grid for the data flow task, not the component.

|||Hello Darren,

Thank you for the quick response. But the expression editor doesn't show in the propertiesgrid either.

Could I be missing anything?|||

Are you looking at the properties grid of the DATA FLOW component or at your custom data flow component? Also, did you set the flag darren mentioned above so that the data flow component will know to include this property in it's properties expression list?

|||Ah, yes, I see it now. But it is not what I'm looking for.

Is there now way to set the expresison builder on the component itself? When the expression builder is used on the parent task, I cannot access the incomming rows.

Thank you kindly|||

Why do you think your component will have features over and above that available to Microsoft themselves?

Property expressions are ONLY available at the task level, because they are provided in the task framework.

You have created a property expression, so your mention of incoming rows does not make sense. Property expressions are just that, for the property, but not the value itself, the result will override the value. Just look how they work in the rest of SSIS.

A property that holds a text string that could be parsed as an expression is something different and maybe what you want? Perhaps the Derived Column transform may be easier?

Express Edition and Report Builder

Hi all,
Little question for you all: does the SQL express (Advanced) edition come
with end user report builder tools? Will it be possible for an end user to
build reports itself?
Thanks in advance,
PeterNo, the semantic modelling tool which builds the report models used by
Report Builder is not included in SQL Server 2005 Express, even with
the "Advanced Services" add-on, which gives you some of Reporting
Services, just not all of it.
-Eric
On Thu, 17 Aug 2006 16:53:14 +0200, "Peter Bons" <joepie@.blakjsd.bl>
wrote:
>Hi all,
>
>Little question for you all: does the SQL express (Advanced) edition come
>with end user report builder tools? Will it be possible for an end user to
>build reports itself?
>Thanks in advance,
>Peter
>

Expose RS to Internet?

Hello I have just created some reports and I want to expose them to the
internet without windows authentication. I mean that every user outside can
see my reports without being part of the domain.
Thanks for your help.From everything that I have read, I don't think that this is possible. You
need to authenticate against RS using Windows Authentication or some other
type of Forms Authentication (which is only supported in Enterprise Edition).
If you figure out a way, please do share, but I don't think that it is
possible.
I have the same issue, but have SQL Server Standard Edition and can't figure
out away to make this happen.
"Luis Esteban Valencia" wrote:
> Hello I have just created some reports and I want to expose them to the
> internet without windows authentication. I mean that every user outside can
> see my reports without being part of the domain.
> Thanks for your help.
>
>

Exporting User/Role Permissions

I am not a DBA so please be gentle...

I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?

I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.

All my thanks!

- Isaac

Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...

You can query the tables, such as sys.server_permissions, sys.server_principals, sys.database_principals, sys.database_permissions, to display the permissions of database user and role and logins. E.g., if you want to look at the permission of user Bob, you can query as follows

select * from sys.database_permissions where grantee_principal_id =(select principal_id from sys.database_principals where name='Bob')

exporting to pdf without generating report

hi
I am calling a report from asp and if i click the button the report has to
be be stored in a specified location given by the user and report window
should not be generated.
It is very urgent please help me to solve the problem
thanks
terranceHi,
If you are using URL access then try this.
e.g.
http://servername/reportserver?/Sales/YearlySalesSummary&rs:Format=PDF&rs:Command=Render
If you are in the program, you need to change the response.contenttype as
well to "Application/PDF"
Amarnath
"ter" wrote:
> hi
> I am calling a report from asp and if i click the button the report has to
> be be stored in a specified location given by the user and report window
> should not be generated.
> It is very urgent please help me to solve the problem
> thanks
> terrance
>

Exporting to Excel format

Quite often, when a user exports to an Excel spreadsheet and selects "Open"
instead of "Save" for the file download dialog the file does not open.
Instead, they get an error saying "xxxxx.xls could not be found. Check the
spelling of the file name, and verify that the location is correct". This
seems to happen more often than not.
Is there anything which can be done about this? The problem doesn't occur if
they save the file instead of opening it, but they should not have to
perform extra steps for what should be a simple operation.
PeterThis is a bug. You have identified the workaround: Save the file to the
local machine and then open it. At this time I cannot give you any specific
guidance when a fix might be available.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Kenyon" <p.kenyon.no.spam@.paradise.net.nz> wrote in message
news:%23XZJHuPdEHA.3572@.TK2MSFTNGP10.phx.gbl...
> Quite often, when a user exports to an Excel spreadsheet and selects
"Open"
> instead of "Save" for the file download dialog the file does not open.
> Instead, they get an error saying "xxxxx.xls could not be found. Check the
> spelling of the file name, and verify that the location is correct". This
> seems to happen more often than not.
> Is there anything which can be done about this? The problem doesn't occur
if
> they save the file instead of opening it, but they should not have to
> perform extra steps for what should be a simple operation.
> Peter
>

Exporting Stored Procs

Hi,
Novice user here. I am developing a large number of stored procedures
and user defined functions and I want to be able to export them everynight
for back up. I know that I can user the Query Analyzer tool to export them
to .sql files one at a time, but I am approaching about 100 procedures and
functions and that can be quite tedious.
Anyone know how I can batch export a set of procs at one time?
JDWhy don't you just make a special backup of the database every night?
And have you considered using source control for database objects?
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:OmpYe.261467$E95.192029@.fed1read01...
> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export
> them
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>|||Joe
One way to do this is to create a SQL Server Job scheduled to run at the
desired frequency to run an ActiveX Task that scripts out the Stored
Procedures.
Below is a VBScript example that can be used to script all Stored Procedures
in a Database to a file.
~~~
Const LOG_FILE = "c:\script.sql"
Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to script
exists
Const DB_NAME = "pubs" ' This could be replaced with a loop to script all DB
's
' FSO I/O Mode
Const FORWRITING = 2
Const FORAPPENDING = 8
' DMO Scripting Constants
Const SQLDMOScript_ObjectPermissions = 2
Const SQLDMOScript_Default = 4
Const SQLDMOScript_OwnerQualify = 262144
Dim oStoredProcedure
Dim oSQLServer
Dim oDatabase
Dim oFSO
Dim sTexttoWrite
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oSQLServer.LoginSecure = True
oSQLserver.Connect(SQL_INSTANCE)
Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedures
If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
' Script the Object Permissions and Owner
sTexttoWrite =
oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Script
(SQLDMOScript_Default
+ SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
End If
Next
oSQLServer.DisConnect
Set oFSO = Nothing
Set oStoredProcedure = Nothing
Set oSQLServer = Nothing
~~~
- Peter Ward
WARDY IT Solutions
"Joe Delphi" wrote:

> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export the
m
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>
>|||if your problem is only scripting then
you can use Entrprise Manager> All Tasks > Genrate sql secript
you can select all sprocs at a time to .sql
Regards
R.D
"P. Ward" wrote:
> Joe
> One way to do this is to create a SQL Server Job scheduled to run at the
> desired frequency to run an ActiveX Task that scripts out the Stored
> Procedures.
> Below is a VBScript example that can be used to script all Stored Procedur
es
> in a Database to a file.
> ~~~
> Const LOG_FILE = "c:\script.sql"
> Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to scrip
t
> exists
> Const DB_NAME = "pubs" ' This could be replaced with a loop to script all
DB's
> ' FSO I/O Mode
> Const FORWRITING = 2
> Const FORAPPENDING = 8
> ' DMO Scripting Constants
> Const SQLDMOScript_ObjectPermissions = 2
> Const SQLDMOScript_Default = 4
> Const SQLDMOScript_OwnerQualify = 262144
>
> Dim oStoredProcedure
> Dim oSQLServer
> Dim oDatabase
> Dim oFSO
> Dim sTexttoWrite
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oSQLServer.LoginSecure = True
> oSQLserver.Connect(SQL_INSTANCE)
> Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
> For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedure
s
> If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
> ' Script the Object Permissions and Owner
> sTexttoWrite =
> oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Scri
pt(SQLDMOScript_Default
> + SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
> oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
> End If
> Next
> oSQLServer.DisConnect
> Set oFSO = Nothing
> Set oStoredProcedure = Nothing
> Set oSQLServer = Nothing
> ~~~
> - Peter Ward
> WARDY IT Solutions
>
> "Joe Delphi" wrote:
>|||Joe
In new groups. If you have a question do post as question. Never select as
comment. Question attract more responses.
Regards
R.D
"R.D" wrote:
> if your problem is only scripting then
> you can use Entrprise Manager> All Tasks > Genrate sql secript
> you can select all sprocs at a time to .sql
> Regards
> R.D
> "P. Ward" wrote:
>|||"R.D" <RD@.discussions.microsoft.com> wrote in message
news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> Joe
> In new groups. If you have a question do post as question. Never select as
> comment. Question attract more responses.
> Regards
> R.D
In the English language, this is considered a question:
"Anyone know how I can batch export a set of procs at one time?"|||I think one of the frilly web guis to the newsgroup allows you to categorize
a post as a comment or a question, for some unknown reason.
A
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:k1yYe.261492$E95.127285@.fed1read01...
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> In the English language, this is considered a question:
> "Anyone know how I can batch export a set of procs at one time?"
>
>|||Joe
That is the problem. The people who knows only one language interpret in
terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
what I meant was when you select new, you select it as a question for which
question mark appears besides your post.
Aaron
I dont expect those words that downgrade the norms of newgroups, from an MV
P.
Better ask MS why there are two varieties(comments and questions)
Regards
R.D
"Aaron Bertrand [SQL Server MVP]" wrote:

> I think one of the frilly web guis to the newsgroup allows you to categori
ze
> a post as a comment or a question, for some unknown reason.
> A
>
> "Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
> news:k1yYe.261492$E95.127285@.fed1read01...
>
>|||On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:

>Joe
>That is the problem. The people who knows only one language interpret in
>terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
>THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
>what I meant was when you select new, you select it as a question for which
>question mark appears besides your post.
>Aaron
>I dont expect those words that downgrade the norms of newgroups, from an M
VP.
>Better ask MS why there are two varieties(comments and questions)
>Regards
>R.D
Hi R.D.,
What you seem to miss is the fact that these groups are actually usenet
groups, which be used in many ways. The oldest form includes the use of
some dedicated software that can access usenet groups. I use Agent for
example; Joe and Aaron appear to use Outlook Express.
Usenet groups have been around for some decades already. They were quite
popular before "Internet" became a hype.
Nowadays, having a "Forum", "Message board", or other "Community" on a
web site is the fashionable thing. Many companies have recognised the
value of those, but also recognise the value and strength of the
existing usenet groups. So they create a website that appears to be
their "own" message board, but that actually is just a mirror of one or
more usenet groups. Microsoft's community pages are an example of such a
portal; others are dbforums.com, examnotes.net, tech-archive.net,
and of course groups.google.com. All posts you read on those web sites
are taken from a usenet group, and all posts you write there are
directly forwarded to the same group.
Some of those web portals decided to add some extra frills and buttons.
If I recall correctly, the Microsoft site has buttons to say if a post
is usefull or not, and some kind of rating for authors. You seem to be
using thhat site; from your comments I understand that they also offer a
way to distinguish "questions" from "comments".
All that is fine for those who use that site. But it's not supported by
the decades-old usenet architecture. So the result is that the MS site
can only present the extra info for posts that originate from their own
site.
If you, like Aaron, me, and a bunch of other "regulars" in these groups,
attempt to keep up with several hundred new messages each day, and reply
to a dozen or more each day then you'll quickly find that the interface
offered by any of those web portals will constantly get in your way.
You'll want a program that allows you to download all new messages, that
will keep track of which messages you have already read and which
discussions you've decided to skip, etc.
If I had to find each message I post at the Microsoft site, only to make
a few mouseclicks to show others if it's a question or a comment (and
then probably do the same on a few other sites as well), that would cost
me so much time that my total contribution to these groups would be
reduced severly. And it would cost me so much energy that I'd probably
stop contributing and find another hobby before the month is over.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
This is point is still valid.
Regards
R.D
"Hugo Kornelis" wrote:
> On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:
>
> Hi R.D.,
> What you seem to miss is the fact that these groups are actually usenet
> groups, which be used in many ways. The oldest form includes the use of
> some dedicated software that can access usenet groups. I use Agent for
> example; Joe and Aaron appear to use Outlook Express.
> Usenet groups have been around for some decades already. They were quite
> popular before "Internet" became a hype.
> Nowadays, having a "Forum", "Message board", or other "Community" on a
> web site is the fashionable thing. Many companies have recognised the
> value of those, but also recognise the value and strength of the
> existing usenet groups. So they create a website that appears to be
> their "own" message board, but that actually is just a mirror of one or
> more usenet groups. Microsoft's community pages are an example of such a
> portal; others are dbforums.com, examnotes.net, tech-archive.net,
> and of course groups.google.com. All posts you read on those web sites
> are taken from a usenet group, and all posts you write there are
> directly forwarded to the same group.
> Some of those web portals decided to add some extra frills and buttons.
> If I recall correctly, the Microsoft site has buttons to say if a post
> is usefull or not, and some kind of rating for authors. You seem to be
> using thhat site; from your comments I understand that they also offer a
> way to distinguish "questions" from "comments".
> All that is fine for those who use that site. But it's not supported by
> the decades-old usenet architecture. So the result is that the MS site
> can only present the extra info for posts that originate from their own
> site.
> If you, like Aaron, me, and a bunch of other "regulars" in these groups,
> attempt to keep up with several hundred new messages each day, and reply
> to a dozen or more each day then you'll quickly find that the interface
> offered by any of those web portals will constantly get in your way.
> You'll want a program that allows you to download all new messages, that
> will keep track of which messages you have already read and which
> discussions you've decided to skip, etc.
> If I had to find each message I post at the Microsoft site, only to make
> a few mouseclicks to show others if it's a question or a comment (and
> then probably do the same on a few other sites as well), that would cost
> me so much time that my total contribution to these groups would be
> reduced severly. And it would cost me so much energy that I'd probably
> stop contributing and find another hobby before the month is over.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Exporting SQL DB files

i have a VB project utilising SQL server Express. I need to export the database files so that the user data can be copied to a laptop and made available to other users.

My problem is this, connections are remaining open to the DB files even though I close all forms that include data access. Typically, my application takes around 6 minutes before the connections are released and the files can be copied.

Is there a way of either forcing all connections to close or of exporting the DB while it has connections?

Rich

Have you tried closing the connection on closing the form

Dim instance As DbConnection

instance.CloseSorry if the syntax is wrong I'm a C++ man. Same principles though.|||

In addition to what he has said about closing connections (a question might be if your access layer is caching connections, like connection pooling) you can force everyone out by using:

alter database <dbName>
set single_user with rolback immediate

Now, you have to be really careful that you want to do this, because if this is actually a database that someone else has access to they might be in the middle of doing something and this will really harsh their database experience :)

|||

I think this is exactly what's happenning, however, I am confused by your code - VS dosn't recognise it at all!

Cheers,

Rich

exporting SQL data as Access readable file

hi guys.
i'm looking to try and get a web tool together that will basically allow a user to download a file that can be plopped into MS Access, using the data i have on my servers stored in MS SQL Server...
any ideas?nobody, huh?|||bcp out a data file?

You need to be clearer on your process and what you're trying to do..

I'd venture to say that a stored procedure will be called, then using xp_cmdshell bcp out a view or use queryout...

But a little more explination would help us...