SQL Adding a User account: express

Showing posts with label express. Show all posts

Thursday, March 29, 2012

Advice on SQL Server 2005 vs. Express Edition w/ Advanced Services

I've been using SQL Server 2005 Std. for my development work on a laptop w/ 2 GHz Celeron and 768 Mb RAM. Now that Express Edition has Reporting Services and Full-Text Search, would I be wise to just use it instead? I actually have both on my machine since Express Edition installed with VS 2005 Standard. Also, if I uninstall SQL Server 2005 Std. what will I lose? Thanks in advance.

-Mike

It depends what you want to do. SQL Express Advanced Services is not the same as Std, Std has more features (OLAP, Integration Services) richer features (better SSMS, better reporting including report builder) it also has less restrictions on memory, procs etc.

If you are only using this for development you should also be able to use Developer Edition which is < 50USD but is Enterprise Edition, with a no production use clause.

|||

I've decided to uninstall SQL Server 2005 Std. and try to work with Express w/ Advanced Services. I believe it will do all I need. I'm more concerned with performance on my laptop than features like OLAP, etc. If I gain any incite after using it for a while, I'll post back here. Thanks.

-Mike

sql

Thursday, March 22, 2012

Adventureworks version 631 error

I installed Sql Server 2005 Express Edition on an XP system thismorning and tried to attach Adventureworks, but got an error messagestating:

The database 'Adventureworks' cannot be opend because itis version 631. This server supports version 612 andearlier. A downgrade path is not supported.

Since both SqlServer 2005 Express Edition, Management Studio, and the Adventureworksdatabase have all be downloaded within the past three days, I amastonished that there is a compatibility issue. I have runAdventureworks on other systems without problems. Any suggestionson how to proceed?

Hi Moonshadow,

Where did you download that "Adventureworks" ?

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click "SQL Server 2005 SP2a" in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at:http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

Detailed information, see:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1966875&SiteID=1

thanks

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

Hi B.C. When I originally searched for the DB I was redirected tothe Codeplex site where the current default download version is for SqlServer 2008, hence version incompatibility. I tracked down andinstalled the 2005 version at Codeplex and all is well.

AdventureWorks Owner

Hi,

I've installed AdventureWorks, attached it to SQL Express and wanted to see its Database Diagrams. When I click on the Database Diagrams, I get this error message:

TITLE: Microsoft SQL Server Management Studio Express

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

I really see that in properties for this database there's no owner, while if I do

sp_helpdb 'AdventureWorks'

It shows that the owner is 'IDB-SERV\Michael' , which is my user.

I further tried to set this user as an owner in AdventureWorks properties-files window (where it showed no owner), but it says :

TITLE: Microsoft SQL Server Management Studio Express

Set owner failed for Database 'AdventureWorks'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+owner+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred in SMO. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

The login 'IDB-SERV\Michael' does not exist on this server.

Are all these bugs, or did I miss something?

Please, help

Thanx a lot !!!!

Moisha, here are a few threads hitting the same error.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112297&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=228352&SiteID=1

They both seem to be solving the problem using sp_dbcmptlevel.

Take a look at the threads and see if it applies to your environment.

Thanks,
Sam Lester (MSFT)
|||

Moisha,

The quick solution for me - which I distilled from the links posted here - was to execute the sp_dbcmptlevel procedure. I did not need to do anything else.

EXEC sp_dbcmptlevel 'nameOfDatabase', '90';

Sunshine is my life. Clean air is my right.

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
--
Regards
Pawel Potasinski
U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I am
> going through the tutorial and it's asking me to create the following query:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>> I have another DB (storeDB) on the server and I am able to see all the
>> tables under Databases > System Databases > storeDB > Tables > System
>> Tables, but I am unable to see the System Tables under AdventureWorks. I
>> am
>> going through the tutorial and it's asking me to create the following
>> query:
>> SELECT * FROM Production.Product;
>> GO
>> and I receive this error message:
>> Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'Production.Product'.
>> I have a connection the AdventureWorks DB. I'm obviously new to SQL
>> Server
>> 2005.
>> Any help would be appreciated.
>> Regards, Richard
>> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
>> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you
>> can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks Install

AdventureWorks Database not showing up management studio express

After installing the Adventure works database samples they do NOT show up in Management Studio Express?

I have done a clean install of SQL Server Express 2005 SP1 with Advanced services.

The database samples only install as physical files, they are not attached to the SQL Server process. If you want to use them, go in the SSMS and right click on the Database node, choose All Tasks and click Attach database.. Locate the MDF File and the LDF File and click Apply (or OK). SQL Server will attach the databases to the SQL Server process and you can access the data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I found this not quite accurat. The Attach is in the Object Explorer window under the sqlexpress datebase, right click Databases (nt Database) then click Attach. The window which opens should show the AdventureWorks database without having to browse. This required action is not mentioned in the tutorial.

AdventureWorks Database not showing up management studio express

After installing the Adventure works database samples they do NOT show up in Management Studio Express?

I have done a clean install of SQL Server Express 2005 SP1 with Advanced services.

HTH, Jens Suessmeyer.

AdventureWorks database not seen by Management Studio Express CTP

When I install AdventureWorks.db for SQL Express, it runs without errors. The database files (data and logs) are in the SQL Express 2005 data directory. However, I can not see the database in Managment Studio Express CTP. I've tried repairing and uninstall/install again. Does not help.

I have SQL Express 2005 cleanly installed. I have the pubs and Northwinds databases cleanly installed. (I was able to execute the queries that came for installing these). I can see both pubs and Northwinds in Management Studio Express CTP.

Any suggestions?

You won′t be able to see them if you don′t have permissions to the database itself, did you connect to database with the same credentials as you connected as you executed the queries that you mentioned ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, I did connect with the same credentials.

Also, the AdventureWorks did not come with a query like the pubs and Northwind databases. It just comes as a self-installing file. So I didn't have to open it in Mgmt Studio and execute it. It just installs and says it was successful when done.

|||

You must attach the files to an instance of SQL Server Express.

Execute the following script to identify the directory where the master database file is located.

select physical_name from sys.database_files where name = 'master'

Then, execute a similar script to attach the files.

exec sp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'c:\Programas\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf', @.filename2=N'c:\Programas\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldf'

Regards.

|||I did all above and still did not see it in the list.|||

hi,

you mean you executed the attach statement with no exception raised and you can't see the database in the list of registered databases even after a refresh of the list (rx click the database node, refresh)?

regards

|||

Hi there.

When you start SQL Management Studio Express, right click on Databases and click add in the Attach Databases window that opens. This will open the directory tree on your system and it will default to the Data directory where the AdventureWorks database is installed by default. You should see it in the window. Select it and click OK. It will then appear in the Attach Databases window so click OK again.

You may have to refresh the Object Explorer window, and then the database will show up in your list of databases. Hope that helps.

Graham.

AdventureWorks database not seen by Management Studio Express CTP

Any suggestions?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Yes, I did connect with the same credentials.

|||

You must attach the files to an instance of SQL Server Express.

Execute the following script to identify the directory where the master database file is located.

select physical_name from sys.database_files where name = 'master'

Then, execute a similar script to attach the files.

Regards.

|||I did all above and still did not see it in the list.|||

hi,

regards

|||

Hi there.

You may have to refresh the Object Explorer window, and then the database will show up in your list of databases. Hope that helps.

Graham.

Tuesday, March 20, 2012

Adventure Works DB Samples with Express 2005 SQL Server

Downloaded the file for the Adventure Works DB Sample. Which folder do I need to install it in to have show up in SQL Server Express 2005?

You can put it in any folder you want to (normally in the usual data folder you are using, by default this is located somehwere in the programe files , microsoft SQL Server directory. But you have to attach the database using the sp_attachdb procedure or use the GUI to attach the database, right click on the server --> attach database and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You can attach DB's in the Management Studio Express then?|||Should be able to, right click on the Databases folder, if it is not listed try All Tasks|||did you get this to work ?I have attached the database and still could not get the report designer towork with the database.

Advantages of SQL Server 2005 named instance?

The installation of SQL Server 2005 (Express) has an option
for default instance or named instance (SQLExpress as default)
What are the advantages of setting up named instance and default instance?
Thanks for answering...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in
news:64c9478703e41@.uwe:
> The installation of SQL Server 2005 (Express) has an option
> for default instance or named instance (SQLExpress as default)
> What are the advantages of setting up named instance and default
> instance? Thanks for answering...
There can only be one default instance, but multipe named instances with
different names. Advantage of the default instance is that you (or the
user) do not have to remember an instance name.
One of the major reasons for using multiple instances will be complete
separation of database solutions. Each instance has it's own service, and
thus you can set memory constraints per instance. Furthermore, remember
that logins are defined in the master database, so logins are only
available within that instance, which can be a good security measure.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks for replying... I'm currently using SQL Server 2005 Express...
I have set up with Named Instance = SQLExpress, that is, the default name.
So, does that mean I can still set up another copy of Express server
using Default Instance?
Ole Kristian BangÃ¥s wrote:
>> The installation of SQL Server 2005 (Express) has an option
>> for default instance or named instance (SQLExpress as default)
>> What are the advantages of setting up named instance and default
>> instance? Thanks for answering...
>There can only be one default instance, but multipe named instances with
>different names. Advantage of the default instance is that you (or the
>user) do not have to remember an instance name.
>One of the major reasons for using multiple instances will be complete
>separation of database solutions. Each instance has it's own service, and
>thus you can set memory constraints per instance. Furthermore, remember
>that logins are defined in the master database, so logins are only
>available within that instance, which can be a good security measure.
>
--
Pedestrian, Penang.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Yes. There can be one default instance on a machine and a number of named instances (each need a
unique name, of course). Installation order doesn't matter (excluding prior versions of SQL
Server/MSDE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in message news:64d4235358c63@.uwe...
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
> Ole Kristian BangÃ¥s wrote:
>> The installation of SQL Server 2005 (Express) has an option
>> for default instance or named instance (SQLExpress as default)
>> What are the advantages of setting up named instance and default
>> instance? Thanks for answering...
>>There can only be one default instance, but multipe named instances with
>>different names. Advantage of the default instance is that you (or the
>>user) do not have to remember an instance name.
>>One of the major reasons for using multiple instances will be complete
>>separation of database solutions. Each instance has it's own service, and
>>thus you can set memory constraints per instance. Furthermore, remember
>>that logins are defined in the master database, so logins are only
>>available within that instance, which can be a good security measure.
> --
> Pedestrian, Penang.
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||I haven't tried to install two SQL Server 2005 Express instances on the
same computer, but since multiple applications may install their instance
of MSDE I cannot see any reason why this should not work.
"pedestrian via SQLMonster.com" <u16758@.uwe> wrote in
news:64d4235358c63@.uwe:
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default
> name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks to Tibor and Ole... I shall try it out.
--
Pedestrian, Penang.
Message posted via http://www.sqlmonster.com

Advantages of SQL Server 2005 named instance?

The installation of SQL Server 2005 (Express) has an option
for default instance or named instance (SQLExpress as default)
What are the advantages of setting up named instance and default instance?
Thanks for answering...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64c9478703e41@.uwe:

> The installation of SQL Server 2005 (Express) has an option
> for default instance or named instance (SQLExpress as default)
> What are the advantages of setting up named instance and default
> instance? Thanks for answering...
There can only be one default instance, but multipe named instances with
different names. Advantage of the default instance is that you (or the
user) do not have to remember an instance name.
One of the major reasons for using multiple instances will be complete
separation of database solutions. Each instance has it's own service, and
thus you can set memory constraints per instance. Furthermore, remember
that logins are defined in the master database, so logins are only
available within that instance, which can be a good security measure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks for replying... I'm currently using SQL Server 2005 Express...
I have set up with Named Instance = SQLExpress, that is, the default name.
So, does that mean I can still set up another copy of Express server
using Default Instance?
Ole Kristian Bang?s wrote:
>There can only be one default instance, but multipe named instances with
>different names. Advantage of the default instance is that you (or the
>user) do not have to remember an instance name.
>One of the major reasons for using multiple instances will be complete
>separation of database solutions. Each instance has it's own service, and
>thus you can set memory constraints per instance. Furthermore, remember
>that logins are defined in the master database, so logins are only
>available within that instance, which can be a good security measure.
>
Pedestrian, Penang.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Yes. There can be one default instance on a machine and a number of named in
stances (each need a
unique name, of course). Installation order doesn't matter (excluding prior
versions of SQL
Server/MSDE).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pedestrian via droptable.com" <u16758@.uwe> wrote in message news:64d4235358c63@.uwe...[vbco
l=seagreen]
> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
> Ole Kristian Bang?s wrote:
> --
> Pedestrian, Penang.
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||I haven't tried to install two SQL Server 2005 Express instances on the
same computer, but since multiple applications may install their instance
of MSDE I cannot see any reason why this should not work.
"pedestrian via droptable.com" <u16758@.uwe> wrote in
news:64d4235358c63@.uwe:

> Thanks for replying... I'm currently using SQL Server 2005 Express...
> I have set up with Named Instance = SQLExpress, that is, the default
> name.
> So, does that mean I can still set up another copy of Express server
> using Default Instance?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Thanks to Tibor and Ole... I shall try it out.
Pedestrian, Penang.
Message posted via http://www.droptable.comsql

Monday, March 19, 2012

Advanced Series Express vs Express

Can someone tell me the difference between Express and Advanced Series Express are there different limitations? Why would I use one over the other -- thanks!

Check out the comparison at http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||Thanks -- I take it they are all free? Just different requirements and capabilities?|||

Yes, the Advanced Edition has more capabilities.

-- Robert

|||But still free right? You're just creating these edition to minimze by footprint -- can you tell free means a lot to me :)|||

Yes, both editions have the same licensing agreement.

-- Robert

Advanced Search box

Confused Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a

advanced search facility to my site. Problem is I do not know how to begin.

The website is a business directory and displays company names and services that they provide.

Here is where I am at...

So far I have made one query whichallows users to search the database by "town" such as if they enter "London" in the text box, it returns the relevant records into a gridview.

Rather than have a seperate text box for each query, I want to provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc.

I have seen compact advanced search boxes on many sites!.

Replies greatly appreciated.

prontonet

You can use a query with multiple search criterials, and 'disable' a search criterial when the input parameter is null. For example:

USE Northwind
DECLARE @.EId int, @.CId varchar(20)
SET @.EID=6
SELECT *
FROM Orders
WHERE EmployeeID=isnull(@.EId,EmployeeID)
AND CustomerID=isnull(@.CId,CustomerID)

Here we make a trick by using ISNULL function to return the self value of a field if the parameter is null. So in your code, just pass null values to the parameters when corresponding textbox is empty.

|||

Mnay Thanks for your reply. Can you give me another example - somehow not quite

following it yet??

|||Sorry I'm not not clear. Can you explain more about the example you want?|||

easiest way to explain is to give an example of what I would like a user to be able to do

Say I have 1000 companies in my database, then I want to give the user multiple search options

to retrieve only the data they need but in a compact way - As opposed to making a seperate text box for each search criteria which takes up a lot of space on the web page.

Ideally I want the user to be able to select or tick (from a dropdown or (checkbox) which

critieria they are searching by and then enter just enter any text into single box and just hit the search button.

I have included example criterias below...

Search Criteria 1 (By town)

Search Criteria 2 (by Industry sector)

Search Criteria 3 (by number of employees in company

Search Criteria 4 (company ID)

There could be more, also Ideally I would want them also to be able to filter the search further so they can for example select - "companies in "London" with "100+ employees", and that trade in the "technology sector"

I almost understand your example put maybe if you could put it into the above context

that would help. once again I am using visual web developer and Csharp.

Thanks

|||

Im really looking forward to an answer to this. Im stuck on the same thing......

Some1 plz help !!! With an example......

|||

OK, let's look at a simple example. Suppose we have a table as following in database:

CREATE TABLE Companies(ComID int PRIMARY KEY, town nvarchar(100),
IndustrySector nvarchar(50),NumOfEmployees int)

And we have 4 TextBoxes in application used for inputting search criterials. Then we can use such code:

using (SqlConnection conn = new SqlConnection(@."Data Source=.\IORI2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Companies WHERE town=isnull(@.town,town)AND Industrysector=isnull(@.zInSec,Industrysector)AND NumOfEmployees>=@.NOE AND ComID=isnull(@.ComID,ComID)", conn);

cmd.Parameters.Add("@.town", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@.zInSec", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@.NOE", SqlDbType.Int);
cmd.Parameters.Add("@.ComID", SqlDbType.Int);
if (textBox1.Text.Length == 0)
cmd.Parameters["@.town"].Value = DBNull.Value;
else
cmd.Parameters["@.town"].Value = textBox1.Text;
if (textBox2.Text.Length == 0)
cmd.Parameters["@.zInSec"].Value = DBNull.Value;
else
cmd.Parameters["@.zInSec"].Value = textBox2.Text;

cmd.Parameters["@.NOE"].Value = textBox3.Text.Length == 0 ? 0 : Int32.Parse(textBox3.Text);
if (textBox4.Text.Length == 0)
cmd.Parameters["@.ComID"].Value = DBNull.Value;
else
cmd.Parameters["@.ComID"].Value = Int32.Parse(textBox4.Text);

SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
label1.Text= dt.Rows.Count.ToString();
}

|||Thanks so much. How can I use this same example to search more then one column based on a single textbox.|||

That's something you need to implement from your code. For example you can validate the input text of a textbox so that the search criterals are delimited by some chars (such as ';' in connection string), and then split the text into a string array. In this case I prefer dynamic SQL built from concatenated basic SELECT command and WHERE clause. Ok let's take a look at how to build dynamic SQL command:

using (SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{
conn.Open();
string qstring = "SELECT * FROM Orders WHERE";

string[] sa = TextBox2.Text.Split('#');
if (sa.Length == 0)
Response.Write("No correct search criterials!");
else
foreach (string s in sa)
qstring += s+" AND ";
qstring=qstring.Remove(qstring.Length - 4);
SqlCommand cmd = new SqlCommand(qstring, conn);

//add your code

}

But keep in mind: dynamic SQL without using Parameters may suffer from SQL Injection. We should always use parameterized query.

Sunday, March 11, 2012

Advanced functions in SQL Express

Hi,

I would like exploring Reporting Services associated with SQL Express Edition so i tried installing "SQLEXPR_ADV_.EXE" the package available on download SQL site, but after having selected Reporting Services option I've got an error explaining that component "sqlserver2005_bc.msi" is to be changed.

As this component is part of "SQLEXPR_ADV_.EXE" package is there anybody knowing an alternative solution ?

Are you worrying about that the sqlserver2005_bc.msi component will be changed, so that repaire/modify the changed component will fail? I found the simliar issue in the instruction of SQL2005 sp1, which provides a workaround. You can take look at here:

http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQL2005SP1.htm

Advance Tab not available (Greyed Out)

Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.

DKB

Hi,

you can not edit data directly from Management Studio, refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178581&SiteID=1&PageID=1

Hemantgiri S. Goswami

|||

This is the process of setting up a grid for a new database being implemented. I expected to be able to select, edit, update or, delete for the grid. Although this has been a couple of days ago I will try to recreate the issue and record my actions and report the issue and sbmit it to msdn and see what come up.

DKB

Thursday, March 8, 2012

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

ADP is fine for smaller groups of people, and if you have a ready supply of people that can troubleshoot the applications if they have an issue. Visual Studio applications can scale much higher and are better supported.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

Saturday, February 25, 2012

ADODB Connection Open: Intermittent Timeout Expired Errors

Hello all-
Connecting to a SQL Server 2005 Express w/Advanced Services database using
ADODB in vbscript. The database is on the local server and am using shared
memory. The server has dual processors and 3GB Ram, but SQL Server Express i
s
limited to 1 processor and 1 GB of ram.
Every once in a great while I receive timeout expired errors while trying to
connect to the database. When it works, it connects in less than a
millisecond. Therefore I believe that increasing the timeout period in the
connection string rather than using its default of 30 seconds is not going t
o
fix this problem.
Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
Catalog=Ingen;Integrated Security=SSPI;"
Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
Call dbConnection.Open(CONNECTION_STRING)
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
Any suggestions?
Thank you in advance.Go ahead and try the longer timeout. There may be other things happening on
your system that occasionally cause a hesitation. One of the more common
issues is that SQL Express databases have the autoclose option set by
default. This means that when no users are logged into a database, SQL
Server shuts it down to safe resources. The first user to login after the
database shuts down waits for the database to start up before the login is
complete so that may be what is causing the timeout. Generally starting up
a database takes only a few seconds but if the system is busy doing other
things it may take a while for SQL Server to obtain the resources it needs.
You can check this by using ALTER DATABASE to turn off the autoclose option
on your main databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:89E366F9-6946-4B45-86AD-CC7B9B0EB5BE@.microsoft.com...
> Hello all-
> Connecting to a SQL Server 2005 Express w/Advanced Services database using
> ADODB in vbscript. The database is on the local server and am using
> shared
> memory. The server has dual processors and 3GB Ram, but SQL Server Express
> is
> limited to 1 processor and 1 GB of ram.
> Every once in a great while I receive timeout expired errors while trying
> to
> connect to the database. When it works, it connects in less than a
> millisecond. Therefore I believe that increasing the timeout period in the
> connection string rather than using its default of 30 seconds is not going
> to
> fix this problem.
> Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
> Catalog=Ingen;Integrated Security=SSPI;"
> Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
> Call dbConnection.Open(CONNECTION_STRING)
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
> Any suggestions?
> Thank you in advance.|||Thanks Roger for your response. We started getting this error after
upgrading from MSDE to the 2005 Express version. Is this auto-close
something new to 2005? I verified that I do have auto-close turned off for m
y
database properties.
What would you suggest increasing the timeout period to?|||Yes, also look in the SQL Server errorlog file to see if any errors are
logged when the timeout happens.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:D29FD261-9A6C-4083-8D1E-22C1914DB2DC@.microsoft.com...
> Thanks Roger for your response. We started getting this error after
> upgrading from MSDE to the 2005 Express version. Is this auto-close
> something new to 2005? I verified that I do have auto-close turned off for
> my
> database properties.
> What would you suggest increasing the timeout period to?|||Where do I find the SQL Server errorlog file?
I am on SQL Server Express so it will not let me view the logs in the SSMSE
interface. If you are talking about the logs in the Event Viewer, there was
nothing logged.
Thanks
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.|||Where do I find the SQL Server errorlog?
I am on SQL Server Express and cannot view the transaction logs from within
SSMSE. If you are talking about the system logs in the Event Viewer, there
was nothing logged in here during the time frame of the connection error.
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.|||C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
assuming you installed express first and to the default location.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...[vbcol=seagreen]
> Where do I find the SQL Server errorlog?
> I am on SQL Server Express and cannot view the transaction logs from
> within
> SSMSE. If you are talking about the system logs in the Event Viewer,
> there
> was nothing logged in here during the time frame of the connection error.
> "Roger Wolter[MSFT]" wrote:
>|||Roger-
Increasing the timeout in the connection string appears to have fixed the
problem! No errors since making the change, so I think that's a good sign.
Thanks for your help.
"Roger Wolter[MSFT]" wrote:

> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> assuming you installed express first and to the default location.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
> news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...
>
>

Thursday, March 29, 2012

Advice on SQL Server 2005 vs. Express Edition w/ Advanced Services

Thursday, March 22, 2012

Adventureworks version 631 error

AdventureWorks Owner

AdventureWorks Install

AdventureWorks Install

AdventureWorks Install

AdventureWorks Database not showing up management studio express

AdventureWorks Database not showing up management studio express

AdventureWorks database not seen by Management Studio Express CTP

AdventureWorks database not seen by Management Studio Express CTP

Tuesday, March 20, 2012

Adventure Works DB Samples with Express 2005 SQL Server

Advantages of SQL Server 2005 named instance?

Advantages of SQL Server 2005 named instance?

Monday, March 19, 2012

Advanced Series Express vs Express

Advanced Search box

Sunday, March 11, 2012

Advanced functions in SQL Express

Advance Tab not available (Greyed Out)

Thursday, March 8, 2012

ADP or VS

ADP or VS

Saturday, February 25, 2012

ADODB Connection Open: Intermittent Timeout Expired Errors

SQL Adding a User account

Blog Archive

About Me