doubt with sql and an store procedure

I'm working with the stored procedure editor of the Visual Web Developer, and I have this:
ALTER PROCEDURE dbo.ClasificacionSelectTodas
(
@.visibilidad bit = 1
)
AS
SET NOCOUNT ON
SELECT Clas_Id, Clas_Madre, Clas_Titulo, Clas_Descripcion, Clas_Prioridad, Clas_Visibilidad
FROM Clasificacion
WHERE (Clas_Visibilidad = @.visibilidad)
As you see, if I don't set any value to "@.visibilidad" it returns me the registries where visibilidad = 1
But what I want is that if I don't set any value to visibilidad, itturns to me all the registries. I was trying giving a default value tovisibilidad like "*" o "?", but it gives me an error.
Any idea?
Thanks
ALTER PROCEDURE dbo.ClasificacionSelectTodas
(
@.visibilidad bit = null
)
AS
SET NOCOUNT ON
SELECT Clas_Id, Clas_Madre, Clas_Titulo, Clas_Descripcion, Clas_Prioridad, Clas_Visibilidad
FROM Clasificacion
WHERE (Clas_Visibilidad = IsNull(@.visibilidad,Clas_Visibilidad))
This will compare Clas_Cisibilidad to the variable passed in (if not null) or with itself (which will always be true).|||Thanks douglas, it works great
Congratulations for your book!
|||Glad it helped.
Also, I am actually working on a new book, tentatively titledStreamlined Web Forms Development. Should be out by ASP.NET 2.0 release, and will make a great stocking suffer<g>.|||I'm sure it will be highly interesting, I'll wait for it ;)

Doubt with Client Network Utility

Hi. I've been worling with Ms SQL Server 7 / 2000 and on every installation
I've done, after installing the Server I had to Install Connectivity Only on
Clients in order to configure Client Network Utility to add the Server
Alias.
Does MSDE have an option like this ? Does MSDE Needs a Client Network
Utility on a Client PC?
Thanks,
Juan M. Rodriguez
Servicios Profesionales
teg solutions argentina s.a.
juan@.tegsolutions.com
Tel: 54-11-4590-2222
Fax: 54-11-4590-2201
www.tegsolutions.com
La calidad no est en el trabajo que hace la gente, sino en la gente que
hace el trabajo.
hi Juan,
"Juan Rodriguez" <juan@.tegsolutions.com> ha scritto nel messaggio
news:O40GyxCZEHA.2812@.TK2MSFTNGP11.phx.gbl...
> Hi. I've been worling with Ms SQL Server 7 / 2000 and on every
installation
> I've done, after installing the Server I had to Install Connectivity Only
on
> Clients in order to configure Client Network Utility to add the Server
> Alias.
> Does MSDE have an option like this ? Does MSDE Needs a Client Network
> Utility on a Client PC?
> Thanks,
MDAC installs the Client Network Utility (cliconfg.exe) as well... and it's
usually required from all apps... =;-)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks !
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:2l2h5lF7msb3U1@.uni-berlin.de...[vbcol=seagreen]
> hi Juan,
> "Juan Rodriguez" <juan@.tegsolutions.com> ha scritto nel messaggio
> news:O40GyxCZEHA.2812@.TK2MSFTNGP11.phx.gbl...
> installation
Only
> on
> MDAC installs the Client Network Utility (cliconfg.exe) as well... and
it's
> usually required from all apps... =;-)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Doubt to be Clarified

hi

I have a backup up strategy as follows
Differential - every 4 hrs(4am,8am,12pm,...)
transaction - every 10 min

I am using SQL SERVER 2000.

At Some point of time my Differential and Transaction backup clashes
at(4am,8am,...)

when i Check my Entriprise Manager(Locks/process id)
I find Spid (blocked).

1)spid = 661

Properties Window
BACKUP DATABASE [imcl] TO [DiffBkp_IMCL] WITH NOINIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'IMCL Device Differential Backup', NOSKIP ,
STATS = 10, NOFORMAT

2)spid 708 blocked by 661
Properties Window
BACKUP LOG [imcl] TO DISK =
N'F:\DatabaseBackups\IMCL\Tran\NewTranImcl\imcl\im cl_tlog_200505261200.TRN'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

What I want to know is - :
a)Should I ignore this Blocking as it is solved automatically.

b)Whether my backup plan is poor.

c)Will this effect my users who are connected to my Server.

Thank u in advanceThe blocking will be resolved, in that spid 708 is essentially 'on
hold' until 661 completes. Probably the easiest way to avoid the issue
is to set the log backups to run every ten minutes but starting at five
minutes past the hour instead of on the hour, which I guess is what you
currently have.

If the differential backup takes more than 5 minutes, then you'll still
get the blocking, though, so if this is the case, then you might want
to create multiple active schedules for your transaction log backups -
the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
whatever interval allows the differential backup to complete without
blocking) etc.

Your backup plan is fine in principle - see "Reducing Recovery Time" in
Books Online (provided that you're also making full backups at some
point, of course).

Simon|||Thank you Simon for advice

Simon Hayes wrote:
> The blocking will be resolved, in that spid 708 is essentially 'on
> hold' until 661 completes. Probably the easiest way to avoid the issue
> is to set the log backups to run every ten minutes but starting at five
> minutes past the hour instead of on the hour, which I guess is what you
> currently have.
> If the differential backup takes more than 5 minutes, then you'll still
> get the blocking, though, so if this is the case, then you might want
> to create multiple active schedules for your transaction log backups -
> the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
> whatever interval allows the differential backup to complete without
> blocking) etc.
> Your backup plan is fine in principle - see "Reducing Recovery Time" in
> Books Online (provided that you're also making full backups at some
> point, of course).
> Simon

Doubt regarding PFS

Does SQL Server use PFS to check whether a page is allocated or not if the
table has a clustered index.
Regards
BalajiPFS is used for determining if there is enough free space on the page for
the row but only on Heaps.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:C5E97806-A784-4B47-9746-EA01C5A3F84A@.microsoft.com...
> Does SQL Server use PFS to check whether a page is allocated or not if the
> table has a clustered index.
> Regards
> Balaji

Doubt on unique non clustered Index

Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:

> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:

> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>

Doubt in Transaction Scope

Hi ,

I have 2 packages and they get executed in SQL Job.

Each of these package will have seperate transactions with the Database and I am not sure of how to maintain the transactions between these 2 packages.

Can someone explain me the necessary setting to achieve this. If I set the TransactionOption property to Required (or) Supported in both these packages, will the Transaction Scope of the first package extend to the second one.

For me, If the second package ends up in error, the first package should also have to be rollbacked.

Any suggestions would be of great help.

Thanks

Evan

Are the two packages setup so that one is parent and the other is child?

Otherwise, I'm not sure how you can enforce transactions. If so, then set each to "Required" and be sure to set the FailParentOnFailure property to true in the child so that when it fails, so does the parent.

Doubt in select query

my table ha follow structure..
SalaryTable:
Value Salary
--
Jan 1000
Feb 2000
Mar 3000
Apr 4000
i want the o/p as..
Jan Feb Mar Apr
1000 2000 3000 4000
Pls give me a query for this
Regards,
SatheeshHi,
It is not straight forward in SQL 2000, you need to write logic. See the
below URL:-
http://www.sqlteam.com/item.asp?ItemID=2955
Thanks
hari
SQL Server MVP
"BabuLives" <satheeshbabu.b@.gmail.com> wrote in message
news:1130138243.780789.195160@.g43g2000cwa.googlegroups.com...
> my table ha follow structure..
>
> SalaryTable:
>
> Value Salary
> --
> Jan 1000
> Feb 2000
> Mar 3000
> Apr 4000
>
> i want the o/p as..
>
> Jan Feb Mar Apr
> 1000 2000 3000 4000
>
> Pls give me a query for this
>
> Regards,
> Satheesh
>

Doubt in Index

Hi EverB,

I have created a table say T1 with 5 columns (col1.....col5).
I have created an index on col4 and col5.

iam executing a select query say.

select col1,col2,col3 from t1 where col1 =0;

In the above query i hav not used the columns for which i hav created the index.My ques is-

Does the index really works when u execute the above query?

Any answer please.......
It wuld be better if u culd suggest some sites for this.

Thanks and Regds,
Arunno, for that query, the index is ignored

a good tutorial is Optimizing your MySQL Application (http://www.sitepoint.com/print/optimizing-mysql-application) even if you're using something other than mysql

doubt in crystal report(help needed Urgently)

hai

I have one doubt in crystal report.I am using crystal report 7 version.I added a databse to report and i connected that report with visual basic and my report is working.Except that i cant refresh my report at runtime and so it displays the same values repeatedly.

I cant use crystal report 8 or 9 or 10.

Help needed urgently

Bye
Ramyafrom file menu of crystal report , uncheck the option of 'Save Data with Report', then save the file and try .|||hai

i have one doubt i am using crystal report from visual basic and not directly from crstal report.

That is i added report at project itself and so where to change that option

Bye
Ramya

Doubt in " HOW TO: Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .

Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/defaul...Ben-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK."RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.

The article doesn't mention SQL Server anywhere, and it sounds like your
problem is how to automate something in Excel, so I guess you'll get a
better answer in an Excel or C# newsgroup.

Simon|||RK (rakish123@.yahoo.com) writes:
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?

Of course there is.

I have not tried to get data from Excel with SELECT statements, but
would be surprised if "SELECT * FROM Sheet1" would work. I've mainly
use the OLE interface to get data from Excel in Perl. While I understand
that you don't want users to have to highlight things, I fail to see
but that you need to have some pre-defined structure that the Excel
file must have, or else you will have no idea of what you are importing.

But while you said SQL Server twice in your posting (look again Simon! :-),
your question seems more to be related to Excel, so you should find a
group about Excel.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might look at the capabilities of SQL Server DTS Packages. They can
read from Excel spreadsheets. However, your datasheet would have to be
massaged enough that the data was a fairly straightforward import.

I suppose if I were you, I'd use a combination of VBA code to hack up the
spreadsheet, save it to a convenient spreadsheet, then use SQL-DMO to
trigger a DTS package for the import.

Otherwise, you could just do a VBA module that would pass in the interesting
data directly over an ADO, OLEDB or ODBC connection. We've done something
like that, it wasn't terribly difficult.

"RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.|||Hi,

There are a few alternatives. I recommend you to use SQL*XL an addin I
wrote for Excel to do these things. There are some other alternatives
which I will mention.

1. SQL*XL - addin for Excel to access your databases.
SQL*XL is an addin for Excel that allows you to access your database
directly from Excel. You can query data into Excel or you can pump
data from Excel into the database. SQL*XL focusses on making it easy
for the end user but it can also be used to make scripts as it is
fully compatible with Excel macro recording and VBA.

Have a look at SQL*XL at: www.oraxcel.com

2. Define your Excel workbook as a data source in ODBC
If you do this you can issue select statements against it. You can use
it in an openquery statement in SQL server or even make it a linked
server I suppose. Named ranges will appear as tables. Reserve row 1
for the column headers.
Problems with this technique: it is readonly, you may have problems
with accessing the workbook (in use)
The nice thing is that you can use it directly in SQL or DTS

3. You can write a little macro in Excel that dumps the file out in a
nominated format (CSV e.g) and a nominated directory. You could have
DTS to pickup the file and import it in your database table.

Hope this helps,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Author of SQL*XL

doubt about sqlserver data types

I'm working with sqlserver express 2005, and I have a lot of doubts about what sqlserver data types use. More exactly my doubt is about text types... nchar, nvarchar, varchar... what are the differences?

thanks.::More exactly my doubt is about text types... nchar, nvarchar, varchar... what are the
::differences?

I really hate to tell you, but there is a documentation for SQL Server that has exact definitions of every data type.

Doubt about SQL Server

I need an application to obtain the databases names from my SQL
Server, using Visual Basic 6.0.
Can somebody hel me?(aguzmanc@.gmail.com) writes:

Quote:

Originally Posted by

I need an application to obtain the databases names from my SQL
Server, using Visual Basic 6.0.
Can somebody hel me?

SELECT name FROM master..sysdatabases -- SQL 2000 or earlier
SELECT name FROM sys.databases -- SQL 2005.

Or what is you want help with?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Doubt about connections to sql server.

Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
See if this helps:
http://support.microsoft.com/default...b;en-us;306625
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***

Doubt about connections to sql server.

Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
http://support.microsoft.com/defaul...kb;en-us;306625
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***

Doubt aboult loop in trigger?

Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
ThanksHi,
Anything done inside/called from the trigger will be in a transaction so can
cause blocking which can really quickly degrade the performance and
experience the user gets.
What loopnig are you trying to do, please post your trigger code.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I should do this.
> Thanks
>|||Generally, processing one row at a time inside a loop is not efficient,
compared to processing all rows using a single statement. In many cases,
loops and cursors can be avoided by using SET based queries.
Can you show us what is it that you are trying to do, and may be someone can
suggest an alternative for you.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
Thanks|||Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>|||Here's a small example, that uses a numbers table, to replace the loop with
a singel INSERT statement inside your trigger.
CREATE TABLE Trn (TranID int, TotalSum money, TotalInstallments tinyint)
CREATE TABLE Pmts (TranID int, InstallmentNo tinyint, InstallmentAmt money)
CREATE TABLE Numbers (Number tinyint)
GO
SET NOCOUNT ON
GO
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 12
BEGIN
INSERT INTO Numbers (Number)
SELECT @.i
SET @.i = @.i + 1
END
GO
CREATE TRIGGER TrnInsTrg
ON Trn
FOR INSERT
AS
BEGIN
INSERT Pmts (TranID, InstallmentNo, InstallmentAmt)
SELECT i.TranID, n.Number, i.TotalSum/i.TotalInstallments
FROM inserted AS i
INNER JOIN
Numbers AS n
ON n.Number <= i.TotalInstallments
END
GO
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 30000, 3)
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 120000, 12)
GO
SELECT * FROM Trn
SELECT * FROM Pmts
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ucBkH1i3FHA.4076@.TK2MSFTNGP15.phx.gbl...
Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>

doubt

Hi,

How to retrive the health insurance claim cms form in fastest way using sql server 2005

pse be more descriptive about the issue. u have provided very little info to help

Madhu

Doubt

CREATE PROCEDURE emp_ins @.no integer,@.name varchar(20),@.sa integer,@.msg
varchar(20)output AS
if @.no<0
BEGIN
set @.msg='Empno>0'
RAISEERROR(@.msg,10,1)
end
insert into emp values(@.no,@.name,@.sa)

my requirement is i want to implement normal validations when the user exceeds the condition i want to store the message into out parameter and i want to return that message into the application. is it possible or not
waiting for valuable reply
Sri

If you want to abort processing after the error, I would add a RETURN statement immediately after the RAISERROR (still in the BEGIN...END block.)

Code Snippet

CREATE PROCEDURE Emp_Ins
( @.no integer,
@.name varchar(20),
@.sa integer,
@.msg varchar(20) OUTPUT
AS
IF ( @.no < 0 )
BEGIN
SET @.msg = 'Empno > 0'
RAISEERROR( @.msg, 10, 1 )
RETURN
END
INSERT INTO Emp VALUES ( @.no, @.name, @.sa )

|||Thank u Arnie.
Before u r reply i tried with the same code i got it additionally u given a point that with return statement transaction will be aborted.Arnie u may feel that it may be silly in case of multiple ifs how to write the program like prevoius program because i m ne to sqlserver envi i m much familiar with envi thats wy i m requesting u
Thanks and Regards
Sridhar
|||

Baba urf Sivaji wrote:

i want to return that message into the application. is it possible or not

You should use the proper Severity number & it should be 16 rather 10. You can return the control after the Raiseerror. Now the message will be captured on your UI & the control alos return back to the UI.

RAISERROR (@.msg,16,1);

Return;

|||You need to have the application declare an OUTPUT parameter to capture the value of @.Msg.

Doubt

Hi there

I have a question here. I need to import the excel template into sql server data base tables. I did worked on the regular importing of excel spread sheets to import into the appropriate data base columns. I am not getting idea with the eacel template. My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser. If any body can help i would really appreciate....Welcome to the forums..looks like you signed up just a few mins back.

can you xplain your q in a more detailed way.
>>I am not getting idea with the eacel template.
Did you mean you are not able to use the DTS Wizard to import the excel file into a sql table ?

>> My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser.

I didnt understand this part either.|||Yes I can't use the regular DTS ti import into the data Base tables. Because the the excel templates might be in any order and they have data in any way. I have a sample application with title, genere, format size , frequency, attributes and insertion costs as columns in excel template. The data can come or can't come in exact order as in regular excel spread sheets. I have to read the data cell by cell and import into the data base. For that I can't use the Microsoft Jet provider . I can use the Jet for the regular spread sheets, but not for the templates. The template where I import the data into the data base, I need to make a look up for each and every tables.(ex: Market--country--> string look up int fk to the country table, Devision: string look up fk to a devision table) and at the end I need to display the information to the end user according to his needs. So please tell me any ideas how I can make a look up and how I can read the data from excel template and finally import into sql tables.|||you can prbly write a stored proc that will query from a table . you can import your excel sheet into an sql table. use that as a container..then write a select stmt to query against that table. assuming the column names will be the same no matter what order they are in, this approach might work. IF the column names are also different then it will require a human to read through to identify the columns.|||Do you have any idea about the look ups. How to design the data base for that excel template. Here is I got some sentences...

User uploads an excel sheet template that has been filled out.

Header:

Market -- country string lookup int fk to a country table
Business Group -- division string lookup fk to division table
ALLOCATED BUDGET: float
GRPs (IF APPLICABLE): float
% REACH (IF APPLICABLE): float
OTS (IF APPLICABLE): float
start date smalldatetime
end date smalldatetime
flight type char[1]

row data:

Title string lookup int fk to a media location table
Frequency -- you can ignore insert as it is driven by title
Client cost float
Insertion cost float
Genre string lookup fk to Genre table
Start date smalldatetime
end date smalldatetime
Booking Date smalldatetime
Material Date smalldatetime
Onsale Date smalldatetime
Attrb 1 -- 10 varchars any number of attributes from 0 through 10 posibblities based on flight type (ie circulation format/size) which are
fk to title table

Iam not understanding the what the header is and what the row data is...How sould I make the look ups...Thanks for you answers.|||(1) Market -- country string lookup int fk to a country table
can you xplain what this means.

(2) are you trying to this thing programmatically or manually.|||Iam also understanding that much. If you can explain me what's the look up and how to make a look up in the data base?. What kind of queries you write to get the thing I want that would be great.....

Market -- country string lookup int fk to a country table

I feel if suppose the country is India and country ID is 15, whenever I should enter 15 I need to get India. i think this is the look up...If you knowsome thing about how to make look ups please explain me....

Doubt

I need know what is field equal suid from SQL 7.0 and not exist in
SQL 2000.Use suid.|||What are you trying to do ?|||I wanna mean sid. That column exists in sql7.0 too, but the suid dont exist in 2k now.|||SELECT suid, name, dbname, Admin = sysadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator
FROM master..syslogins WHERE loginname = ' & vfdstrLoginID_App & '

I want the the field equal suid.|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

syslogins.suid --> syslogins.sid
sysdatabases.suid --> sysdatabases.sid
sysremotelogins.suid --> sysremotelogins.sid
sysusers.suid --> sysusers.sid
sysalternates.suid --> sysusers.isaliased
sysalternates.altsuid --> sysusers.isaliased

The related niladic functions also have changed: (the old ones are now "broken")
SUSER_ID () --> SUSER_SID (), returns the user sid given the user name
SUSER_NAME () --> SUSER_SNAME (), returns the user name given the sid

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

SELECT
SUSER_SNAME(syslogins.sid) AS '"user name"',
syslogins.loginname,
syslogins.sid,
syslogins.name,
syslogins.dbname,
syslogins.hasaccess,
syslogins.bulkadmin,
syslogins.dbcreator,
syslogins.diskadmin
FROM
syslogins
INNER JOIN
sysusers
ON
syslogins.sid = sysusers.sid|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

For another example see: http://dbforums.com/t561459.html|||syslogins.suid --> syslogins.sid
Binary Hexadecimal

Not is funcionaly in my programation? I want
retorn a value binary, with is the suid ald what retorn
the value sequential of numbers.|||Check out the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;en-us;104829)|||An MSDN link on SUSER_ID: (may be helpful?)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_00mc.asp
As stated in the link: "SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. In SQL Server 7.0, the security identification number (SID) replaces the server user identification number (SUID). SUSER_ID always returns NULL when used in Microsoft SQL Server 2000."|||Okay.
SELECT SUSER_ID ('sa') in SQL7.0
--
1 --> Binary
(1 row(s) affected)

SELECT SUSER_SID ('sa') in SQL2000
--
NULL

(1 row(s) affected)
OR
SELECT SUSER_SID('sa')
--
0x01 -- Hexa
(1 row(s) affected)

I want number sequential and binary per users, understand?|||Q1 I want number sequential and binary per users, understand?

A1 Maybe, (for the most part suid columns were used in joins to return information). Apparently your requirement is different.

Q1 Are you saying the application you are working with requires a 1s and 0s representation of Binary numbers? for example:

User IntRep HexRep Binary
Tom 10 0xA 1010
sa 1 0x1 0001

If you need to use the string '1010' for user Tom instead of 0xA, you could create a function or stored procedure to return 1010 (given a hex representation)?|||What are you trying to do (deja vu) ?

doubt

hi,

How to search a word in the sqltable?.ie suppose the field name has data type nvarchar(200). From there i enter the persional address of that persion. now i want to know the information whoes are coming from particular city?. The city value can enter in the address field. how we can make search?...

with regards,
ks.kumar

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...753175428d29c27
*****************************************You can use LIKE:

... WHERE col LIKE '%London%'

but this type of search is costly.

If you need to do this type of search then I suggest you separate the
address with a different column for each element: building, street, city,
state/region, postal code, country, etc. This is much easier and more
efficient to search.

--
David Portas
SQL Server MVP
--