Access Database Datatypes, ADOX and VS2005 Question

I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:

If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject

If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text

Where CreateColumn looks like this:

Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean

'ADOX.CreateColumn- Called by Common.CreateNewTable

'CreateColumn creates a column described in the Table object so it assumes it is set.

'One method of setting it is to call Select Table after opening the database

If Not Me.ConnectionIsOpen Then

MsgBox("CreateColumn - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, cNoConn)

Return False

End If

Dim col As New ADOX.Column

col.Name = ColumnName

Try

col.Type = Datatype

Catch e As Exception

MsgBox("CreateColumb - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

col = Nothing

Return False

End Try

If Size <> 0 Then col.DefinedSize = Size

Try

Table.Columns.Append(ColumnName, Datatype)

Catch e As Exception

If Err.Number() <> 0 Then

MsgBox(Err.Source & "-->" & Err.Description, , "Error")

End If

MsgBox("CreateColumb - Failed to Append Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

Return False

End Try

col = Nothing

Return True

End Function

in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)

the 18 specifies the field width in the database. Yet no matter whether I use adWChar or

adVarWChar, Access always shows the field size to be 255.

Does anyone know why or how to fix that?

I can see that you created col variable, and assigned values to the fields of it. But, unless I'm missing something, I do not see how it is used in Table.Columns.Append call.

I guess that you should either use col instead of ColumnName, or add Size as a third parameter, and do not use col at all.

Access Control and Security?

Are there any books/article/forum posts out there that document the
best practices for SQL Server security?
For example, what account should create the database?
What account should create the schema?
In a given database should there be a login that is solely responsible
for DDL and a seperate account that only does DML and Queries?
What conventions are people using?
I'm kind of clueless about this and trying to figure out where to
start.
-ThxWhat version of SQL Server are you using? Best practices for SQLS 2000
do not necessarily apply to 2005, where there are expanded options not
available in earlier versions. A good starting point is always SQLS
Books Online, supplemented with a good searh engine :)
-mary
On Wed, 22 Aug 2007 05:58:08 -0700, kilik3000@.gmail.com wrote:
>Are there any books/article/forum posts out there that document the
>best practices for SQL Server security?
>For example, what account should create the database?
>What account should create the schema?
>In a given database should there be a login that is solely responsible
>for DDL and a seperate account that only does DML and Queries?
>What conventions are people using?
>I'm kind of clueless about this and trying to figure out where to
>start.
>-Thx|||On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
wrote:
> What version of SQL Server are you using? Best practices for SQLS 2000
> do not necessarily apply to 2005, where there are expanded options not
> available in earlier versions. A good starting point is always SQLS
> Books Online, supplemented with a good searh engine :)
> -mary
> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
> >Are there any books/article/forum posts out there that document the
> >best practices for SQL Server security?
> >For example, what account should create the database?
> >What account should create the schema?
> >In a given database should there be a login that is solely responsible
> >for DDL and a seperate account that only does DML and Queries?
> >What conventions are people using?
> >I'm kind of clueless about this and trying to figure out where to
> >start.
> >-Thx
SQL 2005.
-Thx|||That's a tough one. Basic information is in SQLS Books Online
(http://msdn2.microsoft.com/en-us/library/ms161948.aspx) but it
doesn't directly answer your questions, you have to infer the answers.
Also, your security architecture depends on your needs. DDL has gotten
a lot easier with user-schema separation in 2005 once you understand
it. Erland Sommarskog's web site has some good resources - see Giving
Permissions through Stored Procedures
(http://www.sommarskog.se/grantperm.html).
-mary
On Tue, 28 Aug 2007 20:01:27 -0000, kilik3000@.gmail.com wrote:
>On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
>wrote:
>> What version of SQL Server are you using? Best practices for SQLS 2000
>> do not necessarily apply to 2005, where there are expanded options not
>> available in earlier versions. A good starting point is always SQLS
>> Books Online, supplemented with a good searh engine :)
>> -mary
>> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
>> >Are there any books/article/forum posts out there that document the
>> >best practices for SQL Server security?
>> >For example, what account should create the database?
>> >What account should create the schema?
>> >In a given database should there be a login that is solely responsible
>> >for DDL and a seperate account that only does DML and Queries?
>> >What conventions are people using?
>> >I'm kind of clueless about this and trying to figure out where to
>> >start.
>> >-Thx
>SQL 2005.
>-Thx

Access a previous cell in the matrix

Hi,

It's possible to access a previous cell in the matrix, like excel?

For example:

A

B

C

1

A1

2*(A1)

B1 + A1

Thank you,

Bruno Giminiani

Can you expalain indetail

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
|||Do not think there are arrays you can use fixed length , delimited or
XML
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
--
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

About Web service or HTTP ENDPOINT

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Can you try http://yourmahine/HumanResources?wsd in your browser address bar and see if you can get the wsdl document in xml?

|||

No, I couldn't get the description for the web services, just a "page not found" like error.

In fact, when I run SQL to create HTTP EndPoint, I have to stop the web server first. after the execution of the SQL, I got an internal error from IIS if I restart the server. even though I restart the computer and web server, I still couldn't get the web services description.

I talked with some programmers, none of them use web services directly from sql server2005. and I notice there is few articles about it in the forum. Maybe microsoft just create this funtion for fun?

Anyway, Thanks a lot for you information. Hope get more help from you.

Chris

|||

Chris,

There's a small typo in Nan's post. The url is http://servername/HumanResources?wsdl

His post missed the 'L' at the end. Also, I'm assuming you are using the same user to retrieve the WSDL document as the user who created the endpoint. If not, you will need to grant the user permissions. "GRANT CONNECT on ENDPOINT::<name> to [user]".

HTH
Jimmy

|||

Sorry for the typo. The wsdl url should be http://servername/HumanResources?wsdl. If you can't get the wsdl xml document, please post the error code, 4XX or 5XX.

|||

I'm using IIS 5.1 on WindowsXP SP2. and I'm using the same user account for creation and access the WS.

the error code is HTTP 404 - File not found. Thanks.

Chris

|||

Ah, since you are running on a WinXP SP2 box, please make sure that IIS is not running. SQL 2005's native web serivces uses the OS HTTP kernel mode listener, which is introduced in WinXP SP2 and W2k3, but the IIS 5.1 on WinXP OS does not make use of it. This causes a port contention.

The alternative is to create the endpoint on a different port (ie. port 8080 or 88), such that the request does not end up going to IIS.

Jimmy

|||

I had the exact same problem. I found the answer at the following URL. I have excepted the piece that helped get me on the right path.

http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

4.1.18 Remote Connections to a SOAP/HTTP Endpoint May Fail Using Integrated Authentication

Remote connections to a SOAP/HTTP endpoint may fail when the SQL Server service account is a domain account and authentication for the endpoint is set to INTEGRATED, even though local connections succeed. The problem can occur when the Service Principal Name (SPN) is registered to the local system account. You can work around the issue by either registering an SPN for the SQL Server Service account or specifying NTLM authentication for the endpoint. For more information about registering an SPN, see "Registering Kerberos Service Principal Names by Using Http.sys" in SQL Server Books Online.

Then in SQL Books I found the following;

To support mutual authentication under Kerberos, an instance of SQL Server 2005 must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by a specific instance of SQL Server 2005 are determined by the type of service account it has been configured under. If SQL Server 2005 is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server 2005 is running under a domain user account, SPNs must be registered under the domain user name.

Using SetSPN.exe

To enable association of an SPN to the account that the instance of SQL Server 2005 is running on, use the Windows SetSPN.exe support tool. The tool adds the SPN for the computer name on which the instance of SQL Server 2005 is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.

For example, if the SetSPN.exe tool is executed from instance of SQL Server 2005 running on MyComputer, the following two SPNs are associated with the account the instance of SQL Server is running under and must be added to directory:

HTTP/MyComputer;

HTTP/MyComputer.fully.qualified.domain.name.com

If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:

setspn –A http/MySQLHost MyDomain\MySQLAccount

setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount

About Web service or HTTP ENDPOINT

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Can you try http://yourmahine/HumanResources?wsd in your browser address bar and see if you can get the wsdl document in xml?

|||

No, I couldn't get the description for the web services, just a "page not found" like error.

In fact, when I run SQL to create HTTP EndPoint, I have to stop the web server first. after the execution of the SQL, I got an internal error from IIS if I restart the server. even though I restart the computer and web server, I still couldn't get the web services description.

I talked with some programmers, none of them use web services directly from sql server2005. and I notice there is few articles about it in the forum. Maybe microsoft just create this funtion for fun?

Anyway, Thanks a lot for you information. Hope get more help from you.

Chris

|||

Chris,

There's a small typo in Nan's post. The url is http://servername/HumanResources?wsdl

His post missed the 'L' at the end. Also, I'm assuming you are using the same user to retrieve the WSDL document as the user who created the endpoint. If not, you will need to grant the user permissions. "GRANT CONNECT on ENDPOINT::<name> to [user]".

HTH
Jimmy

|||

Sorry for the typo. The wsdl url should be http://servername/HumanResources?wsdl. If you can't get the wsdl xml document, please post the error code, 4XX or 5XX.

|||

I'm using IIS 5.1 on WindowsXP SP2. and I'm using the same user account for creation and access the WS.

the error code is HTTP 404 - File not found. Thanks.

Chris

|||

Ah, since you are running on a WinXP SP2 box, please make sure that IIS is not running. SQL 2005's native web serivces uses the OS HTTP kernel mode listener, which is introduced in WinXP SP2 and W2k3, but the IIS 5.1 on WinXP OS does not make use of it. This causes a port contention.

The alternative is to create the endpoint on a different port (ie. port 8080 or 88), such that the request does not end up going to IIS.

Jimmy

|||

I had the exact same problem. I found the answer at the following URL. I have excepted the piece that helped get me on the right path.

http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

4.1.18 Remote Connections to a SOAP/HTTP Endpoint May Fail Using Integrated Authentication

Remote connections to a SOAP/HTTP endpoint may fail when the SQL Server service account is a domain account and authentication for the endpoint is set to INTEGRATED, even though local connections succeed. The problem can occur when the Service Principal Name (SPN) is registered to the local system account. You can work around the issue by either registering an SPN for the SQL Server Service account or specifying NTLM authentication for the endpoint. For more information about registering an SPN, see "Registering Kerberos Service Principal Names by Using Http.sys" in SQL Server Books Online.

Then in SQL Books I found the following;

To support mutual authentication under Kerberos, an instance of SQL Server 2005 must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by a specific instance of SQL Server 2005 are determined by the type of service account it has been configured under. If SQL Server 2005 is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server 2005 is running under a domain user account, SPNs must be registered under the domain user name.

Using SetSPN.exe

To enable association of an SPN to the account that the instance of SQL Server 2005 is running on, use the Windows SetSPN.exe support tool. The tool adds the SPN for the computer name on which the instance of SQL Server 2005 is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.

For example, if the SetSPN.exe tool is executed from instance of SQL Server 2005 running on MyComputer, the following two SPNs are associated with the account the instance of SQL Server is running under and must be added to directory:

HTTP/MyComputer;

HTTP/MyComputer.fully.qualified.domain.name.com

If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:

setspn –A http/MySQLHost MyDomain\MySQLAccount

setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount

About SQL field Type

Hi Friends
I have question.
I connected oracle and MS access tables(via Delphi7).
I wanna get type of field in indicated table. For example if it is
integer or boolean e.t.c

Please tell me the SQL function or whatever that retrievs type of field.
Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ulan yrysbaev (ulasir@.yahoo.com) writes:
> Hi Friends
> I have question.
> I connected oracle and MS access tables(via Delphi7).
> I wanna get type of field in indicated table. For example if it is
> integer or boolean e.t.c
> Please tell me the SQL function or whatever that retrievs type of field.

Since you use different platforms, you should be using the
INFORMATION_SCHEMA views, which are part of the ANSI standard. But
I have no idea whether any of Access and Oracle supports them.

You are probably better off asking in comp.databases.ms-access and
comp.databases.oracle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

about Semiadditive

About Semiadditive the olap server just intruduce its concept,i don't know how to setting Semiadditive,for example ,some mesures in mesures group just want to be aggregated along geography dimension,not time dimension,how to do it?

Semiaddtive measures have special aggregation behavior only along the time dimension and are additive along all other dimensions. I am not sure I understand your specific requirements. Generally speaking, if you want special aggregation rules along non-time dimensions you can use unary operators or custom rollups.

About HTTP Endpoint/Web services

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Did you find your answer? I've been through this a little now and would be happy to help.

If, in Visual Studio, you add a web reference, you'll be asked a location in the form of a URI. In the example that you show, the URI would be something like http://localhost/HumanResources?wsdl, which, when entered, should result in the finding of services and a proposed reference name of localhost ... which you can name to something else. The default namespace that will be created in your VS project will be something like <NameOfYourProject>.localhost ... or whatever you renamed it to be.

If you use an Object Viewer to look at the contents of the namespace, you'll see a couple of clients generated ... one something like HRService, and the other HRServiceWse. From here, I would suggest you follow the link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=536560&SiteID=1&mode=1, since this picks up from there.

Tom

about assembly

How can i use assembly in sqlreport, and give me an example.
--
å?ªæ'ä¸?æ'ç'å?ªå'ï¼?æ'å'æ'ä¼?ç¦»ä½ è¶?æ?¥è¶?è¿?ï¼?You may want to read these articles with samples:
* http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
* http://blogs.sqlxml.org/bryantlikes/articles/839.aspx
In addition, you may also want to read an MSDN article that is referenced in
the articles above:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"lonelywolf" <lonelywolf@.discussions.microsoft.com> wrote in message
news:B4BFD0CA-7B45-452C-BF8C-15FD4301FFE9@.microsoft.com...
> How can i use assembly in sqlreport, and give me an example.
> --
> '?,''!