Is using a SP return value bad technique?

I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
SteveUsing a return value to return simple integer scalal values is *THE* way to do what you want.

Returning a scalar values using a recorset with just one row and one column is too expensive.

You cannot return "Yes" or "No" with return values anyway, just integers are allowed. If you need to return "yes" or "no" in a string format use output values.|||My fault. That was a complete lapse of brainpower on my part. What you described is exactly what I meant to say(either a 1 or 0 for true or false). Oh well. That's what I get for working on a Saturday.

Thanks,
Steve|||Using a return value to return simple integer scalal values is *THE* way to do what you want.

Absolutely not.

Use an ouput variable and leave the return code alone...

Even if you specify

Return -1

For example, SQL Server in some cases can and will override the value...

So if you code for it, it could be a problem.|||Brett I've never had any problem using return values. Even BOL doesn't mention it. That would be awful! :)

Anyway what i wanted to evidence is that returning as scalar value in a recordset is a bad idea. Some more info here:

http://www.sqlteam.com/item.asp?ItemID=2644|||Yeah, I remeber Bills article.

But it was after a long thread that I think Arnold or Nigel identied/explained the problem.

I then went on and posted an example of where the return value was over ridden, making an output variable the only safe way.

I should blog that one...|||Well, surely it'll be an interesting read. Please do it.

Btw this "feature" seems to be more a bug than anything else...isn't it?|||Here's the thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35642sql

Is this possible? SQL Server 2000 Write times

No timezone discrepancy, they did it in our labs here at our institution. I
didn't think so, but had to ask. What logs would i search and what would I
look for?

Shawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
>
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?Shawn Ferguson wrote:
> No timezone discrepancy, they did it in our labs here at our
> institution. I didn't think so, but had to ask. What logs would i
> search and what would I look for?
>
That would be a question for your web server folks. Your web server
should maintain logs of the visits to the web site.|||> No timezone discrepancy, they did it in our labs here at our institution.
That doesn't mean that some computer along the way doesn't have an incorrect
timezone and/or system time specified!

Is this possible? SQL Server 2000 Write times

I created an online application for one of our educational programs. The da
ta is inserted via website entry -> client side validation -> stored procedu
re -> database. Given a table stucture as follows, if 100 or even 1000 peop
le applied at the exact same time using an online application, what is the m
aximum time interval that could separate each record. How long would SQL Se
rver 2000 generally take to insert each record into the database. The probl
em is that each year, only the top 100 students are admitted in the program,
but there is one student who says they applied at 9:00 am and their record
insert time shows 9:59am. There are records before it and after it that sho
ws times like 9:58, 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL
Server 59 minutes to actually write the record if 1000 people hit the datab
ase at the same time? Please help, they want to get counsil involved.
ApplicantID int primary key identity
SSN varchar(11)
FirstName varchar(30)
LastName varchar(50)indexed
Address varchar (50)
City varchar (50)
State char (2)
Zip varchar (11)
Phone varchar (20)
Email varchar (50)
dateEntered smalldatetimeShawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?

Is this possible?

Hi,

I want to write some t-sql that selects data from a different database from which the tsql is being written in, some like this:

there are 2 databases A and B

I want to select the unit price from database A where the ID feild of database A is equal to the ID feild of database B

kinda something like this pseudo code:

select UNIT_PRICE

FROM Prices

WHERE Prices.Product_ID = (DATABASE B)Prices.Product_ID

can somebody please give me an example of how this can be done

Many Thanks

Try:

select unit_price

from dbo.prices as a

where product_id in (select b.product_id from another_db.dbo.prices as b)

-- or

select unit_price

from dbo.prices as a

where exists (select * from another_db.dbo.prices.product_id as b where b.product_id = a.product_id)

-- or

select a.unit_price

from dbo.prices as a inner join another_db.dbo.prices as b

on a.product_id = b.product_id

AMB

|||

But before executing the query you have to setup those remote database server as linked server in your current database.

here the sample code to setup the linked server..

Code Snippet

EXEC sp_addlinkedserver

@.server = 'ServerBAliasName',

@.provider = 'SQLOLEDB.1',

@.srvproduct = '',

@.provstr = 'Privider=SQLOLEDB.1;Data Source=ServerB;Initial Catalog=Database'

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'ServerBAliasName',

@.useself = true,

@.locallogin = null,

@.rmtuser = 'Userid',

@.rmtpassword = 'Password'

--Later you can use any of the above query to get the result

|||

Good point Manivannan, but just if they reside in a different server.

AMB

|||Yes... if it is on different servers then we should have the Linked Server|||Thanks for you help guys. It is much appreciated.

Is this possible?

I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:

>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.

Is there query I can write to remove empty tags from a xml field?

I want to remove empty tags:

<name></name>

from an xml type field in a table. Can I do it using a query and a modify?

Use the modify method and the XQuery delete instruction as follows (uses an XML variable but can of course also be done with a column of type XML):

Code Snippet

DECLARE @.xml xml;

SET @.xml = '<root>

<name/>

<name></name>

<element>

<name />

</element>

</root>';

SET @.xml.modify('

delete //*[not(node())]

');

SELECT @.xml;

|||

PERFECT!!! I am thrilled. Easy and simple and solved my problem.

Is there cascading permission?

Let's say that I have a view that has no explicit permissions defined for
users. Let's also say that I write a stored procedure which uses this view.
Now, if I give explicit permissions to a user to EXEC the stored procedure,
will the procedure execute correctly even if I don't explicitily give
permissions (such as SELECT) for the user to the view?
Michael HocksteinYes, it is.
"michael" <howlinghound@.nospam.nospam> wrote in message
news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
> Let's say that I have a view that has no explicit permissions defined for
> users. Let's also say that I write a stored procedure which uses this
> view.
> Now, if I give explicit permissions to a user to EXEC the stored
> procedure,
> will the procedure execute correctly even if I don't explicitily give
> permissions (such as SELECT) for the user to the view?
>
> --
> Michael Hockstein|||So, as long as a user has permissions to execute a stored procedure, the
stored procedure will execute correctly even if the user does not have
explicit permissions defined for the objects consumed within the stored
procedure?
If the user has permissions to execute a stored procedure but that the user
is explicitly denied permissions to objects consumed by the stored procedure
will the stored procedure still execute correctly?
--
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||So, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user does not have explicit permissions
defined, the stored procedure will execute without security issues?
And, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user is explicitly denied permisions, will th
e
stored procedure still execute without security issues?
Where can I find documentation on how this security cascades?
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
yes, it will , unless you have dynamic sql within a stored procedure , then
you'll have to grant permision on undelaying tables

> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
Yes , it will
"michael" <howlinghound@.nospam.nospam> wrote in message
news:2DF71CC1-0FAF-4D82-A396-782AC53757C9@.microsoft.com...[vbcol=seagreen]
> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||See:
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"michael" <howlinghound@.nospam.nospam> wrote in message
news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...[vbcol=seagreen]
> So, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user does not have explicit permissions
> defined, the stored procedure will execute without security issues?
> And, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user is explicitly denied permisions, will
> the
> stored procedure still execute without security issues?
> Where can I find documentation on how this security cascades?
>
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||Thanks. I'll look at the reference. BTW, your tag line is one of my favorite
all time sayings.
Michael Hockstein
"Arnie Rowland" wrote:

> See:
> Security -Giving Permissions through Stored Procedures
> http://www.sommarskog.se/grantperm.html
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...
>
>|||Check out 'ownership chains' in the SQL Server Books Online. The principal
is basically that permissions on indirectly referenced objects are not
needed as long as the objects (or schema on 2005) involved have the same
owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:0EED2BFD-1EA5-4DA8-A512-C4D8E91CEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks. I'll look at the reference. BTW, your tag line is one of my
> favorite
> all time sayings.
> --
> Michael Hockstein
>
> "Arnie Rowland" wrote:
>

Is there anyway to issue a http POST from SQL Server?

Just wondering if there is a way to issue a http post from SQL? Basically I
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>

Is there anyway to issue a http POST from SQL Server?

Just wondering if there is a way to issue a http post from SQL? Basically I
want to write an insert trigger that will create a SOAP msg and then send it
to a webservice, anyone know if there is an internal mechanism in
SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
thinking an answer might be to write it in C and the create an Extended
Stored Procedure but was ondering if there was another way.
It's not SQL based, but there is a way in SQL Server 2005, using .NET
procedure or trigger. It's conceivable you might do the same in SQL Server
2000 using SP_OACreate and friends, but IIRC, the COM component that
implements this (from Wininet?) may not be thread safe. Your own XP is
another way.
Bob Beauchemin
http://www.sqlskills.com/blogs/bobb
"James Morton" <james_morton@.hotmail.com> wrote in message
news:OUoPHrroFHA.420@.TK2MSFTNGP09.phx.gbl...
> Just wondering if there is a way to issue a http post from SQL? Basically
> I
> want to write an insert trigger that will create a SOAP msg and then send
> it
> to a webservice, anyone know if there is an internal mechanism in
> SQL/SQLXML/SOAP Toolkit or otherwise that will allow you to do this? I am
> thinking an answer might be to write it in C and the create an Extended
> Stored Procedure but was ondering if there was another way.
>

Is there anything like rowid, rownum like in MySql and Oracle?

Hi,

i am new to SQL Server. I want to write a query where in i want to delete duplicate rows from a table keeping the master copy.

If it is MySQL or Oracle we can write that using built in rownum or rowid. How to do that task in SQL Server 2005. Is there anything like rowid, rownum in SQL Server? If not suggest me a way to do that?

...aazad

not exactally , but u can make use of 'TOP' or row_number() function..

select top 1 from table 1 order by column1

u can use top intelligently to get top/bottom nth row... given u have somethin to orderby

|||Rownum is a psuedo column that generates a logical sequence number so it will change depending on the query execution plan, data etc. Rowid on the other hand is a physical identifier (at least in Oracle). So how are you using these in your queries? What is the purpose of using something like ROWID? You do have primary key or unique key constraints on your tables right! It will be easier to suggest the alternatives if we know your use cases.|||

Hi chandar,

There can be a senario where a table has no primary key and has data. Later when i want to make a column as primary key, i need to delete the duplicates, which i dont want to do it manually. so i shud write a query where i can delete duplicate rows keeping one copy of it. I worked with MySQL and in MySQL i can write a query as follows

delete from test where rowid in ( select rownum from test where rownum not in ( select min(rownum) from test group by all_columns having count(*) > 1 ) group by all_columns having count(*) > 1

The above code deletes the duplicates the master copy in MySQL. I am using that logical column rownum. How to do the same job in SQL Server 2005?

Regards..,

Aazad

|||

okk...lect us say u want to make column1 as ur primary key in table1 , so to find out the duplicate(or more) entries of this key , use the following query...

select column1 from table1

group by column1

having count(column1)>1

this will enlist all the entries for column1 which r repeating...

|||

Thank god .. you are using SQL Server 2005 use the following query

Example:

CREATE TABLE Table1

(

[Id] [int] NULL

)

go

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(20);

INSERT INTO Table1 values(20);

go

With Test(rownum,ID)

as

(

Select Row_Number() OVER (ORDER BY ID), * From Table1

)

Delete From Test Where rownum in

(

Select A.rownum From Test A JOIN Test B On B.Id=A.ID and A.rownum >= B.rownum

Group bY A.rownum,A.ID Having Count(A.ID) <> 1

)

|||hi
use newid() function
good luck

Is there any way better than XP_CMDSHELL?

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanksYou could BCP the SQL file into a temporary table and then execute the code through dynamic SQL, but that is pretty round-a-bout.

Is there any sample code to demo the SSB send messages with same sql instance?

Is there any sample code to demo the SSB send messages with same sql instance?

my case is very simple:

I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.

this should be exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Is there any Property Like Rownum in oracle

Hi All...

I have to write one query where i have to display the ID as 1,2 3, 4 in oracle we used to ROWNUM to display ...in MS SQL server is there any property to show?

i have to display like

ID ProgramCount
1 4
2 6
3 5
4 1
5 2

AshutoshHave look here:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx