Need to connect to SQL Server on other side of firewall, listening to a different port

Hi,
I want to communicate to SQL Server through my client. Server is listening
via port 16433 instead of defualt 1433.
And this particular server is on the other side of the firewall and it is in
a workgroup. Client Server is in a domain.
I have tried specifying ServerName,PortNo. but it does not work.
Any one know some other way to communicate to this server. Please help.
Thanks
PushkarCan you show us a connection string?
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:OS%23hNh2hFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to communicate to SQL Server through my client. Server is listening
> via port 16433 instead of defualt 1433.
> And this particular server is on the other side of the firewall and it is
in
> a workgroup. Client Server is in a domain.
> I have tried specifying ServerName,PortNo. but it does not work.
> Any one know some other way to communicate to this server. Please help.
> Thanks
> Pushkar
>|||Hi,
I am trying to connect through enterprise browser. And while registering for
the server I am giving server name as
"MyServer,16433"
where 16433 is port on which SQL Server is listening.
Thanks
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Can you show us a connection string?
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:OS%23hNh2hFHA.3288@.TK2MSFTNGP09.phx.gbl...
> in
>|||"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to connect through enterprise browser. And while registering
> for the server I am giving server name as
> "MyServer,16433"
> where 16433 is port on which SQL Server is listening.
Does the name MyServer resolve on your system? Names must be resolved to IP
addresses (or MAC addresses) using some mechanism, NetBIOS broadcasts, DNS,
WINS -- something. Try pinging it. If you have no reason to believe the
name will resolve, you have no expectation of connectivity with SQL Server.
If you have no mechanism for resolving the name, use the IP address of the
SQL box in place of the name.
Also, are you on the inside of the firewall, or the outside? If you are on
the outside, that port must have been opened by the admin of the firewall.
If you are on the inside, the firewall is *most*likely* not a significant
factor (though that is not exclusively true, a capable firewall can be
configured to block traffic in either direction, it just isn't usually done
that way.)
-Mark

> Thanks
> Pushkar
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>|||Pushkar
Go to the Client Network Utility and create an alias to SQL Server that is
listening 16433 port
Make sure that TCPIP is at begining of the list.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to connect through enterprise browser. And while registering
for
> the server I am giving server name as
> "MyServer,16433"
> where 16433 is port on which SQL Server is listening.
> Thanks
> Pushkar
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
is[vbcol=seagreen]
>|||Hi,
Thanks it works by creating alias.
Thanks
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OfLT1d3hFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Pushkar
> Go to the Client Network Utility and create an alias to SQL Server that is
> listening 16433 port
> Make sure that TCPIP is at begining of the list.
>
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> for
> is
>

Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

Need some help with a Trigger

I created an instead of insert trigger which checks to see if the "key" of
the inserted record already exists. If it does, it copies the existing
record to another table, deletes it and inserts the new one. The problem I
get is when the insert statement coming into the trigger looks like the one
below I don't get my idx returned.
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
here's the trigger:
ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
INSTEAD OF INSERT AS
declare @.count int, @.comp int
declare @.resft int, @.data float, @.alf datetime
select @.resft = inserted.resft, @.data = data, @.alf = inserted.alf from
inserted;
select @.comp = idx from research where idx in (select research from resft
where idx = @.resft)
select @.count = count(data)from resdata where resft = @.resft and data =
@.data
if (@.count > 0) /* This record already exists so we don't want it added*/
return; // Don't know what to put here
else
begin
/* Create the same record in the history table */
insert into resdatah (resft, alf, data, ohm) select resft, alf, data,
getdate() from resdata where resft = @.resft
/* Delete the existing record from the this (resdata) table */
delete from resdata where resft = @.resft
/* Insert the new record */
insert into resdata (resft, data, alf) select resft, data, alf from
inserted
end"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
Please move this into the SQL Server 2005 newsgroups...
http://www.aspfaq.com/sql2005/show.asp?id=1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I'm actually using 2000 and 2005 (for testing). The trigger needs to work in
2000.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#mcqNYcGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Please move this into the SQL Server 2005 newsgroups...
> http://www.aspfaq.com/sql2005/show.asp?id=1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
> I'm actually using 2000 and 2005 (for testing). The trigger needs to work
in
> 2000.
The EXECUTE AS syntax is new for 2005 so you're going to have some
problems there...
Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
should be used rather than SCOPE_IDENTITY() -- since the insert is being
done in the scope of the trigger, not in the scope of the initial INSERT
statement, @.@.IDENTITY should return the correct value. Second, you could
SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Your trigger will fail if more than one row is inserted. Never write
triggers like that.
When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
useful within a trigger itself because a trigger should always be able
to handle multiple row inserts.
EXECUTE AS CALLER isn't supported in SQL2000.
Please post DDL and sample data INSERTs if you need more help.
David Portas
SQL Server MVP
--|||ok so how do I set the value of idx for the inserted return?
idx = @.idx // if record already existed
and
idx = select_scope_identity() if the trigger inserted the row?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#7Qr6ecGFHA.552@.TK2MSFTNGP12.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
work
> in
> The EXECUTE AS syntax is new for 2005 so you're going to have some
> problems there...
> Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
> should be used rather than SCOPE_IDENTITY() -- since the insert is being
> done in the scope of the trigger, not in the scope of the initial INSERT
> statement, @.@.IDENTITY should return the correct value. Second, you could
> SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||This table is a read-only table except for a single user. The inserts are
done like this:
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
The goal is to check and see if there is a row with resdata =
inserted.resdata. If so, the existing record needs to be copied to another
table and the new one inserted.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109177795.995989.220250@.f14g2000cwb.googlegroups.com...
> Your trigger will fail if more than one row is inserted. Never write
> triggers like that.
> When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
> inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
> useful within a trigger itself because a trigger should always be able
> to handle multiple row inserts.
> EXECUTE AS CALLER isn't supported in SQL2000.
> Please post DDL and sample data INSERTs if you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
suggested but anyway IDENTITY should never be the only key of a table
therefore you can use an alternate key to retrieve the inserted
IDENTITY:
INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
SET @.id =
(SELECT id_col
FROM x
WHERE key_col = @.key_col)
David Portas
SQL Server MVP
--|||I'm . Given this insert statement (which is being generated from a
SqlDataAdapter):
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
how do I reference column idx to set the value in both cases? 1 - when I
want to set it to an existing idx; 2- when I want to set it to the new idx?
In case 2 I can use the @.@.IDENTITY to get the identity of the column but how
do I assign it so it's returned back. Maybe I'm with the way the
SqlDataAdapter gets the values back.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109179471.440296.167900@.f14g2000cwb.googlegroups.com...
> I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
> suggested but anyway IDENTITY should never be the only key of a table
> therefore you can use an alternate key to retrieve the inserted
> IDENTITY:
> INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
> SET @.id =
> (SELECT id_col
> FROM x
> WHERE key_col = @.key_col)
> --
> David Portas
> SQL Server MVP
> --
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u9rGUwcGFHA.332@.TK2MSFTNGP10.phx.gbl...
> This table is a read-only table except for a single user. The inserts are
> done like this:
> insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
> scope_identity() as idx
I could be wrong, but it looks to me like that is two separate statements:
1) insert into resdata(resdata, data, alf) values(@.p1, @.p2, @.p3)
2) select scope_identity() as idx

> The goal is to check and see if there is a row with resdata =
> inserted.resdata. If so, the existing record needs to be copied to another
> table and the new one inserted.
I think what your trigger needs to do is something like:
--Backup Matched Rows
INSERT INTO backuptable
SELECT * FROM maintable INNER JOIN inserted ON
maintable.resdata=inserted.resdata
--Delete Matched Rows
DELETE From maintable
WHERE EXISTS(
SELECT * from mainTable INNER JOIN inserted
ON maintable.resdata = inserted.resdata)
--Add All Rows
INSERT Into maintable
SELECT * from Inserted
Good Luck,
Jim

need help writing batch without cursor

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.
description of what i'm trying to do:
there is TABLE1, TABLE2, and TABLE3
i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3
how do i do this in a T-SQL batch?
thanks in advance!What kind of analysis?
AMB
"iaesun@.yahoo.com" wrote:

> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
> description of what i'm trying to do:
> there is TABLE1, TABLE2, and TABLE3
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
> how do i do this in a T-SQL batch?
> thanks in advance!
>|||i'd be curious how to do it even if there were no analysis, since it's
the row-by-row part that i'm not sure how to do in a set-based
solution.
but! in case it helps, here's the analysis i was thinking of (pardon
the psuedo-code for the row-by-row portion)
for each ROW in TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
begin
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
end
set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
}|||>> i want to select each row from TABLE1, do some analysis on the data
of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3 <<
Read what you wrote! What kind of spec is that? How do we debug code
which is not here. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. Sample data is also a good idea,
along with clear specifications.
Frankly, it sounds likeyou are splitting this vague Table1 into two
tables when you should be using a VIEW or a column with whatever the
criteria for this split is. The whole idea of databases was to avoid
redundant data.|||You don't know Joe.
Maybe he's actually normalizing table1.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112986095.913817.293370@.g14g2000cwa.googlegroups.com...
> of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3 <<
> Read what you wrote! What kind of spec is that? How do we debug code
> which is not here. Please post DDL, so that people do not have to guess
> what the keys, constraints, Declarative Referential Integrity,
> datatypes, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications.
> Frankly, it sounds likeyou are splitting this vague Table1 into two
> tables when you should be using a VIEW or a column with whatever the
> criteria for this split is. The whole idea of databases was to avoid
> redundant data.
>|||Try,
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"iaesun@.yahoo.com" wrote:

> i'd be curious how to do it even if there were no analysis, since it's
> the row-by-row part that i'm not sure how to do in a set-based
> solution.
> but! in case it helps, here's the analysis i was thinking of (pardon
> the psuedo-code for the row-by-row portion)
> for each ROW in TABLE1
> {
> if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
> begin
> insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
> end
> set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
> insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
> }
>|||Correction,
Swith the order of the statements.
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"Alejandro Mesa" wrote:
> Try,
> insert into t2 (colA)
> select col1
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
> insert into t3 (colB, colC)
> select col2, col3
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
>
> AMB
> "iaesun@.yahoo.com" wrote:
>|||well, i'm trying to keep the topic abstract, because i was hoping for a
more general description of how to do row-by-row processing in a
set-based solution. but, if such details are needed in this case, then
let me try to invent some. regarding the latter portion of your
message: this is, in a manner of speaking, splitting table1 into two
tables. however, it is more of a complex tranformation, not redundant
information. table1 is a staging table, and will be dropped after this
process is complete.
first, the three table definitions:
CREATE TABLE [sourcetable] (
[ID] [int] NOT NULL,
[column1] [int] NULL,
[column2] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable1] (
[ID] [int] NOT NULL,
[columnA] [int] NULL,
[columnB] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable2] (
[ID] [int] NOT NULL,
[columnY] [int] NULL,
[columnZ] [int] NULL,
) ON [primary]
GO
what i would like to do, read [sourcetable] row-by-row. for each row, i
would like to perform the following batch:
IF NOT EXISTS (SELECT * FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1)
begin
INSERT INTO destinationtable1 (columnA) VALUES
(sourcetable.column1)
end
SELECT @.idvariable = ID FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1
INSERT INTO destinationtable2 (columnY, columnZ) VALUES
(sourcetable.column2, @.table2id)
is that any clearer?|||yes, that is precisely the nature of this task. table1 is an imported
table from an outside system. i'm just splicing it into its logical,
normalized form.|||> what i would like to do, read [sourcetable] row-by-row. for each row,
i
would like to perform the following batch
Wrong. The idea is precisely to AVOID processing anything row-by-row.
Try this:
INSERT INTO destinationtable1 (columna)
SELECT DISTINCT column1
FROM sourcetable
WHERE NOT EXISTS
(SELECT *
FROM destinationtable1
WHERE columna = sourcetable.column1)
INSERT INTO destinationtable2 (columny, columnz)
SELECT S.column2, D.id
FROM sourcetable AS S
JOIN destinationtable1 AS D
ON S.column1 = D.columna
David Portas
SQL Server MVP
--