I dont know how to use storedProcedure that returns one value as a result to a VB calling

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value

I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, someone pls can help

check the second part ofthis article.|||Thanks.

I dont know How can i upload my sqlserver file to the server

I don't know How can i upload my sqlserver file to the server
could you help me?
could you write an example code in query analyser for
uploading sql server file to databaseDo you have access to the SQL Server on the server? That is, can you connect to the server from the machine with your SQL Server? If so, you can use DTS (in internet explorer, right click on the database and select Import (or export, depending upon the database you right click on).

I can't understand the meaning of a prediction query

Dear friends,
I'm reading Wiley's Data mining with SQL Server 2005... There are MANY things I can't understand about MovieClick example (Chapter 3).
I hope someone is going to help me with this troubles...

WARNING (1): I'm a dummy both with sql server and data mining.
WARNING (2): My English is not good at all.

Just two questions for now:

1) When I create the model to predict the number of bedrooms for homeowners, the book says to check BEDROOMS as Predictable... question: is it also an INPUT for the model, or PREDICTABLE only?

2) I'd like to keep this model (number of bedrooms.......) and make a prediction query.

- Query builder
- select case table -> Homeowners
- Drag the Customer ID column from the Homeowners table and drop it on the grid
- Drag the BEDROOMS column from the mining model and drop it on the grid.
- On the last row: Source=PredictionFunction, Field=PredictProbability
- Drag the BEDROOMS column from the mining model and drop it into Criteria/Argument
- Add (i.e.) 'Two or Three' to the field Criteria/Argument

I execute the query and I obtain many rows in a table with the following colums: CustomerID, BEDROOMS and Expression: WHAT DOES THIS MEAN?
WHICH INFO DO I GET FROM THOSE NUMBERS? WHAT CAN I LEARN FROM THEM?

Thanx a lot in advance, please help me!

1: It won't actually matter if you make it INPUT as well. When you mark something as PREDICTABLE, it generally means that the algorithm will try to learn about the data in that column. When you mark it as INPUT, it generally means that the algorithm will consider this column as information about the predictable columns. For trees, for example, the algorithm will create a tree for each predictable column, and the trees can have splits on the input columns. If you have a column that is marked INPUT and PREDICTABLE, in general, it only matters if you have more than one PREDICTABLE, since the columns don't act as inputs for themselves, for example, the tree for # of bedrooms isn't going to have a split on # of bedrooms, but if you also make Home Ownership predictable, than # of bedrooms could be a condition in the tree for Home Ownership. (the one exception is for clustering, but that's a longer explanation).

2: What do the numbers mean? The first column is just the customer ID from the input - this could also be the customer name, for example. The second column is the # of bedrooms predicted by the algorithm - that is, the count of bedrooms that has the highest probability of all options given the input. The last column is the probability that the # of bedrooms would be 'Two or Three'.

What can you use this for? Take for example you were building a model to predict whether people would buy a product. You would want to predict which people would buy or not buy. In addition, you would probably want to contact those people with the highest probability of buying - therefore you need to use the PredictProbability function with a specified value. As you learn to use the product more, you will see that you can use the Profit Chart to determine the maximum profit for a marketing campaign using a Mining Model. The chart will give you a probability threshold. You want to use this threshold in a DMX query to select all customers who have a probability higher than that threshold in order to achieve maximum profit.

Hope this helps

-Jamie

I can't get ANY query with Report Builder to work!

I don't want to use a stored procedure, or do I have to?
I can't get any of my querying (whether from a dropdown or not) to do
anything other than display entire table...I'm creating report from
report wizard.
Help IS appreciated.
TrintTrint,
Each report has one, or more, datasets assigned to it.
Each dataset is independant of each other (except foe the datasources of
course), and each table on any report can be assigned to one of those data
sets.
I would finish with the wizard (I NEVER use those wizards) open the report
in design view, look at the dataset associated with the report, and alter it
accordingly. The dataset GUI in VS2003 is exactly the same as Enterprise
Manager for SQL.
Any further problems, just ask,
Hope this assists,
Tony
"trint" wrote:
> I don't want to use a stored procedure, or do I have to?
> I can't get any of my querying (whether from a dropdown or not) to do
> anything other than display entire table...I'm creating report from
> report wizard.
> Help IS appreciated.
> Trint
>

I can't get a SQL QUery to accept a null for a parameter

I have straight forward Insert Query, which takes values mainly from text boxes, however I am having trouble when the value is null.

eg
cmdP0.Parameters["@.EmpID"].Value=txtEmpNo.Text;

When I run the Query I get...
Message="Parameterized Query '(@.ID int,@.EmpID int,@.Photo nvarchar(260),@.DoB smalldatetime,@.Med' expects parameter @.EmpID, which was not supplied."

I also get this when the parameter can be a string.
I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)

No luck.

Have you tried to set DBNull.Value as value of the parameter, if the text is returned as null?|||

That worked for the strings it took a bit of effort forn integers.

Thanks to someone eles code, which I don't quite understand...

string inputGap = txtGap.Text;
Int32? searchGap = null;
if(!string.IsNullOrEmpty(inputGap)) {
Int32 Gap;
if(Int32.TryParse(inputGap , out Gap))
searchGap = Gap;
}
if(searchGap!=null) {
cmdP0.Parameters["@.MedicalGap"].Value=searchGap;
} else {
cmdP0.Parameters["@.MedicalGap"].Value=DBNull.Value;}

I don't know what Int32? searchGap = null; does

|||

Int32 is a struct (a value type). That means it can't be null. .NET 2.0 adds so called Nullable Types. That means you can set an Int32 null, by using a special syntax. Int32? (with the question mark) means that this is a nullable. It means that this variable can be set to null.

This piece of code is to complex. You can make it a lot easier by doing this:

int value = 0;
if (int.TryParse(txtGap.Text, out value))
cmdP0.Parameters["@.MedicalGap"].Value = value;
else
cmdP0.Parameters["@.MedicalGap"].Value = DBNull.Value;

|||

Thank you

A nice clear explanation and some nice code

Much appreciated, this has been driving me nuts!

I Cant "show sql query" - Crystal Report 8.5

Hey Guys, all fine ?

I created a report and using 3 tables( or more) with joins, group and etc. And this report calls a subreport. Well, the report is perfect, no problems! But, i can't see your sql definition(In crystal report, menu database,Show sql query) and, in Visual Basic i need to set a similar sql of report, only changing the where clause, by filter controls in VB.

In old times, i have used crystal report 8, and can show sql query.

In VB, how i can input a sql string in report, after execute it ?

How i can show sql query in CR?

Please, i need this help with urgency! The rope is in my neck! =(]

Thanks a lot!"Show SQL Query" option is very much present in crystal reports 8.5, if you don't have the option, then you try re-installing crystal reports.

i can not make query for any information in arabic language

HI,

i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank you

and my code like this

use today

select CatID from dbo.tbl_Categories
where CatName like '%???????%'

Try to set the default collation to "Arabic_CL_AS"

|||

hi,

please tell me from where can i set that? or if i use specific syntax please send it to me,thank you.

|||try this:

alter table tbl_Categoriesalter column CatNamevarchar(100) COLLATE Arabic_CI_AI

Note the collation provided for this column. Collation is used by SQL Server to store data for different languages. Have a look at COLLATE or COLLATION in BOL. You can use

select *from ::fn_helpcollations()

to check all the installed collations on your server.

Hope this will help.

|||

How to change default collate in SQL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36707

|||

Hi,

by the way i finished from added may data to database in arabic language i made import from Excel file but the problem is when i make query on arabic data i recieved no row selected how is?,thank you

|||

hi,

i still face the problem, by the way i finished from added my data to database in arabic language i made import from Excel file but the problem is when i make query on arabic data i recieved no row selected how is?,help me,thank you

|||

First of all, you can run the alter table query I provided earlier any time whether your table contains data or not. One more thing is that you can use collation at the time of writing select query too, the syntax is below:

select *from <table name>where <column name>like'<value>' collate Arabic_CI_AI

Now the comparison will be made using the Arabic_CI_AI collate for this select. As I've told earlier there are so many collates to choose from ( even for Arabic ), which differ in some way or another. Choose the collate you want to use and either alter the table permanently or at the time of selecting the values. Just go and have a look at BOL for COLLATE.

Hope this will help

|||

hi,

i altered my table with Arabic_CI_AI after that i write your syntax but still no new :S,thanks

|||

How can I reach all the possible collation options for Arabic?

This is my SQL statement which returns NO VALUES:

SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '%?%' COLLATE Arabic_CI_AI)

I also tried

SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '%?%' COLLATE Arabic_CL_AS)

as was suggested in one of the forum replies, but it gives an error message saying :
"Invalid Collation" with an error source: " .NetSqlClient Data Provider"

Thanks.

I am trying to convert a MS Access Query to a SQL query and I need help?

I am trying to convert a MS Access Query to a SQL query and I need help with some IIF Statements?

2 in particular that I've encountered problems with are:

Count(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[zip]))))'AS wk2count,

AND

Format(Avg(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[elphours])))),[0.0])AS wk2avg,

Any help that someone could offer would be greatly appreciated!
Thanks!After some more effort, I was able to get this working:
Count(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])) then
[zip]
end
end
end) AS wk2count,
Round(Avg(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd(day,13,[dFirstPSYDate])) then
elphours
end
end
end), 0.0) AS wk2avg,

Quote:

Originally Posted by newtechiebug

I am trying to convert a MS Access Query to a SQL query and I need help with some IIF Statements?

2 in particular that I've encountered problems with are:

Count(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[zip]))))'AS wk2count,

AND

Format(Avg(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[elphours])))),[0.0])AS wk2avg,

Any help that someone could offer would be greatly appreciated!
Thanks!

I am Really confusing Please help me about a single query

Hi Guys,
This is my Problem.
A table contain following desing for handling different level of categories, bu it is dynamic

int_categoryid,
int_parent_categoryid,
int_categorylevel,
str_categoryname,
bit_active
thatall.
I want to list data from table as following order
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
..................
................................
.....................
like this..
ie we can insert parent category and sub category to n level dynamically without adding a new table
please mail me for another clarification...

please help me
regards
Abdul
For the example data you provided, can you tell us what data appears in the int_categorylevel column for each row?
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
|||

Thanks, tmorton
yes I am also interesting in int_categorylevel, and it gives the information about category level, 0,1,2 etc...
for Example Suppose we can take hotel menu categories...
Lunch Catgeroy level is 0
Veg Catgeroy level is 1
Average Catgeroy level is 2
Expensive Catgeroy level is 2
Non Veg Catgeroy level is 1
Chicken Catgeroy level is 2
Mutton Catgeroy level is 2
thanks ur response...

I am having QUERY/ DATASET problem with SQL Reports.

Hi every1,

I really need your help.

I have a web form which is having Drop Down box in which I have 2 selection 'P' and 'C'. Depending upon the selection I have to call the Dataset( If I can make it in code behind) or I can do it in one Complex query if you guys will help me.

Query should be like this.

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a

where

acolumn2 = "USERINPUT"

but now the problem is in FROM and Where Clause.

when selection is 'P' then I have to include ptable in FROM Clause and in where clause I have to include two condition like (p.column1 = a.column1 and p.column2="USERINPUT1" or

if selection is 'C' then I have to include ctable in FROM clause and in where clause I have to include two different condition like (c.column1 = a.column1 and c.column2="USERINPUT")

So at once the Query will be any of one like below:

(1) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ptable p

where

a.column2 = "USERINPUT" and

p.column1 = a.column1 and p.column2="USERINPUT1"

-

(2) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ctable c

where

a.column2 = "USERINPUT" and

c.column1 = a.column1 and c.column2="USERINPUT"

Please let me know if you have any kind of confusion so I can clear this but if you got everything from my description please do help me to solve this. I am really struggling for this.

One way I was thinking to generate 3 dataset one(query or dataset) for 'P' , one for 'C' and one for else so I can get the input (selection) from the form and then decide in codebehind which dataset I have to make call so no need to write complex query. But this is my thought and I dont know how to make a call and everything either so please help me in this as well, if you like this solution .

Or try to solve the above query please.

Thanks

Roy

The best way to do this is to construct the query dynamically as a string.

And the best way to do THAT -- IMHO -- is to have your query say only:

Code Snippet

=Code.GetSQL()

... now you can write a simple little VB function that evaluates your parameters and builds up the string, returning that string. You will find it a lot more comfortable to do it this way than doing it in the query window. If you have never written a custom function before, don't worry, you don't need to build a DLL for something like this -- just use the Code tab in the Report Properties dialog. This is the kind of code that IMHO really *deserves* to be embedded in a report, since it is specific to that report and that report's parameters.

If you have the ability to write stored procedures or table-valued functions in the database, that is another option. You can pass your parameters to the procedure or function, and have that code evaluate the parameters and provide the result. It really depends on two things: what your permissions are (for writing to the database) and what your relative comfort level is (for writing VB or writing T-SQL).

HTH

>L<

Hyphen padding in t-sql query result

A newbie question here:

In T-SQL my query to text file results in the padded second line containing a set of hyphens. How do I turn that off so that just the first line contains the headers and the results immediately follow in line 2.

Thanks!

You need to describe your problem a bit. It would help to have relevant code, data definition, what the incorrect results look like and the correct results look like. It would also help to have the associated sample data.|||

Certainly.

I'm composing a job in sql server using sqlcmd. Here's my command-line

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv"'

Output is just fine with the exception of that pesky line 2 of padded hyphens. Appears like this:

department,FundCode,Month

-,--,--,
DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

That 'pesky line 2' is part of the output 'header'.

You can turn the headers off (that will include the line 1 with the column names) by adding this switch to the SQLCmd command line:

-h-1

(No space between the h and the -1.)

You probably don't need line 1 or 'pesky line 2' in your application.

As far as I am aware, those are your choices...

|||

Plus, if you don't like their output, it is pretty easy to build a console app to get the results. I had to do it once to deal with Text values that were very large and I couldn't get sqlcmd to do.

In 2005, you might even be able to write a CLR stored procedure (albeit an unsafe one) that will do the work directly that you have complete control over.

|||I was aware of the -h switch but I'd prefer to keep the headers to be explicit. If sqlcmd doesn't support it then I'll have to write something up. Thanks for your input everyone!|||This is kind of a cheesy solution, but it works.

Use this command:

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON Select 'department','FundCode','Month' SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv" -h-1'

Notice the header switch (-h) has a value of -1 which means that it is off. The first Select statement returns one more row of results which contains the "headers"

Your output should look like this:

department,FundCode,Month

DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

Brilliant! I had to use double single-quotes (e.g., ''Department'') to get it to parse correctly but this is what I needed.

Not cheesy at all. I too hide my inelegance with cleverness.

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
Derrick
Derrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>
|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>
|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/larar/archive/2006/11/13/maximum-degree-of-parallelism-and-hyperthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
DerrickDerrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>> We have a new server, 4 physical dual core processors, with
>> hyperthreading, so it shows up as 16 logical processors.
>> A simple query joining 3 tables, a distinct, an order by, with maxdop set
>> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
>> milliseconds. setting maxdop between 1 and 15, pretty linear time
>> progression.
>> Does this sound sane? Is there some magic matrix where under load a
>> higher maxdop will result in this simple query running faster than where
>> there is no load (the current case)
>> Thanks in advance!
>> Derrick
>|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/larar/archive/2006/11/13/maximum-degree-of-parallelism-and-hyperthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>> Derrick,
>> It is true: Parallelism does not alway help. In fact, for some queries it
>> may make them perform abysmally.
>> Although I do not do this personally, several posters recommend setting
>> an OLTP server to a maximum degree of parallelism of 1. They indicate
>> good success and few discernable problems because of the setting.
>> I do follow the guideline to limit the server's maximum degree of
>> parallelism to the number of physical chips, which in your case would be
>> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
>> FWIW,
>> RLF
>> "Derrick" <derrick1298@.excite.com> wrote in message
>> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>> We have a new server, 4 physical dual core processors, with
>> hyperthreading, so it shows up as 16 logical processors.
>> A simple query joining 3 tables, a distinct, an order by, with maxdop
>> set to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
>> milliseconds. setting maxdop between 1 and 15, pretty linear time
>> progression.
>> Does this sound sane? Is there some magic matrix where under load a
>> higher maxdop will result in this simple query running faster than where
>> there is no load (the current case)
>> Thanks in advance!
>> Derrick
>>
>

Hyperthreading killing performance of simple query?

We have a new server, 4 physical dual core processors, with hyperthreading,
so it shows up as 16 logical processors.
A simple query joining 3 tables, a distinct, an order by, with maxdop set to
0, takes over 2 seconds. With maxdop set to 1, it takes 200 milliseconds.
setting maxdop between 1 and 15, pretty linear time progression.
Does this sound sane? Is there some magic matrix where under load a higher
maxdop will result in this simple query running faster than where there is
no load (the current case)
Thanks in advance!
DerrickDerrick,
It is true: Parallelism does not alway help. In fact, for some queries it
may make them perform abysmally.
Although I do not do this personally, several posters recommend setting an
OLTP server to a maximum degree of parallelism of 1. They indicate good
success and few discernable problems because of the setting.
I do follow the guideline to limit the server's maximum degree of
parallelism to the number of physical chips, which in your case would be 4.
Then, if a query is troubled we can specifically run it at MAXDOP 1.
FWIW,
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
> We have a new server, 4 physical dual core processors, with
> hyperthreading, so it shows up as 16 logical processors.
> A simple query joining 3 tables, a distinct, an order by, with maxdop set
> to 0, takes over 2 seconds. With maxdop set to 1, it takes 200
> milliseconds. setting maxdop between 1 and 15, pretty linear time
> progression.
> Does this sound sane? Is there some magic matrix where under load a
> higher maxdop will result in this simple query running faster than where
> there is no load (the current case)
> Thanks in advance!
> Derrick
>|||Russell,
Thanks for the info, one more q, so would you reccomend hyperthreading be
enabled, or disabled?
We ran tests both ways and turning hyperthreading off does not seem the way
to go based on our results, does that match up with what you have seen/read?
Thanks again,
Derrick
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
> Derrick,
> It is true: Parallelism does not alway help. In fact, for some queries it
> may make them perform abysmally.
> Although I do not do this personally, several posters recommend setting an
> OLTP server to a maximum degree of parallelism of 1. They indicate good
> success and few discernable problems because of the setting.
> I do follow the guideline to limit the server's maximum degree of
> parallelism to the number of physical chips, which in your case would be
> 4. Then, if a query is troubled we can specifically run it at MAXDOP 1.
> FWIW,
> RLF
> "Derrick" <derrick1298@.excite.com> wrote in message
> news:%23E%23LO250HHA.5644@.TK2MSFTNGP05.phx.gbl...
>|||Derrick,
Lara Rubbelke made these comments:
http://blogs.digineer.com/blogs/lar...rthreading.aspx
He links to Slava Ok's
http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
Their bottom seems to be, do not enable hyperthreading unless you have
proved it will help you.
RLF
"Derrick" <derrick1298@.excite.com> wrote in message
news:u1F0WU60HHA.464@.TK2MSFTNGP02.phx.gbl...
> Russell,
> Thanks for the info, one more q, so would you reccomend hyperthreading be
> enabled, or disabled?
> We ran tests both ways and turning hyperthreading off does not seem the
> way to go based on our results, does that match up with what you have
> seen/read?
> Thanks again,
> Derrick
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eJddSQ60HHA.3788@.TK2MSFTNGP02.phx.gbl...
>

Hyperlink to a new window

I have two hyperlink-related questions:
1. How can I generate a URL from within a query or stored procedure such that it appears as a hyperlink within the report? On my first attempt, the text of the link shows but it does not appear as a "live" link within the generated report.
2. Once I've achieved #1, how can I get that link appear in a new browser window rather than the one in which the report is currently displayed?
Thanks in advance for the advice.#1:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
#2: Take a look at _LinkTarget device info setting:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> I have two hyperlink-related questions:
> 1. How can I generate a URL from within a query or stored procedure such
that it appears as a hyperlink within the report? On my first attempt, the
text of the link shows but it does not appear as a "live" link within the
generated report.
> 2. Once I've achieved #1, how can I get that link appear in a new browser
window rather than the one in which the report is currently displayed?
> Thanks in advance for the advice.
>|||Your links proved unhelpful for the following reasons:
#1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
#2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
Example:
If my report gets information from the following query...
select url from urlsource
and urlsource contains
* http://www.site1.com/
* http://www.site2.com/
How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
"Ravi Mumulla (Microsoft)" wrote:
> #1:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> #2: Take a look at _LinkTarget device info setting:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > I have two hyperlink-related questions:
> >
> > 1. How can I generate a URL from within a query or stored procedure such
> that it appears as a hyperlink within the report? On my first attempt, the
> text of the link shows but it does not appear as a "live" link within the
> generated report.
> >
> > 2. Once I've achieved #1, how can I get that link appear in a new browser
> window rather than the one in which the report is currently displayed?
> >
> > Thanks in advance for the advice.
> >
>
>|||On the question of treating your field data as a URL, there is the URLEncode command.
=Web.HttpUtility.UrlEncode(Fields!Serial.Value)
(http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=
)
There is another thread that can answer some potential issues you may have with implementing this.
(http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=)
On your second question, about being able to open a link in a new window...if you are using Report Manager for your distribution platform, then there is NOT a current way to do so. Even if you are using the HTML Viewer and sending in rc: comands, it is an all or nothing deal - either all your links open in a new window or none of them do. I have sugested a few features that I hope MS will implement into one of their future SPs, but until that time, your options are few.
"virtualfergy" wrote:
> Your links proved unhelpful for the following reasons:
> #1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
> #2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
> Example:
> If my report gets information from the following query...
> select url from urlsource
> and urlsource contains
> * http://www.site1.com/
> * http://www.site2.com/
> How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
> "Ravi Mumulla (Microsoft)" wrote:
> > #1:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> > #2: Take a look at _LinkTarget device info setting:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> > news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > > I have two hyperlink-related questions:
> > >
> > > 1. How can I generate a URL from within a query or stored procedure such
> > that it appears as a hyperlink within the report? On my first attempt, the
> > text of the link shows but it does not appear as a "live" link within the
> > generated report.
> > >
> > > 2. Once I've achieved #1, how can I get that link appear in a new browser
> > window rather than the one in which the report is currently displayed?
> > >
> > > Thanks in advance for the advice.
> > >
> >
> >
> >|||Look at my post from 8/26:
I think I have a solution. If you want a particular link to be opened in a
new window, enter this into the "Jump to URL" field:
javascript:if(window.open(yourPage.aspx','RsWindow','width=400,height=500,location=0,menubar=0,status=0,toolbar=0,scrollbars=1',true)){}
"Rob 'Spike' Stevens" wrote:
> On the question of treating your field data as a URL, there is the URLEncode command.
> =Web.HttpUtility.UrlEncode(Fields!Serial.Value)
> (http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=
> )
> There is another thread that can answer some potential issues you may have with implementing this.
> (http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=)
> On your second question, about being able to open a link in a new window...if you are using Report Manager for your distribution platform, then there is NOT a current way to do so. Even if you are using the HTML Viewer and sending in rc: comands, it is an all or nothing deal - either all your links open in a new window or none of them do. I have sugested a few features that I hope MS will implement into one of their future SPs, but until that time, your options are few.
> "virtualfergy" wrote:
> > Your links proved unhelpful for the following reasons:
> >
> > #1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
> >
> > #2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
> >
> > Example:
> >
> > If my report gets information from the following query...
> >
> > select url from urlsource
> >
> > and urlsource contains
> >
> > * http://www.site1.com/
> > * http://www.site2.com/
> >
> > How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > #1:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> > > #2: Take a look at _LinkTarget device info setting:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> > > news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > > > I have two hyperlink-related questions:
> > > >
> > > > 1. How can I generate a URL from within a query or stored procedure such
> > > that it appears as a hyperlink within the report? On my first attempt, the
> > > text of the link shows but it does not appear as a "live" link within the
> > > generated report.
> > > >
> > > > 2. Once I've achieved #1, how can I get that link appear in a new browser
> > > window rather than the one in which the report is currently displayed?
> > > >
> > > > Thanks in advance for the advice.
> > > >
> > >
> > >
> > >

Hurry! Query needs help

Hello Fellow Developers,

I am very new to SQL and ASP.net and in the learning process. I have to do a small web application that handles documents stored on a sql database. The requirements of the functionality of the application are below. I am just having trouble setting my query to work correctly.

Problem:

I want to be able to pull documents and folders that have the same ClientID. However, here is the catch, the query should take two inputs - ClientID and ParentID (which is an ID representing FolderID from the Folder Table; the document/folder does not belong to another folder then the system will enter ZERO as the ParentID). I hope this is clear? Can this even be done?

Any help is greatly appreciated, as life is a learning experience.

Database Structure:

Table Name: Client

Fields:

ClientID primary key autonumber

Name nvarchar 255

Table Name: ClientDocs

Fields:

DocID primary key autonumber

Document image (the actually file in bytes)

FriendlyName nvarchar 255

ClientID foreign key -> [Table Client]

ClientFolderID foreign key -> [Table ClientFolders]

Table Name: Folders

Fields:

FolderID primarykey autonumber

FolderName nvarchar 255

Table Name: ClientFolder

Fields:

ClientFolderID primarykey autonumber

FolderID foreign key -> [Table Folders]

ParentID int

ClientID foreign key -> [Table Clients]

saeapu:

Problem:

I want to be able to pull documents and folders that have the same ClientID. However, here is the catch, the query should take two inputs - ClientID and ParentID (which is an ID representing FolderID from the Folder Table; the document/folder does not belong to another folder then the system will enter ZERO as the ParentID).

What's the exact thing will the query do? To me it seems an insert to some table (may be ClientFolders?). Then where do the 2 input valuesClientID and ParentIDcome from? You mentioned when will the ParentID be set to 0, so what's the value of ParentID if it dose not satisfy the condition to be 0? And how do you want to deal with FolderID in your query, as there is a FolderID column in ClientFolders?

|||Not sure if i understand the question, is it just to return the data using a sql statement or stored procedure?

If it is, using a stored procedure you could:

Declare 2 parameters, @.ClientId and @.ParentId.

In the Where clause of your statement have something like

WHERE
ClientId = @.ClientId
AND (ParentId = @.ParentId
OR @.ParentId = 0)|||

Iori_Jay:

What's the exact thing will the query do? To me it seems an insert to some table (may be ClientFolders?). Then where do the 2 input values ClientID and ParentID come from? You mentioned when will the ParentID be set to 0, so what's the value of ParentID if it dose not satisfy the condition to be 0? And how do you want to deal with FolderID in your query, as there is a FolderID column in ClientFolders?

Thanks Lori_jay for the input and questions. Hopefully below I can explain the problem better.

Basically I am creating a small web application. Currently what my app can do is upload files to a sql database and associate the file(s) to a clientID. So there is 1 Client to many documents.

This works fine and dandy, but what I am trying to do is give the capability to create folders in the application. And let users insert a document into a particular folder which that folder is associated to a client and those documents inside the folder is also associated to the same ClientID. But they do not have to put the document inside a folder. They can just upload the document and just associate it to the ClientID. The reason I did parentID is that if they wanted to create a folder inside another folder. So the queries I will be needing help with is the 'Select' query to retrieve all documents and folders that have a two paremetersPartentID andClientID.

ParentID = a integer representing the folder that document/folder belongs too. If the folder /document is at the root level of the client then the value will be "0".

ClientID = a integer representing a Client.

Maybe my table structures are incorrect. What do you think?

Here is a small tree like structure I have in mind.

ClientID

|

|__ Folders

| |

| |__________Folder

| |

| |__Documents

|

|__ Documents

|||

DrGonzo:

Not sure if i understand the question, is it just to return the data using a sql statement or stored procedure?

If it is, using a stored procedure you could:

Declare 2 parameters, @.ClientId and @.ParentId.

In the Where clause of your statement have something like

WHERE
ClientId = @.ClientId
AND (ParentId = @.ParentId
OR @.ParentId = 0)

Hey Thanks DrGonzo, I have tried that already and no luck Can you give me an example on how you would use the tables I have given above and JOIN them to make this query work.

Thanks.

|||

saeapu:

So the queries I will be needing help with is the 'Select' query to retrieve all documents and folders that have a two paremetersPartentID andClientID.

ParentID = a integer representing the folder that document/folder belongs too. If the folder /document is at the root level of the client then the value will be "0".

ClientID = a integer representing a Client.

Then how do you konw the folder/document is at the root level? Can I say a row in ClientFolders table which has 0 for ParentID column represents a top level entity? If so we do not need to consider the @.ParentID with value 0 as a particular issue, we can just treat @.ParentID as it is other value (1,2,3...ect.). The top level entity logic is implemented when you insert the row into ClientFolders table, right? Let's try such query:

declare @.ClientID int,@.ParentID int
select @.ClientID=1,@.ParentID=1
select c.ClientID,c.Name,cf.ClientFolderID,cf.ParentID,
f.FolderID,f.FolderName,cd.FriendlyName
from ClientFolders cf join Folders f on cf.FolderID = f.FolderID
join ClientDocs cd on cf.ClientFolderID=cd.ClientFolderID
join Clients c on cf.ClientID = c.ClientID
wherecf.ClientID=@.ClientID
andcf.ParentID=@.ParentID