Direct connection Windows CE 4.2 + SQL Server 2005

Hello,

I have a problem with SqlClient (WindowsCE 4.2 & .NET CF2.0 SP2). PDA is connects on PC via USB-cradle and ActiveSync 4.0. PC is in the LAN and SQL Server 2005 is in the LAN too. I can't connect to SQL Server 2005 (after connection.Open() I get exception "PlatformNotSupported"). When I use SQL Server 2000, it's O.K.

Can I connect on the SQL Server 2005 from Windows CE 4.2?

Thanks.

See this.

Direct Connection to Local SQL express gives error

I'm just starting out here.

I created a simple formview connected to a local file copy of the database by adding the database into the project. It worked fine.

However, I noticed that this copy of the database was not synch'ing with the database with the same name in SQL express. So I created a new connections string to access the SQL database directly out of SQL express. Its just a simple Select * from column. However, this gives an error. Why?

Server Error in '/NETCatMgr' Application.

---

The data types ntext and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.

This is the connection string

<

addname="CatalogMgrConnectionString"connectionString="Data Source=mymachine\sqlexpress;Initial Catalog=CatalogSQL;Integrated Security=True"providerName="System.Data.SqlClient" />

Doesn't make sense. How can the formview work fine with the SQL integrated into the project but not working with a connection string to the SQL Express proper?

The error seems to be a data operation error other than a connection error. It indicates your code was trying to compare nvarchar value and ntext using "=" operator. Please make sure you choose proper data for comparation. To manipulate text/ntext/image data in SQL Server, you should use specific functions:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp

|||Its not me -- the problem appears to be some kind of bug in the GridView. The database column is a type nText and the GridView doesn't seem to be able to write code for SQL databases with column types nText. It writes the code with a string comparison and then it crashes... I didn't write any code at all.

Different SQL Server Users on ASP.Net

Hi there,

When conecting to a MS SQL Server 2005, I could define in my connection string Windows Authentication.

What account it will be used? The one used by IIS (I still don't know the user ASP.net runs, if it is independent of IIS or not)?

And if I whant that some aspx pages have diferente acess rights? I need to use SQL Server Authentication with diferent users created in MS SQL Server right?

In ASP.net, what is best practice: Have one SQL account, and make users access rights in Business Logic, or have different SQL Accounts por the diferent access rights I whant in every page?

Sorry if this are basic questions, but In the find's I made, I saw very confusion ideas.

Thanks in advance, and sorry my english.

Alberto Ferreira

Hi Alberto,

ASP.NET runs under the ASPNET account (you can check under users to get more info about this account)

Unlike Client Server application. You dont have to follow the same logic in Web application.

You have to implement a Forms Authentication for authentication and implement GenericPrincipal containing IPrincipal and IIdentity for authorization.

In that you would create your Custom Screen Permission or Screen-Screen Action permission lookup table. You would find plenty of articles on this.

For connection pooling to take place the connection string has to be the same so in ASP.NET we reuse the same user name and password but we control the security at the application level than the database level which we previously used to do.

And never mind the english

Happy Programming

Anton

Different row delimiters in same connection manager

I have a situation where two CSV dmited types are read in. One file row type could be CRLF while another could be CR. Seeing how the only difference between these file types is the row delimiter, I would like to use one conn manager. Is it possible? Ideas?

Try this:
create a string variable Delimiter.
In Connection Manager window select your csv connection and look at its properties.
Open Expressions form and add an expression to the property RowDelimiter setting it to the variable User::Delimiter
Then you should be able to progamatically switch from one delimiter to another (toggling the variable value) before starting to load from the csv.
(i cannot try now this solution, take is as a hint)
|||

Unfortunately, this solution will not work. The RowDelimiter property is used only initially when no flat file connection columns are defined. After the columns get created the actual row delimiter is a column delimiter of the last column. You cannot do the same trick with the column delimiter because it is not expressionable.

Are you only concerned about the redundancy (with the two connection managers), or you had in mind more elegant data flow (with a multi flat file connection and one flat file source in a loop, perhaps)? I would like to find out more about your motivation to merge those two connection managers.

Thanks,

|||I am looking mainly to cut down the redundancy. There are 100+ inbound conns that need to support CRLF and CR returns, and it would end up being way too redundant to create a seperate manager for each return.|||

I can settle with just looking for CR for row delimiter. So, is there a way to maybe pad (or ignore) any LF's?

|||You can try that. The additional LF character would wrap up to the start of the first column of the next row. You should be able to clean it up using the Derived Column transform. However, I suspect it will make a headache with the incomplete last row, which would consist of a single LF character.

The solution I would go with is to have a Script Task before the Data Flow and preprocess files in it -- to unify the row delimiters.

Thanks,|||

Yah, I thought about doing this, however, won't this drain performance by having to essentially read/write the file twice (keeping in mind that some of these files can be rather large in size)? How would you handle the pre-processing in script?

Thanks.

|||

It will certainly have some performance impact. You should probably do some tests to verify how acceptable the cost is for the added manageability of your packages.

In the script, I would look for CR character and if the next one is not LF I would insert it. If you can be sure about the files with expected initial format ({CR}{LF}), you can skip those.

Thanks,

Different kind of connection issue

Was able to solve my connection issues with my local (test) machine. However, now I've got a much bigger issue on my intranet machine (W2K Server that acts as a domain controller).

When I use essentially the same set up for connecting as I did for the other machine (adjusted for machine name, etc), I get the following message:

Login failed for user '[username]'. The user is not associated with a trusted SQL Server connection.

I went into the SQL Server Express Config, and discovered under "SQL Server 2005 Services>SQL Server(SQLEXPRESS)>Properties" that this service is set up to login as the Local System built-in account. When I try to change this to Network Service, I get the following popup error message:

Cannot perform this operation on built-in accounts. [0x8007055b]

1) This happens regardless of wheter I stop the service and try to reset, apply and restart, or simply try to change it while it's running

2) I can find nothing on the error code above anywhere on the Web (Google came up EMPTY).

Anyone have any ideas about this one?

Seems like your SQL Server is setup for WIndows Authentication only. So if you want to use mixed (and therefore also SQL Server authentication) you have to configure the Server for this, in the administion console or with the registry settings:

--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097
--
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
--
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>

-URL-

If you don′t wanna use mixed authentication and only Windows Auth. you have to provide the "Integrated Authentication=True" in the connectionstring rather the UserId / Pwd.

HTH, Jens Suessmeyer.

|||

Changing the registry key worked.

One minor issue though - I tried the "Integrated Authentication=True" solution before I tried the registry key solution, and I got an error message saying that this was not a valid keyword or some such. Any idea why? Just Curious.

|||

Sotty mixed the two thing up, its "Trusted Connection=true" or Integrated Security=True, try these one, the connection string (just ti keep this in mind for you) can be found here:

http://www.connectionstrings.com

HTH; Jens Suessmeyer.

Different format in flat file for header and data

Hello all,

Is it possible to have two different formats for the header and the data in a flat file connection?

An example text file would look like this:

Col1,Col2,Col3

abcdefghi12345testtesttesttest

abcdeeeee12333setsetsetsetsets

where the header is delimited and the data is ragged right.

It looks like you should be able to accomplish this from the Flat File Connection Manager Editor interface, but perhaps having different delimiter dropdown boxes for the header and columns can only be used if you are using the Delimited format?

Thanks for any info you can provide!

Jessica

Do you even need the header?|||

No, but it would save a lot of time if we didn't have to type in hundreds of column names

I was more interested in knowing if this functionality is supposed to work and I'm missing some check box... or if it isn't supposed to work.

|||I don't think this is supported currently.

different database connection in clr stored proc

Hi,
Is it possible, in a CLR Stored Procedure, to connect to a different
database on the same server as the database under which the current
connection is for/running?
In other words, within a CLR Stored Proc running on database A, is it
possible to then connect to Database B, and insert records into tables in
Database B from data contained in tables in Database A, where both Database
A
and Database B reside on the same instance of SQL Server 2005 (same server)?
Thanks for any help or ideas on this.Thank you for posting in the MSDN newsgroup.
As for SQL 2005 CLR stored procedure, we can establish other connection to
external database(even external database server instance/remote) in the CLR
sp's code. e.g.
=======================
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PriceSum(out SqlString value)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
//access the same database's resource...
}
// access an external database on the same server/same instance
using (SqlConnection conn = new SqlConnection("Data
Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
Name FROM Production.ProductCategory", conn);
SqlDataReader reader = comm.ExecuteReader();
using (reader)
{
while (reader.Read())
{
sb.Append(")(" + reader.GetString(1));
}
}
}
value = new SqlString(sb.ToString());
}
}
========================
And as for such CLR assembly, you need to grant it "External Access"
permission(safe is insufficient).
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
Thanks for that Steven! I granted my assembly "External Access" as opposed
to "Safe", and it compiled fine. However, when I attempted to deploy it to m
y
SQL Server, i got the following error message:
CREATE ASSEMBLY for assembly 'MyCLRStoredProcTest' failed because assembly '
MyCLRStoredProcTest ' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS
.
The assembly is authorized when either of the following is true: the
database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the databas
e
has the TRUSTWORTHY database property on; or the assembly is signed with a
certificate or an asymmetric key that has a corresponding login with EXTERNA
L
ACCESS ASSEMBLY permission.
I'm guessing for best security, I should sign my assembly with a certificate
or an asymmetric key. Would you be able to suggest where to look to find out
what is involved with that? (and the other 'fix' options noted in my error
message) Thanks.
Best Regards,
Mark
"Steven Cheng[MSFT]" wrote:

> Thank you for posting in the MSDN newsgroup.
> As for SQL 2005 CLR stored procedure, we can establish other connection to
> external database(even external database server instance/remote) in the CL
R
> sp's code. e.g.
> =======================
> public class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void PriceSum(out SqlString value)
> {
> StringBuilder sb = new StringBuilder();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> //access the same database's resource...
>
> }
>
> // access an external database on the same server/same instance
> using (SqlConnection conn = new SqlConnection("Data
> Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True")
)
> {
> conn.Open();
> SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
> Name FROM Production.ProductCategory", conn);
> SqlDataReader reader = comm.ExecuteReader();
> using (reader)
> {
> while (reader.Read())
> {
> sb.Append(")(" + reader.GetString(1));
> }
> }
> }
> value = new SqlString(sb.ToString());
> }
> }
> ========================
> And as for such CLR assembly, you need to grant it "External Access"
> permission(safe is insufficient).
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>|||Hello Mark,
The error message indicate that you need to turn on the "TRUSTWORTHY"
property of the database which will add an "external access" assembly.
Anyway, you can follow the below web article's steps which include the
complete things we need to configure when install a custom assembly which
will require External_Access:
#CLR Stored Procedure Calling External Web Service
http://davidhayden.com/blog/dave/ar...04/25/2924.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks again Steven! Works beautifully. And thanks for the link to the
blog...very helpful.|||You're welcome Mark,
Have a nice day!
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Different answers on limits in MSDE

I've seen a number of reputable NG posts (including one from an MVP)
reffering to the 5 concurrent connection limit of MSDE.
However I found this MS site reffering to an 8 operations limit for
MSDE?
"The Microsoft=AE SQL Server=99 2000 workload governor is designed to
limit the performance of an instance of the database engine any time
more than eight operations are active at the same time."
http://msdn.microsoft.com/library/?u...ec/8_ar_sa2_0=
ciq.asp?frame=3Dtrue
I have also read in the NG's that you can use the Performance
Monitoring counter called SQLServer:General Statistics\User Connections
to see if you go over the threshold. I've been regularly jumping to 10
User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
says the following . . .
Concurrency violations since 2005-09-21 07:16:11.217
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
This indicates I've not went over any limits.
Can anyone give a definitive answer on this one?
Tostao
hi,
Tostao wrote:
> I've seen a number of reputable NG posts (including one from an MVP)
> reffering to the 5 concurrent connection limit of MSDE.
> However I found this MS site reffering to an 8 operations limit for
> MSDE?
> "The Microsoft SQL ServerT 2000 workload governor is designed to
> limit the performance of an instance of the database engine any time
> more than eight operations are active at the same time."
> http://msdn.microsoft.com/library/?u...asp?frame=true
>
> I have also read in the NG's that you can use the Performance
> Monitoring counter called SQLServer:General Statistics\User
> Connections
> to see if you go over the threshold. I've been regularly jumping to 10
> User Connections but when I run DBCC CONCURRENCYVIOLATION to check it
> says the following . . .
> Concurrency violations since 2005-09-21 07:16:11.217
> 1 2 3 4 5 6 7 8 9 10-100 >100
> 0 0 0 0 0 0 0 0 0 0 0
> Concurrency violations will be written to the SQL Server error log.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> This indicates I've not went over any limits.
> Can anyone give a definitive answer on this one?
you are right... the limit (not an actual limit, but a condition that makes
the built-in Query Governor kicks in) is not in 5 conncurrent connections
but 8 concurrent workloads, and actually only the ones listed in
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp )
you can stay in this number even with more active connections, as active
connections can be not all "working" at the same time but just sleeping in a
rounding way.. thus you do not exceed that "limit"...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the prompt response Andrea. I should add that the MVP who
gave the contradicitory info was from microsoft.public.access and not
this NG.
One final question. I'm a Sys Admin not a DBA, so rather than use the
DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
counter that I can use to see if I'm nearing the 8 concurrent
operations on my MSDE instance?
|||hi,
Tostao wrote:
> Thanks for the prompt response Andrea. I should add that the MVP who
> gave the contradicitory info was from microsoft.public.access and not
> this NG.
> One final question. I'm a Sys Admin not a DBA, so rather than use the
> DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor
> counter that I can use to see if I'm nearing the 8 concurrent
> operations on my MSDE instance?
not that I'm aware of..
and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
full blown SQL Server editions and oly returns
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and not the kind of info reported on Personal Edition and MSDE 2000...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||There will be a message in the SQL Server error log when the throttle kicks
in. A warning is not all that valuable because the concurrent activity
limit is a transient thing. You might have 20 users logged on and never hit
it and in other cases you might hit it with 8 users if they are all doing
long running queries simultaneously. Hitting the limit isn't catastrophic -
it just injects a delay into the activities so other than poor performance,
your users won't notice.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:42ffrcF1iql40U1@.individual.net...
> hi,
> Tostao wrote:
> not that I'm aware of..
> and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on
> full blown SQL Server editions and oly returns
> --
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and not the kind of info reported on Personal Edition and MSDE 2000...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger - the background behind my request is I'm working on a new
Credit Card payment system for my employer. The solution is coming from
a vendor who usually implement MSDE.
Although it's structurally the same as SQL 2000, I am not comfortable
using MSDE. We're going to install the Enterprise Manager tools even if
we are forced into MSDE. This means we're liable to pay for the full
SQL license.
So I'm basically looking at the MSDE limitations to put a case to the
vendor to make a special exception for us and support installing their
programme onto a full version MS-SQL