Dreaded TEXT column

I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:

Example: table name is CREDITS

vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.

Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.

Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.

Lars

Yes, this would work, you could also leave your tables intact the way they were and implement bi-directional transactional replication.

However I assume you're on SQL 2000, correct? If you're on SQL 2005, immediate updating would work just fine if you replaced text datatype with varchar(max) datatype, it's also more optimal and efficient in terms of data storage.

|||

Thank you,

yes, we still are on SQL 2000 and won't upgrade until another 6 months. I have never heard of bi-directional replication since it wasn't mentioned anywhere. I found a great link on MS com and will take a look at it. Thanks again.

L

Dreaded "SQL Server does not exist or access denied. " Error

I have built a web application that runs on my local web server against my local SQL server and my development server, but now I'm trying to get it to work on the live server and I'm not getting a connection. Whenever I try to open the connection, I get a.

SQL Server does not exist or access denied.

error.

Here is my connection string...

Public connString as string

Dim SQLServerAsString = "xxx.xx.xx.xxx"

connString = "server=" & SQLServer & ";" _

& "database=DBName;Trusted_Connection=No;user id=xxxID;password=xxxPassword;"

myConnection2 =New SqlClient.SqlConnection(connString)

------------------

I'm starting my connection to run a datareader this way...

Dim strSQLAsString

strSQL = "SELECT ClientID, ClientName " & _

"FROM Client; "

' Create a connection to the table in the SQL database located on

' the remote computer.

Dim myCommand2AsNew SqlClient.SqlCommand(strSQL, myConnection2)

myCommand2.CommandTimeout = 1200

myConnection2.Open() 'HERE IS WHERE THE ERROR OCCURS

Dim myReaderAs SqlClient.SqlDataReader = myCommand2.ExecuteReader(CommandBehavior.CloseConnection)

While myReader.Read()

-------------------

I can communicate with the server with no problem from my local computer. I can ping the server, and I also tested it by creating a system DSN using the login and password in my application, and it finds the database with no problem. I've tried to connect using the SQL Server's server name and the IP address and both return the same result. I've also saw something in another post to set the trusted connection parameter to "yes" and that didn't work either.

I'm developing on a Windows XP Professional and I'm trying to connect to a Windows 2003 Server with SQL Server 2000 installed on the machine.

I did add the database by restoring a backup from my local server onto the live server. I don't think that should matter, but maybe it does. If anyone has any ideas, I would greatly appreciate it.

Try adding this to the end of your connection string and see if it makes a difference:

;Network Library =dbmssocn

This will force a TCP/IP connection instead of a named pipes connection.

(fromBUG: Named pipes do not work when worker process runs under ASPNET account)|||Thanks for the suggestion. Unfortunately that didn't work. Could there be some server firewall setting that is preventing the web user account from accessing the server?|||I would also try adding a port number to the end of the IP address:

Dim SQLServerAsString = "xxx.xx.xx.xxx,1433"

And I would remove Trusted_Connection=No completely. Seehttp://www.connectionstrings.comfor connection string examples. If none of that works, perhapsthe user ID and password are incorrect? Sorry, just grasping atstraws. My last recommendation is to review this KB article:Potential causes of the "SQL Server does not exists or access denied" error message. As you'll see, there are a lot of things to check.|||Thanks for your help. It looks like the problem stemmed from a security hole in Windows 2003 Server, which was shutting down the TCP ports. We applied SP4 and it seems to be working for now. Hopefully that's all it was.

Drawing X & Y axis line on Scatter Chart where cross at: 0

I have a problem with a scatter graph.

The x values range from -80 to +40
The y vaules range from -6000 to +9000
Both need the axis to cross at 0

No gridlines are required on the graph apart from the main axis'. Which is where I'm struggling. The axis lines aren't drawn for the main axis points. However if I try to use 'gridlines' I end up with lines in other parts of the graph too, which is undesired.

How can I get the axis' to have their line drawn?

Thanks,
David (HN)

Did you try setting the axis major interval to a very large value (e.g. 10000)?

-- Robert

Drawing up the spec for our Datawarehose Server

I am in the process of drawing up the spec for our data warehouse server. The
setup that I am having to cater for is a server that will hold:
1) a replicated copy of the tables that are going to be used to extract that
data from as we have 22 offices this will be 22 databases. (ranging in size
from 500 Mb to 20 Gb)
2) A staging database
3) The Data warehouse database
4) Cognos OLAP Cubes
In terms of the hardware I am looking to get a dual processor box (with the
capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to limit
sql to a portion of this leaving a good amount available for the OS and the
OLAP Cubes.
The part that I am stuck on is the RAID config. It is envisaged that the
cubes will be refreshed once a week.
So far the RAID Solution I have come up with is below:
RAID 1 - For the OS, SQL Binaries etc.
RAID 5 - For the Database files
RAID 5 - For the Transaction Logs
RAID 5 - For the Cognos OLAP Cubes
What are your thoughts on the above?
Id stay away from RAID 5 (you're gonna hear this a lot I suspect).
If you want performance, you should try to go with RAID 1+0 AT Least for the
Data and Logs.
Get RAID Controller(s) with Battery Backed Cache as well.
Hope this helps
Greg Jackson
PDX, Oregon
|||So you appear to be looking for a one size fits all solution. This might
work for a short while during the ramp up phase but most DW installations
will split the functions apart as they run into contension.
From the notes:
You are planning on having the following on one server.
22 source databases with a total space used between 11GB and 440GB
SQL replication running against all 22 source databases
1 stagging DB
1 DW DB
Cognos PowerPlay
I assume IIS and some of the other Cognos products unless you are planning
on using the network to copy the cubes or direct LAN connect from clients.
What ETL tool will you be using DTS, straight TSQL, or third party product?
If third party product where will it be installed?
With this much source data, is there an estimate as to how large the DW
database will be?
Unless you are only popluating a very small amout of data this configuration
will be overloaded inside six months and the first two to three months is
usually used for initial development and limited user testing.
Sorry for the bad news but better to know up front...
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:BA7C6084-ACD6-4B55-9125-0165108B7D9B@.microsoft.com...
>I am in the process of drawing up the spec for our data warehouse server.
>The
> setup that I am having to cater for is a server that will hold:
> 1) a replicated copy of the tables that are going to be used to extract
> that
> data from as we have 22 offices this will be 22 databases. (ranging in
> size
> from 500 Mb to 20 Gb)
> 2) A staging database
> 3) The Data warehouse database
> 4) Cognos OLAP Cubes
> In terms of the hardware I am looking to get a dual processor box (with
> the
> capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to
> limit
> sql to a portion of this leaving a good amount available for the OS and
> the
> OLAP Cubes.
> The part that I am stuck on is the RAID config. It is envisaged that the
> cubes will be refreshed once a week.
> So far the RAID Solution I have come up with is below:
> RAID 1 - For the OS, SQL Binaries etc.
> RAID 5 - For the Database files
> RAID 5 - For the Transaction Logs
> RAID 5 - For the Cognos OLAP Cubes
> What are your thoughts on the above?
>

Drawing up the spec for our Datawarehose Server

I am in the process of drawing up the spec for our data warehouse server. Th
e
setup that I am having to cater for is a server that will hold:
1) a replicated copy of the tables that are going to be used to extract that
data from as we have 22 offices this will be 22 databases. (ranging in size
from 500 Mb to 20 Gb)
2) A staging database
3) The Data warehouse database
4) Cognos OLAP Cubes
In terms of the hardware I am looking to get a dual processor box (with the
capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to limit
sql to a portion of this leaving a good amount available for the OS and the
OLAP Cubes.
The part that I am stuck on is the RAID config. It is envisaged that the
cubes will be refreshed once a week.
So far the RAID Solution I have come up with is below:
RAID 1 - For the OS, SQL Binaries etc.
RAID 5 - For the Database files
RAID 5 - For the Transaction Logs
RAID 5 - For the Cognos OLAP Cubes
What are your thoughts on the above?Id stay away from RAID 5 (you're gonna hear this a lot I suspect).
If you want performance, you should try to go with RAID 1+0 AT Least for the
Data and Logs.
Get RAID Controller(s) with Battery Backed Cache as well.
Hope this helps
Greg Jackson
PDX, Oregon|||So you appear to be looking for a one size fits all solution. This might
work for a short while during the ramp up phase but most DW installations
will split the functions apart as they run into contension.
From the notes:
You are planning on having the following on one server.
22 source databases with a total space used between 11GB and 440GB
SQL replication running against all 22 source databases
1 stagging DB
1 DW DB
Cognos PowerPlay
I assume IIS and some of the other Cognos products unless you are planning
on using the network to copy the cubes or direct LAN connect from clients.
What ETL tool will you be using DTS, straight TSQL, or third party product?
If third party product where will it be installed?
With this much source data, is there an estimate as to how large the DW
database will be?
Unless you are only popluating a very small amout of data this configuration
will be overloaded inside six months and the first two to three months is
usually used for initial development and limited user testing.
Sorry for the bad news but better to know up front...
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:BA7C6084-ACD6-4B55-9125-0165108B7D9B@.microsoft.com...
>I am in the process of drawing up the spec for our data warehouse server.
>The
> setup that I am having to cater for is a server that will hold:
> 1) a replicated copy of the tables that are going to be used to extract
> that
> data from as we have 22 offices this will be 22 databases. (ranging in
> size
> from 500 Mb to 20 Gb)
> 2) A staging database
> 3) The Data warehouse database
> 4) Cognos OLAP Cubes
> In terms of the hardware I am looking to get a dual processor box (with
> the
> capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to
> limit
> sql to a portion of this leaving a good amount available for the OS and
> the
> OLAP Cubes.
> The part that I am stuck on is the RAID config. It is envisaged that the
> cubes will be refreshed once a week.
> So far the RAID Solution I have come up with is below:
> RAID 1 - For the OS, SQL Binaries etc.
> RAID 5 - For the Database files
> RAID 5 - For the Transaction Logs
> RAID 5 - For the Cognos OLAP Cubes
> What are your thoughts on the above?
>sql

Drawing line between table rows and columns

how to insert line between rows and columns in reporting services 2005Couldn't you just set the borderstyle top, bottom, left, or right according to where you wanted the lines?

Drawing line at last record

Hi.
I have a table containing two groups and a detail area.My report has multiple pages.I want to draw line after the last record on every page.I think that expression will be written in record's bottom expression area.How can I choose the last record in every page.Or is there another way to do this.Could you help me please?why wont u draw a line on page footer? when u draw a line on page footer it will diplay on the bottom of each page.|||Thanks but it didn't work
I drawed horizontal line across the page footer.
But there is several blank rows between the last record and the line in page footer.And this is never looking good.If you have another idea about this please share with me.

Drawing error when rendering report in PDF

Hi
We had run SQL Server Report Services Service PAck2 and after installing that we are not able to open reports in PDF format. it gives drawing error .

Any help in this regard is appreciated.What the exact error message ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Hi Jens
The exact error message would be "A Drawing Error Occured"

As I had told we are having SQL Server 2000, SQL Server 2000 Reporting Services. Somebody in the forum suggested to install service pack2 which we did for earlier another issue where it was printing extra pages in both Excel and PDF. So after installing sp2 the PDF got messed up.

Any help in this regard is appreciated

Thanks|||

Hi,

This is probably an error in SP2, you can get a fix from Microsoft by contacting them on the mentioned ways here: http://support.microsoft.com/kb/912424/en-us

This is a PDF Error, probably a ressources not reachable or an out-of-memory error.

http://support.microsoft.com/kb/912424/en-us

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi
Thanks Jens the link helped us solve the issue.There is another related issue which if you could show us a little direction in solving it. Here is the issue

We are working on SQL Server Reporting Services 2000. We designed a

report and exported the same into excel. The original report has lets

say one page information, then before printing when we see print

preview it shows up an extra blank page and similarly if the there are

two pages in the report ,in the preview we are seeing 4 pages ie two

extra blank pages. Any help to solve this issue would be appreciated.

The same thing we are exporting in PDF format also and here to same

issue.

We are in need of a solution. It would be of great help if you could help us in getting a solution to this issue.
We have SQL Server 2000 and SQL Server 2000 Reporting services and Microsoft Office 2003

Thanks
|||Thats a really common problem. You defined the report margins to big. If the report exceeds the page format it will break the report to multiple pages. So make sure that the visible area of your report (and the additional borders) does not exceed the format specified on the report properties. Sizing the report will let you keep your report on one page per report page. Its something like playing around with the margins and sizes to fit all your data on one page.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Drawing Circles and line and text note in report

I am not sure if this is possible with the reporting tool, but I thought I
would ask. In MS Word, you are able to select an option to â'drawâ' a line or
object on the screen. thought that it would be useful to allow a user to add
a circle or line and a text note to the report prior to printing it. I want
to know whether this is possibleExample: if I wanted to circle the name of
one employee on the report before printing it.
Is this possible with the SQL report writer?
Thanks!Currently there is no feature in RS for users to be able to modify the report in anyway after the report is generated, but would like to have this feature in the later versions.
>--Original Message--
>I am not sure if this is possible with the reporting tool, but I thought I >would ask. In MS Word, you are able to select an option to =E2?odraw=E2?=9D a line or >object on the screen. thought that it would be useful to allow a user to add >a circle or line and a text note to the report prior to printing it. I want >to know whether this is possibleExample: if I wanted to circle the name of >one employee on the report before printing it.
>Is this possible with the SQL report writer?
> >Thanks!
> >
>.
>

drawbacks of Saving documents to Sql server

Hi all,
..Net 1.1
Sql server 2000
i am working on a document managment module of my application, all i want to
do here is save the documents uplaoded by the user from my winforms over
webservices application\ webforms applicatoin to sql server 2000. I have been
told saving it on the webserver's file system might be a better way but seems
that microsft is gong more towars saving everthing in sql server. So my
question to the gurus out there is
1. does any one really have any hands on experience saving documents to sql
server 2000 and if so please tell me if u have any kind of performance issues
with this.
2. i was told that sql server is kind of a little slow in streaming out the
documents from the database, expecially bigger ones when compared to the
retriving it from the web server's file system, is this true ?
3. please tell me any other problesm that u might have faced when saving\
retriving files from the database.
4. is sql server 2005 any better in saving and retriving documents from the
database.
thanks
sameer
Answered in .programming.
Please do not post the same message independently to multiple newsgroups.
sql

drawbacks of Saving documents to Sql server

Hi all,
.Net 1.1
Sql server 2000
i am working on a document managment module of my application, all i want to
do here is save the documents uplaoded by the user from my winforms over
webservices application\ webforms applicatoin to sql server 2000. I have been
told saving it on the webserver's file system might be a better way but seems
that microsft is gong more towars saving everthing in sql server. So my
question to the gurus out there is
1. does any one really have any hands on experience saving documents to sql
server 2000 and if so please tell me if u have any kind of performance issues
with this.
2. i was told that sql server is kind of a little slow in streaming out the
documents from the database, expecially bigger ones when compared to the
retriving it from the web server's file system, is this true ?
3. please tell me any other problesm that u might have faced when saving\
retriving files from the database.
4. is sql server 2005 any better in saving and retriving documents from the
database.
thanks
sameerAnswered in .programming.
Please do not post the same message independently to multiple newsgroups.

drawbacks of Saving documents to Sql server

Hi all,
.Net 1.1
Sql server 2000
i am working on a document managment module of my application, all i want to
do here is save the documents uplaoded by the user from my winforms over
webservices application\ webforms applicatoin to sql server 2000. I have bee
n
told saving it on the webserver's file system might be a better way but seem
s
that microsft is gong more towars saving everthing in sql server. So my
question to the gurus out there is
1. does any one really have any hands on experience saving documents to sql
server 2000 and if so please tell me if u have any kind of performance issue
s
with this.
2. i was told that sql server is kind of a little slow in streaming out the
documents from the database, expecially bigger ones when compared to the
retriving it from the web server's file system, is this true ?
3. please tell me any other problesm that u might have faced when saving\
retriving files from the database.
4. is sql server 2005 any better in saving and retriving documents from the
database.
thanks
sameerAnswered in .programming.
Please do not post the same message independently to multiple newsgroups.

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file an
d
> maybe the tempdb? Most of the load in my database is due to read operation
s.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file and
> maybe the tempdb? Most of the load in my database is due to read operations.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

drawbacks of full text search of sql server 2005

hi,
i m building a big websites which has larger database.so i want to know that what are the drawbacks and benefits of full text search of sql server 2005.
shikha

Quote:

Originally Posted by shikha srivastava

hi,
i m building a big websites which has larger database.so i want to know that what are the drawbacks and benefits of full text search of sql server 2005.
shikha

Hi there,

Not so sure of that issue yet, in fact just got the chance to install MSSQL 2005 today.

Need some time to get familiar with it's features, since it's very new i doubt you can get any feedback within this few weeks. Hang on, give some time, let others to play around with it to learn it's advantages as well as disadvantages.

Good luck & Take care.sql

Draw rectancle with rounded corners

Is there a way to draw a rectangle that has rounded corners?
Thanks.On Jun 1, 9:36 am, "Tim Kelley" <tkel...@.company.com> wrote:
> Is there a way to draw a rectangle that has rounded corners?
> Thanks.
As far as I know, there is not. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Draw marking line on Chart

I want to mark a value on the x-axis of a (stacked) bar chart. (So I want a horizontal line that is fixed on a chart.)

What I tried so far is adding an expression that says: =260 to the dataset, and I added that field to the Data Area of the chart. Then I plotted that value as a line.

This approach leaves me with 2 problems:
1. When the category grouping only has one group, there is no line, just a dot (or another marker you can select).
2. Because of the extra field in the Data Area of the chart, the legend holds useless information.

Isn't there another way to plot a horizontal line on a fixed value of the X-axis ?

I also took some screenshots so you can see for yourself. The charts display the ManDays per Year. The green color are the productive days and the blue are the non productive. I made charts of how they look right now, and how I want them to look. http://users.telenet.be/master/ict/charts.html

Owkey I couldn't make the line go all the way through but I managed to edit the legend by an example of Brain Welcker on custom legends. It can be found here: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

So to draw a straight marking line on a chart: Add a field to the dataset containing the value where there has to be marked. Add that field to the data area of the chart. Set it so it will be displayed as a line. Remove the legend. Add a custom legend.

|||

My first question is how you plotted that field values - let's say we say that field name is Target Value right, how you plot that value as a Line in Bar Graph, I can't see in the Data area of that chart.

Can you explain in detail.

Thanks

|||

When I click twice on the chart, I see 3 area's: The Data, Series and Category area. In my data area there are two fields. One field is a sum of a database field, so that will create the bars.

The other field is a calculated field with expression "= 260" (because that is the value I want to mark on the chart) the field is called Marker. This field is added to the dataset which is used by the chart.

Then when you have dragged that field onto the data area, double click it and check these settings:
On the Values tab: the value property is =Fields!Marker.Value (when field is called marker)
On the Appearance tab: check the "show markers" and "plot data as line" checkboxes. This will draw a line with height= 260 on the x-axis (horizontal).

I hope this is clear enough, otherwise let me know ;)

|||

I am using Crystal Report 8.5... you are doing the same activity on Sql Server Reporting service ?

Thanks

NOOR

Draw many to many relationship diagram

Does anyone how to draw many to many relationship diagram in Enterprise
Manager? It only allow me to draw one to many, but does not allow me to dra
w
many to many relationship.
Thanks!A many-to-many relationship in a relational model requires an intersection
table. For example:
People --> AssignedRoom <-- Rooms
AssignedRoom handles the intersection of many people assigned to many rooms,
with some being assigned to the same room.
RLF
"she" <she@.discussions.microsoft.com> wrote in message
news:65D409AD-1FC2-4ADB-8DB3-A61F7B6D1A80@.microsoft.com...
> Does anyone how to draw many to many relationship diagram in Enterprise
> Manager? It only allow me to draw one to many, but does not allow me to
> draw
> many to many relationship.
> Thanks!|||Thank you, Russell.
"Russell Fields" wrote:

> A many-to-many relationship in a relational model requires an intersection
> table. For example:
> People --> AssignedRoom <-- Rooms
> AssignedRoom handles the intersection of many people assigned to many room
s,
> with some being assigned to the same room.
>
> RLF
> "she" <she@.discussions.microsoft.com> wrote in message
> news:65D409AD-1FC2-4ADB-8DB3-A61F7B6D1A80@.microsoft.com...
>
>

Draw Horizontal Line at Threshold

I have a table in reporting services. I have a field that calcuates a
percentage and the report is sorted in ascending order by this
percentage. I want to draw a single red horizontal line at a
threshold, let's say 95% so that any record lower than 95% would be
above the line and any record higher would be below the line. How can
I do this?On Apr 4, 6:54 am, robertpet...@.hotmail.com wrote:
> I have a table in reporting services. I have a field that calcuates a
> percentage and the report is sorted in ascending order by this
> percentage. I want to draw a single red horizontal line at a
> threshold, let's say 95% so that any record lower than 95% would be
> above the line and any record higher would be below the line. How can
> I do this?
I would suggest adding an extra query/stored procedure return field/
column to include in the report that is used as a flag for the
percentages. Have the flag set for the minimum row at or above 95%.
Then in the report, set the table properties (F4) (BorderColor->Top
and BorderStyle->Top) according to that flag. Something like these
expressions should work:
For BorderColor:Top:
=iif(Fields!Flag.Value = 1, "Red", "White")
For BorderStyle:Top:
=iif(Fields!Flag.Value = 1, "Solid", "None")
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Draw Bitmap from Points in report

Hi, i have a database field that has a drawing stored as points, for example....

(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)

In my VB.NET application, i can take those points and recreate the image. I need to do the same in reporting services... i am trying to replace a legacy ACCESS report, that had the drawing object.

How can i recreate the image in reporting services? is there an easy way to do so? i believe i tried to create a class and tried to reference it and call the function to return the data as an image, but i got a system.drawing not found error...

There are at least two options:

1. use the built-in charts with chart type = scatter. Note: the scatter chart must have a category grouping based on a unique value (e.g. data point id in your case). I attached a small sample report to the bottom of this posting to show the idea.

2. or draw the image yourself and use it in Reporting Services. However, make sure to follow these steps:

2.1. Design and implement a custom assembly to generate images.
The custom assembly must retrieve the data on its own, take care of grouping/sorting the data, and generating the chart image.
Note: The custom assembly has to return the image as byte[]. It cannot return it as a System.Drawing.Image. You can often convert a System.Drawing.Image object with code similar to the following.
System.IO.MemoryStream renderedImage = new MemoryStream();
myChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();

2.2. Add an image to the report.
Set the image type to Database. If the generated image is a bitmap in the PNG image format, set the image mimetype property to “image/png.” For the image value property, use an expression like the following.
=MyCustomAssembly.GenerateChart()

2.3. View the report in Report Designer Preview view to verify that the report is working correctly.
Note: In a default configuration, custom assemblies run in FullTrust in Report Designer preview. Hence, operations that require certain code access security permissions (such as file input/output, data provide access, etc.) are automatically granted these permissions in Fulltrust.

2.4. Deploy the custom assembly on a report server.
Make sure that the security policy configuration of the report server grants sufficient permissions to your custom assembly at runtime; otherwise the image generation will fail. For more information, see Understanding Code Access Security in Reporting Services (http://msdn2.microsoft.com/en-us/library/ms155108.aspx) in SQL Server 2005 Books Online.

-- Robert

=========================================

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>67061ec4-b72e-4a04-a7f3-714536211b9c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Line</Subtype>
<Title />
<Height>2in</Height>
<CategoryAxis>
<Axis>
<Title />
<Style>
<Format>MMM dd</Format>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<MajorInterval>5</MajorInterval>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Scatter</Type>
<Width>3.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!MeasurementId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=CDate(Fields!TimeStamp.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!MeasurementId.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>EarthTones</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=CDate(Fields!TimeStamp.Value)</Value>
</DataValue>
<DataValue>
<Value>=Fields!Value.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>4.5pt</Default>
</BorderWidth>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>a068be44-d5ee-4243-91ed-445f05622d2c</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as MeasurementId, '07/16/2006' as TimeStamp, 10 as Value union
select 2 as MeasurementId, '07/17/2006' as TimeStamp, 10 as Value union
select 3 as MeasurementId, '07/17/2006' as TimeStamp, 8 as Value union
select 4 as MeasurementId, '07/18/2006' as TimeStamp, 8 as Value union
select 5 as MeasurementId, '07/19/2006' as TimeStamp, 10 as Value union
select 6 as MeasurementId, '07/19/2006' as TimeStamp, 12 as Value union
select 7 as MeasurementId, '07/20/2006' as TimeStamp, 12 as Value union
select 8 as MeasurementId, '07/21/2006' as TimeStamp, 12 as Value union
select 9 as MeasurementId, '07/21/2006' as TimeStamp, 9 as Value union
select 10 as MeasurementId, '07/22/2006' as TimeStamp, 9 as Value</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="MeasurementId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>MeasurementId</DataField>
</Field>
<Field Name="TimeStamp">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TimeStamp</DataField>
</Field>
<Field Name="Value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>3.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Thanks for posting a reply ill try it first thing in the morning....

The drawing is collected on a handheld, its actually a signature (but it can be a drawing as well, so lots of points....), the coordinates of the drawing are saved in an xml file along with other data, and is then inserted into the database when the device is synced....

ill try the chart way first, and then the custom assembly again. when i tried it last time it was giving me the bitmap not defined error, i dont remember if i was returning the data as an image or as a byte... :) ill give it a try and post back here so that someone else can also make use of your help!

thank you.

|||omg...... thats for the reply and the hints, i doublechecked everything in your 2nd suggestion with what i had already done, the first one wasnt feasible... and after checking all your suggestions i realised that it had been working all this time!!!., it just doesnt work in DEBUG mode... :( when i ran the application itself outside of VS, it ran with no problems!!!!|||Make sure you have copied your assembly to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder.

Draw Bitmap from Points in report

Hi, i have a database field that has a drawing stored as points, for example....

(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)

In my VB.NET application, i can take those points and recreate the image. I need to do the same in reporting services... i am trying to replace a legacy ACCESS report, that had the drawing object.

How can i recreate the image in reporting services? is there an easy way to do so? i believe i tried to create a class and tried to reference it and call the function to return the data as an image, but i got a system.drawing not found error...

There are at least two options:

1. use the built-in charts with chart type = scatter. Note: the scatter chart must have a category grouping based on a unique value (e.g. data point id in your case). I attached a small sample report to the bottom of this posting to show the idea.

2. or draw the image yourself and use it in Reporting Services. However, make sure to follow these steps:

2.1. Design and implement a custom assembly to generate images.
The custom assembly must retrieve the data on its own, take care of grouping/sorting the data, and generating the chart image.
Note: The custom assembly has to return the image as byte[]. It cannot return it as a System.Drawing.Image. You can often convert a System.Drawing.Image object with code similar to the following.
System.IO.MemoryStream renderedImage = new MemoryStream();
myChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();

2.2. Add an image to the report.
Set the image type to Database. If the generated image is a bitmap in the PNG image format, set the image mimetype property to “image/png.” For the image value property, use an expression like the following.
=MyCustomAssembly.GenerateChart()

2.3. View the report in Report Designer Preview view to verify that the report is working correctly.
Note: In a default configuration, custom assemblies run in FullTrust in Report Designer preview. Hence, operations that require certain code access security permissions (such as file input/output, data provide access, etc.) are automatically granted these permissions in Fulltrust.

2.4. Deploy the custom assembly on a report server.
Make sure that the security policy configuration of the report server grants sufficient permissions to your custom assembly at runtime; otherwise the image generation will fail. For more information, see Understanding Code Access Security in Reporting Services (http://msdn2.microsoft.com/en-us/library/ms155108.aspx) in SQL Server 2005 Books Online.

-- Robert

=========================================

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>67061ec4-b72e-4a04-a7f3-714536211b9c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Line</Subtype>
<Title />
<Height>2in</Height>
<CategoryAxis>
<Axis>
<Title />
<Style>
<Format>MMM dd</Format>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<MajorInterval>5</MajorInterval>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Scatter</Type>
<Width>3.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!MeasurementId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=CDate(Fields!TimeStamp.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!MeasurementId.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>EarthTones</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=CDate(Fields!TimeStamp.Value)</Value>
</DataValue>
<DataValue>
<Value>=Fields!Value.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>4.5pt</Default>
</BorderWidth>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>a068be44-d5ee-4243-91ed-445f05622d2c</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as MeasurementId, '07/16/2006' as TimeStamp, 10 as Value union
select 2 as MeasurementId, '07/17/2006' as TimeStamp, 10 as Value union
select 3 as MeasurementId, '07/17/2006' as TimeStamp, 8 as Value union
select 4 as MeasurementId, '07/18/2006' as TimeStamp, 8 as Value union
select 5 as MeasurementId, '07/19/2006' as TimeStamp, 10 as Value union
select 6 as MeasurementId, '07/19/2006' as TimeStamp, 12 as Value union
select 7 as MeasurementId, '07/20/2006' as TimeStamp, 12 as Value union
select 8 as MeasurementId, '07/21/2006' as TimeStamp, 12 as Value union
select 9 as MeasurementId, '07/21/2006' as TimeStamp, 9 as Value union
select 10 as MeasurementId, '07/22/2006' as TimeStamp, 9 as Value</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="MeasurementId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>MeasurementId</DataField>
</Field>
<Field Name="TimeStamp">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TimeStamp</DataField>
</Field>
<Field Name="Value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>3.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Thanks for posting a reply ill try it first thing in the morning....

The drawing is collected on a handheld, its actually a signature (but it can be a drawing as well, so lots of points....), the coordinates of the drawing are saved in an xml file along with other data, and is then inserted into the database when the device is synced....

ill try the chart way first, and then the custom assembly again. when i tried it last time it was giving me the bitmap not defined error, i dont remember if i was returning the data as an image or as a byte... :) ill give it a try and post back here so that someone else can also make use of your help!

thank you.

|||omg...... thats for the reply and the hints, i doublechecked everything in your 2nd suggestion with what i had already done, the first one wasnt feasible... and after checking all your suggestions i realised that it had been working all this time!!!., it just doesnt work in DEBUG mode... :( when i ran the application itself outside of VS, it ran with no problems!!!!|||Make sure you have copied your assembly to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder.

Draw Axis on Barchart

Hi,

I have a little problem with my barchart.
The chart shows negative and positive values and i want a simple vertical black line at 0.
But I havent found any solutions for this problem yet that satisfies me.

What I tried:
- Draw a normal line and place it above the graph -> doesnt work in all renderers
(Overlapping Reportitems are not supported ....)

-with the parameter "Cross At"
i have labels on the axis and if i "cross at" 0, the labels also move into the middle and overlap
with the bars/point labels

- with Gridlines (so far the best solution)
Chart with a fixed range (for example -100 to +100) and set the gridline interval to 100
this prints 3 Gridlines, 1st at -100, 2nd at 0 (what I want) and a 3rd at +100 - that would be ok for me
BUT i need a variable chart range!
so if the chart range goes from -100 to +200, i have a gridline at +100 crossing all bars !!

here are some pictures of my problem

what i want:
http://www.hmayer.net/show_image.php?id=4387&scaled&scalesize=800&nocount=y

what i got:
http://www.hmayer.net/show_image.php?id=4386&scaled&scalesize=800&nocount=y

any other ideas?

thanks,
GerhardYou can accomplish this with the following expression, if you are willing to have the min and max for the axis be the same absolute value. The expression calculates the largest value, in absolute terms, and sets the min(with negation), max and major interval properties on the Y-axis to this expression.

Min:

=-Math.Max(Max(Fields!FieldName.Value), Math.Abs(Min(Fields!FieldName.Value)))

Max:
=Math.Max(Max(Fields!FieldName.Value), Math.Abs(Min(Fields!FieldName.Value)))

Major Interval:
=Math.Max(Max(Fields!FieldName.Value), Math.Abs(Min(Fields!FieldName.Value)))

Note: Expressions for these properties are not su supported in RS 2000

Another approach would be to set the Cross At to 0 and not use tick marks or axis labels on the axis. Instead of using the labels, you could use a table placed beside the chart that contains these values. It may be tricky to get the labels line up correctly--make sure to sort the category group and the table the same way.

Ian

Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
David
are there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>
|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>
|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon
|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>
|||Where do I check for open connections?
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>
|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>
|||sp_Who2 is a good start
Greg Jackson
PDX, Oregon
|||sp_Who2 was a great help! Turns out, someone had checked the AutoShrink
checkbox and it was running and grinding everything to a halt.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
Davidare there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>|||Where do I check for open connections?
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>|||sp_Who2 is a good start
Greg Jackson
PDX, Oregon|||sp_Who2 was a great help! Turns out, someone had checked the AutoShrink
checkbox and it was running and grinding everything to a halt.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
Davidare there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes less
>> that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes less
>> that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
--
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Since it is SBS I have to assume you have other apps running on the
>> server than Sql Server. As such you should set a MAX memory setting in
>> SQL Server to assure the other apps and the OS have at least some memory
>> to use. I rarely see SBS servers where the apps are not fighting each
>> other for memory. How much do you have and how is the memory configured
>> for SQL Server?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David C" <dlchase@.lifetimeinc.com> wrote in message
>> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes
>> less that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>>
>

drastic change in speed

I was executing sp, which was taking time. I then dropped it and recreated
it. It started executing very fast.
Can Anyone have idea why this happens and what exactly happen when we drop
and recreate sp.When you drop SP it will cause to recompilation ( generating a new execution
plan). The same thing you might achive by creating a stored procedure WITH
RECOMPILE option
For more details see BOL
"Vikram" <aa@.aa> wrote in message
news:%23QQDPBcQGHA.1676@.TK2MSFTNGP14.phx.gbl...
>I was executing sp, which was taking time. I then dropped it and recreated
> it. It started executing very fast.
> Can Anyone have idea why this happens and what exactly happen when we drop
> and recreate sp.
>|||Also, before recompiling all the stored procedures, look into running DBCC
UPDATEUSAGE for critical tables. This will incure that the new execution
plans are compiled using the latest usage statistics.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euJAtBdQGHA.2012@.TK2MSFTNGP14.phx.gbl...
> When you drop SP it will cause to recompilation ( generating a new
> execution plan). The same thing you might achive by creating a stored
> procedure WITH RECOMPILE option
> For more details see BOL
>
>
> "Vikram" <aa@.aa> wrote in message
> news:%23QQDPBcQGHA.1676@.TK2MSFTNGP14.phx.gbl...
>

drastic change in response time

All processes have slowed to a crawl - 1 user running a
simple query in ARC brings all app's residing on this
machine to a crawl. Machine resourses are abundant and
available. I know these are VERY sparse details... I'll
provide whatever info I can if someone can let us know
something. ThanxThis is a multi-part message in MIME format.
--=_NextPart_000_04C0_01C3CE1E.3D63B070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Some suggestions:
1. Run DBCC OPENTRAN for your database. See what SPID has the longest
running txn. Run DBCC INPUTBUFFER on the SPID.
2. Run sp_lock and see who is putting up the most locks.
3. Based on the above, consider killing the offending SPID.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
All processes have slowed to a crawl - 1 user running a
simple query in ARC brings all app's residing on this
machine to a crawl. Machine resourses are abundant and
available. I know these are VERY sparse details... I'll
provide whatever info I can if someone can let us know
something. Thanx
--=_NextPart_000_04C0_01C3CE1E.3D63B070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Some suggestions:
1. Run DBCC OPENTRAN =for your database. See what SPID has the longest running txn. Run =DBCC INPUTBUFFER on the SPID.
2. Run sp_lock and =see who is putting up the most locks.
3. Based on =the above, consider killing the offending SPID.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"David" wrote in message news:07aa01c3ce47$4c=c7aca0$a001280a@.phx.gbl...All processes have slowed to a crawl - 1 user running a simple query in =ARC brings all app's residing on this machine to a crawl. Machine resourses are abundant and available. I know these are VERY =sparse details... I'll provide whatever info I can if someone can let us =know something. Thanx

---

--=_NextPart_000_04C0_01C3CE1E.3D63B070--|||What are your server specs? Can you run other operations on the server
quickly(ie. is it SQL or the whole box)?
Other things to list --
mem usage
Free pages
IO
CPU usage
Cost of the query in question
We have had random slow and stoppage of performance on SQL2K.
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> All processes have slowed to a crawl - 1 user running a
> simple query in ARC brings all app's residing on this
> machine to a crawl. Machine resourses are abundant and
> available. I know these are VERY sparse details... I'll
> provide whatever info I can if someone can let us know
> something. Thanx|||1gb RAM
4gb harddrive available (about 40%)
2 - 933 processors
ALL application are affected. Task Mgr show both 933
processors at 100 capacity. All seem fine until we run a
simple SQL query on an ARC database. THAT qry soaks up
100% RAM.
>--Original Message--
>What are your server specs? Can you run other operations
on the server
>quickly(ie. is it SQL or the whole box)?
>Other things to list --
>mem usage
>Free pages
>IO
>CPU usage
>Cost of the query in question
>We have had random slow and stoppage of performance on
SQL2K.
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
>> All processes have slowed to a crawl - 1 user running a
>> simple query in ARC brings all app's residing on this
>> machine to a crawl. Machine resourses are abundant and
>> available. I know these are VERY sparse details... I'll
>> provide whatever info I can if someone can let us know
>> something. Thanx
>
>.
>|||What is the cost of the query? Can you run --
SET SHOWPLAN_ALL ON
GO
<QUERY IN QUESTION>
and post the output.
Also there is a SQL bug on SQL2K that will create mem leaks when jumping
between DBs in a query, is that happening? What version of SQL and SP are
you on?
"DAvid" <anonymous@.discussions.microsoft.com> wrote in message
news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> 1gb RAM
> 4gb harddrive available (about 40%)
> 2 - 933 processors
> ALL application are affected. Task Mgr show both 933
> processors at 100 capacity. All seem fine until we run a
> simple SQL query on an ARC database. THAT qry soaks up
> 100% RAM.
>
> >--Original Message--
> >What are your server specs? Can you run other operations
> on the server
> >quickly(ie. is it SQL or the whole box)?
> >
> >Other things to list --
> >mem usage
> >Free pages
> >IO
> >CPU usage
> >Cost of the query in question
> >
> >We have had random slow and stoppage of performance on
> SQL2K.
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> >> All processes have slowed to a crawl - 1 user running a
> >> simple query in ARC brings all app's residing on this
> >> machine to a crawl. Machine resourses are abundant and
> >> available. I know these are VERY sparse details... I'll
> >> provide whatever info I can if someone can let us know
> >> something. Thanx
> >
> >
> >.
> >|||Please elaborate on this "bug"., KB articles, PSS cases, thanks.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kevin Brooks" <jeepnreb@.yahoo.com> wrote in message
news:uL9#ZMlzDHA.2456@.TK2MSFTNGP10.phx.gbl...
> What is the cost of the query? Can you run --
> SET SHOWPLAN_ALL ON
> GO
> <QUERY IN QUESTION>
> and post the output.
> Also there is a SQL bug on SQL2K that will create mem leaks when jumping
> between DBs in a query, is that happening? What version of SQL and SP are
> you on?
>
> "DAvid" <anonymous@.discussions.microsoft.com> wrote in message
> news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> > 1gb RAM
> > 4gb harddrive available (about 40%)
> > 2 - 933 processors
> >
> > ALL application are affected. Task Mgr show both 933
> > processors at 100 capacity. All seem fine until we run a
> > simple SQL query on an ARC database. THAT qry soaks up
> > 100% RAM.
> >
> >
> > >--Original Message--
> > >What are your server specs? Can you run other operations
> > on the server
> > >quickly(ie. is it SQL or the whole box)?
> > >
> > >Other things to list --
> > >mem usage
> > >Free pages
> > >IO
> > >CPU usage
> > >Cost of the query in question
> > >
> > >We have had random slow and stoppage of performance on
> > SQL2K.
> > >
> > >"David" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> > >> All processes have slowed to a crawl - 1 user running a
> > >> simple query in ARC brings all app's residing on this
> > >> machine to a crawl. Machine resourses are abundant and
> > >> available. I know these are VERY sparse details... I'll
> > >> provide whatever info I can if someone can let us know
> > >> something. Thanx
> > >
> > >
> > >.
> > >
>|||Will have to respond tomorrow - office is closed for the
day
>--Original Message--
>What is the cost of the query? Can you run --
>SET SHOWPLAN_ALL ON
>GO
><QUERY IN QUESTION>
>and post the output.
>Also there is a SQL bug on SQL2K that will create mem
leaks when jumping
>between DBs in a query, is that happening? What version
of SQL and SP are
>you on?
>
>"DAvid" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
>> 1gb RAM
>> 4gb harddrive available (about 40%)
>> 2 - 933 processors
>> ALL application are affected. Task Mgr show both 933
>> processors at 100 capacity. All seem fine until we run
a
>> simple SQL query on an ARC database. THAT qry soaks up
>> 100% RAM.
>>
>> >--Original Message--
>> >What are your server specs? Can you run other
operations
>> on the server
>> >quickly(ie. is it SQL or the whole box)?
>> >
>> >Other things to list --
>> >mem usage
>> >Free pages
>> >IO
>> >CPU usage
>> >Cost of the query in question
>> >
>> >We have had random slow and stoppage of performance on
>> SQL2K.
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
>> >> All processes have slowed to a crawl - 1 user
running a
>> >> simple query in ARC brings all app's residing on this
>> >> machine to a crawl. Machine resourses are abundant
and
>> >> available. I know these are VERY sparse details...
I'll
>> >> provide whatever info I can if someone can let us
know
>> >> something. Thanx
>> >
>> >
>> >.
>> >
>
>.
>|||It was a bug that hit us a few years back. We did not open a PSS case, but
you can search KB for it(if they acknowledge it). The client apps where
made in PB and a SQL2K(Compaq) server gold release. We no longer see this
problem and are on SP3a/SP2. It was a sporatic and not a serious outage at
the time, it sounded familar to is problem though.
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uKFCBJmzDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Please elaborate on this "bug"., KB articles, PSS cases, thanks.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Brooks" <jeepnreb@.yahoo.com> wrote in message
> news:uL9#ZMlzDHA.2456@.TK2MSFTNGP10.phx.gbl...
> > What is the cost of the query? Can you run --
> >
> > SET SHOWPLAN_ALL ON
> > GO
> > <QUERY IN QUESTION>
> >
> > and post the output.
> >
> > Also there is a SQL bug on SQL2K that will create mem leaks when jumping
> > between DBs in a query, is that happening? What version of SQL and SP
are
> > you on?
> >
> >
> > "DAvid" <anonymous@.discussions.microsoft.com> wrote in message
> > news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> > > 1gb RAM
> > > 4gb harddrive available (about 40%)
> > > 2 - 933 processors
> > >
> > > ALL application are affected. Task Mgr show both 933
> > > processors at 100 capacity. All seem fine until we run a
> > > simple SQL query on an ARC database. THAT qry soaks up
> > > 100% RAM.
> > >
> > >
> > > >--Original Message--
> > > >What are your server specs? Can you run other operations
> > > on the server
> > > >quickly(ie. is it SQL or the whole box)?
> > > >
> > > >Other things to list --
> > > >mem usage
> > > >Free pages
> > > >IO
> > > >CPU usage
> > > >Cost of the query in question
> > > >
> > > >We have had random slow and stoppage of performance on
> > > SQL2K.
> > > >
> > > >"David" <anonymous@.discussions.microsoft.com> wrote in
> > > message
> > > >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> > > >> All processes have slowed to a crawl - 1 user running a
> > > >> simple query in ARC brings all app's residing on this
> > > >> machine to a crawl. Machine resourses are abundant and
> > > >> available. I know these are VERY sparse details... I'll
> > > >> provide whatever info I can if someone can let us know
> > > >> something. Thanx
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Problem turned out not to be SQL Server generated. Was
instead a problem with SDE reconciliation...sorry!
david
>--Original Message--
>All processes have slowed to a crawl - 1 user running a
>simple query in ARC brings all app's residing on this
>machine to a crawl. Machine resourses are abundant and
>available. I know these are VERY sparse details... I'll
>provide whatever info I can if someone can let us know
>something. Thanx
>.
>

Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before.
Also the DB after removal / adding indexes seems to be different in a unknown way.
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam Heczko
Just recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov
|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko
|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon
|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko

Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and adde
d it again. Database after that operation is noticeably slower than the same
DB before.
Also the DB after removal / adding indexes seems to be different in a unknow
n way.
I could prove that running SQL Analyzer - query plan analysis on both DBs ar
e different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before">
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczkosql

Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before
Also the DB after removal / adding indexes seems to be different in a unknown way
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?
So the DBs are identical but in some strange way different
Could you have any ideas or maybe it is known SQL Srv 2000 problem
We run SQL 2000 SP3
Kind regards
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko

Draging and Droping scripts

I just upgraded from SQL2000 to SQL 2005. When I have a query opened for a database already and I want to drag and drop a script in, I get the login box. Is there anyway to get this to not happen? I would like it that when I drag and drop the script it is just loaded in a new query with the database connection that I was using already. (This is what it did in SQL2000) Thanks.

Right-click on the database in question in the object explorer and select "New Query".

Buck Woody

Draging and Droping scripts

I just upgraded from SQL2000 to SQL 2005. When I have a query opened for a database already and I want to drag and drop a script in, I get the login box. Is there anyway to get this to not happen? I would like it that when I drag and drop the script it is just loaded in a new query with the database connection that I was using already. (This is what it did in SQL2000) Thanks.

Right-click on the database in question in the object explorer and select "New Query".

Buck Woody

Dragging multiple fields to a table?

Ok, there has to be a way of dragging multiple fields from a dataset
to a table like there is in Access. I have a dataset of about 30-40
columns and it will be excruciating to right click on a column in the
table 30-40 times to add more columns so that I can then drag one at a
time all the fields to each new column.
Please tell me that I don't have to do this one by one. Thanks for
any input.You can drag one field at a time from the field list and drop it in the
cell. You don't to right-click on the cell and add it through the
expression builder. you can select multiple fields and drag&drop them but I
don't think that will work unless your using a list.
"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
news:e876e3c8.0407211308.39c6da3a@.posting.google.com...
> Ok, there has to be a way of dragging multiple fields from a dataset
> to a table like there is in Access. I have a dataset of about 30-40
> columns and it will be excruciating to right click on a column in the
> table 30-40 times to add more columns so that I can then drag one at a
> time all the fields to each new column.
> Please tell me that I don't have to do this one by one. Thanks for
> any input.

Drag columns around?

Hello,

I am a complete newbie to CR and am evaluating CR-XI. I have a requirement to create user-customizable reports - i.e. reports where the user can move columns around to get a layout he/she likes for printing. For example, if there were 5 fields in the report:

Column1 Column2 Column3 Column4 Column5

and the user wanted to print the report with the field order:-

Column1 Column2 Column5 Column3 Column4

is it possible for the user to be able to drag the columns around? Being an amateur, could someone please list the steps I need to go thru to achieve this? Is this possible programatically - via VB6/VB.Net and if so could someone direct/show me some detailed code for this?

Apart from what I described above, the bigger requirement is to create reports at run-time from scratch. I need to create an application that will accept SQL queries from the user, parse it, run it, and display the resultant recordset as a CR. Could someone please direct/show me some code on how to do it - I am a beginner and so will need as much help as possibe.

I use Delphi 7/.Net but am pretty sure I will be able to convert VB to Delphi.

I very much appreciate any help I can get.

Thanks,
VrijeshSee if you find solution at the suppoet section of this site
www.BusinessObjects.comsql

Drag and drop query onto Management Studio

Everytime I drag and drop a query file (.sql) from Windows Explorer onto
Management Studio, I am prompted to login. How do I get drag and drop to use
existing connection?
guy,
Install SP2 I believe. (At least it works for me.)
RLF
"guy" <guy@.hotmail.com> wrote in message
news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Everytime I drag and drop a query file (.sql) from Windows Explorer onto
> Management Studio, I am prompted to login. How do I get drag and drop to
> use existing connection?
>
|||That"s the ticket. Thank you.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OGnQpwRqHHA.3740@.TK2MSFTNGP02.phx.gbl...
> guy,
> Install SP2 I believe. (At least it works for me.)
> RLF
> "guy" <guy@.hotmail.com> wrote in message
> news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
>