Application starts before SQL is running.

Another option is to have a vbscript with a timer to wait 5 mins or so on
boot, THEN fire off the executable for the app.
TheSQLGuru
President
Indicium Resources, Inc.
"Graham." <me@.privacy.com> wrote in message
news:4686ab60$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Very basic question.
> OS Windows XP pro
> On bootup, an application that starts automatically in the start-up
> folder, starts before SQL server is running
> This only happens on slower hardware, It must be a common problem, what's
> the usual solution?
> --
> Graham.
> %Profound_observation%
>
you can also check on whether the SQL server service is running
before your application starts trying to connect to the SQL server.
Change the Application to wait and try for 4-5 times(or based on the
customer experience) to connect to the MSSQL service.

Application Roles with IIS

I wish to use SQL Server 2000 Application Security with an ASP (Classic) app
lication.
When I run the ASP app and look at SQL Server Current Activity Process Info
, the column Application is showing "Internet Information Services". How do
I align SQL Server and IIS so that Application Security can be utilised?
regards
Greg
PS I have already set the Application Name for this site in IIS.If you are using ASP to connect to SQL Server then the application is IIS
so that is what is diplayed. What application would you prefer it to
display?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Application roles

I an having problems setting up an application role:
-- create the app role
exec sp_addapprole 'MyAppRole', ''approlepassword'
-- grant it ALL priv
grant all to MyAppRole
-- create new new user
exec sp_addlogin 'User1', 'password', 'MyDatabase'
use MyDatabase
sp_grantdbaccess 'User1'
-- new user login and executes
exec sp_setapprole 'MyAppRole', ''approlepassword'
now User1 tries to do anyithing and they have no access to any objects, why
is this, I have granted all priv to the app role. I must be missing
something basic here.
Can anyone point me in the right direction.
Thanks,
Tony"Tony" <tonyng2@.spacecommand.net> wrote in message
news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I an having problems setting up an application role:
> -- create the app role
> exec sp_addapprole 'MyAppRole', ''approlepassword'
> -- grant it ALL priv
> grant all to MyAppRole
> -- create new new user
> exec sp_addlogin 'User1', 'password', 'MyDatabase'
> use MyDatabase
> sp_grantdbaccess 'User1'
> -- new user login and executes
> exec sp_setapprole 'MyAppRole', ''approlepassword'
> now User1 tries to do anyithing and they have no access to any objects,

---

why

quote:

---

> is this, I have granted all priv to the app role. I must be missing
> something basic here.
> Can anyone point me in the right direction.
> Thanks,
> Tony
>
>

---

GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
object permissions, you need to grant individual permissions for each
object:
grant execute on proc1 to MyAppRole
grant update on table1 to MyAppRole
etc.
You can make this easier by using built-in roles like
db_datareader/db_datawriter, or by cutting, pasting, reviewing and executing
the output of a query like this:
select 'grant execute on ' + routine_name + ' to MyAppRole'
from information_schema.routines
where routine_type = 'procedure'
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

---

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

---

executing

quote:

---

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

---

--
But, I dynamically add/remove objects all the time. I surly don't want to
have to reset ll the permissions each time.
I guess using application roles is not going to cut if for me, since this
would be way to much of a maint. headache. Guess I will just have to assign
the users to built in roles unless someone knows an easier way to maintain
the app role when new objects are being added/removed at any time without
requiring the app role to be changed.
Would be really nice if I could grant the app role as another role such as
dbadmin.
Thanks,
Tony|||Okay figure out I can assign app role to be a member of db_owner.
But, when a user is set to the app role, all objects created by this user
get the app role owner, and not dbo as they should if they have db_owner.
Why is this?
Example:
sp_addlogin User1, password, db1
use db1
sp_grantdbaccess User1
sp_addapprole MyAppRole, rolepassword
sp_addrolemember db_owner, MyAppRole
Now user logins in as User1
sp_setapprole MyAppRole, rolepassword
Create table Test(Name varchar(50))
Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
as is should (well I think it should)
Why is this?
Tony
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

---

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

---

executing

quote:

---

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

---

|||A db_owner role member needs to explicitly specify owner 'dbo' in order to
create dbo-owned objects.
CREATE TABLE dbo.Test(Name varchar(50))
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony" <tonyng2@.spacecommand.net> wrote in message
news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Okay figure out I can assign app role to be a member of db_owner.
> But, when a user is set to the app role, all objects created by this user
> get the app role owner, and not dbo as they should if they have db_owner.
> Why is this?
> Example:
> sp_addlogin User1, password, db1
> use db1
> sp_grantdbaccess User1
> sp_addapprole MyAppRole, rolepassword
> sp_addrolemember db_owner, MyAppRole
> Now user logins in as User1
> sp_setapprole MyAppRole, rolepassword
> Create table Test(Name varchar(50))
> Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
> as is should (well I think it should)
> Why is this?
> Tony
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:401ff029$1_2@.news.bluewin.ch...
objects,[QUOTE]
> executing
>

---

|||Hmm, I 'm used to it doing that automatically if you ARE a db_owner.
But I can handle that.
Thanks,
Tony
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23pkSCPy6DHA.2432@.TK2MSFTNGP10.phx.gbl...

quote:

---

> A db_owner role member needs to explicitly specify owner 'dbo' in order to
> create dbo-owned objects.
> CREATE TABLE dbo.Test(Name varchar(50))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...
user[QUOTE]
db_owner.[QUOTE]
dbo.Test[QUOTE]
> objects,
>

---

Application role to access xp_cmdshell

I have an Access app linked to a SQL server db. This app uses
xp_cmdshell in some stored procedures and it works fine. As long as
the user is administrator... I'd like to set up an application role
that can execute xp_cmdshell and access my db but I don't know how to
do it as xp_cmdshell is in the Master db while everything else is in
my own db. I'm also unsure whether to call sp_setapprole from the sp's
or from the Access app.
Can somebody please give me some code examples or direct me to a good
site?
/CarlAs long as the ownership chain is unbroken, direct permissions on
xp_cmdshell are not needed. This necessitates that your user procs be owned
by 'dbo', your user database be owned by 'sa' and cross-database chaining
(intoduced in SQL 2000 SP3) be enabled. Example script below.
For security reasons, it is important that your user proc be coded in such a
way that only the intended command can be executed. Also, you should enable
cross-database chaining only if you fully trust users that have permissions
to create dbo-owned objects. See Cross-database chaining in the SQL 2000
Books Online for more information.
You will also need to allow non-sysadmin users to execute xp_cmdshell. You
can do this from Enterprise Manager under Management-->SQL Server
Agent-->Job System. Uncheck the 'Only users with sysadmin privileges...'
check box and specify the Windows account you want to use as the OS security
context for non-sysadmin users. This account should have the minimal
permissions need to perform the needed tasks.

> I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
You'll need to execute sp_setapprole directly from your application. From
the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_sa-sz_6tt1.htm">
The sp_setapprole stored procedure can be executed only by direct
Transact-SQL statements; it cannot be executed within another stored
procedure or from within a user-defined transaction.
</Excerpt>
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
-- for SQL 2000 SP3+
EXEC sp_dboption 'MyDatabase', 'db chaining', true
GO
CREATE PROC dbo.MyXpCmdShellProc
AS
EXEC master..xp_cmdshell 'MyCommand'
GO
GRANT EXEC ON dbo.MyXpCmdShellProc TO MyAppRole
GO
EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'
EXEC dbo.MyXpCmdShellProc
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402170439.d8b1453@.posting.google.com...
> I have an Access app linked to a SQL server db. This app uses
> xp_cmdshell in some stored procedures and it works fine. As long as
> the user is administrator... I'd like to set up an application role
> that can execute xp_cmdshell and access my db but I don't know how to
> do it as xp_cmdshell is in the Master db while everything else is in
> my own db. I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
> Can somebody please give me some code examples or direct me to a good
> site?
> /Carl|||Thanks Dan for your excellent explanation. But unfortunately I'm still
at SQL 7... Any other options?
Carl
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<#kQj98V9DHA.3176@.TK
2MSFTNGP11.phx.gbl>...
> As long as the ownership chain is unbroken, direct permissions on
> xp_cmdshell are not needed. This necessitates that your user procs be own
ed
> by 'dbo', your user database be owned by 'sa' and cross-database chaining
> (intoduced in SQL 2000 SP3) be enabled. Example script below.
>|||The technique will work with SQL 7 too. The only difference is that
cross-database chaining is not configurable under SQL 7 and pre-SQL2000 SP3
(it is always on). Just remember to run the 'db chaining' option on in your
user database if you later upgrade to SQL 2000 SP3+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402172316.6a6cf8b@.posting.google.com...
> Thanks Dan for your excellent explanation. But unfortunately I'm still
> at SQL 7... Any other options?
> Carl
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<#kQj98V9DHA.3176@.TK2MSFTNGP11.phx.gbl>...
owned
chaining

Application needs replication

Hi
We have an MS Access application which runs on the server but some laptop
users need the app to work offline. Our solution has been to use Access
replication which automatically syncs data (both ways) when laptop is
connected to the network. We are looking to rewrite the app to vb.net/sql
server. My question is how does replication work in sql server specially in
context of a vb.net front end i.e. what sort of coding/configuration we are
we looking at?
Thanks
RegardsWell since you will have disconnected users; you will need to have a local
instance of the SQL Server installed on the computer. Then you can SQL
Server 2005 Merge Replication Topology to do what yaa needed. Read up on it
at ..
http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially in
> context of a vb.net front end i.e. what sort of coding/configuration we are
> we looking at?
> Thanks
> Regards
>
>|||Am I right to believe I need to code as if my app is connected to the same
single sql server and various sql server installs would automatically take
care of the sync between themselves without intervention by app when laptops
are connected to the network?
Thanks
Regards
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> Well since you will have disconnected users; you will need to have a local
> instance of the SQL Server installed on the computer. Then you can SQL
> Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> it
> at ..
> http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John" wrote:
>> Hi
>> We have an MS Access application which runs on the server but some laptop
>> users need the app to work offline. Our solution has been to use Access
>> replication which automatically syncs data (both ways) when laptop is
>> connected to the network. We are looking to rewrite the app to vb.net/sql
>> server. My question is how does replication work in sql server specially
>> in
>> context of a vb.net front end i.e. what sort of coding/configuration we
>> are
>> we looking at?
>> Thanks
>> Regards
>>|||In a sense yes, but actually no. What I mean is, since you will have a
local install of SQL Server on each laptop; you can code the application to
connect to localhost and no coding required for developer for connecting
parts. But in fact; these are all different instances of SQL Server on
different computer, everyone will have different names.
I haven't coded for replication enviornment, but few challanges that face
you developers is handling merge conflicts. Three possible cases can happen:
1) Record is updated, but no longer exists.
2) Insert a record; and causes a duplicate primary key.
3) Updating same records in two locations, both valid but are in conflict.
Replication is usually timed event; so onces the server is on the network,
the local server will talk to the distributor and the publitioning database
to update information as needed. I hope this gives you some ideas. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Am I right to believe I need to code as if my app is connected to the same
> single sql server and various sql server installs would automatically take
> care of the sync between themselves without intervention by app when laptops
> are connected to the network?
> Thanks
> Regards
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> > Well since you will have disconnected users; you will need to have a local
> > instance of the SQL Server installed on the computer. Then you can SQL
> > Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> > it
> > at ..
> > http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> >
> > Thanks!
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "John" wrote:
> >
> >> Hi
> >>
> >> We have an MS Access application which runs on the server but some laptop
> >> users need the app to work offline. Our solution has been to use Access
> >> replication which automatically syncs data (both ways) when laptop is
> >> connected to the network. We are looking to rewrite the app to vb.net/sql
> >> server. My question is how does replication work in sql server specially
> >> in
> >> context of a vb.net front end i.e. what sort of coding/configuration we
> >> are
> >> we looking at?
> >>
> >> Thanks
> >>
> >> Regards
> >>
> >>
> >>
>
>|||John,
You have at least two possiblilties, use a replication of the SQL server
using by instance SQL Client, or make your application in a way that it uses
DataSets from the needed information.
The latter is very easy to do and the way I would go in your situation.
Cor|||And you don't need SQL Server installed on the client(s). Another
alternative is SQL Server Compact Edition on the clients. It can also act as
a Subscriber to a SQL Server Publisher. Note that a SQL Server Publisher
must be Workgroup or better--SQL Express does not support this
functionality. In addition, you might consider using the new ADO.NET 3.5
Sync Services due out with Orcas (before the end of the year). In this case
you don't need SQL Server to act as a Publisher.
I discuss most of these options in my Ebook on SQL CE.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||Hi Cor
Thanks. How does one keep data persistent on client side when using
datasets, bearing in mind that clients (laptops) will be disconnected from
the network/sql server often but would need access to sql server data
offline? If you can point to some reading that would be great.
Many Thanks
Regards
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:14F13D20-64C8-46CB-BEF1-CC700A4A6474@.microsoft.com...
> John,
> You have at least two possiblilties, use a replication of the SQL server
> using by instance SQL Client, or make your application in a way that it
> uses DataSets from the needed information.
> The latter is very easy to do and the way I would go in your situation.
> Cor
>|||Take a look at Sybase SQL Anywhere. It's replication capabilities are truly
amazing.
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||John,
The nature from a dataset is to work ofline, it is build for that.
When you do an update, there is looked if there has been a change (this is
called optimistic concurrency). The way as the data is organised makes that
the chanch for that is low or high.
By instance as you are adding and subtrackting values from tablerows,
instead of adding mutation rows, then you can probably forget it.
Cor|||Ah, I expect he means you can serialize the DataTable(s) to an XML file or
somesuch.
Again, the SQL Server Compact Edition (which supports several kinds of
replication) is a better option.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
> John,
> The nature from a dataset is to work ofline, it is build for that.
> When you do an update, there is looked if there has been a change (this is
> called optimistic concurrency). The way as the data is organised makes
> that the chanch for that is low or high.
> By instance as you are adding and subtrackting values from tablerows,
> instead of adding mutation rows, then you can probably forget it.
> Cor|||Thanks Bill.
Regards
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:OYv3bNdCIHA.6012@.TK2MSFTNGP03.phx.gbl...
> Ah, I expect he means you can serialize the DataTable(s) to an XML file
> or somesuch.
> Again, the SQL Server Compact Edition (which supports several kinds of
> replication) is a better option.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
> news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
>> John,
>> The nature from a dataset is to work ofline, it is build for that.
>> When you do an update, there is looked if there has been a change (this
>> is called optimistic concurrency). The way as the data is organised makes
>> that the chanch for that is low or high.
>> By instance as you are adding and subtrackting values from tablerows,
>> instead of adding mutation rows, then you can probably forget it.
>> Cor
>|||Bill,
I disagree this with you, for extra database you need replication, with a
dataset you just needs common updates. The logical problems stays exactly
the same.
The serialization is completely automaticaly done by Dataset.ReadXML(...)
and Dataset.WriteXML(...), you write it as if this is a problem.
Cor|||Cor, you missed the point. Replication in this case is used for data
sharing--not just data persistence.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:C859F647-3DB1-404A-AC9E-10213DDFA7AC@.microsoft.com...
> Bill,
> I disagree this with you, for extra database you need replication, with a
> dataset you just needs common updates. The logical problems stays exactly
> the same.
> The serialization is completely automaticaly done by Dataset.ReadXML(...)
> and Dataset.WriteXML(...), you write it as if this is a problem.
> Cor|||> Cor, you missed the point. Replication in this case is used for data
> sharing--not just data persistence.
I did not miss that Bill, however why would you do that if there are now
simple methods possible in ADONET and not only replication. Using a dataset
is in my idea much easier to handle conflicts by your own program.
Cor

Application hangs on SqlCeConnection.Dispose()

Hi,

I'm having the problem that my application hangs on SqlCeConnection.Dispose() when I'm closing the app.

public class DatabaseManager : IDisposable
{
private DbConnection connection = null;

// Singleton
private DatabaseManager()
{
this.CreateConnection();
}

~DatabaseManager()
{
this.Dispose();
}

public void Dispose()
{
if (this.connection != null)
{
this.connection.Dispose();
}
}
// ...
}

This only happens when I'm calling Application.Exit(); and Dispose is called through the destructor of the DatabaseManager class. When I'm disposing the connection during normal work the call works as intended. BTW I'm using SQL Server Mobile 3.0.5214.0 on a PPC 2003 AKU2 (Symbol PPT8846 industrial device).

Thanks for support,
Klaus

Your implementation is incorrect. There's some special handling involved if called from finalizer:

// Dispose(bool disposing) executes in two distinct scenarios.
// If disposing equals true, the method has been called directly
// or indirectly by a user's code. Managed and unmanaged resources
// can be disposed.
// If disposing equals false, the method has been called by the
// runtime from inside the finalizer and you should not reference
// other objects. Only unmanaged resources can be disposed.
private void Dispose(bool disposing)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemIDisposableClassDisposeTopic.asp

Generally you don't need finalizer in this class as you don’t have any native resources.

APP_DATA directory

If I already have SQL2005 installed, can I create SQL express databases for distribution in my app, or do I need to install SQLExpress to run side-by-side?

I actually had SQLExpress originally but upsized it to the full version. Now I want to be able to create portable DBs with my application.

And if this is possible, how do I go about creating the DB?

Thanks!

You can refer to the upgraded SQL2005 instance just as you did to SQL Express. One thing to note is that if you have set "User Instance" attribute to true in your connection string, the connection to SQL2005 may fail with error message saying "User Instance can only be used with SQL Express..." (not exact, but something like this)|||

Thanks for the answer.. that helps on that error that I have received...

I think I might have been a bit unclear though...

On my local dev machine, I have the full SQL2005 version, but I want to create an application that can be distributed with a SQLExpress database in its app_data directory. This application will not require that the user attaches the .mdf through sql2005, but only that they have SQLexpress running as the DB will live locally in the APP_DATA directory.

Question is: Can I create the DB through my version and then just drop the .mdf in the APP_DATA directory and will it be compatible with SQLExpress?

Also, can I use a SQLExpress connection string / DB on my system during development without having to attach the DB to my sqlserver instance?

Sorry if my questions seem ignorant, but I am just trying to wrap my hands around the whole thing.

Thanks!

|||

NevermindIf you do not want to attache the database file at run time, you have to use a database in your SQL Server. That's because what your application needs is not only a database file, but also needs a SQL Server instance. So if you want to switch between SQL Express and SQL 2005 (means different SQL instances) without attaching the database file at run time, you have to change your connection to SQL, and move database as well. There are some options you can choose to move database:

How to move database using detach/attach:

http://msdn2.microsoft.com/en-us/library/ms187858(d=ide).aspx

And copy database with backup/restore:

http://msdn2.microsoft.com/en-us/library/ms190436(d=ide).aspx

This article shows a good torturial for changing SQL connections in web application:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Anyways, I recommend attaching the database file at run time--then you just need to change connection to SQL without moving database

app.config and connectionstrings with several databases

Hello all

I need to know the best way to work with databases (Sql Server) in my app.config.

I have an VB application that binds data from a SQL server (All Customers install these application and retrieve data via VPN). I use two databases for each customer. The first DB has some commun tables/data and the second one is its ID (Until now I setup manually the connections because we don't have more that 10 Customers but we need improve that for next year). I know that a connection string is created in the app.config file .

I don't know if it is possible from this way or if there is another way to do it... but the goal is:

1. Customer is logged in the application -User Name, ID, Password

2. Customer retrieve a database with commun data and his own database (that is his ID). (From the app.config?) How can I setup my app.config in order that every customer retrieve his database automatically when it is logged?

Thaks, I don't know if it's clear but I didn't find another way to explain my trouble

Hi

I hope I am following you.

If there are many customers I don't think it is a good way to store all there personal info(their ids) in app.config.

You can put all info into database and retrive them when the customer is logged in.

I don't what do you mean by saying own database , you might need to better design your database since it is not a good idea to add a new database(or even a new table) when you create a new customer.

App. Can't find database after a RESTORE

Hi,
My application in another server can't find the SQLServer database
after a restore. The name of the DB is exactly the same! The application say
s
the the Target Database does not match destination database. My question is:
Is it possible that the reason comes from the database even if the name is
exacty the same?
Do I have to restart the database server?
Thanks!
JoeYou don't have to 'restart' the server after a restore.
Can you connect to the database using Enterprise Mangler or Query Analyzer?
Please try connecting with EM and/or QA and post the entire error message.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Barb" <Barb@.discussions.microsoft.com> wrote in message
news:95F0ED39-6083-4586-936D-9BC582C314AC@.microsoft.com...
> Hi,
> My application in another server can't find the SQLServer database
> after a restore. The name of the DB is exactly the same! The application
> says
> the the Target Database does not match destination database. My question
> is:
> Is it possible that the reason comes from the database even if the name is
> exacty the same?
> Do I have to restart the database server?
> Thanks!
> Joe
>
>

App server unable to connect to SQL Server 2000 developer edition on localhost.

Anyone encounter differences between SQL2K EE and SQL2K Dev Editon?

FAILS
--
Machine_A [App Server] <-- JDBC --> Machine_A\SQL2K_Dev_Ed..db

Error: "com.inet.tds.SQLException: Connection refused"

When app server on Machine_A attempts to connect to database on
Machine_A the conenction is refused.

SUCCEEDS
---
Machine_A [App Server] <-- JDBC --> Machine_B\SQL2K_EE..db

When app server on Machine_A attempts to connect to database on
Machine_B the conenction succeeds and the app server runs normally.

JDBC driver: com.inet.tds.TdsDriver

jdbc:inetdae7:locahost:1433

I don't believe the app server is exhausting the 10-connection max on
SQL2K developer edition (there is a 10-connection limit imposed on the
developer edition, correct?)

LBlbunet@.hotmail.com (LB) wrote in message news:<35e1fa55.0404062027.3635b8b9@.posting.google.com>...
> Anyone encounter differences between SQL2K EE and SQL2K Dev Editon?
> FAILS
> --
> Machine_A [App Server] <-- JDBC --> Machine_A\SQL2K_Dev_Ed..db
> Error: "com.inet.tds.SQLException: Connection refused"
> When app server on Machine_A attempts to connect to database on
> Machine_A the conenction is refused.
>
> SUCCEEDS
> ---
> Machine_A [App Server] <-- JDBC --> Machine_B\SQL2K_EE..db
>
> When app server on Machine_A attempts to connect to database on
> Machine_B the conenction succeeds and the app server runs normally.
> JDBC driver: com.inet.tds.TdsDriver
> jdbc:inetdae7:locahost:1433
> I don't believe the app server is exhausting the 10-connection max on
> SQL2K developer edition (there is a 10-connection limit imposed on the
> developer edition, correct?)
> LB

There is no connection limit in Developer Edition - it's Enterprise
Edition with a different licence.

I don't know exactly what "Connection refused" means, but you might
want to check the SQL Server logs for failed logins, and also test
your username/password combination with osql.exe or whatever, just to
make sure it works correctly. You could also check the authentication
modes on the two servers (Windows or Mixed), to see if they are the
same - username/password works only in Mixed mode, if that's what
you're using.

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0404070120.39c31661@.posting.google.com>...

... [See first post for text that appeared here]

> There is no connection limit in Developer Edition - it's Enterprise
> Edition with a different licence.
> I don't know exactly what "Connection refused" means, but you might
> want to check the SQL Server logs for failed logins, and also test
> your username/password combination with osql.exe or whatever, just to
> make sure it works correctly. You could also check the authentication
> modes on the two servers (Windows or Mixed), to see if they are the
> same - username/password works only in Mixed mode, if that's what
> you're using.
> Simon

Thanks for the feedback.

LB

app role

We can use sp_setapprole to enable application role. How to disable the app
role?Once activated, it stays activated for the duration of the connection.
See the sp_setapprole topic in SQL Server Books Online for more
information.
--Mary
On Mon, 21 Nov 2005 11:19:10 -0800, Roy
<Roy@.discussions.microsoft.com> wrote:

>We can use sp_setapprole to enable application role. How to disable the app
>role?|||Roy (Roy@.discussions.microsoft.com) writes:
> We can use sp_setapprole to enable application role. How to disable the
> app role?
In SQL 2000, you cannot unset it.
In SQL 2005, you can use sp_unsetapprole for the task. You need to request
a coookie from sp_setapprole to do it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

App receiving "Options" message from Service Broker

I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )

When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.

But when I run the same app against the local machine SQL Server, I receive the message "Options".

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

JFoushee wrote:

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

From http://msdn2.microsoft.com/en-us/library/ms181122.aspx:

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS ON

CONCAT_NULL_YIELDS_NULL ON

QUOTED_IDENTIFIER ON

NUMERIC_ROUNDABORT OFF

ARITHABORT ON|||

On my local machine,
sp_configure 'user options' returns 0 for config_value .

So I ran the enumeration for the options above...

ANSI_NULLS ON 32
ANSI_PADDING ON 16
ANSI_WARNINGS ON 8
CONCAT_NULL_YIELDS_NULL ON 4096
QUOTED_IDENTIFIER ON 256
NUMERIC_ROUNDABORT OFF
ARITHABORT ON 64

4474

And then I ran this against my local machine...

sp_configure 'user options', 4474

and the program suddenly works!

|||

The recommended way of fixing this is to actualy run the SET options from the user connection. This way the app sets it's needed settings overwritting whatever defaults are in the database/instance.

HTH,
~ Remus

|||

Agreed, I was using the example to explain this wasn't the app's fault.
It still manages to work against the remote server with no intervention. (Why?)

Thanks for your assistance.

|||

JFoushee wrote:

It still manages to work against the remote server with no intervention. (Why?)

sp_configure changes the global instance level settings. I'd guess that the remote server and local server are different at the database level settings (ALTER DATABASE ... SET ...)

HTH,
~ Remus

|||

I ran a compare between the two databases.

The local one, in 80-compatibility, needed the various SET options.

The remote one, in 90-compatibility, needed nothing extra to work.

When I changed the local one to 90-compatibility, the program magically worked, with or without the SET options.

(I downloaded the pubs database from MS for the example and attached to both local and remote.)

App login using credentials from SQL table

I'm trying to create a login screen that will verify a user's username and password from within a SQL table. If the credentials are found in the table, they should be sent to a Main Menu screen. If the credentials are not found, I will throw a message on the screen. Does anyone have code to accomplish this? I am at a total standstill.

Thanks,
BoydMCheck out this tutorial to use .NET security features:

http://www.dotnetjunkies.com/quickstart/aspplus/doc/authandauth.aspx

You can expand on that stuff to use the database or if you want to go ad hoc and not use the .NET security features you could always just have them submit their username and password from the form and compare them to values in the database and whether or not they match depends on where they go. Hope this helps.

- Jesse Williams|||Thanks Jesse. I've read a few articles regarding forms-based authentication, but they all seem to use 1 username and password to compare against (For example, if the username entered equals 'john@.abc.com' and the password equals 'password', then process accordingly). In my situation, I want to compare what the user enters against a table of usernames and passwords to verify they have entered valid credentials.

Any ideas?

Thanks,
BoydM|||You could create your username and password tables in the database and write a stored procedure to execute from your application code. Also if you want to base their privlidges I'd add a userRole (Admin, user, etc.) column in too. The store procedure would have three parameters: username, password, and return value (return the role of the user). Here's what I would envision the procedure to look like:

CREATE PROCEDURE GetUserRole
( @.username varchar(25),
 @.password varchar(10),
 @.userRole varchar(10) OUTPUT
)
AS
BEGIN
IF EXISTS (SELECT userName, password FROM UsersTable WHERE userName = @.userName AND password = @.password)
BEGIN
SELECT @.userRole = userRole FROM UsersTable WHERE userName = @.userName AND password = @.password
RETURN @.userRole
END
ELSE
RETURN -1
END

This is off the top of my head, so I don't know if it's going to run properly. Just configure your SqlCommands in your code to have parameters sending and receiving values and test it out. Anymore help, please post here. Hope this helps. Good luck.

- Jesse Williams|||Thanks. The more I research, the more I'm beginning to realize that I need to use a stored procedure to query the SQL table. I'm fairly new to stored procedures, and even newer to asp.net. A couple questions:
Where do I place the 'CREATE PROCEDURE' code in my aspx.vb file?
Secondly, how do I code my parameters for sending and receiving values?

Thanks again,
BoydM (phellow phan)|||You actually dont place the stored procedure code in your code file. What database are you using? If you are using SQL Server and Enterprise Manager you can use that or Query Analyzer (I prefer Query Analyzer). To create the procedure you would write the code I provided you and execute to create the procedure. This will create the procedure, no need to worry about the create proc stuff anymore. You will execute the proc from your code with the parameters. Here's some code to help you a little more:

'Import SQL Namespace; Required
Import System.Data
Import System.Data.SqlClient
Dim strSQL As String
Dim strConString As String
'Get Connection String From Web.config
strConString = System.Configuration.ConfigurationSettings.AppSettings("sqlCon")
'SQL Statement to execute proc
strSQL = "EXEC GetUserRole"
Dim conSQL As New SqlConnection(strConString)
Dim cmdSqlCommand as New SqlCommand(strSQL, conSQL)
'Add Parameters too command
cmdSqlCommand.Parameters.Add("@.userName", txtUserName.text)
cmdSqlCommand.Parameters.Add("@.password", txtPassword.text)
cmdSqlCommand.Parameters.Add("@.userRole", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output
cmdSqlCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue
'Execute the command
cmdSqlCommand.ExecuteNonQuery()
'Retrieve Return Value
. . .
'Do the Rest of your code here
. . .

Again, this is off the top of my head, so some stuff may need fixing. Hope this helps and good luck.

- Jesse Williams

App Keeps Freezing on me..

I'm using SQL 7 on MS 2000 Server and I'm running this
application that once or twice a week likes to freeze up
on me.. All the rest of the time everything is working
fine so I go in close the app then run it again and it
works fine for another week or so?
What causes the freezing ?
What application are you running?
How is it connecting to SQL Server?
Is there locking issues going on somewhere?
More information would certainly be helpful. What you have given us in
like saying, Hi, I have a car that runs for about a week and then dies on
me. Why does it die on me...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2e0501c47e40$f1a84500$a501280a@.phx.gbl...
> I'm using SQL 7 on MS 2000 Server and I'm running this
> application that once or twice a week likes to freeze up
> on me.. All the rest of the time everything is working
> fine so I go in close the app then run it again and it
> works fine for another week or so?
> What causes the freezing ?
>

App Keeps Freezing on me..

I'm using SQL 7 on MS 2000 Server and I'm running this
application that once or twice a week likes to freeze up
on me.. All the rest of the time everything is working
fine so I go in close the app then run it again and it
works fine for another week or so?
What causes the freezing 'What application are you running?
How is it connecting to SQL Server?
Is there locking issues going on somewhere?
More information would certainly be helpful. What you have given us in
like saying, Hi, I have a car that runs for about a week and then dies on
me. Why does it die on me...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2e0501c47e40$f1a84500$a501280a@.phx.gbl...
> I'm using SQL 7 on MS 2000 Server and I'm running this
> application that once or twice a week likes to freeze up
> on me.. All the rest of the time everything is working
> fine so I go in close the app then run it again and it
> works fine for another week or so?
> What causes the freezing '
>

App Keeps Freezing on me..

I'm using SQL 7 on MS 2000 Server and I'm running this
application that once or twice a week likes to freeze up
on me.. All the rest of the time everything is working
fine so I go in close the app then run it again and it
works fine for another week or so?
What causes the freezing 'What application are you running?
How is it connecting to SQL Server?
Is there locking issues going on somewhere?
More information would certainly be helpful. What you have given us in
like saying, Hi, I have a car that runs for about a week and then dies on
me. Why does it die on me...
HTH
Rick Sawtell
MCT, MCSD, MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2e0501c47e40$f1a84500$a501280a@.phx.gbl...
> I'm using SQL 7 on MS 2000 Server and I'm running this
> application that once or twice a week likes to freeze up
> on me.. All the rest of the time everything is working
> fine so I go in close the app then run it again and it
> works fine for another week or so?
> What causes the freezing '
>

Apostrophes and Dynamic SQL

I am trying to Ad-hoc query from a user input(internal app), where the user
enters in a name just as "Al's Game Crazy". Each time I do this, the
Apostrophe gets in the way. I have tried using the "char(39)" setup for wit
h
the like statement. But I keep getting and error.
Error returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Code:
set nocount on
Declare @.SQL_Where varchar(1000)
Declare @.Brand varchar(50)
Declare @.pos int
Declare @.SQL varchar(2000)
--Set @.Brand = 'Al' + char(39) +'s Game Crazy'
Set @.Brand = 'Al''s Game Crazy'
SET @.SQL_Where = ''
-- Check Brand
if Len(rtrim(ltrim(@.Brand))) > 0
BEGIN
SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
if @.pos > 0
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
Substring(@.Brand, @.pos + 1, len(@.Brand))
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand +
'%' + char(39) + ' and'
END
-- Remove the last part of the where clause filter " and"
SET @.SQL_Where = rtrim(@.SQL_Where)
SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
select @.Brand
select @.SQL_Where
SET @.SQL = 'select top 3000 * from view_ContactLocation where' + @.SQL_Where
select @.SQL
exec (@.SQL)
set nocount off
Any help or direction would be appreciated.
Thanks,
ScottTry replacing occurrances of the single apostrophe ' with double apostrophe
''
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||JT, I tried the modifiying the following:
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) + Substring(@.Brand,
@.pos + 1, len(@.Brand))
to
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' + Substring(@.Brand, @.pos
+ 1, len(@.Brand))
There appears to be no difference. I get the same errors. I believe that
is where you were thinking about doing the double "'".
Thanks,
Scott|||Scott Heffron wrote:
> JT, I tried the modifiying the following:
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> to
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> There appears to be no difference. I get the same errors. I believe
> that is where you were thinking about doing the double "'".
> Thanks,
> Scott
No. He's talking about using the REPLACE function to replace the apostrophe
with two apostrophes:
SET @.Brand=REPLACE(@.Brand,'''',''')
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You need to double-up the apostrophe to pass it into the stored procedure.
So from the app, the call need to look like this:
EXEC dbo.MyProc @.Brand = 'Al''s Game Crazy'
This doubling-up allows the stored procedure to be called successfully, as
the double apostrophe acts as an escape mechanism instead of ending the
string early. Now when you get the string into the stored procedure, it's
back to a single apostrophe only. So, if you are trying to put this string
into a string inside the stored procedure, you need to double them up again.
Of course, inside of SQL, you need to escape all instances of a quote. So,
try:
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' +
REPLACE(@.Brand, '''',''') +
'%' + char(39) + ' and' -- shudder... why add an AND unless you know there
is more WHERE?
Please see the following.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
I think with some work you could streamline this process quite a bit and
make it far less hairy-looking. I'm not going to address the @.sql and
@.sql_where, because I have no idea if and why end users are able to write
your sql statements for you. But for the wildcard search alone, you could
do this:
CREATE PROCEDURE dbo.SearchContacts
@.Brand VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 3000 <column list! don't use *>
FROM View_ContactLocation
WHERE Brand LIKE '%'+@.Brand+'%';
END
GO
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||Thanks, it worked.|||There are several columns that can be used in the where clause that is the
reason for the "and" at the end. Not all the columns are used. I did not
want to do a search on column1 = '%%' if nothing was in the @.column1
variable. I am expecting that is wasting query time and not needed. Sorry,
I used the "*" to save space. You are absolutely right on using the column
names.
What the variable will look like from the users application is "Al's Game
Crazy"
There are 9 different variables. Is it better to allow for a search on '%%'
or leave it out and create the where clause dynamically?
Thanks,
Scott|||> There are 9 different variables. Is it better to allow for a search on
> '%%'
> or leave it out and create the where clause dynamically?
Have you read the following article? I think it applies quite nicely to
your situation. It looks like a long read, but I think you'll be glad you
did it.
http://www.sommarskog.se/dyn-search.html|||Scott Heffron (ScottHeffron@.discussions.microsoft.com) writes:
> I am trying to Ad-hoc query from a user input(internal app), where the
> user enters in a name just as "Al's Game Crazy". Each time I do this,
> the Apostrophe gets in the way. I have tried using the "char(39)" setup
> for with the like statement. But I keep getting and error.
Rather than building the entire SQL string, use sp_executesql instead.
Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
an example. (Further below there is also an example that uses EXEC()
and deals with strings in a structured way.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973B3B229ACAYazorman@.127.0.0.1...

> Rather than building the entire SQL string, use sp_executesql instead.
> Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
> an example. (Further below there is also an example that uses EXEC()
> and deals with strings in a structured way.)
Other way is to use some tool that will do all job for you (provide your use
r
with friendly interface to create queries and generate SQL statement in resu
lt).
We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are so
me
similar products even free ones.
With the best regards, Nik.

Anyway to tell if this is a network issue!

A person using a vb6 windows app can not log into the server from their
machine. They get the error (you failed to connect to the SQL Server) could
be from
1.Not logged on correctly
2.Don't have rights
3.Network down
Client unable to establish connection error # -2147467259.
When the user goes to a different machine they are able to connect ok.
Thanks.
--
Paul G
Software engineer.check the client network utility and make sure it is set to tcp\ip (Not
named pipes)
also check to make sure the nic card on the client machine is set to the
same settings as the server (You probably dont want 'auto' )
Greg Jackson
PDX, Oregon|||Hi pdxJaxon, thanks for the response. Just wondering how to check if the the
nic card on the client machine is set to the same settings as the server?
Also I did a search on Google and found that if the client and server have
different versions of MDAC (Microsoft data access components) it could cause
the error, not quite sure how to find this out.
Also not familiar with the client network utility just wondering how to
access it?
--
Paul G
Software engineer.
"pdxJaxon" wrote:
> check the client network utility and make sure it is set to tcp\ip (Not
> named pipes)
> also check to make sure the nic card on the client machine is set to the
> same settings as the server (You probably dont want 'auto' )
>
> Greg Jackson
> PDX, Oregon
>
>|||Are you the one who wrote this app? Many things involved here:
1. Does the app use ODBC? ODBC driver is local on machine so it works on
the machine having ODBC setup correctly.
2. Is the server local? It may not the case but if 2 machines have
different TCP setup it could cause error.
3. The app uses trusted connection?
4. SQL on server setup with mix or SQL authentication?
There are more. It is hard to tell with this little of info.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:EBEFD042-19C5-40DE-8371-3347520EF9F2@.microsoft.com...
>A person using a vb6 windows app can not log into the server from their
> machine. They get the error (you failed to connect to the SQL Server)
> could
> be from
> 1.Not logged on correctly
> 2.Don't have rights
> 3.Network down
> Client unable to establish connection error # -2147467259.
> When the user goes to a different machine they are able to connect ok.
> Thanks.
> --
> Paul G
> Software engineer.

Anyway to tell if this is a network issue!

A person using a vb6 windows app can not log into the server from their
machine. They get the error (you failed to connect to the SQL Server) could
be from
1.Not logged on correctly
2.Don't have rights
3.Network down
Client unable to establish connection error # -2147467259.
When the user goes to a different machine they are able to connect ok.
Thanks.
Paul G
Software engineer.
check the client network utility and make sure it is set to tcp\ip (Not
named pipes)
also check to make sure the nic card on the client machine is set to the
same settings as the server (You probably dont want 'auto' )
Greg Jackson
PDX, Oregon
|||Hi pdxJaxon, thanks for the response. Just wondering how to check if the the
nic card on the client machine is set to the same settings as the server?
Also I did a search on Google and found that if the client and server have
different versions of MDAC (Microsoft data access components) it could cause
the error, not quite sure how to find this out.
Also not familiar with the client network utility just wondering how to
access it?
Paul G
Software engineer.
"pdxJaxon" wrote:

> check the client network utility and make sure it is set to tcp\ip (Not
> named pipes)
> also check to make sure the nic card on the client machine is set to the
> same settings as the server (You probably dont want 'auto' )
>
> Greg Jackson
> PDX, Oregon
>
>
|||Are you the one who wrote this app? Many things involved here:
1. Does the app use ODBC? ODBC driver is local on machine so it works on
the machine having ODBC setup correctly.
2. Is the server local? It may not the case but if 2 machines have
different TCP setup it could cause error.
3. The app uses trusted connection?
4. SQL on server setup with mix or SQL authentication?
There are more. It is hard to tell with this little of info.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:EBEFD042-19C5-40DE-8371-3347520EF9F2@.microsoft.com...
>A person using a vb6 windows app can not log into the server from their
> machine. They get the error (you failed to connect to the SQL Server)
> could
> be from
> 1.Not logged on correctly
> 2.Don't have rights
> 3.Network down
> Client unable to establish connection error # -2147467259.
> When the user goes to a different machine they are able to connect ok.
> Thanks.
> --
> Paul G
> Software engineer.

Anyway to tell if this is a network issue!

A person using a vb6 windows app can not log into the server from their
machine. They get the error (you failed to connect to the SQL Server) could
be from
1.Not logged on correctly
2.Don't have rights
3.Network down
Client unable to establish connection error # -2147467259.
When the user goes to a different machine they are able to connect ok.
Thanks.
--
Paul G
Software engineer.check the client network utility and make sure it is set to tcp\ip (Not
named pipes)
also check to make sure the nic card on the client machine is set to the
same settings as the server (You probably dont want 'auto' )
Greg Jackson
PDX, Oregon|||Hi pdxJaxon, thanks for the response. Just wondering how to check if the th
e
nic card on the client machine is set to the same settings as the server?
Also I did a search on Google and found that if the client and server have
different versions of MDAC (Microsoft data access components) it could cause
the error, not quite sure how to find this out.
Also not familiar with the client network utility just wondering how to
access it?
Paul G
Software engineer.
"pdxJaxon" wrote:

> check the client network utility and make sure it is set to tcp\ip (Not
> named pipes)
> also check to make sure the nic card on the client machine is set to the
> same settings as the server (You probably dont want 'auto' )
>
> Greg Jackson
> PDX, Oregon
>
>|||Are you the one who wrote this app? Many things involved here:
1. Does the app use ODBC? ODBC driver is local on machine so it works on
the machine having ODBC setup correctly.
2. Is the server local? It may not the case but if 2 machines have
different TCP setup it could cause error.
3. The app uses trusted connection?
4. SQL on server setup with mix or SQL authentication?
There are more. It is hard to tell with this little of info.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:EBEFD042-19C5-40DE-8371-3347520EF9F2@.microsoft.com...
>A person using a vb6 windows app can not log into the server from their
> machine. They get the error (you failed to connect to the SQL Server)
> could
> be from
> 1.Not logged on correctly
> 2.Don't have rights
> 3.Network down
> Client unable to establish connection error # -2147467259.
> When the user goes to a different machine they are able to connect ok.
> Thanks.
> --
> Paul G
> Software engineer.