( ) problem

hi all,

i have some problem in my web application, am using vb.net

i have a text box and an add button, when the user click on the add button, whatever written in the textbox stores on my sql database

the problem is whenever there is a ( ' ) character in the text, it send me error

i know why this problem occure but i dont know what is the reqired code for it

this is the code:

sql.commandtext = "INSERT INTO myDB (Ques) VALUES ('" & txtQues.text & "')

what is the solution?

The answer is very simple in C# those are literals and in ANSI SQL they are Delimited Identifiers per ANSI SQL 92 and SQL Server is compliant so try the link below on how to enable it and handle it correctly. Hope this helps.

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

|||

If you think about it, you are putting together the string before it gets sent to SQL Server. So, if your value has a single quote it, your insert would look something like:

INSERT INTO myDB (Ques) VALUES ('myvalue's')

This is confusing. The string seems to end after the 'e', when you really want it to end after the 's'. As said, SQL Server is taking that literally. If you're going to build the insert statement this way (as opposed to using a stored procedure), you need to replace single quotes with two single quotes (not a double quote - literally two single quotes).

txtQues.Text.Replace("'", "''")

The string passed to the database will be:

INSERT INTO myDB (Ques) VALUES ('myvalue''s')

which the database will interpret correctly.

|||Parameterized queries.|||thanks alot... for your help

"object does not support this property or method" when trying to print

I have a user that gets the error at the bottom of the screen that states "object does not support this property or method " whenever they lcik on the print button. I have run the same report and do not received the error. Does anyone know how to fix this?

They may have an old version of the print control. They should open the Tools menu in IE, go to Internet Options ->settings ->view objects and then delete the RSClientPrint class. Clicking the print icon should download a new control that should work for them.

-Daniel

"Logon failure: unknown user name or bad password"

First, I checked all the obvious problems. The password is right and the login works perfectly.
Here is the output from Developer Studio when trying to deploy/process a cube:

Performing an incremental deployment of the 'MaxMinManufacturingDM' database to the 'localhost' server.
Generating deployment script...
Process Database MaxMinManufacturingDM
Done
Sending deployment script to the server...
Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .
Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Max Min Manufacturing DM', Name of 'Max Min Manufacturing DM'.
Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Manufacturing Fact', Name of 'Dim Time' was being processed.
Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'Year Of Manufacture' attribute of the 'Dim Time' dimension from the 'MaxMinManufacturingDM' database was being processed.

My Analysis Services project (or Business Intelligence project) has only one Data Source, "Max Min Manufacturing DM.ds". I'm using a SQL Server login with a saved password.

I double click the data source to get to the "Data Source Designer" dialog and see the following connection string:

Provider=SQLNCLI.1;Data Source=localhost;Persist Security Info=True;Password=**********;User ID=MaxMinUser;Initial Catalog=MaxMinManufacturingDM

I click "Edit" and "Test Connection" and get "Test connection succeeded". The "Save my password" option is checked. The password is fine and the login should works perfectly. Developer Studio can obviously connect to the database since I've done all the design through it. I can't imagine why I'm getting this error.

I'm using:

Windows XP (dev machine)
Relational Data Mart is using SQL Server 2000 SP4 (I can't upgrade to SQL Server 2005 for business reasons beyond my control)
Visual Studio Studio 2005
SQL Server Analysis Services 2005 (I believe I put SP1 on this).

Any idea?

Hello. Are you running the SQL Server services and the SSAS2005 services with the same or different accounts?

When you process a cube the SSAS2005 must have permissions to read data from the source database. If you run SSAS2005 on a separate machine than the sourec database and the SSAS2005 service account is running as "local system" this server have no permissions on other servers/machines.

You can check this is the SQL Server Configuration manager(for SSAS2005).

Also, try the default impersonation mode for the data source connection.

HTH

Thomas Ivarsson

|||Great!!! Setting the "impersonation mode" to default worked. I guess I should read up on that so I understand what the difference between the impersonation mode and the database login is.

Yes, everything is running on the same server (it's a development box): SSAS2005, MSSQL2000, and Visual Studio.

"Execute Package Utility has encountered a problem and needs to close"

I'm a SQL Server 2000 user trying to get my head around v2005, in particular the changes from DTS to Integration Services.

I've build a few packages in BIDS and they work fine there, but saving them to either the file system or MSDB locations and running them in Management Studio results in:

Execute Package Utility has encountered a problem and needs to close. We are sorry for the inconvenience.

If I click on the debug button, I get this:

An unhandled exception ('System.IO.FileNotFoundException') occurred in DTExecUI.exe [404].

I've searched the Internet and found a few people who has also experienced this, but not received any answers.

Is there a kind guru out there with some idea of what is going on? Preferably something that doesn't involve re-installing, because the database has already been configured for our Blackberry users and I can't re-do it now.
I have the same problem... have you got a soln yet?

Thanks|||I also get the same problem. Looking in the event log, it is appears it cant find a temp file...but that doesnt make sense to me. Any solutions?|||I had the same problem. It got fixed after applying SP2.

"Enable user SQL instances"

Hi I have Visual Studio 2005 with SP1 installed, and I want to install the Management Studio. I tried many times installing that and I always got the same problem, which is when I try to create a database in VS I just don’t get trough as I used to. A message appears saying that I’ve to enable some estance. So to resolve the problem I saw if is everything ok with the SQL Server, which seemed to be fine, but I couldn’t start the Report service.

Now I just formatted the computer and I’m afraid to happen all over again. May be the version with only the Management would be fine, I don’t know if it adds something beside of that.

Thank you very much.

After installing Express, you need to explicitly enable remote connections. Is this the issue you are seeing? If not, please supply an exact error message or a log snippet showing the problem.

Thanks,
Sam Lester (MSFT)

|||

Was about "Enable user SQL instances" and the Report Service didn’t started too.

How do I enable this remote connections?

And in the instalation, Should I Add a user to the SQL Server Administration Role?

And also in the step "ready to install" of the SQL Server SP2 shows the following:

The following components will be installed:

· SQL Server Database Services (Upgrade)

(Database Services, Replication, Full-Text Search)

· Reporting Services

(Reporting Services, Report Manager)

· Client Components (Upgrade)

(Connectivity Components, Management Studio Express)

Warning: Setup found that the following components that already exist are at a different service pack level than the components being installed.

Components: Microsoft SQL Server 2005 Tools Express Edition, Microsoft SQL Server 2005 Express Edition (SQLEXPRESS)

After completing setup, you must download and apply the latest SQL Server 2005 service pack to all the components.

"Enable user SQL instances"

I couldn't reply the another thread so I'm continuing here.

Was about "Enable user SQL instances" and the Report Service didn’t started too.

How do I enable this remote connections?

And in the instalation, Should I Add a user to the SQL Server Administration Role?

And also in the step "ready to install" of the SQL Server SP2 shows the following:

The following components will be installed:

· SQL Server Database Services (Upgrade)

(Database Services, Replication, Full-Text Search)

· Reporting Services

(Reporting Services, Report Manager)

· Client Components (Upgrade)

(Connectivity Components, Management Studio Express)

Warning: Setup found that the following components that already exist are at a different service pack level than the components being installed.

Components: Microsoft SQL Server 2005 Tools Express Edition, Microsoft SQL Server 2005 Express Edition (SQLEXPRESS)

After completing setup, you must download and apply the latest SQL Server 2005 service pack to all the components.

Hi Skyler,

You should really click the Help button in the setup UI, all of these options are explain in the setup help.

Enable User Instances - On be default, this enables a special kind of instance of SQL Express that is used by Visual Studio to allow users without Adminstrator privleges to run applications with data stored in SQL Express.

Remote Connections - You can enable them using either the Surface Area Configuration tool or the SQL Server Configuration Manager, both of which can be launched from the Start menu after SQL Express is installed. You should look at the FAQ at the top of this forum and read the answer to the question about remote connections, it will point you to a KB article that explains additional steps you need to take if you have a firewall on your computer.

Add a user to the SQL Server Administration Role: This is important for Windows Vista to allow you to be an adminstrator on your copy of SQL Express. There is more information about issues with Vista in the SQL Express blog at http://blogs.msdn.com/sqlexpress.

The information shown on the ready to install page is telling you that you are upgrading the Database Service and Client Components and installing Reporting Services. It is also telling you that the Express Toolkit has been installed on your computer and that you will need to apply the SP2 update for the Express Toolkit to update those tools once you have finished installing Express with Advanced Services.

If you are still having problems with Reporting Services, I would suggest you post a question about that directly to the Reporting Service forum.

Mike

"Cannot open user default database"

[2005 Developer Edition] In Management Studio I changed my default database to a user db. I subsequently unselected that db as the default leaving me with no default db (I think - it's been awhile and I may not have this exactly right). But regardless, I can no longer Connect to Server using Management Studio (Windows authentication) - I get

Cannot open user default database. Login failed.
Login failed for user 'DANA\Administrator'. (Microsoft SQL Server, Error: 4064)

The only thing suspicious in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG is

The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.

From Configuration Manager > SQL Server > Properties > Advanced > Startup Parameters

-dC:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf

Any idea what's going on here?

Hi,

I guess these are two different errors. The one first mentioned can be changed by logging on with a sysadmin role account or securityadmin role account and using the statement sp_defaultdb 'DANA\Administrator','master'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Management Studio cannot connect to the database engine, so what other tool or utility do I use to execute sp_defaultdb?|||

See if this thread helps you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=388163&SiteID=1.

You should try specifying the database you want to use at connection time as shown in the above thread.

Thanks
Laurentiu

|||You can specify the database during the connection within Management Studio by clicking on the options button, remove the default value and select a valid database. Or use another tool like SQLCMD or OSQL to connect to the server with specifying a database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||The thread reference did the trick - thanks a lot! BTW the original problem was caused by takinig the default (user) database offline, then exiting Management Studio.

"Advanced search" query in SQL Server2000

Hi query-experts,

I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:

Table has the fields: id, fruit, color

User enters: apple pear green

The query should return all records that contain apple AND pear AND green in any of the fields.

Now I do it like this:

SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')

this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.

Question: is there an easier way to achieve this?

(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)

SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;

you can try something like this:

SELECT

*
FROM table
WHERE fruit in ('apple','pear','green')
and color in ('apple','pear','green')

I recommend you take a look at Erland's article:
http://www.sommarskog.se/dyn-search.html

|||Thanks! I'll check it out

<Select All>

Is there a way to default a multi-value parameter to "Select All"?
Also, can you test to see if the user clicked <Select All>? I want to
display the parameter on the report as the user selecting all.Have you figured it out - default display a multi-value parameter to "Select
All" yet?
"jtstrassburg@.gmail.com" wrote:
> Is there a way to default a multi-value parameter to "Select All"?
> Also, can you test to see if the user clicked <Select All>? I want to
> display the parameter on the report as the user selecting all.
>

<Alter Login sa - SQL 2005 - Login Failed>

Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005. After
doing this, i cannot login using the sa or xxxsa user id. It keeps telling me
that login failed.
How i can i login back?
Thanks
I figured out the problem. When i changed the login to xxxsa, SQL Server
changed the login to 'xxxsa' (with the quotes). When i used the login with
the quotes, it worked.
"Houston Lucifer" wrote:

> Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005. After
> doing this, i cannot login using the sa or xxxsa user id. It keeps telling me
> that login failed.
> How i can i login back?
> Thanks
>

<Alter Login sa - SQL 2005 - Login Failed>

Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005. A
fter
doing this, i cannot login using the sa or xxxsa user id. It keeps telling m
e
that login failed.
How i can i login back?
ThanksI figured out the problem. When i changed the login to xxxsa, SQL Server
changed the login to 'xxxsa' (with the quotes). When i used the login with
the quotes, it worked.
"Houston Lucifer" wrote:

> Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005.
After
> doing this, i cannot login using the sa or xxxsa user id. It keeps telling
me
> that login failed.
> How i can i login back?
> Thanks
>

<Alter Login sa - SQL 2005 - Login Failed>

Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005. After
doing this, i cannot login using the sa or xxxsa user id. It keeps telling me
that login failed.
How i can i login back?
ThanksI figured out the problem. When i changed the login to xxxsa, SQL Server
changed the login to 'xxxsa' (with the quotes). When i used the login with
the quotes, it worked.
"Houston Lucifer" wrote:
> Hi all, i issued the alter login sa name = [xxxsa] on SQL Server 2005. After
> doing this, i cannot login using the sa or xxxsa user id. It keeps telling me
> that login failed.
> How i can i login back?
> Thanks
>

'sa' User Map error

Hello all,

I'm having a problem with my sa account, we just migrated from sql server 2000 to sql server 2005 and 1 of our production applications is failing on the sa login.

I checked the 'User Map' setup to the 'sa' account and the databases that need accessing aren't mapped.

When I try to add the map, I get error "Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)". I get the same error when trying to remove a user map for sa (there are a couple db's already mapped), and I don't remember how I mapped them in the first place, but it certainly isn't working now!

Thanks in advance,
102020

What is ther error that you get back and for waht operation do you get it back? Please also check the server error log and copy the relevant errors logged there.

Thanks
Laurentiu

|||I have fixed this already, thanks though.

The problem was that the db owner was the domain admin and not sa, so I couldn't change the mapping.

Thanks,
102020
|||

Hi,

I have the same problem. I am trying to add sa to several databases created by other users than sa. We are getting error 15405:cannot use the reserved user or role name 'SA'.Microsoft SQL-DMO (ODBC-SQL State:42000)

Is there no solution to this problem? We are using SQL Server 2005

Regards Caroline|||

I found the solution!! Quit simple: exec sp_changedbowner 'sa','true'.

Thanks anyway

|||

Hey,

I find out how to change it using Management Stadio Express:

Right click the database to show properties. then change the owner under files to sa

then you can map it under Logins .

|||Caroline, thanks for posting this - it really helped me out!
|||Thanks Caroline, I've been searching for hours on how to do this, I kept geeting same as as above posts.
|||I've been living without database diagrams for one of my databases for the best part of a year since inheriting it from a previous developer. Now I can finally see the relationships between the tables. Thanks CarolineE!!!

'sa' User Map error

Hello all,

I'm having a problem with my sa account, we just migrated from sql server 2000 to sql server 2005 and 1 of our production applications is failing on the sa login.

I checked the 'User Map' setup to the 'sa' account and the databases that need accessing aren't mapped.

When I try to add the map, I get error "Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)". I get the same error when trying to remove a user map for sa (there are a couple db's already mapped), and I don't remember how I mapped them in the first place, but it certainly isn't working now!

Thanks in advance,
102020

What is ther error that you get back and for waht operation do you get it back? Please also check the server error log and copy the relevant errors logged there.

Thanks
Laurentiu

|||I have fixed this already, thanks though.

The problem was that the db owner was the domain admin and not sa, so I couldn't change the mapping.

Thanks,
102020
|||

Hi,

I have the same problem. I am trying to add sa to several databases created by other users than sa. We are getting error 15405:cannot use the reserved user or role name 'SA'.Microsoft SQL-DMO (ODBC-SQL State:42000)

Is there no solution to this problem? We are using SQL Server 2005

Regards Caroline|||

I found the solution!! Quit simple: exec sp_changedbowner 'sa','true'.

Thanks anyway

|||

Hey,

I find out how to change it using Management Stadio Express:

Right click the database to show properties. then change the owner under files to sa

then you can map it under Logins .

|||Caroline, thanks for posting this - it really helped me out!
|||Thanks Caroline, I've been searching for hours on how to do this, I kept geeting same as as above posts.

'sa' User Map error

Hello all,

I'm having a problem with my sa account, we just migrated from sql server 2000 to sql server 2005 and 1 of our production applications is failing on the sa login.

I checked the 'User Map' setup to the 'sa' account and the databases that need accessing aren't mapped.

When I try to add the map, I get error "Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)". I get the same error when trying to remove a user map for sa (there are a couple db's already mapped), and I don't remember how I mapped them in the first place, but it certainly isn't working now!

Thanks in advance,
102020

What is ther error that you get back and for waht operation do you get it back? Please also check the server error log and copy the relevant errors logged there.

Thanks
Laurentiu

|||I have fixed this already, thanks though.

The problem was that the db owner was the domain admin and not sa, so I couldn't change the mapping.

Thanks,
102020
|||

Hi,

I have the same problem. I am trying to add sa to several databases created by other users than sa. We are getting error 15405:cannot use the reserved user or role name 'SA'.Microsoft SQL-DMO (ODBC-SQL State:42000)

Is there no solution to this problem? We are using SQL Server 2005

Regards Caroline|||

I found the solution!! Quit simple: exec sp_changedbowner 'sa','true'.

Thanks anyway

|||

Hey,

I find out how to change it using Management Stadio Express:

Right click the database to show properties. then change the owner under files to sa

then you can map it under Logins .

|||Caroline, thanks for posting this - it really helped me out!
|||Thanks Caroline, I've been searching for hours on how to do this, I kept geeting same as as above posts.
|||I've been living without database diagrams for one of my databases for the best part of a year since inheriting it from a previous developer. Now I can finally see the relationships between the tables. Thanks CarolineE!!!

##### Sorting a column in the report #####

Hi
I am using SQL Server 2000 Reporting Services.
I want to add sorting in my reports so that when a user
clicks on the column header the data in the report gets
sorted by that column. How can i achieve this.
A descriptive explanation would be really helpful
Thanks
Gautam Kashyaphttp://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=0064e121-fd26-4c83-8add-4a694e4fea7d&sloc=en-us
"Gautam Kashyap" <kashyap_gautam@.rediffmail.com> wrote in message
news:598001c491b7$cfd93da0$a301280a@.phx.gbl...
> Hi
> I am using SQL Server 2000 Reporting Services.
> I want to add sorting in my reports so that when a user
> clicks on the column header the data in the report gets
> sorted by that column. How can i achieve this.
> A descriptive explanation would be really helpful
> Thanks
> Gautam Kashyap

# user questions ?

Hi, group if I have 120 user with my application how many licences must be I
have in my SQL Server ?
Best regard
MArio120. Unless you use Per Processor licensing.
http://www.microsoft.com/sql/howtobuy/faq.asp
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oj6EAkOwDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi, group if I have 120 user with my application how many licences must be
I
> have in my SQL Server ?
> Best regard
> MArio
>