I have no experience with MSDE and I am trying to learn how to start using it. I just installed MSDE 2000 release A and I am stuck and don't know what to do next. I heard that MSDE is only a database engine and you need to download tools/toolkits to creat
e databases and tables. Where can I get the necessary tools to start creating my database in MSDE?
Thanks.
-laura
Hi,
You can use the "SQL Server Web Data Administrator". It can be downloaded
from
http://www.microsoft.com/downloads/d...798-c57a-419e-
acbc-2a332cb7f959&displaylang=en
System Requirements for SQL Server Web Data Administrator is
Supported Operating Systems:
Windows 2000 Service Pack 4, Windows Server 2003, Windows XP
Web Data Administrator requires:
Microsoft SQL Server 7 SP2 or later
Microsoft .NET SDK or Microsoft Visual Studio .NET
Microsoft Internet Explorer 5.5 or later
Microsoft Internet Information Services (IIS) -- optional, a portable
web server* is also distributed with the project
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||If you are going to be doing much development, get the SQL Server
Developer's Edition. I gather it's currently only around $49 USD. You won't
find any value anywhere that compares with what you get for that.
If you aren't getting that and web isn't your style, we have a free personal
tool (MSDE Manager) at our site that might help.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"lmm" <anonymous@.discussions.microsoft.com> wrote in message
news:F4598769-B27F-488C-B859-9E46DBE344AA@.microsoft.com...
> I have no experience with MSDE and I am trying to learn how to start using
it. I just installed MSDE 2000 release A and I am stuck and don't know what
to do next. I heard that MSDE is only a database engine and you need to
download tools/toolkits to create databases and tables. Where can I get the
necessary tools to start creating my database in MSDE?
> Thanks.
> -laura
Showing posts with label learn. Show all posts
Showing posts with label learn. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Getting Return Value from system SPROC
I'm trying to learn how to write/use system sprocs for the 1st time. SYSTEM
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
> Should my "System Sproc" be an extended system Sproc?
>
NO!!!
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
> Should my "System Sproc" be an extended system Sproc?
>
NO!!!
Subscribe to:
Posts (Atom)