Creating a SQL script file from a stored procedure

I'd like to create a SQL script file from within a stored procedure. How do I:

1. Open the text file
2. Write to it
3. Close it when done

Thanks in advance,


Barrysounds like a job better accomplished through application code. filesystem object in vbscript perhaps.

but if you insist, perhaps I would write my sql string to a table and then fire a dts package from my sp to export the field to a text file.

what are you trying to accomplish? I can't imagine how this might be particularly useful.

more details would be helpful.

are you trying to open an application on the server side. Not a good idea. Saw someone trigger Access to open on the server side from the web once. If the the user closed the browser or something that instance of Access just stayed open on server and the mess this caused until I fixed it.|||You can use master..xp_cmdshell extended stored proc to run DOS command. It should look like

Exec master..xp_cmdshell 'Echo Whatever > MylogFile.Txt'

Cheers.|||Thrasymachus and mkostadinovic, thank you for your replies.

I ended up using osql to output the text from the SQL code:

osql -U xx -P xxxxxx -S (local) -d xxxx -h-1 -w 4000 -n -i "C:\...\ExtractUpdatePlateData.sql" -o "C:\...\Insert plate data.sql"

In case you are still wondering why, the reason for doing this was to script changes made to the contents of a number of database tables from ~20 updates that had built up over the last 18 months or so. I needed to be able to extract out the data, but had a constraint that I couldn't just use a file containing the data, and a script to import it. Here is part of the SQL from ExtractUpdatePlateData.sql:

SELECT InsertScript = 'INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES (' + '''' + vv_secttype + '''' + ', ' + CAST(vi_proctype AS NVARCHAR) + ', ' + CAST(vi_numwells AS NVARCHAR) + ', ' + '''' + vv_descrip + '''' + ')'
FROM tblUPDsecttype

This is how it appears in Insert plate data.sql:

INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES ('ANO2B', 1, 96, 'ANAEROBE SYSTEMIC Man MiC')

It all seems to work fine (and has saved me a week's tedious work already).


Best regards,


Barry

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff Smyrski
Hi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

creating a dropdown list of Saturdays

Hello Everyone,
This is my first time posting here. I am sorry if this is a beginner
question, but... I am a beginner. I am trying to write this as a stored
procedure. I need to create a dropdown list of Saturdays starting with the
first date in the database.
Here are my questions:
(1)What I am unsure about is taking the date given to me, changing it to a
Saturday, and then creating a temp table filled with a list of Saturdays up
to last Saturday.
(2)Would it be better to use the t_date(varchar) to create the temp table
with the Sat. and then convert to datetime datatype?
Table - - transactions
field1 - - t_date
field2 - - t_a_date
Any help is greatly appreciated.
Thanks in advance!!
Butch
--
Message posted via http://www.sqlmonster.comWhy don't you try something like this:
select t_date
from transactions
where datepart(dw, t_date) = 7
and t_date < getdate()
This will give you all dates that are on Saturday up through last Saturday
This will not count TODAY if today IS saturday - if you want to count today
if it is a saturday, then change the t_date < getdate() to t_date <= getdate()
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Hello Everyone,
> This is my first time posting here. I am sorry if this is a beginner
> question, but... I am a beginner. I am trying to write this as a stored
> procedure. I need to create a dropdown list of Saturdays starting with the
> first date in the database.
> Here are my questions:
> (1)What I am unsure about is taking the date given to me, changing it to a
> Saturday, and then creating a temp table filled with a list of Saturdays up
> to last Saturday.
> (2)Would it be better to use the t_date(varchar) to create the temp table
> with the Sat. and then convert to datetime datatype?
> Table - - transactions
> field1 - - t_date
> field2 - - t_a_date
> Any help is greatly appreciated.
> Thanks in advance!!
> Butch
> --
> Message posted via http://www.sqlmonster.com
>|||Thank you for your quick response.
I tried this and it did not produce any results. :-(
here is my code:
select trans_id, t_a_date
from transactions
where trans_id = 6
AND datepart(dw, t_a_date) = 7
AND t_s_date < getdate()
trans_id gives me the first occurance of a date in the table. The date I get
is not a Saturday. I had to use t_a_date because of the datetime datatype.
Again, thank you for your time and help!
lonnye wrote:
>Why don't you try something like this:
>select t_date
>from transactions
>where datepart(dw, t_date) = 7
>and t_date < getdate()
>
--
Message posted via http://www.sqlmonster.com|||Is trans_id unique and/or the primary key on the table?
If you run the following, what do you get?
select datepart(dw, getdate())
Today is Thur, March 13... You should get 5 as your result.
Please let me know. (this is fun for me)
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Thank you for your quick response.
> I tried this and it did not produce any results. :-(
> here is my code:
> select trans_id, t_a_date
> from transactions
> where trans_id = 6
> AND datepart(dw, t_a_date) = 7
> AND t_s_date < getdate()
> trans_id gives me the first occurance of a date in the table. The date I get
> is not a Saturday. I had to use t_a_date because of the datetime datatype.
> Again, thank you for your time and help!
>
> lonnye wrote:
> >Why don't you try something like this:
> >
> >select t_date
> >from transactions
> >where datepart(dw, t_date) = 7
> >and t_date < getdate()
> >
> --
> Message posted via http://www.sqlmonster.com
>|||trans_id is unique and is the primary key. And I do get 5 as my result
Thanks,
lonnye wrote:
>Is trans_id unique and/or the primary key on the table?
>If you run the following, what do you get?
>select datepart(dw, getdate())
>Today is Thur, March 13... You should get 5 as your result.
>Please let me know. (this is fun for me)
>> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||Then you wouldnt want to include the "where trans_id = 6" since that will
only return that one row - and wont return it if that row does not fall on a
saturday.
Let me know if you still have an issue.
--
~lb
"cearnhart via SQLMonster.com" wrote:
> trans_id is unique and is the primary key. And I do get 5 as my result
> Thanks,
> lonnye wrote:
> >Is trans_id unique and/or the primary key on the table?
> >If you run the following, what do you get?
> >
> >select datepart(dw, getdate())
> >Today is Thur, March 13... You should get 5 as your result.
> >Please let me know. (this is fun for me)
> >> Thank you for your quick response.
> >>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||It is still not producing results. The first date in the database is
09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
that date is not in the database.
Do I need to create a datetime variable and set that to the first date, and
then go from there? Does that make sense?
lonnye wrote:
>Then you wouldnt want to include the "where trans_id = 6" since that will
>only return that one row - and wont return it if that row does not fall on a
>saturday.
>Let me know if you still have an issue.
>> trans_id is unique and is the primary key. And I do get 5 as my result
>[quoted text clipped - 7 lines]
>> >Please let me know. (this is fun for me)
>> >> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||If that date does not exist in the database and you want it shown,then you
will need to pull from a variable that increments.
Personally I would rather it not show if I already know from the query that
there is no data for that date. This way the users would not be selecting a
date only to have nothing show up (but this is just personal preference).
--
~lb
"cearnhart via SQLMonster.com" wrote:
> It is still not producing results. The first date in the database is
> 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
> that date is not in the database.
> Do I need to create a datetime variable and set that to the first date, and
> then go from there? Does that make sense?
>
> lonnye wrote:
> >Then you wouldnt want to include the "where trans_id = 6" since that will
> >only return that one row - and wont return it if that row does not fall on a
> >saturday.
> >Let me know if you still have an issue.
> >> trans_id is unique and is the primary key. And I do get 5 as my result
> >>
> >[quoted text clipped - 7 lines]
> >> >Please let me know. (this is fun for me)
> >> >> Thank you for your quick response.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||On Mar 13, 10:26=A0pm, lonnye <lon...@.discussions.microsoft.com> wrote:
> If that date does not exist in the database and you want it shown,then you=
> will need to pull from a variable that increments.
> Personally I would rather it not show if I already know from the query tha=t
> there is no data for that date. This way the users would not be selecting =a
> date only to have nothing show up (but this is just personal preference).
> --
> ~lb
>
> "cearnhart via SQLMonster.com" wrote:
> > It is still not producing results. =A0The first date in the database is
> > 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), whic=h
> > that date is not in the database.
> > Do I need to create a datetime variable and set that to the first date, =and
> > then go from there? =A0Does that make sense? =A0
> > lonnye wrote:
> > >Then you wouldnt want to include the "where trans_id =3D 6" since that =will
> > >only return that one row - and wont return it if that row does not fall= on a
> > >saturday.
> > >Let me know if you still have an issue.
> > >> trans_id is unique and is the primary key. =A0And I do get 5 as my re=sult
> > >[quoted text clipped - 7 lines]
> > >> >Please let me know. (this is fun for me)
> > >> >> Thank you for your quick response.
> > --
> > Message posted via SQLMonster.com
> >http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1- =Hide quoted text -
> - Show quoted text -
Solution is creating a function ( with single date input ) which will
give the complete date table from imput date till recent date and from
that reslut set , u can eaisly run below query to extact what u
need .. We worked out here & its running fine .
select DateID, datename(dw, DateID) as Dayname from [dbo].[fnname]
('2006-01-01 00:00:00')
where datepart(dw, DateID) =3D 7 and DateID <=3D getdate()
even with this result set, you can map with your own fact table to get
the date for the missing dates in your database.
--Ayyappa

Creating a Databse on the Fly

I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
But when i am trying to do so i am getting error
--------------------------
create procedure create_DB
@.db_name as varchar(30)
as
create database @.db_name
------------------------
The error is as Follows
'Incorrect syntax near '@.db_name'.'

Pl help me in this regardsOriginally posted by pankaj_bidwai
I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
But when i am trying to do so i am getting error
--------------------------
create procedure create_DB
@.db_name as varchar(30)
as
create database @.db_name
------------------------
The error is as Follows
'Incorrect syntax near '@.db_name'.'

Pl help me in this regards

I guess you missed the brackets:

create procedure create_DB
(@.db_name as varchar(30))
as
create database @.db_name|||Hi
No that is not the prob even if i use the brackets i will get an error
If i give harcoded name instead of variable i don't get an error
Originally posted by DoktorBlue
I guess you missed the brackets:

create procedure create_DB
(@.db_name as varchar(30))
as
create database @.db_name|||pankaj_bidwai, CREATE DATABASE does not accept a variable for the db name. Have you tried:

declare @.db_name varchar(30)
set @.db_name = 'PSYTEST'
execute('create database ' + @.db_name)
execute('drop database ' + @.db_name)|||Remove the "AS" in the parameter list in the stored proc signature.|||Hu?? Please post corrected code demonstrating how this works.|||Here you go...

create procedure create_DB
@.db_name varchar(30)
as

exec('create database ' + @.db_name)

go|||What happend to removing the "AS"?|||Alas, that wasn't the problem after all. Using "as" in the parameter list is a technique that I've not seen before. I didn't think it was valid SQL. The real problem was the lack of dynamic SQL to build the CREATE DATABASE statement properly.

I didn't read any of the other threads, so my reply may have been redundant.

Creating a CE db at the desktop PC

Hello,

Is it possible to create a .SDF file at the develop machine?I want to write an exe which creates a sdf.So, my customer will be able to create a sdf and import this sdf to the device.

Thanks.

Hi xyzt,

Yes, you can create the .sdf either by writing an application (by calling the engine's CreateDatabase function, and scripting the schema), or with SQL Server Management Studio. You could then deliver that .sdf file to the device in your installation, or some other way.

Either way you do it, people seem to have the best results with scripting the database schema - I've heard the designers in SQL Server Management Studio need a little work.

Good luck!

create views in the order of their dependency

In SQL 2K...

i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?

For example if View_BOB says...

CREATE VIEW VIEW_BOB
AS
SELECT COL_1 FROM VIEW_JOHN

I need the script to generate a script that creates View_JOHN before View_BOB.You can use Enterprise Manager to generate the script for you and it will take care of dependency of the objects.

SQL Server Helper
http://www.sql-server-helper.com