dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connec
tion is established, instead of the default 1433. However, our application
requires a fixed port number, either the default 1433 or an explicitly set p
ort number.
For now we are accessing this small database via the ODBC connector using th
e Microsoft-supplied ODBC for SQLServer driver. However, this is not a solu
tion for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex IvascuYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the po
rt numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>sql

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number.
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex Ivascu
You can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>
|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu
|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved
Any assistance will be very appreciated
-
Alex IvascYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try
Thanks, Don.
Alex Ivasc|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

dynamic operator ?

Is there anyway we can have dynamic operator in filters '
instead of > i want to put parameters!operator.valueYes but you need to generate your sql dynamically... see my post her
http://www.sqltalk.org/ftopic34616.htm
So you'll need a datasource of > < = >= <= then get you
user to pick from the field
Then dynamically add that parameter into your sq
so you'd have 'WHERE quantity ' + @.operator + @.valueparamate
Then exec the sql

dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?

Dynamic Columns

Hi everybody.
I've to make somthing like a table in RS, but instead to habe dynamic rows,
i would like to have dynamic columns.
I see that it may be possible with matrix, but RS told me that i can only
have grouping expression in my cells, and i don't want it.
Thanks four your help.
--
Nicolas HOOGE
Ingénieur Conception Développement
OMNILOGYes, you can do that. Open the sample project that is installed when you
install Reporting Services. You should find this under your SQL Server
installation folder, for example: c:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\Samples\Reports. open the solution in Visual
Studio then open the report called Company Sales.rdl. You will see that it
has both dynamic rows and dynamic columns. And you will be able to see how
they did it.
HTH
Charles Kangai, MCT, MCDBA
"Nicolas HOOGE" wrote:
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> Ingénieur Conception Développement
> OMNILOG|||thanks
but in this example, you've got a dynamic columns with 2 static rows without
label.
I would like to have to same thing that a table but with 90° of rotation !!!
"Nicolas HOOGE" wrote:
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> Ingénieur Conception Développement
> OMNILOG|||If the only grouping you want is sum/totals, in a matrix you can right-click
on the data and select Subtotal.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Nicolas HOOGE" <nicolas.hooge(sansspam)@.omnilog.fr> wrote in message
news:BDE3FADD-C883-4A88-AB30-B85C3D3A16F3@.microsoft.com...
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic
> rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> Ingénieur Conception Développement
> OMNILOG

Dynamic Column Names?

Using SQL 2000
I am passing in the name of the column I want to update, @.Column. When I
SELECT @.Column it returns the value of @.Column instead of the value of the
actule table column. I have tried to use
Col_Name(OBJECT_ID('antwerp_cutoff'),OBJ
ECT_ID(@.Column)) but this does not
get the column name.
When I pass in Display1 I need to get back the value 2/2/2005
|Display1 | Display2|
--
|2/2/2005 | 2/3/2005|
--
Any help would be greatly appreciated.
Thanks,
Scott R. Butler
Stored Proc:
Create Procedure "Update_Antwerp_Displays"
(
@.Column varchar(256)
)
As
Declare @.i int
Declare @.Count int
set @.Count = (SELECT Count (*) FROM antwerp_trans)
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
1 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 1
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
2 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 2
UPDATE antwerp_depart
Set @.Column = (select @.Column+(Select Delta from antwerp_depart where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
UPDATE antwerp_arrive
Set @.Column = (select @.Column+(Select Delta from antwerp_arrive where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
While (@.i < @.Count)
BEGIN
UPDATE antwerp_trans
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
@.i ) from antwerp_cutoff where Delta = 0)
WHERE ID = @.i
SET @.i = @.i +1
ENDYou cannot use variables for tables, columns, etc. in queries. You'll
need to use dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for the quick response... even though it was not the response I was
looking for.
Quess I will have to find another way to get it done.
Scott
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OYjcU7uDFHA.3324@.TK2MSFTNGP15.phx.gbl...
> You cannot use variables for tables, columns, etc. in queries. You'll need
> to use dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Go back to your freshman software engineering books and look up the
concepts of coupling and cohesion.
What you want is a procedure with so little cohesion that any random
user, present or future, has more control over the RDBMS than the DBA.
This is **foundations of programming**, not anything particular to SQL.|||It seems that you are using column names to represent data. This is a
fundamental mistake and causes lots of problems, such as the one you
are having here. Columns should represent a single attribute and
repeating groups of columns are in violation of First Normal Form. Fix
your table design and you won't need messy dynamic SQL to accomplish
this.
David Portas
SQL Server MVP
--