Iterate a procedure on several db

Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
FreddyFrederico
What does the SP do? Is it located in the master database?
Using undocumented storede procedure
EXEC sp_MSForeachDB "Use ? Exec spname 'parameter'"
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
> Hi All,
> My problem is that I have to iterate a procedure on several db ... db
> names are known at runtime. I tried "Use @.dbname" without success.
> Someone can help me?
> Thanks in advance.
> Freddy
>|||Here's an example:
DECLARE @.DBName sysname, @.Qry varchar(1000)
SET @.DBName = ''
WHILE 1 = 1
BEGIN
SET @.DBName = (
SELECT MIN(CATALOG_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME > @.DBName
)
IF @.DBName IS NULL
BEGIN
BREAK
END
SET @.Qry = 'USE ' + QUOTENAME(@.DBName)
SET @.Qry = @.Qry + '; SELECT DB_NAME()'
EXEC (@.Qry)
END
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
Freddy

Iterate a procedure on several db

Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
FreddyFrederico
What does the SP do? Is it located in the master database?
Using undocumented storede procedure
EXEC sp_MSForeachDB "Use ? Exec spname 'parameter'"
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
> Hi All,
> My problem is that I have to iterate a procedure on several db ... db
> names are known at runtime. I tried "Use @.dbname" without success.
> Someone can help me?
> Thanks in advance.
> Freddy
>|||Here's an example:
DECLARE @.DBName sysname, @.Qry varchar(1000)
SET @.DBName = ''
WHILE 1 = 1
BEGIN
SET @.DBName =
(
SELECT MIN(CATALOG_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME > @.DBName
)
IF @.DBName IS NULL
BEGIN
BREAK
END
SET @.Qry = 'USE ' + QUOTENAME(@.DBName)
SET @.Qry = @.Qry + '; SELECT DB_NAME()'
EXEC (@.Qry)
END
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Federico" <feddem@.gmail.com> wrote in message
news:1154425199.523942.3590@.h48g2000cwc.googlegroups.com...
Hi All,
My problem is that I have to iterate a procedure on several db ... db
names are known at runtime. I tried "Use @.dbname" without success.
Someone can help me?
Thanks in advance.
Freddysql

Issues/Problems with SQL MSDE running on Windows XP

Can anyone tell me if there are known problems/issues with running SQL MSDE
on Windows XP? We have a wise install program that installs an Excel
application with msde and it runs fine on Windows 2000 but not on XP. The
install program completes fine but when we go to view the database we find
that the SQL Server instance is there with the msdb, model, and other default
system databases but none of the databases that the install script creates.
When the same installation is ran on 2000 everything works beautifully.
Any help greatly appreciated!
Thanks,
Ang
This is likely an issue with your installation program and not with MSDE.
MSDE works just fine with XP. Contact your application vendor.
-Andrew
"Angela" <Angela@.discussions.microsoft.com> wrote in message
news:E0E98FDB-4923-4CE0-9E1F-D2F8733B4B79@.microsoft.com...
> Can anyone tell me if there are known problems/issues with running SQL
> MSDE
> on Windows XP? We have a wise install program that installs an Excel
> application with msde and it runs fine on Windows 2000 but not on XP. The
> install program completes fine but when we go to view the database we find
> that the SQL Server instance is there with the msdb, model, and other
> default
> system databases but none of the databases that the install script
> creates.
> When the same installation is ran on 2000 everything works beautifully.
> Any help greatly appreciated!
> Thanks,
> Ang

Issues on Decimal Type

Dear All,

The SQL server supports 38-digit deciaml numbers. Is there any known issues, error reported or penalties if all 38 digit places are declared in DECIMAL type columns?

My server version is 2005.

Thanks for all...

Gabriel
All know issues supposed to be fixed. Considering the size, don't use 38 digit wide types unless you have to. Look at bigint datatype, which takes only 8 storage bytes instead of 17 for decimal(38,0)|||

Thanks for your reply.

If I really have to use 38-digit decimal, e.g. decimal(29,9) or decimal(10, 18), will it be any known potential problem?

Issue with SQL Parameter when used with % character.

Hi all,

is there a known issue when using a SQL Paramater in a SQL string that uses a '%' character for wildcard string searches?

For example, if I used the following...

SQLCeParameter pKeyword = new SQLCeParameter("@.Keyword", SQLDbType.Char);
pKeyword.value = "Some Text";

string strSQL = "SELECT * FROM tbl_Keywords WHERE (Keyword = @.Keyword%)";

This just never returns and results. But if I run the query in a query analyser window replacing the @.Keyword% with "Some Text", I get results.

I guessing that the placing of the character '%' in the string is the cause maybe...

try putting the % in the string...

pKeyword.value = "Some Text%";

|||

for wildcard searches, you need the LIKE statement. example:

SELECT * FROM someTable WHERE FieldName LIKE @.parameter + '%'

|||Sorry, my query does actually have the LIKE statemet, its just when I post the thread I wrote the query off the top of my head.|||

try this

string strSQL = "SELECT * FROM tbl_Keywords WHERE Keyword LIKE" + @.Keyword + "%";

|||

>>This just never returns and results. <<

What does this mean? It returns nothing? Or it takes forever?

Check the plan of the query using the Display Estimated Plan feature. You should see a very large hotspot in there somewhere that is taking large amounts of the plan (since you are only dealing with one table, it might not be as useful). Then run it without the wildcard and see how the plan looks.

My guess is that it is just taking forever. And is there lots of data? Is that column indexed? An index will work with a LIKE 'value%' criteria, so that shouldn't be a problem. It could be that your indexes aren't optimized too, but first things first :)

|||My Issue is that I no resuts are being returned. I doubt it is taking forever as I am doing a search on a table that has no more that 100 records.

Thanks|||

so have you tried my suggestion earlier?

if you copy and paste your query into query analyzer or SQL management console or something, and execute it - does it bring back results?

so do you get results now, or are you have a perf issue?

another way to increase the perf really is to use stored procedures, if you havent already - safer and faster than standard SQL Injection as you maybe doing ;-)