Multi value parameter use in "IN" expression.

Hi
While writing my latest report i have been trying to use the result of a
multi value parameter as the value for a "IN" expression in my SQL query,
this does not seem to work, the multi value parameter shows that it has
values in it but the "IN" expression wont use them to filter the SQL query.
I have since found that the way i am using the Parameter in the SQL is
correct but i am now unsure of how the parameter should be set up.
Can anyone help?
Steve D'IN' should have multiple values seperated with a comma. I hope you know
that. So you need to use =join() to build the multi value parameters into
something like this (1,2,3,4) then pass this value into the query it should
work.
Amarnath
"Steve Dearman" wrote:
> Hi
> While writing my latest report i have been trying to use the result of a
> multi value parameter as the value for a "IN" expression in my SQL query,
> this does not seem to work, the multi value parameter shows that it has
> values in it but the "IN" expression wont use them to filter the SQL query.
> I have since found that the way i am using the Parameter in the SQL is
> correct but i am now unsure of how the parameter should be set up.
> Can anyone help?
> Steve D
>
>|||When I use a multi-value parameter I just do this:
select somefield from sometable where anotherfield in (@.Param)
Where @.Param maps to a multi-value parameter. There is no need for anything
fancy unless passing the parameter to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:56A2A663-692A-460A-8D64-4FD1F1B0B80C@.microsoft.com...
> 'IN' should have multiple values seperated with a comma. I hope you know
> that. So you need to use =join() to build the multi value parameters into
> something like this (1,2,3,4) then pass this value into the query it
> should
> work.
> Amarnath
>
> "Steve Dearman" wrote:
>> Hi
>> While writing my latest report i have been trying to use the result of a
>> multi value parameter as the value for a "IN" expression in my SQL query,
>> this does not seem to work, the multi value parameter shows that it has
>> values in it but the "IN" expression wont use them to filter the SQL
>> query.
>> I have since found that the way i am using the Parameter in the SQL is
>> correct but i am now unsure of how the parameter should be set up.
>> Can anyone help?
>> Steve D
>>

Multi Value Parameter Passing in OLEDB or ODBC

Hi Robert
We are integrating Sql server 2005 with sybase through ODBC.We are using
named parameters in the query...query which will fetch data from sybase
database.Now named parameters are those parameters which we specify with
'@.'.i.e. @.emp_id.
As we are conneting sybase from sql server 2005 through ODBC...but ODBC
doesnt support named parameter...so we are using unnamed parameters (?)
that is for e.g.
Select emp_id,emp_name from t_employee where emp_id = ?
but this is single value unnamed paramater...do u have idea how to pass
multivalue unnamed parameter which i can use with "In" clause..
Select emp_id,emp_name from t_employee where emp_id in ()
if i use ? along with "In" clause...it throws an error saying "Multivalue
Parameter is not supported in Data Extension"
Based on your last thread , we try to get something regarding creation of Custom Data Extension, but unfortunately we are not able to find much more on this particular topic.
We are able to find some readymain Custom Data Extension code from following link
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
but unfortunately we are not able to find more from that link also.
Can u plz help us regarding this, as we are passing through very crucial problem.
We will really very glad, if you can help us to solve this problem.
Thanks in advance :).

Take your sql and replace the ? with a valid emp_id and then run the query:

Select emp_id,emp_name from t_employee where emp_id = 2056

Goto report parameters and delete all of the query parameters the sql was using. Then create a report parameter named EmpID. Set in to multivalue. You then can change your sql to the following:

="Select emp_id,emp_name from t_employee where emp_id in (" & Parameters!EmpID.Value & ")"

You have to follow the process as described. Also, if EmpId is a string then you need to surround your values with single quotes. ie. When you select your drop down to choose the EmpID's to run for you should see" '20034' and '23456' etc. This will then pass a comma delimited string value to the SQL.

Multi value parameter in db2 query

hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

IBM's Ole DB driver only supports parameterized queries with single valued parameters. See e.g. this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81223&SiteID=1

SSRS 2005 contains specific functionality to perform query rewrite for multi-valued query parameters in the cases of SQL Server, Oracle 9 or later, and SSAS / XML/A-based data sources.

For all other cases you would need a custom data extension implementation to provide the query rewrite functionality.

-- Robert

Multi Table Query

Does anyone know how to create a query using tables from different sql server database's? looking for the simplest solution.

I have two databases in sql server. Both are in the same "server registration"
How do i reference a table in another database?

Do i do something like this?

SELECT Database1.Table1.Fields, Database2.Table1.Fields
FROM Database1.table1.PKField INNER JOIN Database2.table1.FKField;You may try this

SELECT Database1.dbo.Table1.Fields,
Database2.dbo.Table1.Fields FROM
Database1.dbo.table1.PKField INNER JOIN
Database2.dbo.table1.FKField|||you might also want to look at the OPENQUERY funtion. buddu's suggestion will work but you can generate a buch of unwanted I/O if the table on the remote server is big.|||You only need to do the full naming convension in the FROM clause

SELECT x.column1,
x.column2,
y.column1,
y.column3
FROM database1.dbo.tableX x
JOIN database2.dbo.tabley y
ON x.pk = y.pk|||can anyone help me please...
what query/syntax could i use for retrieving fields from various tables in Access for my one FORM.?
Ive tried using every possible codes but then it didn't work out.
SHould i declare diffrent recordsets for accesing this?

Multi table query

Hi

Need hellp with some query I'm trying to develope.

How do I count the number of records from table X1, colum X1 and from table Y2, colum Y2?

I whould need it to presente it like this:

Total X1 Total Y2

1313412 12341324

Thanks for any help.

One way to do this would be similar to:

Code Snippet

SELECT

TotalX1 = ( SELECT count(1) FROM TableX1 ),

TotalY2 = ( SELECT count(1) FROM TableY2 )

sql

multi rows SELECT

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = TransactionID FROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

Quote:

Originally Posted by Cshrek

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = VolIDFROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

hi
I am not clear with your code. try with this code .this is not exactly suit for ur requirement but this idea will help you

[code]
declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end|||hey, thanks.
wehn i try to run that code:

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

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,

@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end

----------------------------------
it all seems to be good, but i get that error:

"Msg 156, Level 15, State 1, Procedure AccountTrakingSELECT, Line 22
Incorrect syntax near the keyword 'for'."

and i defently have no idea what is that mean? or what am i doing worng?

appriciate your help.sql

Multi Parameter Select query

Hi All,

I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.

When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?

Thanks

Josh

Procedure:

create procedure MyMultiReport @.customername nvarchar(30), @.businessplantype nvarchar(30), @.businessplanyear nvarchar(10) as

Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@.customername) order by PlanDatameta.sort,Plantext.text,text1

return

Hi,

If thecustomer name is the multi valued parameter you can't use that customer name @.CustomerName in the Where clause.

suppose Customer Name parameter contain these values:aaa, bbb , ccc then if you select the aaa, bbb from the parameter it will pass to the Stored procedure in the following format:

@.CustomeName='aaa,bbb'

When you selecting the one value from customer name parameter it is passing to the stored procedure like this: 'aaa' this when you used in In cluase will give you the result.

This @.CustomeName you can't directly use IN Cluase of where.

Select .. from Where name in('aaa,bbb') this will not give any result just you check by running the above select.

Instead you can do one thing

a)First create a table valued function like the following which will take the @.CustomerName as Input parameter and will return the table containg the

aaa

bbb splits the input string by comma and place in the Table.

ALTER function [dbo].[GetCSV]

(@.array varchar(max))

Returns @.t Table (Col1 varchar(max))

as

Begin

DECLARE @.separator_position INT

,@.array_value VARCHAR(1000)

,@.separator CHAR(1)

Set @.separator=','

--For my loop to work I need an extra separator at the end. I always look to the

-- left of the separator character for each array value

SET @.array = @.array + @.separator

-- patindex matches the a pattern against a string

WHILE PATINDEX('%' + @.separator + '%', @.array) <> 0

BEGIN

SELECT @.separator_position = PATINDEX('%' + @.separator + '%',@.array)

SELECT @.array_value = LEFT(@.array, @.separator_position - 1)

INSERT INTO @.t SELECT @.array_value

-- This replaces what we just processed with and empty string

SELECT @.array = STUFF(@.array, 1, @.separator_position, '')

END

Return

End

And use that resulted table in the where clause of your select statement

Select ..

from ..

Where name in (Select * from dbo.GetCSV(@.CustomerName))

It will give you the result.

Hope this helps.

Thanks

|||

You can also use dynamic SQL and filter the records and put them in a temporary table (#temp) first and use this query clause in main query:

code for dynamic SQL:

DELCARE @.strSQL VARCHAR(MAX)

CREATE TABLE #temp (name VARCHAR(50))

SET @.strSQL = 'SELECT name INTO #temp FROM [Table] WHERE name IN (' + @.customername + ')'

EXEC(@.strSQL)

code for main query:

AND name in (SELECT name FROM #temp1) AND ....

Also, dont forget to change the datatype of the input parameter @.customername to NVARCHAR(MAX).

Shyam

multi lingual where clause

I have a table with nvarchar column. In query analyzer when I run query with foreign language words that are already there in database it dose not return any rows

To test it I returned rows with Select * table. Then from result window I copy chinese characters and put it in SQL where clause in SQL analyzer .When I run query it dose not return results.

What could be the cause..its SQL 2k

found it add N infront of characters|||

As kyus94 indicated, to use UNICODE characters, including Chinese, you must preface the string with the character [ N ].

For example,

SELECT

Column1,

Column2,

etc

FROM MyTable

WHERE Column3 = N'ThisCouldBeChinese'

(Note the character immediately before (no space) the string.)

sql

multi instances of sql server

Hi,
if I have 3 instances of sql server 2000 on a machine, how to use Enterprise
Manager - sql query analyzer to get a list of them all (name, current status,
the databases under each of them)? or have to use some other tool to do it?
thank you very much!
As I gather they are not showing up, check to see if these instances are
using TCP/IP or NAMED PIPES. [Start Programs - Microsoft SQL Server - SERVER
NETWORK UTILITY]. They may not show in the active directory - or at least
not allow connections - if one of these two protocols is not enabled.
Regards,
Jamie
"bangwo" wrote:

> Hi,
> if I have 3 instances of sql server 2000 on a machine, how to use Enterprise
> Manager - sql query analyzer to get a list of them all (name, current status,
> the databases under each of them)? or have to use some other tool to do it?
> thank you very much!
sql

multi instances of sql server

Hi,
if I have 3 instances of sql server 2000 on a machine, how to use Enterprise
Manager - sql query analyzer to get a list of them all (name, current status
,
the databases under each of them)? or have to use some other tool to do it?
thank you very much!As I gather they are not showing up, check to see if these instances are
using TCP/IP or NAMED PIPES. [Start Programs - Microsoft SQL Server - S
ERVER
NETWORK UTILITY]. They may not show in the active directory - or at least
not allow connections - if one of these two protocols is not enabled.
--
Regards,
Jamie
"bangwo" wrote:

> Hi,
> if I have 3 instances of sql server 2000 on a machine, how to use Enterpri
se
> Manager - sql query analyzer to get a list of them all (name, current stat
us,
> the databases under each of them)? or have to use some other tool to do i
t?
> thank you very much!

Multi Column FTS (2005) Query

Hi
I am trying to get my query syntax to work given a typical query I expect to
passed into my FTS procedure.
The typical search string could be Italy investment. The FTS must search
for Italy filtering by investment.
With an FTS index on CountryName and Item (item is where the word investment
resides)
Some t-sql like this would do the trick:
SELECT CountryName, ItemName
FROM SearchView
WHERE CONTAINS(*, 'italy AND investment')
Given that a single textbox is displayed to allow users to type their search
criteria, how would one seperate such a query so that country names were
searched against the CountryName FTS column and other (non country names)
searched against the FTS Item column?
Should I have several queries to determine if countries exist? If so, then
apply those country names in the AND of the WHERE clause, then how would I
then parse to remove the country names from the intial query string so that
the remaining words can be searched against the FTS Item column?
To add to the delimea, I'm using the FORMSOF(THESAURUS...) functionality, so
a user can query investment Europe (getting countries in the thesaurus file
matching Europe)
Craig
Unfortunately what you would have to do is the following
select *From SearchView where contains(CountryName, 'Italy') and
contains(ItenName,'investment')
This would offer the best performance, you could also do the following
select * from SearchView where CountryName='Italy' and
contains(ItenName,'investment')
unfortunately you will have to parse the search string to see which
countries are present in it, and then build your query accordingly.
Hilary

> To add to the delimea, I'm using the FORMSOF(THESAURUS...)
functionality, so
> a user can query investment Europe (getting countries in the
thesaurus file
> matching Europe)
> Craig
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Mulitple Update in mYSQL

I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.

Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1

But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??

Thanking you in advance..Originally posted by nikks525
I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.

Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1

But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??

Thanking you in advance..
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.

Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?|||I just noticed this before replying to the same thread in the MySQL forum. Simple answer; to the best of my knowledge, you can't. It's not valid SQL. And to extend my learned chum andrewst's comments, MySQL doesn't support triggers or views so no go there I'm afraid.

I'm also intrigued as to why you need to do this?|||I just wanted to do a easy job with writing the update in a single query.. rather than 2 different queries..
yea i think it needs to be broken up into 2 different Queries ..

anyway thanks for your replies ..|||Originally posted by andrewst
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.

Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?

CAN U PLEASE LET ME KNOW HOW TO CREATE VIEWS IN MYSQL TO UPDATE 2 TABLES IN MYSQL|||No need to shout :p

It's been a while since I checked up with developments over at MySQL AB but (see my post above) as far as I know, you can't. No triggers, no updateable views and transaction support only in certain table types.

Why can't you fire off two update statements?

(caveat: I'm quite happy to have my comments above proven wrong by someone more up-to-date on the latest MySQL releases)

Mulitple Select Statements in One Query

I'm trying to make a query in Access that will search for events in a single month where the events all have different names. I have SELECT Count(*) AS EVENT1 in September FROM blah WHERE Date LIKE Sep and EVENT LIKE 1 and then I have another SELECT COUNT statement for event2 in sept with event2 being the heading for the column and it aparently doesn't like that. I'm sorry, but I'm really new to this so I don't know if I'm explaining my problem correctly.SELECT Event, COUNT(*)
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event

?|||could you show some sample rows of the table, and then some sample result rows that you expect to get

rudy|||number date type
224433 sep fha
224432 sep fha
224424 sep fnma
234443 oct fha

I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return

FHA in Sep FNMA in Sep
2 1

And then I'll run a separate query on Oct with the same types in the headings|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
from yourtable
where [date] = 'sep'

rudy
http://r937.com/|||Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?

And if you don't like type (good for you), better get used to [brackets]|||I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.

What do the brackets do? Set it apart as being a field name and not some special modifier or something?|||exactamundo|||I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.

SELECT SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(case when ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';

It threw a syntax error.|||As close as I can get:

USE Northwind
GO

CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO

INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO

SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm INTO #bk_Temp
FROM myTable99
WHERE [date] = 'sep'
GROUP BY type

DECLARE @.Result varchar(8000)

SELECT @.Result = ''
SELECT @.Result = @.Result + Denorm + ' ' FROM #bk_Temp

SELECT RTRIM(@.Result)
GO

DROP TABLE myTable99
GO
DROP TABLE #bk_Temp
GO|||I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:

select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"

The syntax error it throws is a missing operator in the statement:
SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end)

I think the rest of it is okay.|||Originally posted by poontz13
I'm sorry, but that's totally past my understanding.

My sample is cut and paste-able, and should run in QA with no problem..

check it out...

You're problem though, is everytime you get a new type, you'll be hosed..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server

if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it

this would explain why the CASE structure throws an error

in access you have to use IIF

rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:

SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';

So obviously my count isn't specifying the productName correctly and it is just couting all items.

Any other advice? I do appreciate it.|||AW, CRAP

my bad

sincere apologies

yes, you did mention access

hang on a sec and i'll rewrite the query for ya...

rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:

SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great

once again, i apologize for chiding you, when in fact you did mention access right in your first sentence

rudy|||No problem...you're just trying to help me.

I do have one other question.

Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes

is LockDate an actual date/time field?

SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes

is LockDate an actual date/time field?

SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]

Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...

I was going to give you this

USE Northwind
GO

CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO

INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO

DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)

DROP TABLE myTable99
GO

But since you don't have QA, it's useless...

What it does though is what you want..it's dynamic sql and build a select based on data in your table...

I've done stuff in Access.(sortof) using the make query function...

But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime

meanwhile, try this --

... where LockDate like '*' + [enter month xxx] + '*'

and don't forget to change it in the SELECT as well

rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime

meanwhile, try this --

... where LockDate like '*' + [enter month xxx] + '*'

and don't forget to change it in the SELECT as well

rudy

I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month

because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...

You're using Access 2000 and Forms?

AND, if you are, don't you expect that you need n number of controls to accept the results?

Your row length is variable

And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."

Here's my code:

SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];

Spaces in column names is not a good thing...

EDIT Look at that, the same exact time...

double quote in Access mean data, [] mean columns|||take the square brackets off the alias

... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it

i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...

rudy

Mulitple Queries in a Stored Procedure

I have written a stored procedure that contains queries which return data.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.

Mulitple Create Views in Query Batch

I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.

> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]

Muliprotocol Encryption with Firewall ?

Hi,
I'm trying to setup a connection between a remote SQL server 2000 and Query
Analyser using an encrypted Multiprotocol connection but because the SQL
server is sitting behind a firewall I'm having trouble finding a way to
connect - is there any way to specify the port that the SQL server listens
on for this protocol rather than the default random assignment ?
Thanks,
Dave.Any reason you can't use SSL ? Just wondering why you are using
multiprotocol as it is really just included in SQL2000 for backwards
compatability.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
news:epWo4Vc4DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to setup a connection between a remote SQL server 2000 and
Query
> Analyser using an encrypted Multiprotocol connection but because the SQL
> server is sitting behind a firewall I'm having trouble finding a way to
> connect - is there any way to specify the port that the SQL server listens
> on for this protocol rather than the default random assignment ?
> Thanks,
> Dave.
>|||No - SSL sounds good - what port does that connect on though ?
Thanks,
Dave.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:#GyTNnc4DHA.876@.TK2MSFTNGP10.phx.gbl...
> Any reason you can't use SSL ? Just wondering why you are using
> multiprotocol as it is really just included in SQL2000 for backwards
> compatability.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
> news:epWo4Vc4DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm trying to setup a connection between a remote SQL server 2000 and
> Query
> > Analyser using an encrypted Multiprotocol connection but because the SQL
> > server is sitting behind a firewall I'm having trouble finding a way to
> > connect - is there any way to specify the port that the SQL server
listens
> > on for this protocol rather than the default random assignment ?
> >
> > Thanks,
> >
> > Dave.
> >
> >
>|||It works at the SuperSocket Net-Library level so no new ports are required
to be open. You can use TCP/IP, Named Pipes etc as it sits behind them all.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
news:ODHODCd4DHA.1428@.TK2MSFTNGP12.phx.gbl...
> No - SSL sounds good - what port does that connect on though ?
> Thanks,
> Dave.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:#GyTNnc4DHA.876@.TK2MSFTNGP10.phx.gbl...
> > Any reason you can't use SSL ? Just wondering why you are using
> > multiprotocol as it is really just included in SQL2000 for backwards
> > compatability.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
> > news:epWo4Vc4DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > I'm trying to setup a connection between a remote SQL server 2000 and
> > Query
> > > Analyser using an encrypted Multiprotocol connection but because the
SQL
> > > server is sitting behind a firewall I'm having trouble finding a way
to
> > > connect - is there any way to specify the port that the SQL server
> listens
> > > on for this protocol rather than the default random assignment ?
> > >
> > > Thanks,
> > >
> > > Dave.
> > >
> > >
> >
> >
>|||Thanks !
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJhhgAe4DHA.3436@.tk2msftngp13.phx.gbl...
> It works at the SuperSocket Net-Library level so no new ports are required
> to be open. You can use TCP/IP, Named Pipes etc as it sits behind them
all.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
> news:ODHODCd4DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > No - SSL sounds good - what port does that connect on though ?
> >
> > Thanks,
> >
> > Dave.
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:#GyTNnc4DHA.876@.TK2MSFTNGP10.phx.gbl...
> > > Any reason you can't use SSL ? Just wondering why you are using
> > > multiprotocol as it is really just included in SQL2000 for backwards
> > > compatability.
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Dave LLoyd" <dlloyd2000@.hotmasl.com> wrote in message
> > > news:epWo4Vc4DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > >
> > > > I'm trying to setup a connection between a remote SQL server 2000
and
> > > Query
> > > > Analyser using an encrypted Multiprotocol connection but because the
> SQL
> > > > server is sitting behind a firewall I'm having trouble finding a way
> to
> > > > connect - is there any way to specify the port that the SQL server
> > listens
> > > > on for this protocol rather than the default random assignment ?
> > > >
> > > > Thanks,
> > > >
> > > > Dave.
> > > >
> > > >
> > >
> > >
> >
> >
>

MTS Query

Hi,
This is a silly question
1. What the precausions we need to take care while coding a procedure, if we
have an MTS. Do we need to explicitly mention the Transactions in the sps ?
And what about setting the Value of 'XACT_ABORT' .
2. Another situation in the same.
Normally all our master tables will have a curresponding Audit table. and
what we do usually in case we dont have a transaction server is given below
SET XACT_ABORT ON
step 1. Insert into master table
step 2. Insert into Audit table
step 3. Begin Trans
.........
.........
.........
If @.@.ERROR<> 0
rollback trans
step 4. Commit Trans
Now how can i do the same in case we have a transaction server.
thanks and regards
LaraAFAIK, the behaviour might be the same. forget about new lines or any stuff
"lara169" wrote:

> Hi,
> This is a silly question
> 1. What the precausions we need to take care while coding a procedure, if
we
> have an MTS. Do we need to explicitly mention the Transactions in the sps
?
> And what about setting the Value of 'XACT_ABORT' .
> 2. Another situation in the same.
> Normally all our master tables will have a curresponding Audit table. a
nd
> what we do usually in case we dont have a transaction server is given belo
w
> SET XACT_ABORT ON
> step 1. Insert into master table
> step 2. Insert into Audit table
> step 3. Begin Trans
> .........
> ..........
> ..........
> If @.@.ERROR<> 0
> rollback trans
> step 4. Commit Trans
>
> Now how can i do the same in case we have a transaction server.
> thanks and regards
> Lara
>
>

ms-sql-s established

Hello,
I'm running a webapplication which uses msde 2000 as database server.
All webpages query the database.
If I refresh a lot of time a webpage then I get a time-out error .
With the netstat command I found a lot of ms-sql-s connections.
Is there a way to disconenct these connections?
Is there a way to repair this problem?
Thanks!
Hi
Make sure that you explicitly close the connections when you are done with
them.
You may want to try to disable connection pooling by setting it in
cliconfg.exe on the web server.
Regards
Mike
"Arjen" wrote:

> Hello,
> I'm running a webapplication which uses msde 2000 as database server.
> All webpages query the database.
> If I refresh a lot of time a webpage then I get a time-out error .
> With the netstat command I found a lot of ms-sql-s connections.
> Is there a way to disconenct these connections?
> Is there a way to repair this problem?
> Thanks!
>
>

ms-sql-s established

Hello,

I'm running a webapplication which uses msde 2000 as database server.
All webpages query the database.

If I refresh a lot of time a webpage then I get a time-out error.
With the netstat command I found a lot of ms-sql-s connections.

Is there a way to disconenct these connections?
Is there a way to repair this problem?

Thanks!I would ensure that you are ALWAYS closing connections on the page. Use try/finally blocks to ensure that open connections are always closed.

ms-sql-s established

Hello,
I'm running a webapplication which uses msde 2000 as database server.
All webpages query the database.
If I refresh a lot of time a webpage then I get a time-out error .
With the netstat command I found a lot of ms-sql-s connections.
Is there a way to disconenct these connections?
Is there a way to repair this problem?
Thanks!Hi
Make sure that you explicitly close the connections when you are done with
them.
You may want to try to disable connection pooling by setting it in
cliconfg.exe on the web server.
Regards
Mike
"Arjen" wrote:

> Hello,
> I'm running a webapplication which uses msde 2000 as database server.
> All webpages query the database.
> If I refresh a lot of time a webpage then I get a time-out error .
> With the netstat command I found a lot of ms-sql-s connections.
> Is there a way to disconenct these connections?
> Is there a way to repair this problem?
> Thanks!
>
>