Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew = Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:
> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
> > Hi,
> > Is this the correct syntax for this Access pass-thru query? I'm new to
> > TSQL and I have a book on order but I still need to move forward with
> > this project while I'm waiting for it.
> > I want to update a field in a SQLServer table,
> > BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> > to strip one off so I had the dba create a new field BillingNumberNew
> > which I trying to update with BillingNumber minus one leading zero.
> > Would this be correct?
> > UPDATE tblPhysLineFinal
> > SET tblPhysLineFinal.BillingNumberNew => > Substing(tblPhysLineFinal.BillingNumber,
> > 2,Len(tblPhysLineFinal.BillingNumber)-1)
> > thanks
> > bobh.- Hide quoted text -
> - Show quoted text -

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 there anything similar in TSQL to oracle code "SEQUENCE.NEXTVAL

Hello,
I trying to do a SELECT that will show me the next value automatically
everytime.
for example in Oracle, I can do
Select SEQUENCE.NEXTVAL from Dual
1
Select SEQUENCE.NEXTVAL from Dual
2
Select SEQUENCE.NEXTVAL from Dual
3
with Oracle there is a function that allows me to use auto increment value
without specifying for insert into the table.
Can I do the same with a TSQL? Anything in TSQL close to this function.
Please help. Thanks again.No there isn't a function that can do this. But can you tell what is the
actual requirement, so that we can give you an alternative
--
"sqlapprentice" wrote:

> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||You can look into using IDENTITY, but it really depends on what you are
trying to accomplish.
"sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
news:C5A27B55-64ED-468F-A61B-5DC4715687BE@.microsoft.com...
> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||in SQL server, one uses the IDENTITY attribute of a column to specify
what would be similar to an Oracle Sequence.
CREATE TABLE mytable(
entryid INT IDENTITY(1,1),
entrydata VARCHAR(100)
)
Use the following to get the current value of the "Sequence"
DBCC CHECKIDENT ('owner.tablename')
in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
and also check out DBCC CHECKIDENT.|||"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1146499671.388074.220710@.i39g2000cwa.googlegroups.com...
> in SQL server, one uses the IDENTITY attribute of a column to specify
> what would be similar to an Oracle Sequence.
> CREATE TABLE mytable(
> entryid INT IDENTITY(1,1),
> entrydata VARCHAR(100)
> )
> Use the following to get the current value of the "Sequence"
> DBCC CHECKIDENT ('owner.tablename')
> in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
> and also check out DBCC CHECKIDENT.
>
besides DBCC CHECKINDENT, you can also use
select ident_current('table')
or
select max(identitycol) from table
ident_current() is the preferred method of the two.
dean

Is there any way in a sproc to LOOP thru the records of a table ?

Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a 'StoreGroupe'
parameter. I would like to add a case where if the call has NO
StoreGroupe parameter, the sproc should LOOP thru all records in table
StoreGroupeTable, read the column StoreCode, and pass that value as a
param to the other sprocs, as in:

CREATE PROCEDURE MySproc

(
@.StoreGroupe nvarchar(6) = NULL
)

AS
if (@.StoreGroupe is not null)
Begin
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
End
Else
Begin
A 'Group Code' has NOT been specified
I want to take all the StoreGroups in table
StoreGroupeTable, in turn.
I would like to do SOMETHING LIKE THIS:
Do While not [StoreGroupeTable].EOF
Read [Code] from [StoreGroupeTable]
Set @.StoreGroupe = The value I just read
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
Loop
End
GO

Is that feasible in a sproc, or do I have to do this in the client
(ADO) ?

Thanks a lot.
Alex.You can do this using a cursor - executing a stored proc repeatedly for
each value in a column is one of the (few) cases where they're useful.
In performance terms it would probably be better to rewrite proc1,
proc2 etc. to operate on a set of values using set-based code (but that
may not be possible, of course, depending on what the procs are doing).

declare @.StoreGroupe int

declare cur cursor local static
for select SomeCode from dbo.StoreGroupes

open cur
fetch next from cur into @.StoreGroupe
while @.@.fetch_status = 0
begin
exec dbo.proc1 @.StoreGroupe
exec dbo.proc2 @.StoreGroupe
-- etc.
fetch next from cur into @.StoreGroupe
end

close cur
deallocate cur

Simon|||Thanks a lot, Simon, I'll implement this right now.

Thanks again ! :-))))

Alex.|||You have not learned to think in SQL yet and are still writing 3GL
procedural code. Let's get back to the basics of an RDBMS. Rows are not
records; fields are not columns; tables are not files.

>> I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a 'StoreGroupe' parameter. <<

Why?? You have forgotten or never learned the very Basics of
programming. Remember why we NEVER begin a name with an underscore?
Remember coupling and cohesion in your first Software Engineering
class?

Since you did not tell us anything about the modules, give us DDL or
even a hint of a specification, it is impossible to tell exactly what
is happening, but these are the basics. However, each module should
handle a NULL parameter on its own. If you reallllly want to write
stinking bad code, then use a CURSOR, keep writing separate modules
that are incomplete, etc.

If you post more specs, you will get more help. Otherwise yiou will
get kludges.|||plz tell me the punchline-- why do we never use underscores?

ps - i agree, loops on the TSQL side are for newbies

Is there any REGEXP library for TSQL?

Hi guys,

Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.

yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos

Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.

--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.

It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.

But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.

In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.

I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
>> routine that uses the RegEx classes in .Net.
> I guess for SQL 2000, you could use a non-COM library as an "extended
> procedure"?

But performance would be awful and the code would be messy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.

I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?

I imagine the call could be as:

select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)

Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.

> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)

That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.

Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.

> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.

Then when are XPs actually useful?

> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.

I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?

When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)

Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".

But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.

Thanks for the explanation.

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com