Thanks...
It is not a best practice to create user stored procs in master. This SP was undocumented and not subject to the 3 release deprecation policy. I think it went away with the move to the resource database in SQL2K5.
With that, what is your specific scenario for creating stored procs in master?
I have a sp that dumps text into a textfile but I am having trouble
creating the textfile.
EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'
The directory "The File" has a space in it. I've tried putting the
carat ^ before the space, and putting double quotes...but I keep
getting this error
'\\servername\d$\The' is not recognized as an internal or external
command, operable program or batch file.
If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
File\sub\filename.dat"' I get the same thing.
If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
File\sub\filename.dat""' I get
'"\\servername\d$\The File\sub\filename.dat"'
is not recognized as an internal or external command, operable program
or batch file.
Does anybody see what I am doing wrong?At a quick glance, it looks like filename.dat is simply not an
executable file - what exactly are you trying to do?
Simon|||Hi
Enquote the filename with double quotes should have worked, therefore the
file may not exist or could be still open. If "\\servername\d$\The
File\sub\filename.dat" from a command prompt says the file does not exist
then it probably doesn't or permissions are wrong.
If this is a batch file you may want to use a more appropriate extension.
John
"phantom" <phantomtoe@.yahoo.com> wrote in message
news:1126224325.956647.140030@.g43g2000cwa.googlegr oups.com...
> Greetings,
> I have a sp that dumps text into a textfile but I am having trouble
> creating the textfile.
> EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'
> The directory "The File" has a space in it. I've tried putting the
> carat ^ before the space, and putting double quotes...but I keep
> getting this error
> '\\servername\d$\The' is not recognized as an internal or external
> command, operable program or batch file.
> If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
> File\sub\filename.dat"' I get the same thing.
> If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
> File\sub\filename.dat""' I get
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.
> Does anybody see what I am doing wrong?|||phantom (phantomtoe@.yahoo.com) writes:
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.
Windows executes files depending on the suffixes. .dat is normally not
tied to any application. Thus, Windows does not know how to run the file.
If the file is a simple command file, the extension should be .BAT.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
When creating databases is it a good idea to use the master.dbo.sysdatabases database and then create databases under this.
An example I saw for a forum app did this:
IF EXISTS(SELECT name FROMmaster.dbo.sysdatabasesWHERE name= N'EggHeadCafeForum')
DROP DATABASE[EggHeadCafeForum]
GO
CREATE DATABASE[EggHeadCafeForum]ON(NAME= N'EggHeadCafeForum_Data', FILENAME = N'C:\EggHeadCafeForum_Data.MDF' ,SIZE= 5, FILEGROWTH = 10%)LOG ON(NAME= N'EggHeadCafeForum_Log', FILENAME = N'C:\EggHeadCafeForum_Log.LDF' ,SIZE= 1, FILEGROWTH = 10%)
COLLATESQL_Latin1_General_CP1_CI_AS
GO
It would offer one advantage in terms of when having the site hosted all my databases could be moved under the masterdatabase,However I am thinking there may be disadvantages, one being I use theasp.net roles/admin database that isn't created under master
Any feedback would be great.
Cheers
Adam
You cannot create a database "under" master database. All DB's are at the same level. "sysdatabases" is a table located in master db that has a listing of all the databases on the server. So the code "IF EXISTS.." is only checking if the DB already exists by querying against the list and if it does, its dropping it and recreating it.
|||You can connect to master and create the database from there. The database you created however is not UNDER master. There isn't a hierarchy in databases.|||Thanks guys, see what you mean now
|||Thanks guys, see what you mean now
