Does SQL Server Exist

Hi All,

I am trying to write an application to install a product that my company has developed. As part of the install I want to perform some validation ie is IIS running is SQL 2000 running etc before installation. The application is in VB .net can anyone tell me if it is possible to check if SQL Server 2000 exists and is running?

Thanks in advance for your help.

Cheers,

SamBefore using SQL DMO or something to check the service state of SQL Server, why not issuing a simple command like "Select 1" to check whether the server is up or not. Of you want to check for the presence of a database you might issue the command:
Select 1 from master..sysdatabases Where name = 'YourDBNametoCheckfor'

HTH, Jens Suessmeyer|||i would first check if network is available, after check if the server (not sql database server) is on-line... and then check if database server is installed... i'm using this for now till i find something better
i open connection to the server for default database and if its not there (exception) then the sql server is either not runing or it's service is stoped...
hope this helps a bit.... its not perfect but it helps for now...
Private Function CheckServer() As Boolean
Try
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
MyCnn.Close()
Return True
Catch ex As SqlException
Return False
Catch ex As Exception
Return False
End Try
End Function

and here i check if database with that name exists
Private Function CheckDb() As Boolean
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
With MyCmd
.Connection = MyCnn
'this line of code well sql query i found somewhere on this forums
.CommandText = "Select 1 from master..sysdatabases Where name = '" & m_dbName & "'"
.CommandType = CommandType.Text
End With
Try
Select Case (MyCmd.ExecuteScalar).ToString
Case 1
Return True
MyCnn.Close()
Exit Function
Case Else
Return False
MyCnn.Close()
Exit Function
End Select
Catch ex As NullReferenceException
Return False
MyCnn.Close()
Exit Function
End Try
MyCnn.Close()
End Function

Does SQL Server Exist

Hi All,

I am trying to write an application to install a product that my company has developed. As part of the install I want to perform some validation ie is IIS running is SQL 2000 running etc before installation. The application is in VB .net can anyone tell me if it is possible to check if SQL Server 2000 exists and is running?

Thanks in advance for your help.

Cheers,

SamBefore using SQL DMO or something to check the service state of SQL Server, why not issuing a simple command like "Select 1" to check whether the server is up or not. Of you want to check for the presence of a database you might issue the command:
Select 1 from master..sysdatabases Where name = 'YourDBNametoCheckfor'

HTH, Jens Suessmeyer|||i would first check if network is available, after check if the server (not sql database server) is on-line... and then check if database server is installed... i'm using this for now till i find something better
i open connection to the server for default database and if its not there (exception) then the sql server is either not runing or it's service is stoped...
hope this helps a bit.... its not perfect but it helps for now...
Private Function CheckServer() As Boolean
Try
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
MyCnn.Close()
Return True
Catch ex As SqlException
Return False
Catch ex As Exception
Return False
End Try
End Function

and here i check if database with that name exists
Private Function CheckDb() As Boolean
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
With MyCmd
.Connection = MyCnn
'this line of code well sql query i found somewhere on this forums
.CommandText = "Select 1 from master..sysdatabases Where name = '" & m_dbName & "'"
.CommandType = CommandType.Text
End With
Try
Select Case (MyCmd.ExecuteScalar).ToString
Case 1
Return True
MyCnn.Close()
Exit Function
Case Else
Return False
MyCnn.Close()
Exit Function
End Select
Catch ex As NullReferenceException
Return False
MyCnn.Close()
Exit Function
End Try
MyCnn.Close()
End Function

Does SQL have a function that return "null" for records which dont exist in a FK realation

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.

Thank you very much.

when doing select with 'join's over two tables it would return null.

For example, you have customers and customerdetails table

if you do

select *

from customers

left join customerdetails on customers.customerId = customerdetails.customerId

if there is no customer details for a customer the fields corresponding to the customer details table would be null.

Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)

Cheers,

Yani

|||

Thank you very much indeedYani Dzhurov.

Does not exist or access denied

Newbie thing here. When you get the message (maybe not verbatim) "SQL Server
does not exist or access denied", is there a way to tell which one it is?
Problem is, client may type in the server name wrong, or may have wrong
password. It'd be nice to know if they had the correct instance name (Server
does not exist) or the wrong credentials (access denied).
Can I differentiate between the two?
TIA,
MikeMike,
That one means the machine was not found by that name.
If it was a login failure, you'd get "Login failed for user '%ls'"
%ls will be substituted by the login you supplied.
James Hokes
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e7ZtDK01DHA.4060@.TK2MSFTNGP11.phx.gbl...
> Newbie thing here. When you get the message (maybe not verbatim) "SQL
Server
> does not exist or access denied", is there a way to tell which one it is?
> Problem is, client may type in the server name wrong, or may have wrong
> password. It'd be nice to know if they had the correct instance name
(Server
> does not exist) or the wrong credentials (access denied).
> Can I differentiate between the two?
> TIA,
> Mike
>

Does not exist or access denied

Newbie thing here. When you get the message (maybe not verbatim) "SQL Server
does not exist or access denied", is there a way to tell which one it is?
Problem is, client may type in the server name wrong, or may have wrong
password. It'd be nice to know if they had the correct instance name (Server
does not exist) or the wrong credentials (access denied).
Can I differentiate between the two?
TIA,
MikeMike,
That one means the machine was not found by that name.
If it was a login failure, you'd get "Login failed for user '%ls'"
%ls will be substituted by the login you supplied.
James Hokes
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e7ZtDK01DHA.4060@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Newbie thing here. When you get the message (maybe not verbatim) "SQL

---

Server

quote:

---

> does not exist or access denied", is there a way to tell which one it is?
> Problem is, client may type in the server name wrong, or may have wrong
> password. It'd be nice to know if they had the correct instance name

---

(Server

quote:

---

> does not exist) or the wrong credentials (access denied).
> Can I differentiate between the two?
> TIA,
> Mike
>

---

Does it exist a boolean data type in SQL SERVER ?

Hello. A question please. To define a column of a table in SqlServer, Does it exist a boolean data type ?

Thanks...

Yes, it has a data type for this, this is bit (it is an integer value with the constraint to only allow 0 and 1 (and NULL of course)).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Does HTML interpretation in SQL Server Reporting Services exist?

Hi everybody

Is there a way to show HTML tags in a SQL Server Reporting services just like we are used to do in Crystal Reports?

I my case, I use the property Text Interpretation: HTML (right click in parameter field inside report and click edit parameter menu option).

I was wondering if we have something similiar in Reporting services?!

HTML inlining is not supported with RS 2005. It is on the top list for the next release.|||Thank you for being responsive Teo. We`ll be waiting for the next SQL Server Reporting Services release.

best regards

Wander Mahet|||when is the next release coming out? are there any workarounds in the meantime? are there any resources, links, etc on importing/using custom controls in reports?|||

The release date of the next release of SQL Server "Katmai" is not announced yet. It will probably happen at TechEd next month. As you would notice by searching on the web the rumors are that it will ship sometimes in 2008. Currently, the only workaround I can think of is to create a custom report item although it will be probably an overkill. This article introduces CRI development. I recommend you put the requirements on the backburner for a year or so if possible. Inline HTML rendering is on the top list for the next release and I will be suprised if doesn't get implemented.

Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...

Does Database Exist?

How can I run a query to see if a database exists?
I see the queries like using the sysobjects and xtype to query
different table names and stored procs...but can I use something
similar to see if a database exists?
Something along the lines of: if exists(Select * from sysobjects where
xtype = 'database' Where database name = 'Client1DB')?IF DB_ID('foo') IS NULL
PRINT 'does not exist';
ELSE
PRINT 'exists';
"INeedADip" <ineedadip@.gmail.com> wrote in message
news:1151520416.422554.20030@.j72g2000cwa.googlegroups.com...
> How can I run a query to see if a database exists?
> I see the queries like using the sysobjects and xtype to query
> different table names and stored procs...but can I use something
> similar to see if a database exists?
> Something along the lines of: if exists(Select * from sysobjects where
> xtype = 'database' Where database name = 'Client1DB')?
>|||Thanks.

Does a tool like this exist?

Hi:
I am looking for a tool/utility that can compare 2 tables (indexes and
constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
statements that would update the second table so it is the same as the first
table.
Does anything like this exist? It's a bit of a long-shot isn't it?
TIA,
MartinH.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
--Keith
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the
> first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
|||Hi,
SQLCompare is an excellent tool to compare and generate the script for the
differences between databases.
Try out the 14 day trial.
http://www.red-gate.com/SQL_Compare.htm
Thanks
Hari
SQL Server MVP
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Martin Hart - Memory Soft, S.L. wrote:
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as
> the first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
>
Imceda offers Speed Change Manager. 14-day trial on web site.
David Gugick
Imceda Software
www.imceda.com
|||Keith:
Thanks, looks *very* interesting.
Regards,
MartinH.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> escribi en el mensaje
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Innovartis does DB Ghost - more than a comparison tool - 30 day trial
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Martin Hart - Memory Soft, S.L." wrote:

> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the first
> table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
>
|||Could you tell me Where is the web site ?
Thanks a lot
"mark baekdal" <markbaekdal@.discussions.microsoft.com> glsD:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...[vbcol=seagreen]
> Innovartis does DB Ghost - more than a comparison tool - 30 day trial
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
> "Martin Hart - Memory Soft, S.L." wrote:
|||http://www.dbghost.com
"Agnes" wrote:

> Could you tell me Where is the web site ?
> Thanks a lot
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> ???g?ó?l￥ó·s?D:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...
>
>

does a table exist?

Does anyone know if you can check if an object (eg a table) exists. I am
having trouble with procs that drop tables before creating them. Other users
get the message, "cannot drop table tblTest ... doesn't exist in the system
catalog".
I need to do something like,
If tblTest exists
Drop tblTest
...
then recreate the table
ThanksNickl wrote:
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other use
rs
> get the message, "cannot drop table tblTest ... doesn't exist in the syste
m
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Say I want to check if table "Departments" exists (from the "Generate
SQL Scripts" option in Enterprise Manager):
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Departments]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Departments]
Just substitute your table name (and owner [dbo]?) for Departments.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgA9TYechKqOuFEgEQIBvwCgyWX4oG3wDVTW
URVNdrLkAz6/WKUAnRak
h8BmLTTPTkyE2MvNYl2mkVGH
=UYde
--END PGP SIGNATURE--|||Hello Nickl,
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblTest'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> Does anyone know if you can check if an object (eg a table) exists. I
> am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks|||Or...
if object_id('your_tb','U') is not null
drop table your_tb
-oj
"Nickl" <Nickl@.discussions.microsoft.com> wrote in message
news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>|||Thanks everyone, good methods all. I really like yours oj, it looks very bri
ef
NIck Lindner
"oj" wrote:

> Or...
> if object_id('your_tb','U') is not null
> drop table your_tb
> --
> -oj
>
> "Nickl" <Nickl@.discussions.microsoft.com> wrote in message
> news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
>
>

Does a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.

Does "SQL Server 2005 Mobile Edition" exist?

"SQL Server 2005 Mobile Edition" is a standalone software?

or

it equals "SQL Server 2005" + "SQL Server 2005 Mobile Edition Device SDK"?

Hi Raphael,

You need to download SQL Server 2005 Mobile Edition Device Device SDK which is the standalone software. You do not need SQL Server 2005 on the desktop unless you plan to sync your Mobile data to a SQL Server using Remote Data Access (RDA) or Merge replication. Otherwise you only need SQL Server 2005 Mobile Edition Device Device SDK.

Here is a sysreqs url which has all this mentioned there :

http://www.microsoft.com/sql/editions/sqlmobile/sysreqs.mspx

Hope that helps,

Amol.

|||

Amol.

Thank yor for your help!

I have seen the link you provided before and read some other articles. But too much information make me quite confused.

My situation is that I need to create a mobile application (use SQL server mobile) in the device. Also, I need to sync my mobile data to a SQL Server.

Questions:

Then I want to ask "SQL Server 2005 Mobile Edition" is a standalone software like "SQL Server 2005 Express Edition"?

Or "SQL Server 2005 Mobile Edition" is a compact database stored in device, can use "SQL Server 2005 Express Edition", "SQL Server 2005 Standard Edition",......etc to open it on server side?

|||

Hi Raphael,

SQL Server 2005 mobile edition IS a standalone software which runs by itself on the device. The data is stored with a small footprint on the device. It's like having a server and its data on the device which u carry around. As an enterprise requirement, you can connect to a SQL Server 2005 running in your enterprise and sync your device data with the server data. I believe you can also connect to a SQL Server from your device to view and work with the server data.

Regards,

Amol.

|||Thank you!|||

Hi Amol,

I am also running 2005 mobile ed. on a handheld. I'm trying to replicate the data to a PC running SQL 2005 Express then have Express replicate to an Enterprise SQL Server. I understand that Express can only be the subscriber. Is it possible although I'm synching to a PC (thru a cradle) to replicate the 2005 mobile DB to an enterprise SQL Server directly, bypassing the PC with Express?

Does "SQL Server 2005 Mobile Edition" exist?

"SQL Server 2005 Mobile Edition" is a standalone software?

or

it equals "SQL Server 2005" + "SQL Server 2005 Mobile Edition Device SDK"?

Hi Raphael,

You need to download SQL Server 2005 Mobile Edition Device Device SDK which is the standalone software. You do not need SQL Server 2005 on the desktop unless you plan to sync your Mobile data to a SQL Server using Remote Data Access (RDA) or Merge replication. Otherwise you only need SQL Server 2005 Mobile Edition Device Device SDK.

Here is a sysreqs url which has all this mentioned there :

http://www.microsoft.com/sql/editions/sqlmobile/sysreqs.mspx

Hope that helps,

Amol.

|||

Amol.

Thank yor for your help!

I have seen the link you provided before and read some other articles. But too much information make me quite confused.

My situation is that I need to create a mobile application (use SQL server mobile) in the device. Also, I need to sync my mobile data to a SQL Server.

Questions:

Then I want to ask "SQL Server 2005 Mobile Edition" is a standalone software like "SQL Server 2005 Express Edition"?

Or "SQL Server 2005 Mobile Edition" is a compact database stored in device, can use "SQL Server 2005 Express Edition", "SQL Server 2005 Standard Edition",......etc to open it on server side?

|||

Hi Raphael,

SQL Server 2005 mobile edition IS a standalone software which runs by itself on the device. The data is stored with a small footprint on the device. It's like having a server and its data on the device which u carry around. As an enterprise requirement, you can connect to a SQL Server 2005 running in your enterprise and sync your device data with the server data. I believe you can also connect to a SQL Server from your device to view and work with the server data.

Regards,

Amol.

|||Thank you!|||

Hi Amol,

I am also running 2005 mobile ed. on a handheld. I'm trying to replicate the data to a PC running SQL 2005 Express then have Express replicate to an Enterprise SQL Server. I understand that Express can only be the subscriber. Is it possible although I'm synching to a PC (thru a cradle) to replicate the 2005 mobile DB to an enterprise SQL Server directly, bypassing the PC with Express?