|
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
|
|
|
|
|
I am getting an error when I run the following query:
"SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DateDiff('d',2009-11-08,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')"
The error is "Invalid argument to Function. Function name is DateDiff"
The 'd' is a standard argument, The date is standard, and so is the data field [TICKET-NEXT-DUE-DATE].
Here is how I have it in the code:
SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, " +
"DateDiff('d'," + strDate + ",[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')
This statement I took straight out of MS Access.
Any help would be appreciated!
Jude
|
|
|
|
|
within SQL statements literal dates need delimiters, either ' or # or [] depending on DB.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Thanx Luc! That brought me to another error of Invalid Function Name. Function name is DATEDIFF??? Isn't datediff() a standard function?
As I said before, the same string works in Access....
Here's what the string looks like now:
"SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DATEDIFF('d','2009-11-08','2009-11-08') AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')"
Thanx!
Jude
|
|
|
|
|
TheJudeDude wrote: DATEDIFF('d','2009-11-08','2009-11-08')
You now have to many quotes. It should be one of the following:
DATEDIFF(d,'2009-11-08','2009-11-08')
Or
DATEDIFF(dd,'2009-11-08','2009-11-08')
Or
DATEDIFF(day,'2009-11-08','2009-11-08')
Of course this is a poor example as datediff of the same dates is always 0.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
True, bad example..I was just trying to troubleshoot, so I just pasted in the same date. I saw the bad quotes and changed that:
"SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DATEDIFF(d,'2009-11-08','2009-11-08') AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')"
But I am still getting an error stating:Build Error : Invalid Function Name, Function name is 'DATEDIFF'.
??
Jude
|
|
|
|
|
DateDiff is an SQL function, how can a C#, C++, or VB compiler complain about it?
FWIW: If your SQL statement spans more than a single line of source code, you should take appropriate measures, which depend on your programming language.
Please show actual code and error message. And check the line numbers as to where the error occurs.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
I shortened up the SQL a bit, but I am getting the same error The error is on DbReader = DbCommand.ExecuteReader():
OdbcConnection DbConnection = new OdbcConnection("DSN=rsss");
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT TICKET-STORE, DATEDIFF(day,'" + strDate + "','" + strDate + "') AS EXP FROM TICKET";
DbConnection.Open();
try
{
DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
{
intStore = Int32.Parse(DbReader["TICKET-STORE"].ToString());
}
}
catch(OdbcException caught)
{
Console.WriteLine(caught.ToString());
Console.Read();
DbConnection.Close();
}
Here is the error:
System.Data.Odbc.OdbcException: ERROR [HY000] Build Error: Invalid Function Name. Function name is 'DATEDIFF'.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteReader()
at acctmgr.Class1.Main(String[] args) in c:\documents and settings\brian\my documents\visual studio projects\acctmgr\class1.cs:line 43
Thanx for your help!
Jude
|
|
|
|
|
I don't see anything wrong, but then I'm not a DB expert at all.
However, from earlier messages, I think you target SQL Server 2000, and this[^] seems to suggest you use SqlConnection instead of OdbcConnection. Which would mean a different connection string, some C# code changes, and probably some SQL changes too.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|