Edit 1600+ stored procedures at once

I'm looking for a way to track usage of SPs. I haven't found any TSQL tool that is already tracking that information. Long ago another DBA added an exec to the end of some SPs to log the usage to a table.

If there is a function already tracking this I'd like to know.

If not, I want to tack an exec line to the end of all the SPs but I don't want to do it by adding it 1600 times manually. Is there any trick in the scripting process I can do to make this easier?

JBSelect
Replace(sc.Text +
Case
When sc.colid = s0.maxColid Then N'Exec your_proc' + NChar(10)
Else NChar(10)
End , N'Create Proc', 'Alter Proc') + NChar(10) + N'Go'
From syscomments sc
Join sysobjects so On sc.id = so.id
Join ( Select so.id, Max(colid) As 'maxColId'
From syscomments sc
Join sysobjects so On sc.id = so.id
Where so.type = 'P'
Group By so.id
) s0 On so.id = s0.id
Where so.type = 'P'
Order By so.name, sc.colid|||This is awesome! I did have to take it half at a time due to the limitation of row size, but it appears it did just what I wanted. Thank you very much. This will help for many other projects.

John

Easy way to find connection leaks?

I, just like others, am experiencing problems with data leaks. I was wondering if anyone knows of a tool out there that can help pinpoint the pages with problems? I basically want to find the culprits!

Thanks,
--
YonahBack in the old days I usedNumega's BoundsChecker but it's for C++.

In principal with managed code (i.e., C# and VB.Net) you shouldn't have problems with memory leaks, though I'm sure there are ways to generate them.

There may be some tricks you can use to recover memory faster. The articles below may help:

Garbage Collector Basics and Performance Hints

Garbage Collection: Automatic Memory Management in the Microsoft .NET Framework|||::In principal with managed code (i.e., C# and VB.Net) you shouldn't have problems with
::memory leaks.

Interesting statement given that the question is for CONNECTION LEAKS, not memory leaks. The articles are of similar relevance :-(

I, just like others, am experiencing problems with data leaks.

No, not really.

What you can do is get a profiler (there are some good free ones around) that helps you to figur out what objects are in memory and why. If you experience a "forgot to close the connection" issue, though - you are stuck here.

What I do in general (in our O/R mapper) is handle all database connection open/close from one class - so I actually never have this issue, simply because my code is very sure things are easy here. Consider going this appraoch, concentrating this in one location.

Besides this:

You an set for SQL Server an application "name" that shows up in SQL Server's enterprise manager when you look at open connections. You COULD set this name differently for every page (like the page name), then you would know WHERE you forget to close the connection.

Easy Reporting

Hello,

I am searching for a reporting tool in which I can graphically and easily report on the cubes built using SQL Server Analysis Services. I don't want build complex reports using SSRS, but instead use something like MS Office Data Analyzer. The version of Data Analyzer I have goes back to 2001. I am able to view reports based on SQL 2005 SSAS but was wondering if there is a new version of this product or perhaps a totally new product that does the same job and is as easy.

Many Thanks,

Grace

? Grace, You might want to take a look at Excel 2007's BI capabilities when Beta 2 becomes available in approximately a couple of months time. Andrew Watt [MVP] <Grace SQL@.discussions.microsoft.com> wrote in message news:dd669009-5729-48c3-9a58-adc79200e070@.discussions.microsoft.com... Hello, I am searching for a reporting tool in which I can graphically and easily report on the cubes built using SQL Server Analysis Services. I don't want build complex reports using SSRS, but instead use something like MS Office Data Analyzer. The version of Data Analyzer I have goes back to 2001. I am able to view reports based on SQL 2005 SSAS but was wondering if there is a new version of this product or perhaps a totally new product that does the same job and is as easy. Many Thanks, Grace|||

Hi Grace

You may also like to try RSinteract from ICS (www.rsinteract.com). RSinteract allows unskilled individuals to build interactive Reporting Services Reports via an intuitive zero footpriint browser UI.

easy design tool

I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?

Can someone suggest a good beginner's book on sql server 2005?

Use Access.

Explore how an Access Data Project works (it has a SQL Server backend).

Dynamic value on Tool tip

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Quote:

Originally Posted by sajithamol

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Check the following link

http://msdn2.microsoft.com/en-us/library/system.windows.forms.tooltip.settooltip.aspx

Dynamic Tool Configuration

Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.

Thanks in advance,
Mitch

Start here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/60fe148b-a7c4-4289-ae3e-2e949fc1886c.htm

Dynamic SQL Help

HI !!

We are creating an Ad Hoc query tool for our Voters Profile system. Voters Profile Table has voting history for person. It has columns like : G04 P04 G03 P03 G02 P02 G01 P01 G00 P00 and History

G = general election P = primary election xx = represent year

G04 etc columns has value = "Y" if person voted in that year else its null

If Person A voted in General Election in year 2004 and Primary Election in 2003 then

History Column = G04P03

Now, we need to create a Ad Hoc Query where people ask for:

"Give me a list of People who votes in All 4 general election"

OR

"Give me a list of people who voted 2 times in Primary Election"

In order to do such query what we should do? Can some one help?

Hey Chum!

Please take a look at the following URL.

http://www.sommarskog.se/dyn-search.html#dynsql

Thanx,

Smith

|||

I have read the article and implemented the Dynamic Sql. We are giving users capability of Ad Hoc Query on 800,000+ records. Sometime query takes long time to return results and we have time out issue. OR sometime return results are too many (150,000) records.

We have designed Ad Hoc Query tool with idea of

http://www.aspose.com/Products/Aspose.AdHoc/Demos/

Since it is Dynamci SQL I can't have indexes. Can someone help how to reduce execution time on Dynamic SQL?

Our Dynamic SQL Looks like this

CREATE PROCEDURE TEST

(

@.whereClause varchar(4000)

)

DECLARE @.sql varchar(2000)

SET @.sql = 'SELECT FirstName, LastName, Address, City, State, Zip, County, Township, Phone

FROM VotersProfile

WHERE + ' @.whereClause

"whereClause" is build into C# code based on the columns user select in Tool

For eg @.whereClause is FirstName = 'John' AND 'County = 'Bergen' AND (Gender = 'M' OR Gender = 'F')

Can partitioned view or some other technique can help me? Its like search.. can we you some other search techniques?

Thanks

|||prbly not a performance issue but from a security issue consider using QuoteName(). Check books online for more info.
|||No my code works perfectly fine. I need some advice to improve response time for query.|||check ifthis linkhelps. Note the part about using sp_ExecuteSql instead of using EXEC()
|||

I have been using this article only. Articles states:

sp_executesql there are better odds for the query plan to be reused, because sp_executesql builds a prepared query. But you should observe that it depends on what you parameterize on. If you change table or column each time, there will not be any plan reused.

This is what the case is. So I guess my problem is not Dynamic SQL. My problem is result set and seach on almost 1 million records. How do I implement search on large record set in SQL Server?

|||

Let me give an Example:

How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?