How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

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

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to increase the SQL Server memory

We are using SQL server 7.0, our users use to connect to SQL server remotely using Visual Basic Application. When all the users are connected it keeps on increasing the memory utilization and at 1,836,848 KB memory utilization it stops increasing the memory i.e memory utilization become stangnet. The actual amount of physical Memory is around 5GB but SQL server dont increase it after the treshhold level and the performance of the server get affected badly. Any one please suggest me the possible solution.In Enterprise Manager,
- right click on the server name and choose properties.
- choose memory tab

You should be able to set your min/max memory settings there. Also look into worker threads (on the processor tab).

Something else to think about,
How big is the DB (Gigs) ? How big are the tables being referenced? Perhaps something could be re-designed to make things more efficient. Are there a lot of table scans where you could have indexes, etc..?

Also,
Open the performance moniter and check the sql counters..

More info at -> http://www.sql-server-performance.com/performance_monitor_tips.asp|||It looks like you're running Standard Edition of SQL2K, which will not recognize any more memory, period! You need to upgrade to Enterprise and then you'll have more options:

1. /3GB in boot.ini will allow SQL Server to use more than the default 2GB
2. /PAE in boot.ini and enabling AWE on SQL Server will give SQL service whatever amount you specify.

How to improve the efficiency when search data from more than 1000000 records?

Hi everyone,
My company has a website, use ms sql server. One table has more than 1000000 records.
When users search data from this table(such as search records which contain the word "school"

in NewsTile field.
And the server often occurred deadlock error.

How can I improve it?
Thanks.

P.S. The table has these fields:
NewsID
NewsTitle
NewsContent
NewsClickTimes
NewsInsertTime

1,000,000 records isn't too much... some of my clients have 90 million records and more. The most "expensive" search to do is a "contain the word" search (such asWHERE NewsContent LIKE '%school%')

But a lot has to do with the indexes:http://www.singingeels.com/Articles/SQL_Performance__Clustered_Indexes.aspx

Can you give me a sample query that takes a long time to run that you would like to see it speed up?

Thanks,

|||

First thanks for your reply.

Select NewsID, NewTitle, NewsClickTimes from News where NewsTitle like '%xxx%' order by NewsClickTimes desc, NewsInsertTime desc

The result of search must oder by ClickTimes first, if these records have same Click Times, oder by inser time.

Set the two filed, NewsClickTimes and NewsInsertTime as Clustered index. First is NewsClickTimes.

But if search records contains one word, use like '%school%', the search can't use index.

Can you give me some suggetions about improve this situation?

|||

Hi cime63,

First, based on my understanding, i think one table cannot have more than one clustered index. So, you cannot set both NewsClickTimes and NewsInsertTimes as clustered index.

Second, "But if search records contains one word, use like '%school%', the search can't use index. " --> I think in this case still we can use full-text index. For example:

Select NewsID, NewTitle, NewsClickTimes from News where contains(NewsTitle,' "*school*"') order by NewsClickTimes desc, NewsInsertTime desc

More information , you can refer to :http://technet.microsoft.com/en-us/library/ms187787.aspx (see the examples in this article)

Hope my suggestion helps

|||

HiBo Chen – MSFT,

First thanks for your suggestion.

What I say above is not exact. Now I want to correct it.

Bo Chen – MSFT:

First, based on my understanding, i think one table cannot have more than one clustered index. So, you cannot set both NewsClickTimes and NewsInsertTimes as clustered index.

I know one table can only has one clustered index. What I mean is I set the two fileds "NewsClickTimes" and "NewsInsertTimes" as complex clustered index. First is NewsClickTimes, and then is NewsInsertTimes.

Bo Chen – MSFT:

I think in this case still we can use full-text index.

About full-text index, I heard it. But at the same time, I heard that the result is not exact especially when search Chinese from string(it's a Chinese system). Is this opinion right or not?

Of course I'll have a look at the article you give me.

Thanks again.

|||

cime63:

Bo Chen – MSFT:

I think in this case still we can use full-text index.

- Full-text searches are only applicable if the field is a text field. What is the datatype of the field?

Also, is this SQL Server 2000 or 2005?

|||

Having battled with the indexes, if your search will be as ... LIKE '%something%' then the index will not be used. SQL Server will do a full scan. See if you can get your business to narrow down the search to at least first couple of letters rather than a complete wild search.

LIKE 'A%' will be much better than LIKE '%A%'.

|||

Instead of using the like statement, try using a case statement where you only compare the value if certain criteria is met. The "where NewsTitle like '%xxx%' " is your problem (or at least a big part of it).

|||

Nullable:

What is the datatype of the field?

Also, is this SQL Server 2000 or 2005?

The datatype of the field (User will search records contain one word) is nvarchar(64), not text. And we still use SQL2000 now.

|||

ndinakar:

Having battled with the indexes, if your search will be as ... LIKE '%something%' then the index will not be used. SQL Server will do a full scan. See if you can get your business to narrow down the search to at least first couple of letters rather than a complete wild search.

LIKE 'A%' will be much better than LIKE '%A%'.

Thanks you reply.

But in this system, we muse allow user search records that contain one word users input.

Like 'A%' is not enough.

Has anyone use lucence in actual project?

|||

dotnetjunkie2006:

Instead of using the like statement, try using a case statement where you only compare the value if certain criteria is met. The "where NewsTitle like '%xxx%' " is your problem (or at least a big part of it).

Thanks your reply.

But can I use case statement in this situation?

|||

OK, to clarify a few things:

1) Will SQL use your index for a LIKE searchsurrounded by wild cards?
answer) No, a search for "%abc%" will not use the index.

2) Is 1,000,000 records "a lot" to scan with this kind of a search?
answer) Not really... it should take about 1 to 2 seconds.

3) Do you have proof?
answer) Of course :)

I made a table with a VARCHAR(100) field, and populated it with1.6 million records and did the following searches:

SELECT*FROM _myTempDBWHERE TestFieldLIKE'1234%'
-- With no index : Cost = 11.184 (1 second)
-- With PLAIN OLD INDEX : Cost = 0.006 (less than 1 second)

SELECT*FROM _myTempDBWHERE TestFieldLIKE'%1234%'
-- With no index : Cost = 12.750 (2 seconds)
-- With PLAIN OLD INDEX : Cost = 12.750 (2 seconds)

Notice, like was mentioned by someone above that removing the '%' from the front of the search does speed things up... buteven with it still in there, you're query should take about 1 to 2 seconds. (like I said, I have 1.6 million records for my test).

Does that answer your questions?

|||

cime63:

case statement in this situation?

- No, you could use a "CHARINDEX" statement, but the results are the same.

|||

Thanks very much.

But I must search records which contain a word.

|||

Is there anyone can help me?

sql

How to Import data from Excel into MS SQL in .Net?

Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.

I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work.

- Most simple and direct way I've tried is creating a oledb connection to the Excel file in the following codes...

Function TestUploadData1(ByVal vFile As String) As String
Dim Oleda As System.Data.OleDb.OleDbDataAdapter
Dim Olecn As System.Data.OleDb.OleDbConnection
Dim dt1 As DataTable

Olecn = New System.Data.OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & vFile & ";" & _ "Extended Properties=Excel 8.0;HDR=Yes")
Olecn.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.ExecuteNonQuery()

Olecn.Close()
End Function

But from the above codes I kept getting the error "Could not find installable ISAM." at the line Olecn.Open(). I've tried to set the registry to C:\Windows\System32\msexcl40.dll but to no avail...

- I've tried another method using the OpenRowSet in the following codes
Function TestUploadData2(ByVal vFile As String) As String
Dim cn As SqlConnection
Dim cm As SqlCommand
Dim strSQL As String cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")

cn.Open()

strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
cm = New SqlCommand(strSQL, cn)
cm.ExecuteNonQuery()

cn.Close()
End Function

For the above codes, I kept getting the error "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." at the line cm.ExecuteNonQuery() and I've tried to disable the Ad Hoc settings in the registry and tried to create a link server but to no avail as well...

- I've tried DTS but I'd need to DTS package to run whenever the users upload a file. Is there an easy way that I can run the designated DTS package to run through my .Net web application?

Any help would be deeply appreciated.
Thanks & Regards,
Keith Chang

Here is a very simple article which you can extend to your needs.

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

There are some solutions available for this problem. You already tried one. Have a look at this post also.

http://www.dotnetspider.com/qa/Question32454.aspx

Sometimes, even the connections string can be a problem (yours looks fine though)

|||

Here is a small exerpt from one of my projects:

If FileUpload1.HasFileThen

Dim filenameAsString

Dim cmdAs OleDbCommandDim daAsNew OleDbDataAdapter

filename = System.IO.Path.GetTempFileName

FileUpload1.SaveAs(filename)

Dim connAsNew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename &";Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")

' Find Sheet Names

Dim SheetsAs Generic.List(OfString)

Sheets = GetSheets(conn)

' Find Header Row

cmd =New OleDbCommand("SELECT * FROM [" & Sheets(0).ToString &"$A:A]", conn)

Dim dshAsNew DataSet

da =New OleDbDataAdapter(cmd)

da.Fill(dsh)

And the GetSheets function:

Function GetSheets(ByVal connAs OleDbConnection)As Generic.List(OfString)

Dim SheetsAsNew Generic.List(OfString)

Dim dtAs DataTable

If conn.State = ConnectionState.ClosedThen

conn.Open()

EndIf

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,NewObject() {Nothing,Nothing,Nothing,"TABLE"})

' Sheets end in $, but may have quotes around the name, this gets rid of named ranges from the list

Dim dvAsNew DataView(dt,"TABLE_NAME LIKE '%$' OR TABLE_NAME LIKE '%$'''","TABLE_NAME", DataViewRowState.Unchanged)

ForEach drAs DataRowIn dv.ToTable.Rows

If dr("TABLE_NAME").ToString.StartsWith("'")AndAlso dr("TABLE_NAME").ToString.EndsWith("'")Then

'Remove beginning quote, and ending dollar sign quote

Sheets.Add(dr("TABLE_NAME").ToString.Substring(1, Len(dr("TABLE_NAME").ToString) - 3))

Else

'Remove ending dollar sign

Sheets.Add(dr("TABLE_NAME").ToString.Substring(0, Len(dr("TABLE_NAME").ToString) - 1))

EndIf

Next

Return Sheets

EndFunction

|||

Thanks for replying Motley... But I believe your scripts were to extract the data from excel into a datatable. Well I can do that, no problem... Problem is how do I put this data into my MS SQL server?! Is there any way that I can just upload this dataset or datatable straight into the designated table in the MS SQL server?

I'm using .Net 1.1 so I can't use the SQLBulkCopy which is for 2.0 only... but still thanks bullpit.

|||

Once the data is in a dataset, you can then use a sqldataadapter or just iterate through the rows and issue the insert commands.

|||I have thought of that... but problem is that I've around 56 columns and around 600-700 records which will take quite some time to import. And I have around 15 files that need to go through this process... It'd take half a day just to import all these files...|||

Using sqldataadapter in batch mode will be the fastest way to import the data.

|||

Thanks for all help. But I've finally found my own solution to it. I used the DTS and the following code to execute the DTS package prepared to do the transfer in asp.net which did not take more than 2 seconds. Just that I need to open the excel file and adjust the formatting to assure correct importing which takes much longer time around 30 seconds.

'Update respective table (add reference to Microsoft DTSPackage Object Library)
vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

Hope this is useful to whoever facing the same problem as me.

|||

Hi KeithChang,

I am getting same problem as you mentioned on the first post message. I tried the OPENROWSET AND OPENDATASORUCE Command in .Net 1.1. But its not working.

Please tell me, how to implement this above code .

vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

This is helpful for me

Thank you

Sadheesh

How to implement a DropDownList w/o a Table

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

If you want a static set of items in the dropdown list you can define them in the property panel under the property Items you will find a collection, click the "..." and add items with value and text to appear in the dropdown list.

Best regards,
Per Salmi

|||

If you're trying to get the values from the database, you can do something like this:

Sub Page_Load(ByVal senderAs Object,ByVal eAs EventArgs)
If Not Page.IsPostBackThen GetData()End If
 End Sub
 Sub GetData()
Dim connStringAs String
 Dim conAs SqlConnection
Try connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
 con =New SqlConnection(connString)
Dim cmdAs SqlCommand =New SqlCommand()
Dim readerAs SqlDataReader
 cmd.Connection = con
 cmd.CommandText ="EXECUTE dbo.GetDepartmentCode" reader = cmd.ExecuteReaderIf reader.HasRowsThen
 While (reader.Read)
Dim newItemAs New ListItem
 newItem.Text = (reader.Item("DeptCode")).ToString
 newItem.Value = (reader.Item("DeptCode")).ToString
 deptCodeDDL.Items.Add(newItem)
End While
 End If reader.Close()Catch exAs Exception
 Response.Write(ex)
Finally con.Close() con.Dispose()End Try
 Catch exAs ApplicationException
 Response.Write("Could not load the database")
End Try
 End Sub

The ASP.Net side would like this:

<asp:DropDownList ID="deptCodeDDL" runat="server"></asp:DropDownList>

A few things to note though.

In the code I've posted, I use a stored procedure (sproc) to retrieve the data from the database. If you don't what sprocs are, you can read about themhere. Using sprocs is generally considered a better way of accessing the db.

Also, you will of course, have to replace DeptCode with the column name from where you will be getting the values.

Finally, I use a ConnectionString, which is being retrieved from the Web.config file. You should have that in you application folder.

If you don't, given below is a sample:

<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
--><configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString1" connectionString="Data Source=SERVERNAME\SQLSERVER;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 --><compilation debug="false"
</compilation>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 --><authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace.
 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 --></system.web>
</configuration>

You will have to replace SERVERNAME with you server name and DBNAME with your db name.

How to Identify Hot Tables

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.Hi

You could profile system usage, this will not give tables if you are using
stored procedures, but you could work it out knowing which procedures are
being used. Knowing the procedures being called also helps to put things
into context and allows you to examine query plans.

John

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.|||My immediate thought was "dbcc memusage" but I looked in the documentation
and it's now obsolete. Too bad. I think that it showed the top objects in
memory, which is almost exactly what you seek.

If I had to approach this myself, I might give some thought to evaluating
the locks taken out. They should give you at least some clue as to what
objects are important to SQL Server (but not all objects locked may be in
memory). I suppose I'd snapshot the lock list periodically and then develop
some processes to evaluate what was locked and summarize the objects locked
and sort of estimate how much storage of each is locked (you have table,
extend, page and row locks to consider). I don't think this will tell you
much about logs, though, which may be critical.

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.

How to hide the print button on the toolbar?

I want to force users to export my report into PDF or Excel then print from
those applications. So I want to hide the print button on the toolbar. Does
anybody know how to hide the print button on the toolbar?
Thanks a lot.Set the RS property EnableClientPrinting. The only way to set this property
is by making a SOAP request. You will then need to restart IIS to ensure
that RS picks up the new property.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"BF" <BF@.discussions.microsoft.com> wrote in message
news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
>I want to force users to export my report into PDF or Excel then print from
> those applications. So I want to hide the print button on the toolbar.
> Does
> anybody know how to hide the print button on the toolbar?
> Thanks a lot.|||Hi Daniel,
Thanks for the reply. But can you give me some detail information about how
to set the RS property EnableClientPrinting through SOAP request?
Thanks.
"Daniel Reib (MSFT)" wrote:
> Set the RS property EnableClientPrinting. The only way to set this property
> is by making a SOAP request. You will then need to restart IIS to ensure
> that RS picks up the new property.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BF" <BF@.discussions.microsoft.com> wrote in message
> news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
> >I want to force users to export my report into PDF or Excel then print from
> > those applications. So I want to hide the print button on the toolbar.
> > Does
> > anybody know how to hide the print button on the toolbar?
> >
> > Thanks a lot.
>
>|||Please read the following KB article:
http://support.microsoft.com/kb/897121/
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BF" <BF@.discussions.microsoft.com> wrote in message
news:E4CF5F41-E688-46BD-A8D1-C0D6BDE1C188@.microsoft.com...
> Hi Daniel,
> Thanks for the reply. But can you give me some detail information about
> how
> to set the RS property EnableClientPrinting through SOAP request?
> Thanks.
>
> "Daniel Reib (MSFT)" wrote:
>> Set the RS property EnableClientPrinting. The only way to set this
>> property
>> is by making a SOAP request. You will then need to restart IIS to ensure
>> that RS picks up the new property.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "BF" <BF@.discussions.microsoft.com> wrote in message
>> news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
>> >I want to force users to export my report into PDF or Excel then print
>> >from
>> > those applications. So I want to hide the print button on the toolbar.
>> > Does
>> > anybody know how to hide the print button on the toolbar?
>> >
>> > Thanks a lot.
>>

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?
Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?
|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:
> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:
> > Hi,
> > I already set select permission to certain columns in a table for a user.
> > BUt the user still can view all the columns in the Enterprise Manager/ SQL
> > Mgt Studio. HOw to hide those columns without permission ?

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQ
L
> Mgt Studio. HOw to hide those columns without permission ?