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

Item count for Full-text Catalog is zero

After creating several full-text indexes from tables to a Full-text
Catalog, I can see the names of the tables for those indexes. However,
when double-clicking the Full-text Catalog, the the item count was still
zero.
I am sure the Microsoft Search Service started and there was a Full-Text
Search in EM's Support Services folder. My SQL Server databses is the
back-end database for a front-end SharePoint Services server.
Is something wrong with the configuration on my SQL Server databases, or
the SharePoint Services server?
TIA,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Jeffrey,
Most likely as this is a FAQ in this newsgroup... Review all of the
following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
837367 How to turn on full-text search (FTS) in WSS (SharePoint) on a
Windows SBS 2003-based computer
http://support.microsoft.com/?kbid=837367
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:#ABhKdSIFHA.1528@.TK2MSFTNGP09.phx.gbl...
> After creating several full-text indexes from tables to a Full-text
> Catalog, I can see the names of the tables for those indexes. However,
> when double-clicking the Full-text Catalog, the the item count was still
> zero.
> I am sure the Microsoft Search Service started and there was a Full-Text
> Search in EM's Support Services folder. My SQL Server databses is the
> back-end database for a front-end SharePoint Services server.
> Is something wrong with the configuration on my SQL Server databases, or
> the SharePoint Services server?
> TIA,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Italic

Hai
Iam anand and iam new to sql server.

How to store the Italic words in a table
and how to retreive to names which are in italic

Quote:

Originally Posted by anandgidu21

Hai
Iam anand and iam new to sql server.

How to store the Italic words in a table
and how to retreive to names which are in italic

sql server is primarily an RDBMS-back end database, not a front-end tool...if you could give more details, we can see what we can do...|||

Quote:

Originally Posted by anandgidu21

Hai
Iam anand and iam new to sql server.

How to store the Italic words in a table
and how to retreive to names which are in italic

If your Application is a web Application.
On a varchar column you can store

other words <i>Italic word </i> other words

Then if you are printing this column value to the web page HTML parser will identitfy the tags and out put will be;

other words Italic word other words

Issue with xp_cmdshell in SQL2005

Folks,

I would like to make you aware in my App, we use xp_cmdshell to get a list of file names (using dir into a temp table) from a specific directory and process these files one after another to insert into Database through the Job.

In SQL2000 xp_cmdshell was enabled by default so never had an issue, with us migrating to SQL2005 xp_cmdshell needs to be enabled through the Configuration manager (i know how we do that) as it is off by default which is a security concern as suggested by our DBA and hence not allowed. Also, xp_cmdshell could only be executed by SA user or other user using proxy account with appropraite rights.

I looked at the alternative susggested by some about using SQL Agent Proxies so only certain users can access it.

Can somebody walk me through the steps of making it work for my SQL 2005 certain users using proxy account. i have tried but in vain... i'm not so good with creating windows users and domain policies(stuff)

p.s. Note that we will not be allowed to have xp_cmdshell enabled on the server by the DBA. so the solution has to not enable the xp_cmdshell from the SQL Configuration manager.

i also tried using SQL-CLR method of creating stored procedure but since we may have to use EXTERNAL_ACCESS or UNSAFE type while creating assembly as we use kernel.dll etc. to access file system through code it cannto be SAFE. we are back to square one because of security concern.

i also tried using xplog70.dll to create assembly which is the file used under the wrapper xp_cmdshell but it suggested it needs a .NET assembly and the dll is of previous version or something...

Any other ideas are welcome.

please help... my deadline is fast approaching...

my email id : sunnyny2003@.hotmail.com

Thanks.

Sunny.

I know at least 2 ways to solve this problem.

1. Create a job with ActiveX script step. In it, you may use standard VBS statements like Dir() and so on, so it will be relatively easy to obtain list of files, create ADODB.Connection object and insert the entire list into your table. You have only to decide who will be the owner of the job and how you will start it from your T-SQL code (there is a system stored procedure for this, sp_start_job, but you may run into security problems here, too).

2. You may use virtually the same approach as in the previous solution, but this time code it directly in T-SQL. There is a bunch of stored procedures to work with COM objects - sp_OACreate, sp_OAMethod and so on. Look here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ff16a833-01fe-4877-8aa6-55b72603ec2e.htm
for further details.
The only difference here is that you need to use a Scripting.FileSystemObject in order to reach Dir functionality through COM.|||

A SQL Agent Proxy account won't give a non-sysadmin access to use xp_cmdshell as part of a job step. A SQL Agent Proxy Account will only allow a non-syadmin to use a job step with the type of operating system.

In order to do what you need to do following what appear to be the same guidelines that I lay out for my developers.

Use xp_dirtree 'C:\Folder\With\Files\', 1, 1 to get a list of files within the folder that you specify. The second parameter tells it how many levels deep to scan. 1 tells the procedure to only use the current level. The third parameter tells it to include files. If the third parameter is not included then only folders are shown.

Then use the BULK INSERT command to load the files. This can all be done without needing access to xp_cmdshell or to the file system.

Let me know if this does or doesn't do the trick for you.

Thanks,

Denny

|||

Why don't u use sqlserver integration services (SSIS).

That would do the job perfectly.

You just have to create a "for loop container" which loops through the set of files you have on a directory and import them.

all these functionality is in SSIS.

|||

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

|||

Denny,

Appreciate your response,

using xp_dirtree would work for me to gather file names from the folder to process...

this resolves one issue..

i also use xp_cmdshelll to copy, delete and rename these files after processing....

do you have some other xp_..... for these?

also, is there a way we can execute some exe's like we have one to retrieve the dos path for the folder with spaces etc.

Thanks.

Sunny

|||we cannto use ssis as we don't know before hand what files we have to create the process..|||

Sunnyny wrote:

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

Of course they are dynamic. Or you mean that exact directory you need to scan is dynamic also? Well, create a special table in your DB so user will insert there a path to the directory to scan. When SSIS package is started, it can retrieve required path from this table and scan it, as usual. Don't see anything too difficult here.

Sunnyny wrote:

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

Don't understand - do you really need to drop database from your code?|||

yes, the folder is dynamic too and also sometimes the path is UNC path...

I'll look into SSIS..

do you have any samples or point me.. where we could have example of substituition of filenames etc?

sometimes users uninstall our database and start afresh and because of multiple connections created due to job running every minute and using sp_OAmethod and not destorying that object... Database is unable to be dropped.

|||

If you wanted to use SSIS to you could use a .NET script block to get the list name list for processing and then process it via the Loop that was mentioned above.

You can use xp_delete_file to delete files from the hard drive.

There aren't any extended stored procedures which are nativly installed with Microsoft SQL Server which can rename or copy files. You would need to write these in C++ and attach them to the SQL Server. Your DBA may not allow this to happen either. You may need to change your processing reuirements so that you process and delete, and have the process which gets the files back a backup of the file at that time, instead of while the import is processing. Or before you start the import process use a Command Line Job Step to copy any and all files in the folder to another place for backup purposes.

mrdenny

|||

... except xp_delete_file is notoriously flakey - it reports success even when it's not deleted what it's supposed to.

Search +xp_delete_file +problem - loads of stuff out there about how it simply doesn't work and no one has an answer to it.

Issue with xp_cmdshell in SQL2005

Folks,

I would like to make you aware in my App, we use xp_cmdshell to get a list of file names (using dir into a temp table) from a specific directory and process these files one after another to insert into Database through the Job.

In SQL2000 xp_cmdshell was enabled by default so never had an issue, with us migrating to SQL2005 xp_cmdshell needs to be enabled through the Configuration manager (i know how we do that) as it is off by default which is a security concern as suggested by our DBA and hence not allowed. Also, xp_cmdshell could only be executed by SA user or other user using proxy account with appropraite rights.

I looked at the alternative susggested by some about using SQL Agent Proxies so only certain users can access it.

Can somebody walk me through the steps of making it work for my SQL 2005 certain users using proxy account. i have tried but in vain... i'm not so good with creating windows users and domain policies(stuff)

p.s. Note that we will not be allowed to have xp_cmdshell enabled on the server by the DBA. so the solution has to not enable the xp_cmdshell from the SQL Configuration manager.

i also tried using SQL-CLR method of creating stored procedure but since we may have to use EXTERNAL_ACCESS or UNSAFE type while creating assembly as we use kernel.dll etc. to access file system through code it cannto be SAFE. we are back to square one because of security concern.

i also tried using xplog70.dll to create assembly which is the file used under the wrapper xp_cmdshell but it suggested it needs a .NET assembly and the dll is of previous version or something...

Any other ideas are welcome.

please help... my deadline is fast approaching...

my email id : sunnyny2003@.hotmail.com

Thanks.

Sunny.

I know at least 2 ways to solve this problem.

1. Create a job with ActiveX script step. In it, you may use standard VBS statements like Dir() and so on, so it will be relatively easy to obtain list of files, create ADODB.Connection object and insert the entire list into your table. You have only to decide who will be the owner of the job and how you will start it from your T-SQL code (there is a system stored procedure for this, sp_start_job, but you may run into security problems here, too).

2. You may use virtually the same approach as in the previous solution, but this time code it directly in T-SQL. There is a bunch of stored procedures to work with COM objects - sp_OACreate, sp_OAMethod and so on. Look here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ff16a833-01fe-4877-8aa6-55b72603ec2e.htm
for further details.
The only difference here is that you need to use a Scripting.FileSystemObject in order to reach Dir functionality through COM.|||

A SQL Agent Proxy account won't give a non-sysadmin access to use xp_cmdshell as part of a job step. A SQL Agent Proxy Account will only allow a non-syadmin to use a job step with the type of operating system.

In order to do what you need to do following what appear to be the same guidelines that I lay out for my developers.

Use xp_dirtree 'C:\Folder\With\Files\', 1, 1 to get a list of files within the folder that you specify. The second parameter tells it how many levels deep to scan. 1 tells the procedure to only use the current level. The third parameter tells it to include files. If the third parameter is not included then only folders are shown.

Then use the BULK INSERT command to load the files. This can all be done without needing access to xp_cmdshell or to the file system.

Let me know if this does or doesn't do the trick for you.

Thanks,

Denny

|||

Why don't u use sqlserver integration services (SSIS).

That would do the job perfectly.

You just have to create a "for loop container" which loops through the set of files you have on a directory and import them.

all these functionality is in SSIS.

|||

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

|||

Denny,

Appreciate your response,

using xp_dirtree would work for me to gather file names from the folder to process...

this resolves one issue..

i also use xp_cmdshelll to copy, delete and rename these files after processing....

do you have some other xp_..... for these?

also, is there a way we can execute some exe's like we have one to retrieve the dos path for the folder with spaces etc.

Thanks.

Sunny

|||we cannto use ssis as we don't know before hand what files we have to create the process..|||

Sunnyny wrote:

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

Of course they are dynamic. Or you mean that exact directory you need to scan is dynamic also? Well, create a special table in your DB so user will insert there a path to the directory to scan. When SSIS package is started, it can retrieve required path from this table and scan it, as usual. Don't see anything too difficult here.

Sunnyny wrote:

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

Don't understand - do you really need to drop database from your code?|||

yes, the folder is dynamic too and also sometimes the path is UNC path...

I'll look into SSIS..

do you have any samples or point me.. where we could have example of substituition of filenames etc?

sometimes users uninstall our database and start afresh and because of multiple connections created due to job running every minute and using sp_OAmethod and not destorying that object... Database is unable to be dropped.

|||

If you wanted to use SSIS to you could use a .NET script block to get the list name list for processing and then process it via the Loop that was mentioned above.

You can use xp_delete_file to delete files from the hard drive.

There aren't any extended stored procedures which are nativly installed with Microsoft SQL Server which can rename or copy files. You would need to write these in C++ and attach them to the SQL Server. Your DBA may not allow this to happen either. You may need to change your processing reuirements so that you process and delete, and have the process which gets the files back a backup of the file at that time, instead of while the import is processing. Or before you start the import process use a Command Line Job Step to copy any and all files in the folder to another place for backup purposes.

mrdenny

|||

... except xp_delete_file is notoriously flakey - it reports success even when it's not deleted what it's supposed to.

Search +xp_delete_file +problem - loads of stuff out there about how it simply doesn't work and no one has an answer to it.

Issue with Export to CSV

Dear Anyone,

Whenever we export reports to CSV, it seems that the column headers in the CSV fileb are the actual column names in the select statement we used in the data set and not the column names that are in the report.

Is there a way to reflect the column names in the report to the CSV file rather than the actual physical column name in the report?

Thanks,
JosephAs there is no explicit binding in RDL between the data columns and the header columns, CSV export uses the names of the controls in the export. You can either change the textbox name or you can override the name using the DataElementName property (on the data tab of the textbox properties dialog). This is just like the XML data output, described here: http://msdn2.microsoft.com/en-us/library/ms156020(en-US,SQL.90).aspx. You can also exclude them here (DataElementOutput).

I have asked the documentation people to update the CSV topic.

Issue with Attaching database (MS SQL Server 2000)

Attach a database as MYDB_X using Enterprise manager. (File names:
MYDB_X.MDF & MYDB_X_LOG.LDF)
Detach this database,
Rename the files as MYDB_X11.MDF & MYDB_X11_LOG.LDF
Select this MDF file in Attach Database Dlg of Enterprise manager.
"Attach As" gets populated as MYDB_X (Name as last attached)
Attach DB as MYDB_X11.
Detach this database,
Rename the files as MYDB_X.MDF & MYDB_X_LOG.LDF
Select this MDF file in Attach Database Dlg of Enterprise manager.
"Attach As" gets populated as MYDB_X11 (Name as last attached)
Attach DB as MYDB_X.
Detach this database,
Select this MDF file in Attach Database Dlg of Enterprise manager,
"Attach As" gets populated as MYDB_X11
When MDF file is selected shouldnt it get populated with MYDB_X? Or how to
make sql server remember the last "Attach As"'
It seems that, "Database Name" property doesnt get updated everytime in Db
attach process... If database name length is more then only its updating.
DhirenHi
I think this is a known issue http://tinyurl.com/jk8fr, the workaround would
be to use sp_renamedb and attach it as the original name. If you are
attaching using sp_attach_db then it would not be an issue.
John
"Tinu" wrote:
> Attach a database as MYDB_X using Enterprise manager. (File names:
> MYDB_X.MDF & MYDB_X_LOG.LDF)
> Detach this database,
> Rename the files as MYDB_X11.MDF & MYDB_X11_LOG.LDF
> Select this MDF file in Attach Database Dlg of Enterprise manager.
> "Attach As" gets populated as MYDB_X (Name as last attached)
> Attach DB as MYDB_X11.
> Detach this database,
> Rename the files as MYDB_X.MDF & MYDB_X_LOG.LDF
> Select this MDF file in Attach Database Dlg of Enterprise manager.
> "Attach As" gets populated as MYDB_X11 (Name as last attached)
> Attach DB as MYDB_X.
> Detach this database,
> Select this MDF file in Attach Database Dlg of Enterprise manager,
> "Attach As" gets populated as MYDB_X11
> When MDF file is selected shouldnt it get populated with MYDB_X? Or how to
> make sql server remember the last "Attach As"'
> It seems that, "Database Name" property doesnt get updated everytime in Db
> attach process... If database name length is more then only its updating.
> Dhiren
>
>