How to Script Muplitple SPROCS/VIEWS at one time

In Enterprise Manager one can select several SPROCS/VIEWS using the
CONTROL key and then Right-Click to script out those objects.
Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to
create the selected objects.
SQL Management Studio seems to only allow you to script one object at a
time.
Is there a way in SQL Management Studio to select multiple objects and
generate create or modify scripts?Hi, naviagte to the database node and script the objects using the
wizard, select only the procedures you want to script.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

How to script all sql jobs

Hello,

Is there a way in sql2k5 to script all sql jobs at once. In sql2k you could right click on the agent and select that option however I do not see it in sql2k5. Thanks.

Hi John. Click the 'Jobs' folder under the SQL Server Agent node in SSMS, then hit the F7 key (brings up the Summary pane). Highlight all the jobs you want to script using a combination of Shift and Ctrl keys, then right click, Script Job as..., then choose where to script to.

HTH,

|||

Chad,

That works. Thanks.

HOw to Saving data from SQL server

Hi

I have only read ( select access) in a sql server database(2000). I am able to scripts the database.

Can any ony have any idea how to extract the data from database?

not the backup of db, i don't have the access.

No DTS to extract data into text file.

some thing like save the table with data?

I have more than 300 tables.

Thanks

sandipan

I think you don't have many options here. If you only have select permission; the only option I see is to export the data via Select...from... You could script a create table and then load it into a new DB where you, perhaps have rights to create a backup.

You could do all that using SSIS.

sql

how to save query to file in Query Designer

Using SQL Server 2000's Query Designer (not analyzer), I can create a
query, look at the query in SQL pane. On a right click, if I select
save as, there is no file dialog to inquire where to save file. If I
select save, I can't find file.
Is the save-as a bug?
Where should I look for the file; what naming convention is used?
RitaRita,
You are correct that Save As and Save do not work here. See
http://groups.google.com/groups?q=hoegemeier%2Bsigouin
for an answer to this question.
SK
"Rita W." <riwillia@.telus.net> wrote in message
news:40130D48.2050708@.telus.net...
> Using SQL Server 2000's Query Designer (not analyzer), I can create a
> query, look at the query in SQL pane. On a right click, if I select
> save as, there is no file dialog to inquire where to save file. If I
> select save, I can't find file.
> Is the save-as a bug?
> Where should I look for the file; what naming convention is used?
> Rita
>|||Thanks, Steve.
R
Steve Kass wrote:
>Rita,
> You are correct that Save As and Save do not work here. See
>http://groups.google.com/groups?q=hoegemeier%2Bsigouin
>for an answer to this question.
>SK
>"Rita W." <riwillia@.telus.net> wrote in message
>news:40130D48.2050708@.telus.net...
>
>>Using SQL Server 2000's Query Designer (not analyzer), I can create a
>>query, look at the query in SQL pane. On a right click, if I select
>>save as, there is no file dialog to inquire where to save file. If I
>>select save, I can't find file.
>>Is the save-as a bug?
>>Where should I look for the file; what naming convention is used?
>>Rita
>>
>
>

how to save listbox multiple select values

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germanoshouldn't lbAttendees have .SelectedItems?

Next, you should be passing the values to the database through sql or other dataaccess means.|||>>Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there.

I would strongly suggest option 2 (Normalize).

>>I'm not sure how to loop through each value and write it to the database table.

Well you have some options.

i) Loop through your items on the client and perform inserts (one row at a time).

ii) Package up the values as an xml chunk and use openxml to insert (set based method, less chatty)

ii) Package up the values as a delimited string and perform parsing and insert at the server.

And there are others (using OO: collections, persistance frameworks mechanisms, etc..)sql

How to run query on system tables of MS Jet

I am new to use Microsoft.Jet.OLEDB.4.0.

I need to run query (Select * from TABLE...) on system tables of my DB.
I can see data in those table from MS Access. But anytime I run query programatically, I got error saying

exception 80040E09, Record(s) cannot be read; no read permission on 'MSysRelationships'. (80040E09)

Can anyone tell me how to run query on sytem tables?

I can do this type of things in MS SQL Server.

Really appreciate your help!

/HannaI have exactly same problem. PLease help !

How to run only certain tasks

If I'm in the Data Flow tab in VS 2005, how can I select only certain components to run to test? I tried highlighting the ones that I want to run but it's running all of them in the tab...some of the components I want to take out for testing then maybe put back in later. If I delete the tasks I don't want to run, then I end up having to recreate them

First of all, you need to be clear that the objects in the data-flow are called components. Tasks are in the control-flow.

The lowest unit of execution in SSIS is a task. It is not therefore possible to execute a component in isolation and to be fair, why would you want to? It is a task that actually "does" something. All components are dependant on other components in the data-flow.

If you want to carry out some processing on a set of data but not insert into a destination adapter then you can do a number of things:

a) Put a data viewer into the data-flow which causes it to pause

b) Terminate the data-path in a UNION ALL or ROWCOUNT component

c) Install the trash Destination adapter (you can google for it)

-Jamie

|||Why would I want to test only certain components? Because when you're designing a task with many components, sometimes you take some components out in troubleshooting, or like I was doing, just trying something different in a new component and if that doesn't work, just put the other one back...that's why! Then you may want to place them back in. There are different ways to troubleshoot...or you may want to try changing a component and if that didn't work, move the other one that worked back in. I mean, not all of use know SSIS inside out, mostly 99% of use don't and I can be not anyone knows how to use ALL components. AS you know, as a programmer, it can get messy when you're trying a bunch of things to resolve a problem or figure out how a component works or is not working, or trying other ways around something....Just as you'd cut out a portion of ASP.NET code and put it aside, you'd want to do the same...after all most of SSIS is visual...we should be able to try certain components and disable others until we finish our final product.|||

I agree that you may want to test a flow up to a certain point in the flow, you would have to test it from the start though. To achieve that just run package and let it fail at the step you don't want to run.

If your talking about the control flow, then your best bet is to put the tasks into sequence containers, you can then execute a sequence container. You can group your tasks into completable groups. This is what I do.

|||

Simon is right. I can't think of a situation where it is possible to test a single component in isolation because it works in tandem with all the other components in the data-flow and this is the reason why components can not be disabled as you wish.

As Simon said, you can test up to a certain point in the flow. And as I said before you can use a data viewer to pause it.

If you want to test a minor change then how about copying the components that you want to test into a seperate data-flow? Trash destination is your friend here - I recomend it highly.

I feel like I'm repeating myself a little from my earlier post so if this isn't clear, please let me know.

-Jamie

|||I'm not saying a single component. I'm talking about highlighting more than one in a task...like I stated in the intial post...is that possible?|||ok there, I updated my initial post to say "components"...should be more clear now.|||cool, didn't know about the sequence containers, that should work! thanks.|||

FavorFlave wrote:

I'm not saying a single component. I'm talking about highlighting more than one in a task...like I stated in the intial post...is that possible?

No, I'm afraid not. You can achieve the same though by using one of the techniques that I suggested.

-Jamie

|||

FavorFlave wrote:

cool, didn't know about the sequence containers, that should work! thanks.

Favor,

Sequence contains are only relevant to tasks in the control-flow. Whilst being very useful in the context that Simon mentioned they don't have any relevance for what you want to do which is run only certain components inside a single task.

-Jamie

|||

FavorFlave wrote:

ok there, I updated my initial post to say "components"...should be more clear now.

cool. I didn't want it to come across like I was disapproving - it just helps if we're all using the correct terms unambiguously y'know.

-Jamie

How to run an insert without returning anything

I need to run a select statement in a sproc and at the end insert into a
history table without having the insert return anything to the sproc is is
embedded in. How do I do this? Thank you.If that is all the proc does, you can say:
INSERT INTO HistoryTable EXEC myProcedure;
"JT" <xtf@.microsoft.com> wrote in message
news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>I need to run a select statement in a sproc and at the end insert into a
>history table without having the insert return anything to the sproc is is
>embedded in. How do I do this? Thank you.
>|||Problem is that I do a select first and then insert into history and I am
getting the reults of my insert not my select. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u9cLMC2VGHA.2444@.TK2MSFTNGP14.phx.gbl...
> If that is all the proc does, you can say:
> INSERT INTO HistoryTable EXEC myProcedure;
>
>
> "JT" <xtf@.microsoft.com> wrote in message
> news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
I don't know what all of this means. Could you provide some real code, a
simple repro, and explain in detail what you want to really happen? All
these word problems are not very easy to follow.|||JT wrote:
> I need to run a select statement in a sproc and at the end insert into a
> history table without having the insert return anything to the sproc is is
> embedded in. How do I do this? Thank you.
CREATE PROC usp_insert
AS
SET NOCOUNT ON ;
INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...
FROM ... ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||JT (xtf@.microsoft.com) writes:
> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
If this is a quiz, my guess is that your table has a trigger with a
SELECT statement in it.
If it not a quiz, please be more detailed...
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

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:
> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
> > I want to run a script to get the file details on each database.
> > Script:
> >
> > Use DBname
> > Select * from sysfiles
> >
> > I want to cycle through the databases using dbname as variable. I tried a
> > bunch of different ways with no luck. Any help or pointers would be great.
> > What I'm looking to do is "inventory" my sql servers. I need to come up
> > with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> > I would like to come up with a script (or scripts) that will give me list of
> > databases and details on databases for that server. I would like to dump
> > into table and eventually use that to keep track of my servers. Any
> > thoughts '
> >
> > thanks in advance
> > rob|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>> Try this:
>> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
>> "Rob" wrote:
>> > I want to run a script to get the file details on each database.
>> > Script:
>> >
>> > Use DBname
>> > Select * from sysfiles
>> >
>> > I want to cycle through the databases using dbname as variable. I
>> > tried a
>> > bunch of different ways with no luck. Any help or pointers would be
>> > great.
>> > What I'm looking to do is "inventory" my sql servers. I need to come
>> > up
>> > with a Disaster Recovery Plan and I have 10 + sql servers I need to
>> > document.
>> > I would like to come up with a script (or scripts) that will give me
>> > list of
>> > databases and details on databases for that server. I would like to
>> > dump
>> > into table and eventually use that to keep track of my servers. Any
>> > thoughts '
>> >
>> > thanks in advance
>> > rob

How to run a DTS as a trusted user in a stored proc

I get an error when i try to run a dts
but when i create a new dts and select the right server and user
everything went well.
Can someone help me?What is the error you receive ?

This is a cut from a microsoft article which may address your problem:
"Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run. "

Here is the article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;q269074)sql

How To Round With Negative Numbers?

I am using a select statement to obtain a result set back with aggregated
data. The problem is that I am seeing column data with 11 to 13 digits
after the decimal point. I tried using the STR function, but then the Order
By clause does not sort properly because there are negative numbers in the
aggregated data... I tried using Round, but that does no good either - it
still ends up displaying too many digits after the decimal point. Right now
I'm just using Query Analyzer to display the data, so I can live with it for
now. But, in the future, my app will be getting a result set back and I
would prefer not to have to go through each row and do a round on it from
the program. Does anyone know how to solve this problem?

Thanks for any help,

BobBob, what is the actual problem? What does how the numbers display
have to do with the sort order? Do you want the negatvie numbers and
the positive values to sort alike? ABS function? Round in select list
but not in the order by?

Perhaps a sample SQL and output would help someone provide the right
solution.

HTH -- Mark D Powell --|||Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> I am using a select statement to obtain a result set back with
> aggregated data. The problem is that I am seeing column data with 11 to
> 13 digits after the decimal point. I tried using the STR function, but
> then the Order By clause does not sort properly because there are
> negative numbers in the aggregated data... I tried using Round, but
> that does no good either - it still ends up displaying too many digits
> after the decimal point. Right now I'm just using Query Analyzer to
> display the data, so I can live with it for now. But, in the future, my
> app will be getting a result set back and I would prefer not to have to
> go through each row and do a round on it from the program. Does anyone
> know how to solve this problem?

If I could understand the problem, maybe I could solve it. :-)

It sounds as if you are working with floats, which are approxamite
numbers. You can round a value, but you may still see many decimals,
because there may be no exact represenation of the number. You could
convert to decimal, which is a precise type. You could also consider
handling the formatting of the data in the client.

But without knowledge about your data and their data types it's difficult
to say anything more intelligent.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Mark D Powell" <Mark.Powell@.eds.com> wrote in message
news:1106753337.073272.7040@.z14g2000cwz.googlegrou ps.com...
> Bob, what is the actual problem? What does how the numbers display
> have to do with the sort order? Do you want the negatvie numbers and
> the positive values to sort alike? ABS function? Round in select list
> but not in the order by?
> Perhaps a sample SQL and output would help someone provide the right
> solution.
> HTH -- Mark D Powell --

Ok, here is my query:

select [TS Bars Back] as BB, [TS Bars Within] as BW,
str([TS Move %], 6, 4) as "Move %", str([TS Entry %], 6, 4) as "Entry T",
str([TS ATR Profit], 7, 2) as "P Goal", sum([P/L Comm]) as "P/L $",
str(sum(Risk), 7, 2) as "Risk",
str(avg([P/L Avg %]), 7, 2) as "P/L %",
str(sum([P/L %]), 10, 2) as "P/L % Sum",
str(avg([P/L Long]), 7, 4) as "Long $", str(avg([P/L Short]), 7, 4) as
"Short $",
Count([# of trades]) as "# Runs", Sum([# of trades]) as "# Trades",
str(sum([Max Drawdown]), 10, 2) as "Max $ DD"
from [Table1].dbo.SumResults
where Symbol = 'GE_1/1m' and [SE Time] = 3600 and [TS Entry %] = .005 and
[TS ATR Stop] = 0
group by [TS Move %], [TS Entry %], [TS Bars Within], [TS Bars Back], [TS
ATR Profit]
order by [P/L $] desc

The output looks like this:

BB BW Move % Entry T P Goal P/L $ Risk
P/L % P/L % Sum Long $ Short $ # Runs # Trades Max $ DD
30 8 0.0075 0.0050 1.20 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07
30 8 0.0075 0.0050 1.30 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07

So, you can see that the str function works well to limit the # of decimal
points displayed for the other fields.
However, if I use it for the "P/L $" field, then the sort does not come out
right because the order by sorts based
upon the resulting character string and not the number in the field. I need
to limit the number of digits displayed in
the P/L $ field without affecting the sort order. Anybody know how to do
that?

Bob|||Thank you for the idea of using a decimal field instead of a float. Most of
my columns are floats (or reals). So, I tried doing a cast of the real
column to a decimal and it worked like a charm.

For those interested in the syntax, this is what worked:

sum(cast ([P/L Comm] as decimal(10,3))) as "P/L $",

Thanks again,

Bob

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95EB168E856Yazorman@.127.0.0.1...
> Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> > I am using a select statement to obtain a result set back with
> > aggregated data. The problem is that I am seeing column data with 11 to
> > 13 digits after the decimal point. I tried using the STR function, but
> > then the Order By clause does not sort properly because there are
> > negative numbers in the aggregated data... I tried using Round, but
> > that does no good either - it still ends up displaying too many digits
> > after the decimal point. Right now I'm just using Query Analyzer to
> > display the data, so I can live with it for now. But, in the future, my
> > app will be getting a result set back and I would prefer not to have to
> > go through each row and do a round on it from the program. Does anyone
> > know how to solve this problem?
> If I could understand the problem, maybe I could solve it. :-)
> It sounds as if you are working with floats, which are approxamite
> numbers. You can round a value, but you may still see many decimals,
> because there may be no exact represenation of the number. You could
> convert to decimal, which is a precise type. You could also consider
> handling the formatting of the data in the client.
> But without knowledge about your data and their data types it's difficult
> to say anything more intelligent.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

how to return truncated field

Hi,
I'd like to select a field of my table and also a truncated version of
that field:
SELECT Field1, Field1_truncated FROM myTable
This field is of type nvarchar. Field1 should contain the entire string
valule whereas Field1_truncated should only contain the first 10
characters of the original field, followed by 3 dots (...)
So I would get this kind of result:
Field1 Field1_truncated
some long string some long ...
Can you help?SELECT Field1, left(Field1,10) + '...' as Field1_truncated FROM myTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi, Samuel
Try something like this:
SELECT Col1,
CASE
WHEN Len(Col1)>13
THEN LEFT(Col1,10)+'...'
ELSE Col1
END as Col1_truncated
FROM YourTable
Razvan|||Brilliant.
Thanks to you guys !

how to return only last select from stored procedure

I have 3 selects in my stored procedure
I am using first and second select just for calculating number of rows
(@.@.ROWCOUNT), and last one is the final select that I need. But I get all
three selects
TomislawTomislaw,
If the first two are only for calculating the number of rows, then use
select @.numrows = count(*)
from ...
instead of doing the select query and referencing @.@.rowcount.
If you do a select query in a stored procedure, it will be returned as a
result set.
Rob|||it is not so simple
my select is:
SELECT Date1, Date2, Neto, Provision
FROM TERMIN
WHERE (ID = @.ID)
AND (Date1>= @.Date1 AND DatumDo <= @.Date2)
GROUP BY Date1, Date2, Neto, Provision
HAVING (MAX(BedsNo) >= @.BedsNo)
i get 2 rows
with count i get number 3
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:E655A96C-643E-40F1-B045-D6F3DC713737@.microsoft.com...
> Tomislaw,
> If the first two are only for calculating the number of rows, then use
> select @.numrows = count(*)
> from ...
> instead of doing the select query and referencing @.@.rowcount.
> If you do a select query in a stored procedure, it will be returned as a
> result set.
> Rob
>|||Hi
Try this
create proc myproc
as
set nocount on
declare @.row int
declare @.t table (col int)
insert into @.t select orderid from orders
select @.row=@.@.rowcount
select * from orders
go
exec myproc
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OwLMrsjSFHA.248@.TK2MSFTNGP15.phx.gbl...
> it is not so simple
> my select is:
> SELECT Date1, Date2, Neto, Provision
> FROM TERMIN
> WHERE (ID = @.ID)
> AND (Date1>= @.Date1 AND DatumDo <= @.Date2)
> GROUP BY Date1, Date2, Neto, Provision
> HAVING (MAX(BedsNo) >= @.BedsNo)
> i get 2 rows
> with count i get number 3
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:E655A96C-643E-40F1-B045-D6F3DC713737@.microsoft.com...
>|||What ar you doing with the data returned from the first two selects? Why
are you even executing them if you don;t want the results passed back to the
client?
Either you want to use the data as a parameter value in the last Select, or
to decide whether or not to even run the last Select. Either way, The Outpu
t
of a Select statement in a Stored Proc is either returned to the client or
not returned, depending on whether the output values are being assigned to
T-SQL Variables or not. If even one output value is NOT being assigned t oa
T-SQL Variable, then the Output will be returned t othe CLient.
Select @.VarA = <expresssionA>, @.Varb = <ExpressionB>, <ExpressionC>, etc.
From ...
If ALL The output values are being assigned to T-SQL Variables, then the
result set will NOT be sent back to the client.
Select @.VarA = <ExpresssionA>,
@.Varb = <ExpressionB>,
@.VarC = <ExpressionC>
From ...
In the latter case, you have to be careful that the Select only generates
one row, because if it generates more than one, then the T-SQL Variables wil
l
be populated with the values in the Last Row of the resultset, which, withou
t
an Order By, may not be absolutely determinisitic,
"TomislaW" wrote:

> I have 3 selects in my stored procedure
> I am using first and second select just for calculating number of rows
> (@.@.ROWCOUNT), and last one is the final select that I need. But I get all
> three selects
>
> Tomislaw
>
>
>|||There should be no reason why you can't get the same result with COUNT as
with @.@.ROWCOUNT:
SELECT COUNT(*)
FROM
(SELECT NULL
FROM termin
WHERE id = @.id
AND date1 >= @.date1
AND datumdo <= @.date2
GROUP BY date1, date2, neto, provision
HAVING MAX(bedsno) >= @.bedsno)T(x)
If you still have a problem then please post DDL as well as your query so
that we can test it out.
David Portas
SQL Server MVP
--|||The issue with count(*) in your original context, Tomislaw, is that the
"having" filter is applied after the aggregates are calculated. Therefore,
you could get a different solution. But using count(*) is still the right
way, you just need to nest it as in David's example.
He puts 'select null', because there's no point in returning a whole bunch
of rows in a subquery where you're only interested in how many rows there ar
e.
You should find that David's example works veryw ell.
Rob|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:ED19C5E3-A554-4976-B0E0-4C9631A95C78@.microsoft.com...
> The issue with count(*) in your original context, Tomislaw, is that the
> "having" filter is applied after the aggregates are calculated. Therefore,
> you could get a different solution. But using count(*) is still the right
> way, you just need to nest it as in David's example.
> He puts 'select null', because there's no point in returning a whole bunch
> of rows in a subquery where you're only interested in how many rows there
> are.
> You should find that David's example works veryw ell.
> Rob
Thanks to you and David
TomislaW

How to return one xml doument per row

When I do something like "select * from customer for XML auto" I get a single
string containing the xml for all customers in the customer table. I want one
xml string (i.e., document) per row so I can process one document at a time.
Is this possible?
Use FOR XML Explicit. This query should be written in a specific way.
something like this:
select 1 as Tag,
null as Parent,
customers.customerID as [Customer!1!CustomerID!id]
from customers
for xml explicit
Please refer to "EXPLICIT mode" in Books online.
Regards,
Deepak
[I Code, therefore I am]
"ACROWN" wrote:

> When I do something like "select * from customer for XML auto" I get a single
> string containing the xml for all customers in the customer table. I want one
> xml string (i.e., document) per row so I can process one document at a time.
> Is this possible?
|||FOR XML (both auto and explicit mode) is a rowset to XML aggregator. In SQL
Server 2000, this aggregator can only aggregate all the rows.
In SQL Server 2005, you will be able to write something along the lines of
Create table t (id int, name varchar(5))
go
insert into t values (1, 'a')
insert into t values (1, 'b')
insert into t values (2, 'c')
select (select * from t t1 where t1.id = t2.id for xml raw('t'), type)
from t t2
group by t2.id
HTH
Michael
"ACROWN" <ACROWN@.discussions.microsoft.com> wrote in message
news:6D633481-D083-4A18-9FB5-37AA9258FEEE@.microsoft.com...
> When I do something like "select * from customer for XML auto" I get a
> single
> string containing the xml for all customers in the customer table. I want
> one
> xml string (i.e., document) per row so I can process one document at a
> time.
> Is this possible?

How to return a value if recordcount = 0

How would I return a select statement if the records returned is 0
i.e
Select * from orders where employeeID = 0
if @.RowCount = 0
--Return Shipname as 'None'
Select 'None' = shipname ect,
Thanks
Stephen K. Miyasato
MDsyncHi Stephen,
I would do something like...
SELECT * FROM Orders where EmployeeID = 0
IF @.@.ROWCOUNT = 0
SELECT 'None' As ShipName
or
IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
SELECT * FROM Orders WHERE EmployeeID = 0
ELSE
SELECT 'None' As ShipName
Both examples would return a single row with a single column called
[ShipName] with the value 'None' in the event that no rows exist with an
employeeID of 0.
Rich.
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
> How would I return a select statement if the records returned is 0
> i.e
> Select * from orders where employeeID = 0
> if @.RowCount = 0
> --Return Shipname as 'None'
> Select 'None' = shipname ect,
>
> Thanks
> Stephen K. Miyasato
> MDsync
>|||Try this:
Select * from orders where employeeID = 0
if @.@.RowCount = 0
begin
--Return Shipname as 'None'
Select 'None' as 'shipname'
end|||Thanks but I could not get this to work.
Here is the actual query:
The query as it now is return two sets. The first set has 0 rows, the next
set is the one I need to produce.
if I use Exist(Select ..), I get and error since I have NTEXT values and
order by is not allowed.
Any other suggustions?
Stephen K. Miyasato
/****** Object: Table [dbo].[orderTest] Script Date: 5/9/2006 12:42:23
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orderTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [orderTest] (
[PatNo] [int] NULL ,
[Name] [varchar] (24) NULL ,
[Dosage] [varchar] (25) NULL ,
[Sig] [varchar] (30) NULL ,
[PatSig] [varchar] (50) NULL ,
[RxNo] [int] NULL ,
[Breakfast] [varchar] (1) NULL ,
[Lunch] [varchar] (1) NULL ,
[Dinner] [varchar] (1) NULL ,
[QHS] [varchar] (1) NULL ,
[Comments] [varchar] (60) NULL ,
[QuantityL] [varchar] (20) NULL ,
[Quantity] [float] NULL ,
[Generic] [varchar] (60) NULL ,
[BrandName] [varchar] (60) NULL ,
[MDrx] [int] NULL ,
[Category_id] [int] NULL ,
[Daily] [smallint] NULL ,
[Memo] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
Declare @.PatNo int
Set @.PatNo = 7160
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic, BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo) AND (StopDate IS NULL)
ORDER BY Daily
if @.@.RowCount = 0
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig, Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as Category_id,
Null as Daily,
Null as Memo
"Richard O'Brien" <groups@.rjoconsulting.co.uk> wrote in message
news:ecp6gH6cGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Hi Stephen,
> I would do something like...
> SELECT * FROM Orders where EmployeeID = 0
> IF @.@.ROWCOUNT = 0
> SELECT 'None' As ShipName
> or
> IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
> SELECT * FROM Orders WHERE EmployeeID = 0
> ELSE
> SELECT 'None' As ShipName
> Both examples would return a single row with a single column called
> [ShipName] with the value 'None' in the event that no rows exist with an
> employeeID of 0.
> Rich.
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
>|||Stephen,
Select * from orders where employeeID = 0
union all
select
-- whatever values you want
1,2,3,4,...
where not exists(Select 1 from orders where employeeID = 0)|||StopDate isn't a column in the table definition you gave.
Try this.
Declare @.PatNo int
Set @.PatNo = 7160
if exists (select * from ordertest where (PatNo = @.PatNo)) -- AND
(StopDate IS NULL))
begin
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic,
BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo)-- AND (StopDate IS NULL)
ORDER BY Daily
end
else begin
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig,
Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as
Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as
Category_id,
Null as Daily,
Null as Memo
end

How To return a value from stored procedure when select nothing

Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me... Thank

U can use out parameter to get the value. Plz check out

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Thank you for your reply, but i already try this way, can't work because it need argument... do u have any sample or any good idea to get the parameter? Thank You

|||

Hi chohoo,

sqldatasource.select() method need aDataSourceSelectArguments object as a parameter. In your case you can create a empty instance and pass into select method. like this:

DataSourceSelectArguments args=new DataSourceSelectArguments();

sqldatasource1.select(args);

You can use a dataview object to hold the data returned by select().

BTW, " retrun a value from a stored procedure" is another story. Does that has anything to do with sqldatasource.select?

|||

Thank You for Ur Reply, actually I already used other method (Use dataset read to return). Whatever Thank You Becoz You Help Me.

How to Return a subset of resultset of some stored proc

Hi,

I want to write a query to select some of the columns from result of stored proc,

My expected code will be like that, but its not allowed,

Select first_Name, Last_Name, Last_Used_date from (Execute sp_reportNumber15 '9555')

I have around 30 different stored proc but I know some of the columns are in resultset of every stored proc, So I want to write a generalize stored proc to whom I will just pass stored proc name and it will return me the subset of its result.

Thanks,

Imran.

Imran:

I would first try to convince you to take another approach. Reports -- especially heavy reports -- can be very resource intensive. And to take the results of some 30 different reports and the massage these results and take small subsets of each report and the presenting the results to an end user sounds like doing a lot of work to simplify the coding of a developer. To me this is putting the work in the wrong place.

When I implement a database one of my goals always is to provide service to each request as fast as possible. The implementation you propose does not aim at that goal. To me, the implementation you propose aims at taking a round-about path to gathering the data and hoping that the response to the request might be fast enough. This brings to me visions of dozens of tables being table scanned when the data from much fewer tables is needed. If it were my server I would view this as abuse.

Please, rethink this before you go forward.

Dave

|||

Hi Dave,

I have not written that I want to compile resultset of 30 reports at the same time. The scenario is like as,

One enduser want sometimes 5 specific columns from resultset of sp_report1, sometime he needs same 5 columns from resultset of sp_report2, in actual the number of columns returned in resultsets is different of sp_report1 and sp_report2 but same 5 columns are present in resultset of every sp, like sp_report1 return 30 columns, and sp_report4 returns 15 columns.

I want to write a new stored proc for him, say for example sp_getData 'sp_report1'. By this kind of stored proc he doesnt need to fetch full resultset of every sp, this will reduce network load also,

when user passes the parameter value as sp_report1, then I will return 5 columns from resultset of report1, and when user passes parameter value as 'sp_report15' then I will return 5 columns from resultset of report15

My question is only that, if you have a vision to write this kind of statement, conceptually my statement will be as,

Select Col1, Col4, Col6, Col7, Col9 from (Exec sp_getData @.repnumber)

but syntactically it is wrong, if there is some possibility then just reply otherwise dont waste your time as well as my time.

thanks,

Imran.

|||

convert your sp into function..

so you can do Select Col1, Col4, Col6, Col7, Col9 from fn_getData (@.repnumber) as Data

|||

Thank you so much ... :D

This is exactly what I want...

so nice of you

How to return a set of Data ?

Hello,
I have to make queries on a very big database, bon only part by part.
For example : I would like to use a SELECT request and get only the rows
1000 to 1999.
With the TOP keywork, I can get the 10 first rows. But what is the command
to retrieve the rows between tho numbers ?I would use a generated IDENTITY column in the result set, and then filter
on the IDENTITY value.
Martin
"Florent CORNEILLE" <FlorentCORNEILLE@.discussions.microsoft.com> wrote in
message news:DE105ECC-7FD0-44FA-97C8-AB495BBC7601@.microsoft.com...
> Hello,
> I have to make queries on a very big database, bon only part by part.
> For example : I would like to use a SELECT request and get only the rows
> 1000 to 1999.
> With the TOP keywork, I can get the 10 first rows. But what is the command
> to retrieve the rows between tho numbers ?
>|||-- Try:
SELECT *
FROM <@.your_table, SYSNAME, >
WHERE <@.your_field, SYSNAME, > Between 1000 And 1999
-- This query is formatted as a template, so from Query Analyser, press
Ctrl+Shift+M and fill in the parameters.
-- Lookup "Using Templates in SQL Query Analyzer" in BOL
-- Damien
"Florent CORNEILLE" wrote:

> Hello,
> I have to make queries on a very big database, bon only part by part.
> For example : I would like to use a SELECT request and get only the rows
> 1000 to 1999.
> With the TOP keywork, I can get the 10 first rows. But what is the command
> to retrieve the rows between tho numbers ?
>

How to return a row counter?

Is it possible to return a row counter in a single select statement?
For example, I want to return Col1 and Col2 in TableA, but I need a counter
to identify the returned row's positioning.
RowCount ColA ColB
1 A B
2 C D
3 E F
I'd like to do it in a single select statement. If I have to declare a
variable and do a while statement, I could do it but it would be very
process-intensive.
Thanks!> Is it possible to return a row counter in a single select statement?
Sort of. But you really, really, really should consider appending this at
the presentation layer, which has to loop through all rows anyway. Doing
this at the database level forces SQL Server to inspect the entire set for
every row, and this will definitely have high potential to cause you some
performance issues.
http://www.aspfaq.com/2427|||Thanks, Aaron. I'll look into those options.
Do you know if Microsoft is planning on implementing this logic as a
function in the future?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sort of. But you really, really, really should consider appending this at
> the presentation layer, which has to loop through all rows anyway. Doing
> this at the database level forces SQL Server to inspect the entire set for
> every row, and this will definitely have high potential to cause you some
> performance issues.
> http://www.aspfaq.com/2427
>
>|||> Do you know if Microsoft is planning on implementing this logic as a
> function in the future?
Yes, SQL Server added ROW_NUMBER() and other ranking functions in SQL Server
2005.
http://msdn2.microsoft.com/en-us/library/ms186734

How to return a result from a procedure?

I created a procedure to select data. It works fine on SQLPlus. but I can't get any result from front-end which implemented in Coldfusion.
Does any one have an idea how to return a set values from a procedure?
Your reply is appreciate.You can do something like this...

CREATE OR REPLACE PROCEDURE ProcA
(a_dMonth IN DATE,
a_nStatus OUT NUMBER
)
AS

Now call the procedure in a sql script...

declare
nStatus Number :=0;
e_exception EXCEPTION;
begin
ProcA(to_date(&&1,'yyyymmdd',nStatus);
if nStatus > 0 then
dbms_output.put_line('yr message '||nStatus);
raise e_exception;
end if;
EXCEPTION
when e_exception then
null;
end;
/|||Thanks, I got it.