sql permissions: application

Showing posts with label application. Show all posts

Thursday, March 29, 2012

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.
sql

Default Mobile Database Application

Dear All,

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

|||

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

|||

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

|||

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

|||

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

|||

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

...

Another section:

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull

|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.sql

default isolation level

Hi all,
the default isolation level in sql 2k is read committed..
say incase i am connecting to the DB through a jdbc driver and the
application has a default isolation level of repeatable read...
when a transaction is opened from the application end...wat would be the
isolation level...
ll the db override or the application takes up priority..
any help on the context is appreciated..
thnks
anuApplication should override the default level.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C46A6429-6D51-40BF-A4EE-9584224F3625@.microsoft.com...
> Hi all,
> the default isolation level in sql 2k is read committed..
> say incase i am connecting to the DB through a jdbc driver and the
> application has a default isolation level of repeatable read...
> when a transaction is opened from the application end...wat would be the
> isolation level...
> ll the db override or the application takes up priority..
> any help on the context is appreciated..
> thnks
> anu

Tuesday, March 27, 2012

Default installation

I have built an application that uses SQLExpress. When I build the setup with a new setup Project I include in the prerequisites SQLExpress so that if the target PC has no SQL server installed the setup sequence will automatically install an instance.

The Serup Sequence installes SQL Server with default Instance name and settings. I need to make the setup package to install the SQL instance with a specific name and some other setting. These settings would be

/qb ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=pass INSTANCENAME=instname DISABLENETWORKPROTOCOLS=0

if i run it from Command Line.

I searched a lot on my own and didnt find any way of predefining these setting. Tnx in advance

The instance name is hard coded into the manifests used to generate the SQL Express prerequisite and there is no way to dynamically change it. You would need to go in and alter the command line used to install SQL Express that is encoded in the manifest.

You can find more about how the prerequisties in VS work by searching MSDN for "generic bootstrapper". I've also posted instruction on how to build your own prerequisite for SQL Express SP2 (VS only has RTM) that may be instructive. My sample also installs to the default named instance, but again, you can make any changes you want. Check out this blog post for more information.

Finally, you can choose to write your own install wrapper and take full control over the installation process. There is a white paper that discusses how to embed SQL Express installation in a custom wrapper here.

Mike

Default installation

/qb ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=pass INSTANCENAME=instname DISABLENETWORKPROTOCOLS=0

if i run it from Command Line.

I searched a lot on my own and didnt find any way of predefining these setting. Tnx in advance

Mike

sql

default delimiter change from comma to semicolon

Is this possible? I now that i can use custom application to do this, but is
it possible to change semicolon as a default delimiter on server.
Thank's in advance.."AG, NLB d.d." wrote:
> Is this possible? I now that i can use custom application to do this, but is
> it possible to change semicolon as a default delimiter on server.
This probably has to do with the default regional setting on the server. For
US formats comma is used as list seperator, but for most EU countires comma
is used as the decimal seperator so they use semicolon as list seperator.
Otherwise you could get into trouble with a list like this:
5,67,34,56,32,09
Is it 5;67;34;56;32;09 or is it 5,67;34,56;32,09 or any other combination.
In regional settings you can change what the seperator character should be.
Perhaps this link will help you:
http://www.adopenstatic.com/faq/systemdateformat.asp|||i'am sorry to mislead you.
I have a problem with exporting data in CSV format, because data includes
commas, so i have to use some other delimeter like semicolon as default
delimiter on server.
Thanks.
"Glenn F. Henriksen" wrote:
> "AG, NLB d.d." wrote:
> > Is this possible? I now that i can use custom application to do this, but is
> > it possible to change semicolon as a default delimiter on server.
> This probably has to do with the default regional setting on the server. For
> US formats comma is used as list seperator, but for most EU countires comma
> is used as the decimal seperator so they use semicolon as list seperator.
> Otherwise you could get into trouble with a list like this:
> 5,67,34,56,32,09
> Is it 5;67;34;56;32;09 or is it 5,67;34,56;32,09 or any other combination.
> In regional settings you can change what the seperator character should be.
> Perhaps this link will help you:
> http://www.adopenstatic.com/faq/systemdateformat.asp
>|||"AG, NLB d.d." wrote:
> I have a problem with exporting data in CSV format, because data includes
> commas, so i have to use some other delimeter like semicolon as default
> delimiter on server.
Okay, I haven't tried this, but I think you could change the list seperator
value in the control panel->regional settings. But you have to do it for the
user that Reporting services is running under. You may have to change the
default user's settings. Make the change on your own account and then try to
set it to the default system value.
Another option is to make sure that your data in enclosed in quotes, like
this:
"35,54","this, is a test", "example.com, example.net"
--
Glenn F. Henriksen
http://www.henriksen.no/

Thursday, March 22, 2012

Default cursor in SQL 2000

Hello,

I am writing an application which I would like to use a server cursor on. I have noticed that when I try to access a table that I have created using a server cursor with my app, I have problems getting the information. It says that the cursor doesn't support bookmarks. But, when I access the Employees table on the Northwind database with the same settings, everything is fine. Is there some sort of option I'm missing with my tables? Some default cursor or something? Is there a way to tell what cursors are defined with the Northwind DB? Any help would be great, thanks!Check if your table has a primary key|||that was it, thank you very much!

Originally posted by kukuk
Check if your table has a primary key|||Would something similar also go for the dynamic cursor? I setup the primary keys which works great for Keyset type. But I get the old 'Dataset does not support bookmarks' when trying a dynamic cursor on the same dataset. I've tried both MSDASQL and SQLOLEDB providers, both gave errors.

Originally posted by Thread77
that was it, thank you very much!sql

Default charset in sqlserver 2000? / jtds

Hi,

I am writing to a text column in my SQL Server 2000 database. The text
comes from a web form in my java web application, where the character
encoding is ISO-8859-1. (I have no control over the charset, my app is
a plugin inside another app.)
Characters such as (ascii 128) and '(ascii 146) are inserted into
the db as '?'.

I'm connecting using the free jtds driver, and I'm not specifying any
details about charsets in my usage of the driver.

Can anyone tell me what the default charset in sqlserver 2000 is?
Should I be specifying this charset when using my driver?
Thanks.downlode@.gmail.com wrote:
> Hi,
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as (ascii 128) and '(ascii 146) are inserted into
> the db as '?'.
> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?
> Should I be specifying this charset when using my driver?
> Thanks.

You probably want to use ntext instead of text, nvarchar instead of
varchar, etc.|||(downlode@.gmail.com) writes:
> I am writing to a text column in my SQL Server 2000 database. The text
> comes from a web form in my java web application, where the character
> encoding is ISO-8859-1. (I have no control over the charset, my app is
> a plugin inside another app.)
> Characters such as ?(ascii 128) and '(ascii 146) are inserted into
> the db as '?'.

Hm, in iso-8859-1, the slots 128-159 not graphic characters. In Windows-
1252, Microsoft's extension of 8859-1, some of them are indeed graphic.

> I'm connecting using the free jtds driver, and I'm not specifying any
> details about charsets in my usage of the driver.
> Can anyone tell me what the default charset in sqlserver 2000 is?

No, because this depends on the regional settings of the machine. For
instance, if I install SQL Server on my machine, and do not make any
selection, I will get Finnish_Swedish_CI_AS, which implies code page
1252. People in Poland are likely to get Polish_CI_AS, which implies
code page 1250. And that's only the default. This can be overridden
at installation. And then the collation can be set independently by
column.

So start doing

SELECT serverproperty('Collation') -- Server default collation.
SELECT databasepropertyex('db', 'Collation') -- Database default

And then use sp_help to determine the coilations of the columns you
are working with. If you don't know which code page a certain collation
has, there is a function Collationproperty() for this.

If the columns are of different code pages, you will have to use
Unicode somewhere on the way, and as Trevor said, ntext nvarchar are
probably better options.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
sorry for the late follow up to this.
My database has the same collation throughout -
SQL_Latin1_General_CP1_CI_AS
The columns share this collation.
Even when I use a preparedStatement, ensuring that the outgoing text is
treated as Unicode by the free jtds driver, I get the same problems.

I am stumped by this one.

If I change my text column to an ntext column, will this affect the
existing entries?
Thanks,
Mike|||(downlode@.gmail.com) writes:
> sorry for the late follow up to this.
> My database has the same collation throughout -
> SQL_Latin1_General_CP1_CI_AS
> The columns share this collation.
> Even when I use a preparedStatement, ensuring that the outgoing text is
> treated as Unicode by the free jtds driver, I get the same problems.
> I am stumped by this one.

Since SQL_Latin1_General_CP1_CI_AS is share code page with iso-8859-1,
it's indeed a little funny. But as I noted in my previous post, the
characters you have problem with are not in iso-8859-1 - these code
points are control characters to 8859-1. In Windows Latin-1 they are
indeed printable characters.

My guess is that the free jtds takes a strict definiton of what is
8859-1. But I don't it, so you should inquire in a forum for that driver.

> If I change my text column to an ntext column, will this affect the
> existing entries?

No.

--
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.mspxsql

Wednesday, March 21, 2012

Decryption within an application

I need to encrypt one column of data in a single table and I pretty much
have all the operations figured out, including maintaining both the
encrpyted data and a one way hash for searches. I have a view which
decrypts the data properly when the symmetric key has been opened (and
obviously returns null when the key is not open).
I want the view to return the decrypted data only when the user is accessing
the database from a single application. This application maintains a single
database connection per session. My thought was to open the key when the
database connection is established by the application and close it when the
application exits, thereby granting access only through the application. Is
that an acceptable practice?
If I do that, should I protect the key with a password that is then compiled
in the application so that I can open the key? This means that every
installation will have a key protected by the same password. Or is there a
better way to do that?
Thanks for any help."Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
>I need to encrypt one column of data in a single table and I pretty much
>have all the operations figured out, including maintaining both the
>encrpyted data and a one way hash for searches. I have a view which
>decrypts the data properly when the symmetric key has been opened (and
>obviously returns null when the key is not open).
> I want the view to return the decrypted data only when the user is
> accessing the database from a single application. This application
> maintains a single database connection per session. My thought was to
> open the key when the database connection is established by the
> application and close it when the application exits, thereby granting
> access only through the application. Is that an acceptable practice?
> If I do that, should I protect the key with a password that is then
> compiled in the application so that I can open the key? This means that
> every installation will have a key protected by the same password. Or is
> there a better way to do that?
> Thanks for any help.
Well, when the key is opened it's specific to a session. So you could have
several sessions opening up the same key simultaneously and I wouldn't think
you'd encounter any problems. Of course you will probably want to do some
thorough testing to be sure, and also make sure you don't take a performance
hit there. I wouldn't recommend storing the key hard-coded in your
application. How about using the Automatic Key Management feature of SQL
2005? The only real downside to it is that all sysadmins can then decrypt
your data (if that's a concern for you - it is for some folks).|||I'm not neccesarily opposed to the sysadmin being able to decrypt the data,
but I don't want any other user outside of the application to have access to
the data. So what I can't figure out (even with SS key management) is how
to open the key only when connecting from the application, unless I compile
a password into the code.
Any thoughts on that would be helpful.
Thanks.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Well, when the key is opened it's specific to a session. So you could
> have several sessions opening up the same key simultaneously and I
> wouldn't think you'd encounter any problems. Of course you will probably
> want to do some thorough testing to be sure, and also make sure you don't
> take a performance hit there. I wouldn't recommend storing the key
> hard-coded in your application. How about using the Automatic Key
> Management feature of SQL 2005? The only real downside to it is that all
> sysadmins can then decrypt your data (if that's a concern for you - it is
> for some folks).
>|||With automatic key management you should be able to connect to the
application and open the symmetric keys without a password. You can use
GRANT to grant permissions to users on your keys, certificates, etc. Here's
an article with some samples that demonstrate encryption/decryption without
passwords, thanks to automatic key management:
[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
You might also want to look into the DecryptByKeyAutoAsymKey and
DecryptByKeyAutoCert functions that combine the DecryptBy... functions with
OPEN SYMMETRIC KEY automatically.
"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
> I'm not neccesarily opposed to the sysadmin being able to decrypt the
> data, but I don't want any other user outside of the application to have
> access to the data. So what I can't figure out (even with SS key
> management) is how to open the key only when connecting from the
> application, unless I compile a password into the code.
> Any thoughts on that would be helpful.
> Thanks.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||Thanks so much for your help, but I must be dense. If I grant permission to
the users or use automatic key management, then it seems to me that the data
can be encrypted outside of my application by a non-sa user. If I only want
the application to display the unencrypted data, I can't see how this
automatic approach works.
That is why I took the approach of having the application open the key.
Sort of like the old application-role security.
But I would love to find a better way.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Od0T8%23amHHA.4772@.TK2MSFTNGP05.phx.gbl...
> With automatic key management you should be able to connect to the
> application and open the symmetric keys without a password. You can use
> GRANT to grant permissions to users on your keys, certificates, etc.
> Here's an article with some samples that demonstrate encryption/decryption
> without passwords, thanks to automatic key management:
> http://www.sqlservercentral.com/col...ion.asp

> You might also want to look into the DecryptByKeyAutoAsymKey and
> DecryptByKeyAutoCert functions that combine the DecryptBy... functions
> with OPEN SYMMETRIC KEY automatically.
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23C1qbs$mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Thanks so much for your help, but I must be dense. If I grant permission
> to the users or use automatic key management, then it seems to me that the
> data can be encrypted outside of my application by a non-sa user. If I
> only want the application to display the unencrypted data, I can't see how
> this automatic approach works.
Anyone who has the username and password used by the application to log into
the database would have the ability to decrypt the encrypted data.
Alternatively, if a password is stored in the application, anyone with a hex
editor could decrypt the encrypted data outside of the application. The
only way I can think of to force decryption only through the application
would be to encrypt only in the application. But then you take on the
responsibility of encryption key management yourself. I don't know of any
magic bullet to ensure that data encrypted using SQL Server can only be
accessed via a specific front-end application interface.

> That is why I took the approach of having the application open the key.
> Sort of like the old application-role security.
Anyone with a hex editor could conceivably locate a password stored in an
application and use it to decrypt data. Linking it to a specific Windows
login puts the burden of encryption key management back on the operating
system. That's basically the main difference; do you want to manage your
own passwords, or do you want to let Windows and SQL Server manage your
passwords?

> But I would love to find a better way.
Biometrics?

Monday, March 19, 2012

Decreased performance from using blobs

I'm trying to store files such as pdfs in my SQL Server as blobs for a particular .NET application and I'm noticing a rather large performance hit when compared to if the pdf was simply stored in the web server's file system.

I have a pdf of 7MB. If this is on a web server it takes roughly 9 seconds to download. When I store the same pdf in my database and then write it onto the page using the code below it takes roughly 40 seconds to display. Can anyone help me to improve this performance difference? Should I not be using this method to store large files?

Dim PdfCol As Integer = 0 ' the column # of the BLOB field
Dim dr As SqlDataReader = cmd.ExecuteReader()

If dr.Read() Then
Dim mypdf(dr.GetBytes(PdfCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(PdfCol, 0, mypdf, 0, mypdf.Length)
End If

Response.Clear()
Response.Buffer = True
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(mypdf)
Response.Flush()
Response.Close()* Turn off output cache, so that the first data can be sent immediatly.
* Do NOT load the whole thing into memory and send it out. Instead grab little chunks and thend them. Start with 8k, then take like 64k a trip.
Currently you only start sending data once the hole 7mb are loaded from the database, and this is ridiculously inefficient.|||By output cache, do you mean the output in the Page/UC directive?
Also, could you show me an example of grabbing chunks and sending them out? I'm not real experienced with using bytes.

I tried the following but I get a dialogue window that says "The file is damaged and can not be repaired."

If dr.Read() Then
Dim intBufferSize As Integer = 64000
Dim longStartIndex As Long = 0
Dim longRetVal As Long
Dim b(intBufferSize) As Byte

'Read through the first chunk of the document
longRetVal = dr.GetBytes(PdfCol, 0, b, 0, intBufferSize)

'Reset the start index.
longStartIndex = 0

Response.Clear()
Response.Buffer = True
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"

'Continue reading and writing while there are bytes beyond the size of the buffer.
While longRetVal = intBufferSize
Response.BinaryWrite(b)
Response.Flush()

'Reposition the start index to the end of the last buffer and fill the buffer.
longStartIndex += intBufferSize
longRetVal = dr.GetBytes(PdfCol, longStartIndex, b, 0, intBufferSize)
End While

'Write the remaining buffer.
Response.BinaryWrite(b)
Response.Flush()
Response.Close()
End If|||Ok, I've managed to write my blob in chunks to a specified path on my hard drive using the code from: http://support.microsoft.com/default.aspx?kbid=317043
But whenever I try to use a Response.BinaryWrite so that the pdf is loaded onto the web page with ContentType set to "application/pdf" or "application/octet-stream" I get the same error that the file is damaged and cannot be repaired.|||Thona you are awesome! I forgot to mention that earlier :)

I did finally get this to work. Apparently if my buffer size is too large the file gets corrupted. I set my buffer size to 24000 and everything seems to work ok, but if the buffer size is 29000 then the file gets corrupted. Anyone know why this is? Can bytes only be a certain size?

Friday, March 9, 2012

Decision tree in MS SQL Server

Hi,

Can we represent the Decision Tree in a programatically way in an .NET application? I understand that the outcome of a Decision Tree model can be integrated into an .NET application but not sure if we can also visualize it. Does MS SQL Server support any API to render such a tree?

Thanks a lot!

In a WinForm application, you could embed the viewer control (the same control used in BI Developer studio to render decision trees). A sample for this is available on our site, sqlserverdatamining.com. The direct link is http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=1362 and requires creating a free account.

SQL Server also includes (in the Analysis Services samples) the complete source code for a set of web viewer controls which work for a few algorithms, including Decision Trees rendering. The sample is called Data Mining Web Controls

Hope this helps

|||Thank you.

Decision tree in MS SQL Server

Hi,

Thanks a lot!

Hope this helps

|||Thank you.

Decision Statement

Hello all,
I need to generate a result based on 2 fields:
1. RelationshipID &
2. MaritalStatusID
The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ''''
I greatly appreciate any help in architecting the code in
this regard.
Thansk again.
Joshitry this...
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:

> Hello all,
> I need to generate a result based on 2 fields:
> 1. RelationshipID &
> 2. MaritalStatusID
> The above fields are not embedded in the application as a
> mandatory check box so some of the values maybe empty. I
> need to get a list of all the members who have a
> RelationshipID = 2 and if it is NULL look in
> MaritalStatusID = 1 and if that is NULL, display the
> result anyways. I do not need to show the values from
> these fields as they are a part of my parameters (filters)
> in the query. I am not a programmer and wanted to know how
> would I write this in a IF THEN statement in SQL?
> select Distinct pip.MemberID
> from tblMemberInsurancePlan pip
> Inner Join tblMember m on m.MemberID = pip.MemberID
> Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
> pip.InsurancePlanID
> Left Join ctblFamilyRelationship fr on fr.RelationshipID =
> pip.RelationToSubscriber
> Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
> m.MaritalStatusID
> -- Filtering for Current insurance only
> where (pip.InsurancePlanState in ('C') and
> pip.InsurancePlanOrdinal = 1)
> -- Filtering for Dependents only
> and pip.MemberID <> pip.InsurancePlanSubscriberID
> -- Filtering for 'CHILD' relationship to the subscriber
> (and fr.RelationshipID = 2 or
> -- Filter for Relationship = 2 or MaritalStatusID = 1
> and ''''
>
> I greatly appreciate any help in architecting the code in
> this regard.
> Thansk again.
> Joshi
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:

>I need to generate a result based on 2 fields:
>1. RelationshipID &
>2. MaritalStatusID
>The above fields are not embedded in the application as a
>mandatory check box so some of the values maybe empty. I
>need to get a list of all the members who have a
>RelationshipID = 2 and if it is NULL look in
>MaritalStatusID = 1 and if that is NULL, display the
>result anyways. I do not need to show the values from
>these fields as they are a part of my parameters (filters)
>in the query. I am not a programmer and wanted to know how
>would I write this in a IF THEN statement in SQL?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

decimal separator

Im experimenting with the reportViewer control in a .net 2.0 winform application, and I'm having a little problem: I can find no way to change the decimal separator(or the thousand separator) of a column of numbers in a table. I have tried all sorts of Format(), formatNumber() and FormatCurrency(). I've also tried to explicitly set the CurrentCulture of the application to a new cultureinfo object, and I've tried to change the culture of the datasets that the reports are base on.

Also, when calling the FormatCurrency() the output allways shows $ even though I find no trace of using USD anywhere in my system.

Any constructive hints out there?

Than you in advance,

Snorre

In ReportViewer control properties you can change Language property and that will change format for number, date, currency ...
|||

Thanks for your reply. Although I can't find the Language property anywhere: In the property page, in the intellisence, on the LocalReport object or anywhere else. Are you using the winform version of the control? Are there different versions of the control? I'm using the one shipping with visual studio.

Thanks.

Snorre

|||

I finally figured this out.

There was a Language property as suggested, not on the controller, but on the textbox(and table/matrix/chart) element in the report. What confused me though was that this box was overridden when using something like:

=format(sum(Fields!myfield.Value),"#,###.00")

To correct this I had to put the format string in the property field instead, and remove the formatting from the formula. Hope this helps someone...

Snorre

Saturday, February 25, 2012

debugging symbols for the SQLCE dlls while developing native applications

Hi,

I am developing a native C++ application using SQLCE (.NET is not a current option). Am having a problem while using multiple accessors for multiple blobs in a table. After I read the data for the first blob & try to read the data for the second one, I get an error in one of the dlls (something about using heap data that was freed). Where can I get symbol data so that I can use something like Windbg to resolve my problem.

It appears debug symbols are not available, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312588&SiteID=1

Friday, February 24, 2012

debugging SQL stored procedure

I have a simple windows application which uses two stored procedures at the backend to fetch the data and display it in the grid of UI

I wanted to debug these stored procedures line by line like using run time storage dump for various variable and statements used in the procedures as we do it in Visual studio using functional keys etc.

Any way to do this using SQL server 2000 query analyzer? The stored procedures contains nearly five hundred lines of code in sql.

Early reply is much appreciated.

Thanks!

In query analyzer, show object browser. then select your stored proc, and right click.

Select debug.

For more, look at this article http://www.15seconds.com/Issue/050106.htm

|||

Thanks for your help. This debug option is not available in sql2k5. I do not see any debug option available for sql2k5 when I right click the stored proc. Is there any option to do in sql2k5?

Thanks!

Santhosh

Thursday, March 29, 2012

Default Mobile Database Application

Default Mobile Database Application

Default Mobile Database Application

Default Mobile Database Application

Default Mobile Database Application

Default Mobile Database Application

default isolation level

Tuesday, March 27, 2012

Default installation

Default installation

default delimiter change from comma to semicolon

Thursday, March 22, 2012

Default cursor in SQL 2000

Default charset in sqlserver 2000? / jtds

Wednesday, March 21, 2012

Decryption within an application

Monday, March 19, 2012

Decreased performance from using blobs

Friday, March 9, 2012

Decision tree in MS SQL Server

Decision tree in MS SQL Server

Decision Statement

decimal separator

Saturday, February 25, 2012

debugging symbols for the SQLCE dlls while developing native applications

Friday, February 24, 2012

debugging SQL stored procedure

sql permissions

Blog Archive

About Me