Cluster Nodes and Windows Domains

Cluster Nodes and Windows Domains
All nodes in the cluster must be members of the same
domain and able to access a domain controller and a Domain
Name System (DNS) server, as well as a WINS server. The
nodes should not be configured as domain controllers if
you are going to install SQL Server, as you may encounter
some problems, including the fact that domain controller
functionality (such as Active Directory) is not cluster
aware, so all information will be local. This impacts
things such as a directory enabled program's ability to
publish under the virtual server's computer object, which
does not work in a cluster. A WINS server is still
required for Windows 2000/SQL Server 2000 in a clustered
environment if name resolution of virtual resource is
required.
My question:
After reading the above paragraph. I want to setup two
nodes as peer to peer domain controllers running Windows
2000 Advanced, SQL Server 2000, Active directory service
running on each node, in a cluster environment.
What part of this is not cluster aware?
Why do I need a WINS server if I have DNS?
Why can't my nodes be a domain controller?( I'm using the
company's DNS server)
What does this means? (directory enabled program's ability
to publish under the virtual server's computer object,
which does not work in a cluster.)I would never, ever recommend installing SQL Server on a domain controller.
The security exposure is huge.
Steve
"Tony" <tony.baks@.tdh.state.tx.us> wrote in message
news:038001c35089$9061b550$a301280a@.phx.gbl...
> Cluster Nodes and Windows Domains
> All nodes in the cluster must be members of the same
> domain and able to access a domain controller and a Domain
> Name System (DNS) server, as well as a WINS server. The
> nodes should not be configured as domain controllers if
> you are going to install SQL Server, as you may encounter
> some problems, including the fact that domain controller
> functionality (such as Active Directory) is not cluster
> aware, so all information will be local. This impacts
> things such as a directory enabled program's ability to
> publish under the virtual server's computer object, which
> does not work in a cluster. A WINS server is still
> required for Windows 2000/SQL Server 2000 in a clustered
> environment if name resolution of virtual resource is
> required.
> My question:
> After reading the above paragraph. I want to setup two
> nodes as peer to peer domain controllers running Windows
> 2000 Advanced, SQL Server 2000, Active directory service
> running on each node, in a cluster environment.
> What part of this is not cluster aware?
> Why do I need a WINS server if I have DNS?
Many applications still require WINS...
> Why can't my nodes be a domain controller?( I'm using the
> company's DNS server)
HUGE security exposure, consider using a member server for your SQL Server
implementation.
> What does this means? (directory enabled program's ability
> to publish under the virtual server's computer object,
> which does not work in a cluster.)

Cluster installation & Domain Group requirements...

Why does a cluster install of SQL2005 require access to a domain group and what is the purpose of having to create a domain group for the cluster installation? Other than the blurb mentioned in BOL I'm having a hard time finding any information out about this requirement.

From BOL: How to create a failover cluster.

Section 18: On the Domain Groups for Clustered Services page, provide a DomainName\GroupName for each clustered service being installed: the SQL Server service, SQL Server Agent service, Analysis Services service, and Full-Text Search service must run as domain accounts that are members of the built-in administrators group on each node of the cluster. To proceed, click Next. For more information, see Domain Groups for Clustered Services.

The purpose for domain groups in cluster installation is security consideration. As SQL Server service accounts in a local group can be changed or removed after SQL Server 2005 is installed. Then the security setting like ACL will be lost. That is why domain group\account is introduced.

|||

Ok, that's kind of what I thought it was for.

Is there any way to change the group after installation? Can I just move the account to another group and be done with it? Or is there somewhere (in SQL Server) that I need to update the group if we desire to change groups?

sqlsql

Cluster access using IP

I joined a company as a DBA
the company has cluster servers set up.
I was given the 3 ip addresses. i for the cluster and 2 for for the ones the
cluster is made up of. and my login is set up to use SQL Server
Authentication.
How do i connect to the server. I do not know if they are active/active or
active/passive. I do not have any rights on the local boxes for me to telnet
them.
What is the way to connect to the database
what is the default instance that SQl server creates.
thanks a lots in advance
Connect to the address of the SQL Virtual Server, not just the cluster
address or the address of either machine (node).
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Nik" <patelnikhil30@.hotmail.com> wrote in message
news:%23%230sxSelEHA.3156@.TK2MSFTNGP12.phx.gbl...
>I joined a company as a DBA
> the company has cluster servers set up.
> I was given the 3 ip addresses. i for the cluster and 2 for for the ones
> the
> cluster is made up of. and my login is set up to use SQL Server
> Authentication.
> How do i connect to the server. I do not know if they are active/active or
> active/passive. I do not have any rights on the local boxes for me to
> telnet
> them.
> What is the way to connect to the database
> what is the default instance that SQl server creates.
> thanks a lots in advance
>

Clueless ... SBA and ACCESS

Hello,

Hopefully I'm near the right discussion group.

I'm trying to get started with integrating some of my manufacturing company's ACCESS databases with Microsoft's Small Business Accounting (SBA). The SDK for SBA provides plenty of VB code one I get past a certain point.

Can ACCESS be used as a front end for SBA?

Can anyone offer a sample .mdb with code that logs in to the db and adds a new customer (for example)?

TIA

rick

Definitely not the right group, Rick, as Access/VBA can not directly use ADO.NET. I don't know much about SBA, but I took a quick look, and on my system SBA stores its data in a SQL Server instance called ServerName\MICROSOFTSMLBIZ. You should, as far as I know, be able to connect to it from Access using any of the standard methods commonly used to connect to any SQL Server database, e.g. ADP, ODBC-linked tables, or programmatically via ADO 'classic' or DAO. If you haven't already done so, try asking in a forum dealing with Access ADPs or Access and ODBC. -- Brendan Reynolds Access MVP wrote in message news:6f240fe4-d011-4650-a122-ad993505a198@.discussions.microsoft.com...
> Hello, >
> Hopefully I'm near the right discussion group. >
> I'm trying to get started with integrating some of my manufacturing
> company's ACCESS databases with Microsoft's Small Business Accounting
> (SBA). The SDK for SBA provides plenty of VB code one I get past a
> certain point. >
> Can ACCESS be used as a front end for SBA? >
> Can anyone offer a sample .mdb with code that logs in to the db and adds
> a new customer (for example)? >
> TIA > > >
> rick >
>

CLR Stored procedure to access OleDb datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an
Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws
SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type
'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string,
String source_table, String destination_table, Int32 batchSize, Int32
notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputerWhen you created the assembly did you set the permission set? If not,
then it is SAFE by default and you will not be able ot get to your
access database. You MAY have to go all the way to UNSAFE to get
there, I have not checked.
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
SAFE, EXTERNAL_ACCESS, UNSAFE are the options that you have for this.
Steve

CLR Stored procedure to access Ole datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access
database.

When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime.
Output as following:

System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string, String source_table, String destination_table, Int32 batchSize, Int32 notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputer

Hello Va1era,

The exception you encountered indicates that the C# assembly containing the stored procedure that you registered has insufficient permissions to create an OleDbConnection object, with respect to the .Net framework Code Access Security (CAS) policies. This probably means that your assembly was registered WITH PERMISSION_SET = SAFE, which is also the default if PERMISSION_SET is unspecified in the CREATE ASSEMBLY DDL statement.

To fix this, you can re-register your assembly WITH PERMISSION_SET = EXTERNAL_ACCESS to gain access to the ADO.Net classes (including OleDbConnection). If you are using Visual Studio, you will have to make this change from SAFE to EXTERNAL_ACCESS in the properties page of your C# database project.

To understand more about the CAS security permission buckets in SQL Server's hosted CLR (SAFE, EXTERNAL_ACCESS and UNSAFE), you can look at this document: http://bordecal.mvps.org/Nicole/SqlClrCas/SqlClrCasSpeculations.htm. Also, here is the MSDN page on CAS and ADO.Net 2.0: http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx

Hope this helps.

Thanks,
Ravi

CLR problems

I have created a CLR stored procedure. Now I am trying to access that
CLR sproc from another stored procedure. When I try to create the
second sproc, I get the message:
Cannot add rows to sysdepends for the current object because it depends
on the missing object <<whatever>>.
Any ideas on what I'm doing wrong? I let Visual Studio deploy the
assembly and create the first stored procedure.
Thanks,
JoeHello Jmnd,

> I have created a CLR stored procedure. Now I am trying to access that
> CLR sproc from another stored procedure. When I try to create the
> second sproc, I get the message:
How do you mean "access that CLR sproc?" Are you calling it with T-SQL's
EXEC?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Jmnd (jnedumgottil@.gmail.com) writes:
> I have created a CLR stored procedure. Now I am trying to access that
> CLR sproc from another stored procedure. When I try to create the
> second sproc, I get the message:
> Cannot add rows to sysdepends for the current object because it depends
> on the missing object <<whatever>>.
<<whatever>>? Is that the name of your CLR procedure, or the name of
something else?

> Any ideas on what I'm doing wrong? I let Visual Studio deploy the
> assembly and create the first stored procedure.
Obviously Visual Studio did not create the procedure as you intended. Or
you are creating your T-SQL procedure in another database - or schema -
that you indended. How about using the Object Explorer to see what is in
that database?
Personally, I prefer to create CLR objects without Visual Studio. VS is
just so completely confusing. How difficult is it really to compile a
C# or VB program from the command line, saying CREATE ASSEMBLY, and then
CREATE PROCEDURE EXTERNAL NAME?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry, I will clarify. I was trying to access the stored procedure
using T-SQL "EXEC" command. And I tried using regular command lines,
after I ran into issues. I used the "Create Procedure External Name"
(which I modified by pulling the script from the existing CLR Stored
Procedure). However, when I tried to run the create script on the
second stored procedure (the one that calls the CLR sproc), I was still
getting the same error:
Cannot add rows to sysdepends for the current object because it depends
on the missing object <<my CLR stored procedure>>.
The CLR sproc is showing up in SQL Management Studio. And I can call
it - I ran a test script on it. However, this other sproc cannot call
the CLR one. Is this a permissions issue?
Thanks again for your help.
Joe|||Jmnd (jnedumgottil@.gmail.com) writes:
> Sorry, I will clarify. I was trying to access the stored procedure
> using T-SQL "EXEC" command. And I tried using regular command lines,
> after I ran into issues. I used the "Create Procedure External Name"
> (which I modified by pulling the script from the existing CLR Stored
> Procedure). However, when I tried to run the create script on the
> second stored procedure (the one that calls the CLR sproc), I was still
> getting the same error:
> Cannot add rows to sysdepends for the current object because it depends
> on the missing object <<my CLR stored procedure>>.
> The CLR sproc is showing up in SQL Management Studio. And I can call
> it - I ran a test script on it. However, this other sproc cannot call
> the CLR one. Is this a permissions issue?
No. More likely a trivial spelling errors. (Happens in the best families.)
When you look in Mgmt Studio, where does the T-SQL procedure appear?
(The message you get is not an error message, and the procedure is still
created. Which it also says in the message.)
If you are still , please post the T-SQL procedure, as well as
the T-SQL statements to create the CLR proc.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
I agree that its possible that it's something trivial, but it might not be.
We might be missing an assembly, or the called proc might not have been cata
loged.
Or somebody tinkered with the visibility of an assembly, or its a versioning
problem, etc.
Jmnd, here's a simple example of what I think you are trying to do. If so,
this should work. If not, please feel to contact me off-line for follow up.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CalledStoredProcedure()
{
SqlCommand cmd = new SqlCommand("select 'Hello from the called proc'");
SqlContext.Pipe.ExecuteAndSend(cmd);
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallingStoredProcedure()
{
SqlCommand cmd = new SqlCommand("select 'Hello from calling proc';
exec dbo.CalledStoredProcedure");
SqlContext.Pipe.ExecuteAndSend(cmd);
}
};
test with exec dbo.CallingStoredProcedure
I do agree with Erland, posting the code would help much.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for the input so far. I will double-check to make sure there
are no typos
========================================
================
here's my CLR procedure code in C#:
========================================
================
using System;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class QueryBuilder
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void buildIndividualSummaryQuery(
....
/*parameter list*/
....
)
{
QueryBuilder ci = new QueryBuilder();
ci.addSelect("IND_F", "DATE", "CAL_MONTH_YR");
ci.addSelect("DATE_D", "DATE", "FSCL_MONTH_YR",
"FiscalMonthYr", "MAX");
ci.addSelect("IND_F", "I", "TOTAL_IND", "TotalCount", "SUM");
/* This calls another method in the same class */
string sqlString = ci.buildIndividualReportsQuery(
/*params*/ );
SqlContext.Pipe.Send("SqlString=" + sqlString);
SqlConnection connection = new SqlConnection("context
connection=true");
connection.Open();
SqlCommand command = new SqlCommand(sqlString, connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
connection.Close();
}
}
========================================
================
Here's how I created the Stored Procedure wrapper:
========================================
================
USE [CustomerIntelligence]
GO
IF OBJECT_ID ( 'buildIndividualSummaryQuery', 'P' ) IS NOT NULL
DROP PROCEDURE buildIndividualSummaryQuery;
GO
CREATE PROCEDURE [dbo].[buildIndividualSummaryQuery]
...
/*params*/
...
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CI].[QueryBuilder].[buildIndividualSummaryQuery]
GO
========================================
====
Here's my stored procedure that calls the CLR procedure:
========================================
====
ET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'IndividualSummaryReport', 'P' ) IS NOT NULL
DROP PROCEDURE IndividualSummaryReport;
GO
CREATE PROCEDURE IndividualSummaryReport
...
/* parameter list */
...
AS
BEGIN
...
/* other extraneous code */
...
exec [buildIndividualSummaryQuery]
....
/* parameter list */
...
END
GO

CLR Integration Security

Hi,
In BOL it is mentioned that we can control the access of users to file
system resources by using impersonation (WindowsIdentity.Impersonate).
Imagine I have a SP that creates a file on disk but the user sends the path
as a parameter. Although I can control the user to access only to particular
path, but how can I control the developer herself? As a DBA, I need to
control the developer while they can write their codes without using
"WindowsIdentity.Impersonate". If they don't do impersonation, they can work
under the security account of SQL Server.
I want to give permissions based on the user accounts that they log into
their Windows.
Thanks in advance,
LeilaLeila wrote:
> Hi,
> In BOL it is mentioned that we can control the access of users to file
> system resources by using impersonation (WindowsIdentity.Impersonate).
> Imagine I have a SP that creates a file on disk but the user sends the pat
h
> as a parameter. Although I can control the user to access only to particul
ar
> path, but how can I control the developer herself? As a DBA, I need to
> control the developer while they can write their codes without using
> "WindowsIdentity.Impersonate".
I do not really understand the question. What I mean is that; if the
developer writes the code without Impersonate, then everyone runs
without impersonation - so IMHO, it is not a question about dev vs.
regular users. If you are worried about dev doing things they shouldn't
do - well make sure they only have necessary rights on dev machines and
not production boxes.

> If they don't do impersonation, they can work
> under the security account of SQL Server.
> I want to give permissions based on the user accounts that they log into
> their Windows.
Well, I would assume that the account SQL runs under has very low
priviliges, and if you want to give rights according to the log-ins -
require that the code has Impersonate. Also, remember that running under
the account of SQL is only applied when you execute out of SQL Server,
i.e. file I/O etc.
Niels

CLR integration - Could not find Type '?' in assembly '?'.

Hi,

I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.

The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.

I have created the assembly in sqlserver thus:

create assembly PinCodeLoader from
'C:\PinCodeLoader\PinCodeLoader\PinCodeLoader\bin\Debug\PinCodeLoader.dll'
with permission_set = external_access

But when I try to reference the assembly from a stored proc

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main

I get the following error:

Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.

I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows

namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{

Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.

Has anybody come across this or can they offer any assistance?

Many thanks,

Paul

Hi, try to compile the .vb file with this command line:

csc.exe /t:library PinCodeLoader.bv (Put the correct name of the .vb file)

If this work look at the properties of the project and search for 'root namespace' (or something similar), and delete it, then try again compiling the entire solution from de IDE...

Good luck

Alejandro F.

|||

You need to include the namespace as well:

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.[PinCodeLoader.PinCodeloader].Main

However, you still won't be able to create your proc because SQL can't map nvarchar(1024) to the string array args.

|||

Thanks for your replies guys. I didn't actually try that method but found a way around it.

In my c# program I removed the reference to a namespace, as I was told it's not really necessary and changed the code as follows.

public class PinCodeLoad

{

public static void Main(string filename,string datasource,Int32 timeout, string companyname)

{

So instead of using the args parameter I'm implictly defining them. Plus I preceded my class and method with public

I kept the stored proc as before.

I hope that helps anybody else.

Paul

ClR Function errror cant get to lcoal file system

My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?

Thanks
AdminAnupHow are they connecting to SQL Server ? If you are using SQL Server authenitcation, they will use the SQL Server account permissions to access the files, if this one does not have any permissions, they will get a Access denied. The same situation if you are using Windows authentication and the individual users do not have access to the files. Did you try to run the function individually ? Which error do you get there if you try to let it run at a user context (and not probably one with administrative priviledges :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||it is using sql authentication.

Can you let me know which permission does sql authentication need to execute

and also if i change it to be windows does it need any admin privileges|||If you are using SQL Server Authentication, the SQL Server service account SQL Server is running with, will need to have rights on the accessed files / shares. If you are using Windows authentication, the logged in User wil need to have those rights.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||Has the CLR assembly been granted EXTERNAL_ACCESS?|||Yes that was it as it was in Safe mode
Thanks

CLR access to File Info

"KurtK" <KurtK@.discussions.microsoft.com> wrote in message
news:D85635C4-9444-4CD4-8323-1903F8EA9B58@.microsoft.com...
> testFile = My.Computer.FileSystem.GetFileInfo(Filename)
VB My.* code is not supported under SQLCLR. If you used a VS Sql Server
Project you'll notice that you can't compile and deploy it from there as the
My feature is not supported for this project type:
http://msdn2.microsoft.com/en-us/library/ms172698.aspx
Changing your code to
testFile = new FileInfo(Filename)
should work
StevenSteven,
I tried replacing
testFile = My.Computer.FileSystem.GetFileInfo(Filename)
with
testFile = FileIO.FileSystem.GetFileInfo(Filename)
which seemed to be the only way to get to the GetFileInfo method. However I
still get the same exception.
This is my first venture into Visual Studio/.net since we still develop
under VB6 so I'm using the command line to compile my .vb code as was shown
in an example CLR integration sample I found.
Thanks,
Kurt
"Steven Hemingray [MSFT]" wrote:

> "KurtK" <KurtK@.discussions.microsoft.com> wrote in message
> news:D85635C4-9444-4CD4-8323-1903F8EA9B58@.microsoft.com...
> VB My.* code is not supported under SQLCLR. If you used a VS Sql Server
> Project you'll notice that you can't compile and deploy it from there as t
he
> My feature is not supported for this project type:
> http://msdn2.microsoft.com/en-us/library/ms172698.aspx
> Changing your code to
> testFile = new FileInfo(Filename)
> should work
> Steven
>
>|||I got it to work if I set the permission to UNSAFE. However since I'm only
accessing the FileInfo class it seems like EXTERNAL should be good enough.
What gives'
-Kurt
"Steven Hemingray [MSFT]" wrote:

> "KurtK" <KurtK@.discussions.microsoft.com> wrote in message
> news:D85635C4-9444-4CD4-8323-1903F8EA9B58@.microsoft.com...
> VB My.* code is not supported under SQLCLR. If you used a VS Sql Server
> Project you'll notice that you can't compile and deploy it from there as t
he
> My feature is not supported for this project type:
> http://msdn2.microsoft.com/en-us/library/ms172698.aspx
> Changing your code to
> testFile = new FileInfo(Filename)
> should work
> Steven
>
>|||My.Computer.FileSystem is very similar to FileIO.FileSystem so neither
approach works under SQLCLR.
However, I checked that using 'testFile = new System.IO.FileInfo(Filename)'
does work - is there a specific reason you need to use GetFileInfo?
Steven
"KurtK" <KurtK@.discussions.microsoft.com> wrote in message
news:A5EEEC2D-A66A-46DC-BCC2-70978AD47508@.microsoft.com...
> Steven,
> I tried replacing
> testFile = My.Computer.FileSystem.GetFileInfo(Filename)
> with
> testFile = FileIO.FileSystem.GetFileInfo(Filename)
> which seemed to be the only way to get to the GetFileInfo method. However
> I
> still get the same exception.
> This is my first venture into Visual Studio/.net since we still develop
> under VB6 so I'm using the command line to compile my .vb code as was
> shown
> in an example CLR integration sample I found.
> Thanks,
> Kurt
>
> "Steven Hemingray [MSFT]" wrote:
>|||Thanks. That works with permission set to EXTERNAL. And to answer your
question, I don't have to use GetFileInfo if there is another way to test fo
r
a file's existence.
Thanks for your help.
-Kurt
"Steven Hemingray [MSFT]" wrote:

> My.Computer.FileSystem is very similar to FileIO.FileSystem so neither
> approach works under SQLCLR.
> However, I checked that using 'testFile = new System.IO.FileInfo(Filename)
'
> does work - is there a specific reason you need to use GetFileInfo?
> Steven
> "KurtK" <KurtK@.discussions.microsoft.com> wrote in message
> news:A5EEEC2D-A66A-46DC-BCC2-70978AD47508@.microsoft.com...
>
>|||"examnotes" <KurtK@.discussions.microsoft.com> wrote in
news:16E92B69-081D-415E-B168-1F2494ED0D2C@.microsoft.com:

> , I don't have to use GetFileInfo if there is another way to test for
> a file's existence.
>
System.IO.File.Exists(path)
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Closing all open connections/sessions

Hi all
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:

> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.

Close Enough?

I'm a VB6 / MS Access programmer trying to get up to speed in the world of
ASP.NET and SQL Server 2000. I'm on a fairly large web database project so
I'm trying to use VisualUML for management of the database schema. I expect
many generations of design as the requirements become defined in an iterate
process. Therefore a DB design tools like VisualUML is highly desirable.
When I worked through the WebMatrix tutorials I built a table in SQL Server
Enterprise Manager whose Create SQL looks like this (generated from SQL
Query Analyzer):
CREATE TABLE [wmt_Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
CONSTRAINT [PK_wmt_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I've been trying to use VisualUML to create the *same* *exact* thing.
Here's my first "maybe" try (from SQL Query Analyzer):
CREATE TABLE [wmt_Orders_2] (
[OrderID] [int] NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I noticed the lack of the CONSTRAINT on the primary key. Here are two more
tries, setting various attributes in VisualUML.
CREATE TABLE [wmt_Orders_3] (
[OrderID] [int] IDENTITY (100, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [wmt_Orders_4] (
[OrderID] [int] IDENTITY (100, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
UNIQUE NONCLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I haven't been able to figure out how to make VisualUML create a table whose
CREATE SQL is identical to that created "by hand." Are any of these "close
enough" to be completely useful? (If so I hope it's wmt_Orders_2 ! <g> )
The project I'm working on has scores of tables and quite a few 0-M:0M
(zero-Many:zero-Many) relationships so I really want to avoid future
problems caused by current ignorance. ;-)
Thanks, & please go easy on the SQL Server 2000 newbie! <g>
-- MarkHi Mark,
I learned you want to use VisualUML to create table DDL as below:
CREATE TABLE [wmt_Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
CONSTRAINT [PK_wmt_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
For this VisualUML question, you can post it into the VisualUML forum for
further support.
http://www.visualobject.com/forums/
http://www.visualuml.com/default.htm
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.

Client-Server App vs MS Access Data Project

Hello all,

I am looking at building a front end for a SQL Server 2005 database and am looking for an opinion on which way to proceed. My goals are high performance and security. Would it be better to build a desktop client-server application or create a MS Access Data Project to be used as a front end for my database?

ThanksYes, high performance and security comes with SQL Server, not MSAccess.
But client-server app is required when need 2 processes to be running each on a separate machine. Even the database is on a separate machine this doesn't mean a client-server app.
So you need just a desktop application (.Net, VB, Java) with an SQL Server, if it must not be Web accessible.

client/server application using Access as front end

Hi, i have this Access database with data entry forms. Now
i want to move the data to SQL server and keep the entry
system in Access. I tried to create a Access project which
is linked with SQL server. the access project is just like
a local application, i could make any changes on data and
even the design of the database, which is not i have in
mind. I could set up the startup form but it's really easy
to bypass it. What should i do, Access project or ADO
programming? please help!
"ming" <anonymous@.discussions.microsoft.com> wrote in message
news:2056f01c45955$9629f1c0$a301280a@.phx.gbl...
> Hi, i have this Access database with data entry forms. Now
> i want to move the data to SQL server and keep the entry
> system in Access. I tried to create a Access project which
> is linked with SQL server. the access project is just like
> a local application, i could make any changes on data and
> even the design of the database, which is not i have in
> mind. I could set up the startup form but it's really easy
> to bypass it. What should i do, Access project or ADO
> programming? please help!
Go for the programming! It is the only way to scale an Access front end/SQL
Server back end to more than 1 user <g>.
If you are going to dig into this programming, an excellent book on this
subject, loaded with code samples:
Microsoft Access Developer's Guide to SQL Server
by Mary Chipman, Andy Baron
ISBN: 0672319446
Steve
|||Access project is a quick (and easy?) way to create clients.
You can remove the standard Access menus from your your client so that users
cannot easily change your design. Access has about everything when it comes
to security
If you have some experience with access databases you can benefit from this
when you create an access application working with sql server. You can
create your forms by using wizards in access. For more sophisticated
functions you can write ADO calls from Access (in VBA) to SQL Server, for
instance to let the client call stored procedures on the sql server.
Regards
Tore
"ming" <anonymous@.discussions.microsoft.com> wrote in message
news:2056f01c45955$9629f1c0$a301280a@.phx.gbl...
> Hi, i have this Access database with data entry forms. Now
> i want to move the data to SQL server and keep the entry
> system in Access. I tried to create a Access project which
> is linked with SQL server. the access project is just like
> a local application, i could make any changes on data and
> even the design of the database, which is not i have in
> mind. I could set up the startup form but it's really easy
> to bypass it. What should i do, Access project or ADO
> programming? please help!

Client viewing KPI Browser and cube browser

Hi guys

I am just wondering, i know that within VS the developer has access to the KPI Browser and cube browser. How do i give access to the KPI Browser to a business user and allow them to play with the cube browser (without giving them VS ). I know that Reporting services can hook onto the cubes and create reports from that but that’s not quite the same, as I would have to create a report that looks like one that is already available in VS.

I read a little bit here http://office.microsoft.com/en-us/sharepointserver/HA101742051033.aspx which says that sharepoint might be able to access the KPI data and display it but if this is the case does it look the same as it does in VS? Also if i don’t have sharepoint this doesn't help me all that much.

Also i know that a business user has access to the report builder but i have not seen anything in it yet that looks like the cube browser. The closest it comes is the matrix view, which i wouldn't have thought was exactly the same.

Thanks for the help

Anthony

Have you considered end-user tools like Excel 2007 and Proclarity - these can connect to cube KPI's, and are better suited to business users? The latest versions of some other AS 2005 client tools also support KPI's.

Client tools

Hi I am new to MSDE. I see that you can use Access Data Projects to setup
MSDE but don't see some of the tools I have seen in SQL Server enterprise
manager. (Forgive me if I ask some really elementary questions for a while).
I want to set up tables with unique PK's that auto-generate values. I
learned today that the IDENTITY column is the way to do this, but how do I
set up one of these columns from Access?
Thanks
hi,
archer wrote:
> Hi I am new to MSDE. I see that you can use Access Data Projects to
> setup MSDE but don't see some of the tools I have seen in SQL Server
> enterprise manager. (Forgive me if I ask some really elementary
> questions for a while).
> I want to set up tables with unique PK's that auto-generate values. I
> learned today that the IDENTITY column is the way to do this, but how
> do I set up one of these columns from Access?
> Thanks
unfortunately I'm not an Access guy...
perhaps you have to ask in Acces/Office public NG
but you can define that via Transact-SQL Code like
CREATE TABLE dbo.someTable (
ID int NOT NULL IDENTITY(1,1) ,
other columns ...
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||In Access project, which is connected to the said database, open the said
table in design view, at bottom of table design view is table column
properties view, make "Identity" to "Yes".
"archer" <archer@.discussions.microsoft.com> wrote in message
news:B9F59449-222B-45ED-86ED-47113B76D7EE@.microsoft.com...
> Hi I am new to MSDE. I see that you can use Access Data Projects to setup
> MSDE but don't see some of the tools I have seen in SQL Server enterprise
> manager. (Forgive me if I ask some really elementary questions for a
while).
> I want to set up tables with unique PK's that auto-generate values. I
> learned today that the IDENTITY column is the way to do this, but how do I
> set up one of these columns from Access?
> Thanks
>

Client software

I have several developers that want access to my SQL server and I was
wondering instead of giving them Remote desktop access to access Enterprise
manager. I am going to install the SQL client tools locally on their PC.
My SQL server is behind a firewall, does anyone know what port needs to be
opened so I can see my SQL server in my list of available SQL servers.
Because I see all my SQL server on the local network, but not ones on other
networks.
Hope this makes some sense. Thanks for the help.
The following article answers questions related to firewall
ports:
INF: TCP Ports Needed for Communication to SQL Server
Through a Firewall
http://support.microsoft.com/?id=287932
However, what servers are listed in whatever tool drop downs
doesn't necessarily mean you have an issue with the firewall
ports. What servers are listed may not be a complete list
due to broadcast issues, network routers/subnet issues,
instances may not respond within the timeout period, etc.
The network architecture, configuration to wherever this SQL
Server box lives would be another factor.
You can always type in an IP address or a server name if a
server doesn't show up in the list.
-Sue
On Mon, 27 Feb 2006 12:38:02 -0500, "andespoint"
<andespoint@.nospam_yahoo.com> wrote:

>I have several developers that want access to my SQL server and I was
>wondering instead of giving them Remote desktop access to access Enterprise
>manager. I am going to install the SQL client tools locally on their PC.
>My SQL server is behind a firewall, does anyone know what port needs to be
>opened so I can see my SQL server in my list of available SQL servers.
>Because I see all my SQL server on the local network, but not ones on other
>networks.
>Hope this makes some sense. Thanks for the help.
>

Client software

I have several developers that want access to my SQL server and I was
wondering instead of giving them Remote desktop access to access Enterprise
manager. I am going to install the SQL client tools locally on their PC.
My SQL server is behind a firewall, does anyone know what port needs to be
opened so I can see my SQL server in my list of available SQL servers.
Because I see all my SQL server on the local network, but not ones on other
networks.
Hope this makes some sense. Thanks for the help.The following article answers questions related to firewall
ports:
INF: TCP Ports Needed for Communication to SQL Server
Through a Firewall
http://support.microsoft.com/?id=287932
However, what servers are listed in whatever tool drop downs
doesn't necessarily mean you have an issue with the firewall
ports. What servers are listed may not be a complete list
due to broadcast issues, network routers/subnet issues,
instances may not respond within the timeout period, etc.
The network architecture, configuration to wherever this SQL
Server box lives would be another factor.
You can always type in an IP address or a server name if a
server doesn't show up in the list.
-Sue
On Mon, 27 Feb 2006 12:38:02 -0500, "andespoint"
<andespoint@.nospam_yahoo.com> wrote:

>I have several developers that want access to my SQL server and I was
>wondering instead of giving them Remote desktop access to access Enterprise
>manager. I am going to install the SQL client tools locally on their PC.
>My SQL server is behind a firewall, does anyone know what port needs to be
>opened so I can see my SQL server in my list of available SQL servers.
>Because I see all my SQL server on the local network, but not ones on other
>networks.
>Hope this makes some sense. Thanks for the help.
>

Client Server Access

I have designed an application on VB Express 2005 and I want to run it on a PC on a LAN.

I have also installed SQL Server Express on another PC to act as a server.. but I get a login problem when I tried to run the application from the client PC.

Should I installed SQL Server Express on the client PC as well? Or there is another way to connect to it from the client PC?

By default SQL Server Express does not have remote connections enabled. You can do this using the following instructions: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx