Query : Running A Job/step In A Loop For ALL Databases

Jul 20, 2005

Hi,

I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?

Thx,

GC.

use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'

View 1 Replies


ADVERTISEMENT

Running Query On All Databases Within An SQL

Oct 3, 2007

Hi,

I'm running SQL 2000 SP4.

On this server I have a few databases. I need to run the below query against each database. Since the server has approx 50 databases this will take time.

Is there any way for me to modify this so as it will automatically
run against each database one at a time and then create a single output file with all the results.

I do not want it to run against all of the databases at the same time as it will kill my server so it must run and gather results on each database at a time.


QUERY to be run

SELECT RelativeFileName

FROM Collection, Saveset

WHERE Collection.CollectionIdentity = Saveset.CollectionIdentity

View 11 Replies View Related

Running A Query On Two Databases

Mar 31, 2008

Hello all,
Can anyone please tell me what query to use for joining two tables in two databases together?

Basically, we have two databases called 'system' and 'site' respectively. The 'system' database has a table called 'users' and each user has a unique id which is also linked to the tables in the 'site' database.

The only problem i have got is, I want to be able to get the actual user names not the unique id and the only way to get this from my SQL statement is to be able to link the uniqueid for each user in the 'site' database to their corresponding uniqueid in the 'system' database.

Please, how can I do this?

View 14 Replies View Related

Running A Vbs File As A Step Of An SqlServer Job

Dec 11, 2007

HiI have a VBScript file which runs fine when I click it or when I run itfrom a command prompt level.I added it as the 1st step of an SqlServer job and the job always failswith the error message"Executed as user: GVIANTAdministrator. The process could not becreated for step 1 of job 0xBE3A0405EB9F624282C1A9474AA9F719 (reason: %1is not a valid Win32 application). The step failed."Cn anyone help me understanding this error message.Thanks 1David Greenberg

View 2 Replies View Related

Question Regarding Running A Stored Procedure As A Job Step

Apr 16, 2007

I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?

View 3 Replies View Related

Changing Databases In While Loop

May 1, 2008

I am trying to run an update query for 2 identical databases. I am using SQL 2000. Here is the code for the loop.
--*****************************
USE DB1
Declare @Locnum1 integer
set @Locnum1 = 0
While @locnum1 < 2
BEGIN
Update OpenJob
Set JobN = 15
WHERE (OpenJob.CustomerN = 183)
USE DB2
Set @Locnum1 = @Locnum1 + 1
END
--*****************************
When I run this code it runs the update query on DB1 twice instead of switching to db2. Any help you can provide would be greatly appreciated.

Thanks,

Jason

View 14 Replies View Related

Stored Procedure To Loop Through Databases

Mar 16, 2015

We're running SQL Server 2008 and have run into a bit of a situation. We have 5 databases all with the same tables and we are trying to create a query that will loop through the different databases and output the results per company database. I originally did a cursor, but my boss wants the query to be in a more readable format:

His ideal wish would be the query in a stored procedure and the cursor to create the input parameter for the stored procedure for the different databases.I've tried looking through some forums and googling some possibilities but can't seem to make any sense of them.

declare @dbname varchar(100)
,@sql varchar(max)
createtable #TempDBs (
dbname nvarchar(100)
, Orig_Jnl int
, BaseRef int
, Posting_Date date

[code]....

View 7 Replies View Related

Foreach Loop Though All Databases On A Server

Oct 8, 2007

Hi

I am trying to write an SSIS package that will look though all the databases on a server (in this case 2k server) and get the list of tables in each database

I have created a Execute SQL Task with select name from master..sysdatabases where name <> 'Tempdb'

This is connected to a Foreach Loop that uses Foreach ADO Enumerator on the first table from the var generated in step 1 (List of databases)

Within the Foreach loop there is a dataflow that has a a sql command

select @@servername SQLInstanceName
,db_name() DatabaseName
, name TableName
--, getdate() RecordDate
from sysobjects where type = 'u'


This should return all the tables in a database however the databases is not changing it just lists all the tables in the master database times the number of databases on the server


Can anyone point me in the correct direction for this one

Thanks

Kevin

View 1 Replies View Related

Connect To Multiple Databases In Foreach Loop

Jun 21, 2008

I have sql task inside a foreach loop which needs connection to multiple databases on different servers. I can pass the server name and the database name as a parameter. Can someone please tell me what steps to follow? Thanks in advance

View 2 Replies View Related

Reporting Services :: Running Report In A For Loop

Oct 27, 2015

My problem is:

I have an order , (order id) and multiple boxes (ex: 7 boxes)
 
I must create a report, that contains among others a bar code => composed like this : "orderId+01" for the first box, "orderId+02" for the second box, .... , "orderId+07" for the 7-th box

I need this report to repeat itself for each box , only the bar code is changed.

Once I call it , it must generate 7 labels in this example.

Now i am able to generate only one report. Iam using report builder 3.0

View 5 Replies View Related

SQL 2012 :: Agent Job History Shows Step Still Running But Shows Start And End Times?

Jul 1, 2015

I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.

I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?

what conditions a job step my be showing in the job history as both running AND completed successfully?

View 6 Replies View Related

Sample Files From The Book SQL Server 2005 Reporting Services Step By Step

Jan 18, 2008



Hi All,

Please let me know where can i find the sample files and rs2005sbsDW database in msdn library as i dont have CD provided along with the book.

Please give me the link of the sample files so that i can download it for testing the sample application.

Regards,
Prabhanjana

View 1 Replies View Related

Step By Step SQL2000 Failover Cluster Upgrade To SQL2005

Oct 13, 2006

is there such a paper? if so, can you pls point me to it? thx in advance

View 4 Replies View Related

Step By Step To Publish/deploy The Report Project To The User Pc ?

Oct 3, 2007

hye everyone,

after finished do the report and deploy at IIS/report manager in local pc..

i want to know , what is the step /how to deploy the report project at the user's pc/ client pc /other server..


thanks in advance
thank you very much

View 16 Replies View Related

Reporting Services :: How To Convert RDL File To RDLC Step By Step

Jun 18, 2012

I want to convert .rdl  to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.

View 5 Replies View Related

Execute Package Results Different To Step By Step Execution - Uses Raw File

Jan 31, 2007

I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.

Here's the rub:

If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).

I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.

Any help is greatly appreciated.

I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)

Thanks

B.

View 2 Replies View Related

Step-by-step Deployment Of Single-user Database App?

Mar 2, 2008

Hi!

I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.

I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.

I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.

What I have so far is:
1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however.
2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005)
3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)

Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)

The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.

What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.

But I'll take pretty much anything that fixes the specific db connectivity problem I've described.

Thank you very much.

John F.

View 11 Replies View Related

How To Kill A Long Running Query Running On A Background Thread.

Sep 1, 2006


If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?

The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.

Is there a way to do this?

Thanks


View 1 Replies View Related

How To Read An Database Table Step By Step (with ADO)

May 19, 2008









Hi,
I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory.
Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.

Regards,
harry

View 2 Replies View Related

Enabling T-SQL Debugger (SQL2000) - Step Into, Step Over Etc

Dec 5, 2007

Connecting to a networked SQL Server Box from my local machine


Open Query Analyzer from Start menu, logging in using sa account.

from the object browser i select my stored procedure (WEA_InsertClaim) - right click and select Debug.

i am prompted to enter the parameter values, which i do, auto rollback checkbox is checked - click Execute.

T-SQL Debugger opens and runs through the stored procedure.

but only buttons enabled are the "Go", "Toggle Breakpoint", "Clear All Breakpoints"

so i can set breakpoints etc. but when i select Go it will not stop at the breakpoints it just runs through the stored procedure from start to finish. giving the correct return code as its output

is there something i need to enable in order to make it stop at breakpoints??

Cheers,
Craig

View 3 Replies View Related

SQL Server Step-Up Licensing Query

Jan 29, 2008

Hi,

I've recently purchased a number of SQL Server licenses, one was a single processor enterprise license with 2 years SA/Step-Up. The reason I purchased the SA/Step-up was that we plan to move to 2008 when it is released and I was given advice from our suppliers that the step-up would allow us to do this.

However, I've read a few things in the past few days that have made me questions this. Namely a comment that read: "SA Step Up is normally only used if you have for e.g. SQL Server Standard with SA and wish to turn it into SQL Enterprise..a completely different product"

What I'm now wondering is whether I've wasted money purchasing a step-up license. Can it be used to upgrade to 2008 when it is released? I'm going to buy another enterprise license shortly with the same upgrade intentions but if SA isn't going to help then I see no point buying it.


Thanks in advance.
Andrew

View 6 Replies View Related

Creating Multiple Databases Running Off The Same Data Engine

Aug 23, 2005

Hi, I have looked and loked for the answer to this question and made no progress.  I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas).  Is it alright to create another database and have 2 running simultaneous on the same data engine?  Is there any type of limitations that I sould know about - such as number of allowed accesses at one time?  Any help is greatly appreicated.

View 1 Replies View Related

Transact SQL :: Script Not Running For Databases Other Than Master Database

Jun 5, 2015

Script only displays the result for 'Master' database. 

What changes are required for script to display result for all databases on the instance?

SELECT DB_Name() AS DatabaseName, OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC

View 8 Replies View Related

Running Setup To Install AdventureWorks Sample Databases And Samples

Mar 23, 2007

Hi, i read article on how to install the AdventureWorks on my PC, basically i downloaded the sample db from microsoft website, but after i install several times, i seems cant get to see it in my sql server 2005, please assist on this, i have been craking head for this. Thanks :)

View 1 Replies View Related

Step By Step For Indirect Configuration

Feb 15, 2006

Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.

Just wondering if there was something else out there....

Thanks in advance for any help.

MarkA

View 3 Replies View Related

Help! Debugging Stored Procedure From Query Analyser - Step Through Disabled

Jan 5, 2004

Can anybody explain how to do debug a stored procedure from SQL Query Analyser.

When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".

It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"

I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.

The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and
@RETURN_VALUE = 0 message

I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.

What might be the problem, how to solve it do i need to give any permissions.

i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.

Pls help me out

Thanks in advance
Murali Kumar

View 1 Replies View Related

SQL Server Agent Job Step Fails When Creating A SQL Server Integration Serveices Type Of Step

Mar 12, 2008

Hi

I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)

This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"

I have made the following configurations:

The user group (windows group) that the user belongs has the following roles in msdb :

db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

i have made a proxy to sql server agent which has the following subsystems :

"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.

Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.

Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.

Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.

View 3 Replies View Related

Reporting Problems W/ MS SQL Server 2005 Reporting Services Step By Step To MS

Apr 26, 2007

I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:



select * from vProductProfitability

where year = 2003 and

MonthNumberOfYear = 1



The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.



I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?



BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.



This humble grasshopper seeks wisdom.



Respectfully Submitted,



Dave Matthews

Atlanta, GA

aka FlooseMan Dave

View 17 Replies View Related

Loop Through Query

Jun 13, 2007

Out of one messed up table I need to create two related tables. I am stuck in trying to get 'some' of the columns from the messed up table to the new table.
How can I select only the columns I need and insert them in the new table?
Thanks in advance!

View 1 Replies View Related

How Can I Use A For Loop In A Query ?

Nov 15, 2007

hi all i have the following stored procedure.
declare @AreaID int
select @AreaID = 1
 
select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from  table1 INNER JOIN                      table2 ON table1.JobId = table2.JobEvent_JobId where
 (table1.Job_AreaId = @AREAID)
the there are about 300 AREAID's  so my question is do i do a "LOOP"  or is that no efficient and chews up resources on the server ?what would be a nice clean way to do this ?
thanksrobby

View 5 Replies View Related

Query In A Loop

Jul 20, 2005

I am using Report Writer for Ingres II.Is it possible to write a query in a loop?e.g. My table is like thistime position09:01 pos0109:02 pos0309:02 pos0109:04 pos05Can I loop a query to count the number of times each position occurs in each30 minute period in a day?I wish to generate a report that goes something like09:00 09:30 10:00----------------------------------------pos01 3 5 3pos02 0 6 4pos03 4 3 1

View 1 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

Replication Step By Step

Sep 13, 2007

Dear Experts,
please guide me for replcation.....
i'm using sql server 2005 server tools developer edition. my OS is professional 2000.
and i've sqlserver 2000 client tools also

i've two databases in my machine. publisher is in different instance, and the transactions might be maximum 50 per day. my aim is when ever developer enters the data into the main database, automatically it should be updated on the my two databases also.
i mean the server is sysA and the database is srtp.
my machine is sys20 and the databases are srtp1, srtp2.
how should i make sysA as publisher?
i've right click on the server databases, but it is showing newsubscriptions option only.



Vinod
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved