|
The Man from U.N.C.L.E. wrote: You can't.
Beg to differ. Dataset.Nextresult does it (may have syntax slightly wrong, its not strictly a sql question and I don't have visual studio on this machine)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Good point. I have forgotten that method (on the DataReader actualy), as I tend to use DataReaders for single recordsets and dataadaptor.Fill for multiple record sets.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
So do I as a rule, but I remembered it from before ADO.NET, when theree were no such things as datasets. Also useful as datareaders are faster than datasets.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Execute DataReader.NextResult() should do the trick.
Wout Louwers
|
|
|
|
|
I want to know about in physical database design in overview.If one of u has some links about this, pls give me.
|
|
|
|
|
|
Physical database design means what exactly you want to do...?
|
|
|
|
|
How can i iterate each rows for my result set in a sql server stored procedure.
My intention is to call a stored procedure in different databases and combine the result. Like
GetDatabases()
Foreach database -- I am here now
CallStoredProcedure()
StoreTheResultInTempDB()
Return the resultset
My small attempt...
|
|
|
|
|
Look into cursors [^]- they are evil but necessary for this type of processing
First hit looks promising
Alternative use a While loop[^]
|
|
|
|
|
Give a try with this... I made this based on my understanding
Declare @GetDBNames sysname
Declare @DynSql nvarchar(max)
declare @result table ([Database_Name] nvarchar(128), [Stored Procedure Name] sysname)
Declare DBNames cursor for
Select '['+name+']' from master.dbo.sysdatabases
open DBNames
FETCH NEXT FROM DBNames into @GetDBNames
WHILE @@FETCH_STATUS=0
BEGIN
SET @DynSql = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name''
From '+ @GetDBNames+'.Information_Schema.Routines '
insert @result exec sp_executesql @DynSql
FETCH NEXT FROM DBNames into @GetDBNames
END
Close DBNames
Deallocate DBNames
select * from @result
It gives me all the stored procs names pertaining to the databases.
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hello , I want to cut out all space in my text "Where are you now?", so i want this"Whereareyounow?"
Can any want help me (SQL)?
|
|
|
|
|
Try this
declare @str as varchar(100)
set @str = 'Where are you now?' -- original data
select REPLACE(@str,' ' ,'') as RemoveSpace
Output:
RemoveSpace
Whereareyounow?
This will even work for
'Where are you now?'
Let me know in case of any concern.
Niladri Biswas
modified on Monday, November 9, 2009 11:42 PM
|
|
|
|
|
now i reach my result..thanks alot
|
|
|
|
|
Using the sample GenericOLEDB how can you insert a string that has a single quote in the text of a string var?
insert into SOMETABLE ([SOMEFIELD]) VALUES ('Bob's');
|
|
|
|
|
Google SQL literal string, to find the documentation[^]:
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Identifiers. Literals can be delimited by either single or double quotation marks.
And of course, if the SQL statement is itself a string literal (e.g. in C#) then you must escape all double quotes with a preceding backslash.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
insert into SOMETABLE ([SOMEFIELD]) VALUES ('Bob''s');
Two single quotes '' not a double quote ".
|
|
|
|
|
Try this
declare @tbl table(SomeField varchar(50))
insert into @tbl(SomeField) values('Bob''s')
select * from @tbl
Output:
SomeField
Bob's
Instead of 'Bob's' write 'Bob''s'.
Hope this helps
Niladri Biswas
|
|
|
|
|
It is my understanding that the decimal data type in Microsoft SQL Server should be viewed as a base 10 floating point number. Please correct me if that is wrong. Therefore, if I have a field defined as DECIMAL(4,2) called v1 (defined in a table t1) then I should be able to store the number 123.4 in v1. However, the following insert statement does not work:
insert into t1 values ( 123.4 )
I would like to know why.
Thanks
Bob
|
|
|
|
|
BobInNJ wrote: insert statement does not work
Specifics please. define "Does not work".
|
|
|
|
|
By now working, I mean I get the following error:
Msg 8115, Level 16, State 8, Line 1<br />
Arithmetic overflow error converting numeric to data type numeric.<br />
The statement has been terminated.<br />
Bob
|
|
|
|
|
What's the precision of your decimal data type? That could be the cause of the overflow. The code below seems to work, can you verify it on your machine?;
DECLARE @TestTable TABLE(
Column1 DECIMAL(18,2))
INSERT
INTO @TestTable
(Column1)
VALUES (123.4)
SELECT *
FROM @TestTable Using the default precision, two decimals. Might it be that your culture-settings have something else defined for the decimal separator?
I are Troll
|
|
|
|
|
Eddy,
Thanks for the response. I tried your example and it worked. However, your example defines the field as 18,2 not 4,2 as I defined it. I am starting to think that the decimal data type should be thought of as a fixed point data type. That is,
when I define a decimal data type as 4,2 that means exactly two digits to the right of the decimal point, not two or less. Do I have this right?
Bob
|
|
|
|
|
BobInNJ wrote: when I define a decimal data type as 4,2 that means exactly two digits to the right of the decimal point, not two or less. Do I have this right?
That would be padded with zeroes, giving you 123.40. If you try to insert 123.4 into a DECIMAL(4,2) , then it will expect a maximum of 2 digits in front of the decimal separator, since the precision says that there will be 2 digits behind the decimal separator. Thus giving you this layout; "nn.dd"
"123.40" will not fit, as the part on the left side of the decimal separator flows over the reserved amount of two digits.
In other words;
"123.4" = DECIMAL(4,1)
"123.40" = DECIMAL(5,2)
"23.40" = DECIMAL(4,2)
"23.4" = DECIMAL(3,1)
I are Troll
|
|
|
|
|
Select Convert(DECIMAL(4,2), 123.4)
As you have said this does not work, giving an arithmetic overflow error.
The problem is that while the precision of 4 does give you 4 digits to work, with you have specified a scale of 2. This means 2 of the digits must occur AFTER the decimal point. Therefore a Decimal(4,2) will accept any number between -99.99 and +99.99 with two decimal places.
For more info check out the MSDN[^] web site.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
AFAIK decimal(4,2) can hold the range (-99.99,+99.99), and not 123.4
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|