Embedding multiple .sql files

How do I write a .sql script that executes a number of other scripts in .sql
files? This has to be easy. I just can't find it.
Thanks,
JerryRYou can use oSql. See if this helps:
http://www.sql-server-performance.com/rd_osql.asp
Andrew J. Kelly SQL MVP
"JerryR" <JerryR@.discussions.microsoft.com> wrote in message
news:76549937-4F1E-41B5-B148-A548F0B8CA95@.microsoft.com...
> How do I write a .sql script that executes a number of other scripts in
> .sql
> files? This has to be easy. I just can't find it.
> Thanks,
> JerryR|||There is no such feature in SQL like this in Oracle @.Somefile.sql. You
have to put in in a OS batch to execute it via a cmdshell program like
SQLCMD or OSQL or ISQL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes, that's what I was looking for.
Thanks
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1143927815.456011.236990@.v46g2000cwv.googlegroups.com...
> There is no such feature in SQL like this in Oracle @.Somefile.sql. You
> have to put in in a OS batch to execute it via a cmdshell program like
> SQLCMD or OSQL or ISQL.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Embedded SQL Server Express Deployment - Unhandled Exception: Database already exists

Good Afternoon.

I am trying to write a short application within which I would like to embed a SQL Server Express database, all of which would be deployed via ClickOnce through a web site. The installation process seems to be working without trouble, including installation of the .NET Framework 2.0 and SQL Server Express 2005.

When the application launches on the new machine, however, I get a big Microsoft .NET Framework "Unhandled exception" error dialog box that indicates the database already exists and that the database file could not be attached. I am working with a clean virtual machine that I know has never had the database installed on it.

I fear I am overlooking something quite straightforward, but since this is the first time I have ever attempted to build a data-bound application, I am not sure where I am going wrong.

Perhaps the source of the problem, or a related problem, might be revealed even when I run the application in debug mode from within Visual Studio. I can run the application once, but if I try to run it again, I get an error when I try to open the database connection that is similar in content (database already exists...could not attach). I can run the SSEUtil and detach the database and then run the application again in debug mode and it works no problem (the first time!).

Lastly, if I install the application on the machine on which it was developed and make sure the database is detached, it will run without any trouble (even repeatedly). But, on a new blank machine, there is no database listed that matches my database name, so I cannot try to detach anything!?

My apologies for such a novice question!

Sean

Hi Sean,

I would suggest removing the ‘Initial Catalog’ keyword from your connection string. Among the things that are stored about a database when it is attached is the Name/Path pairing; SQL Server gets a little upset if you try to attach a database using an existing name but with a different path. Enter the |DataDirectory| macro…

I’m guessing you’re using ClickOnce deployment for this application. There are things that will cause the install directory of a ClickOnce deployment to move, when this happens, VS magically fixes up things as far as the application goes, but it cannot fix up the fact that SQL Server has recorded your explicit database as being linked to the old file location. When you attempt to open the application now, the attach fails because there is already a database named JMU_2007 listed in the master database, but it is associated with a different path, so you get the error that the database already exists. I’m guessing this is the problem you’re hitting.

By removing the ‘Initial Catalog’ keyword, you’re forcing SQL Express to give the database an autoname based on its path. If the database moves as a result of VS behavior, the name will be changed because the path has changed, so it will appear to be a different database from the SQL perspective. Hopefully this helps.

Mike

|||

Thanks very much Mike. That did the trick...I can't believe I overlooked that after spending too much time trying to fix this problem.

Sean

Embarrassingly simple question about using stored proceedures

The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
if the stored procedure was a table I would write the query
Select COLUMN_NAME from sp_columns
Whats the simplest way of doing this for a stored proceedure
many thanks
David HEither re-write the procedure or use below technique:
CREATE TABLE #tmp...
INSERT #tmp
EXEC sp_columns
SELECT ... FROM #tmp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
> The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
> if the stored procedure was a table I would write the query
> Select COLUMN_NAME from sp_columns
> Whats the simplest way of doing this for a stored proceedure
> many thanks
> David H
>|||An even simpler approach would be to ditch sp_columns and use the
INFORMATION_SCHEMA views instead. For example, the get the columns of the
Authors table in the pubs database use:
USE pubs
GO
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
or
SELECT COLUMN_NAME
FROM pubs.INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
You can get more information INFORMATION_SCHEMA views in BOL:
http://msdn.microsoft.com/library/d...br />
4pbn.asp
Cheers,
Stefan
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> Either re-write the procedure or use below technique:
> CREATE TABLE #tmp...
> INSERT #tmp
> EXEC sp_columns
> SELECT ... FROM #tmp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David Hills" <dhills@.pcfe.ac.uk> wrote in message
> news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
the
> COLUMN_NAME
>|||That's a good point, Stefan!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stefan Delmarco [MSFT]" <StefanDe@.online.microsoft.com> wrote in messag
e
news:eAuYCTZDEHA.3240@.TK2MSFTNGP10.phx.gbl...
> An even simpler approach would be to ditch sp_columns and use the
> INFORMATION_SCHEMA views instead. For example, the get the columns of the
> Authors table in the pubs database use:
> USE pubs
> GO
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> or
> SELECT COLUMN_NAME
> FROM pubs.INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> You can get more information INFORMATION_SCHEMA views in BOL:
>
http://msdn.microsoft.com/library/d..._ia-iz_4pbn.asp[
color=darkred]
> Cheers,
> Stefan
> --
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> the
>|||Many thanks, that's just what I needed to know to do this
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'sipr'
AND COLUMN_NAME LIKE '%rlg%'
TABLE_NAME COLUMN_NAME
--
d21_eed44 rlg_code
D04_RLG rlg_code
D04_RLG rlg_name
D04_RLG rlg_snam
SRS_RLG rlg_code
SRS_RLG rlg_name
SRS_RLG rlg_snam
D04_STU stu_rlgc
D04_CONTCT stu_rlg
D51_STU_CTS stu_rlgc
D51_STU_V26 stu_rlgc
INS_STU stu_rlgc

email notification on job status

In MSDE, since there is no sql mail, I am thinking of doing the following:
1. write a shell script which will query the sysjobhistory table to
determine if any job failed. If so, then an email about the failure is sent
out.
Question: What should my sql command and my mail command look like. I dont
have much experience in shell scripting or am not very familiar with MSDB
database either.
hi,
inquisite wrote:
> In MSDE, since there is no sql mail, I am thinking of doing the
> following:
> 1. write a shell script which will query the sysjobhistory table to
> determine if any job failed. If so, then an email about the failure
> is sent out.
> Question: What should my sql command and my mail command look like. I
> dont have much experience in shell scripting or am not very familiar
> with MSDB database either.
if an SMPT solution is viable, I'd resort on xp_smpt extended stored
procedure provided at http://sqldev.net/xp/xpsmtp.htm
you can then code your task's step to send a mail on success or failure..
SQL Server MVP Tibor Karaszi presents and describes such a feature at
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||SMTP solution is not available. That is why I wanted to send an email through
some script running on the server.
"Andrea Montanari" wrote:

> hi,
> inquisite wrote:
> if an SMPT solution is viable, I'd resort on xp_smpt extended stored
> procedure provided at http://sqldev.net/xp/xpsmtp.htm
> you can then code your task's step to send a mail on success or failure..
> SQL Server MVP Tibor Karaszi presents and describes such a feature at
> http://www.karaszi.com/sqlserver/info_no_mapi.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||If an SMTP server is not available then how were you planing to send email?
Jim
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:D7FE12AC-C359-4B74-A8C6-E497686705CB@.microsoft.com...[vbcol=seagreen]
> SMTP solution is not available. That is why I wanted to send an email
> through
> some script running on the server.
> "Andrea Montanari" wrote:

E-mail address of one or more recipients is not valid

We have setup Reporting Services on a Server 2003 box with SMTP intalled on
it. I am able to write a simple VB Script that uses CDO to send a e-mail out
to a certain address (let's say to john@.test.com). When I setup a
subscription using the same e-mail address I get the error "The e-mail
address of one or more recipients is not valid." Does anyone know why - if I
can write a 6 line CDO vbscript shouldn't SMTP be working?I've figured out what my problem is - apparently SQL Server Reporting
Services is not authenticating correctly. I had to change the settings on
the Relay Restictions on the Access tab for the SMTP Server Properties to be
allow relay through the virtual server with "All except the list below"
instead of the default "Only the list below". Seems like it should work with
the default setting since there is no authentication (anonymous access) but
looks like RS doesn't like that.
"John R" wrote:
> We have setup Reporting Services on a Server 2003 box with SMTP intalled on
> it. I am able to write a simple VB Script that uses CDO to send a e-mail out
> to a certain address (let's say to john@.test.com). When I setup a
> subscription using the same e-mail address I get the error "The e-mail
> address of one or more recipients is not valid." Does anyone know why - if I
> can write a 6 line CDO vbscript shouldn't SMTP be working?