Hi if I do exec sp_MSforeachDB '......etc'
and i get SP sp_MSforeachDB not found how do i reference it in order for the server to find it.
Because when we check in master we find the SP there but when we run the exec sp_MSforeachDB ...
we get SP not found
any help pls
Hey, all...
I'm trying to modify the sp_msforeachdb proc (after copying it and renaming it, of course) to exclude system databases. It could be done in 7.0 by adding a "where name not in ('master', msdb', 'model', 'tempdb')" clause to the select in the cursor, but it doesn't work in the SQL 2000 version.
Does anyone have any ideas? Any insight would be greatly appreciated.
I have this chunk of code that you enter your table and it tells you what sp, views etc it is in. But it does not show if that table is used in an sp in a different DB. i was told that sp_msForeachdb may help but i have no idea how use it. Help please here is the code i have below
SET nocount ON
DECLARE @string VARCHAR(1000)
--SET @string = 'dbo.RetailSales_ByStore_ByCustomer_ByDay' --> This is your search criteria
SET @string = 'dbo.Store' --> This is your search criteria
DECLARE
@errnum INT,
@errors CHAR(1),
@rowcnt INT,
@output VARCHAR(255)
SELECT
@errnum = 0,
@errors = 'N',
@rowcnt = 0,
@output = ''
DECLARE @Results TABLE (
Name VARCHAR(55),
Type VARCHAR(12),
DateCreated DATETIME,
ProcLine VARCHAR(4000)
)
INSERT
INTO @Results
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscomments SC
ON SC.id = SO.id
WHERE
SC.text LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
WHERE
SO.name LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscolumns SC
ON SC.id = SO.ID
WHERE
SC.name LIKE '%' + @string + '%'
ORDER BY
2,
1
SELECT
Name,
'Type' = CASE (Type)
WHEN 'P' THEN 'Procedure'
WHEN 'TR' THEN 'Trigger'
WHEN 'X' THEN 'Xtended Proc'
WHEN 'U' THEN 'Table'
WHEN 'C' THEN 'Check Constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'K' THEN 'Primary Key'
WHEN 'V' THEN 'View'
ELSE Type
END,
DateCreated
FROM
@Results
ORDER BY
2,
1
need help-------------
i understand sp_MSforeachdb system stored procedure can be used to do the same task on all the databases. but when i tried this what i does execute that task/command number of times the available databases on same database instead execute that task once on each database.
this is what i was doing
use master
exec sp_msforeachdb @command1 = "exec sp_grantdbaccess @loginame ='test',@name_in_db = 'test'"
GO
can some one ther please help me how i can execute this for each database
I know this is undocumented code and Im brand new to this but how would i use p_msForeachdb in conjunction with sp_depends
View 4 Replies View RelatedHello,
I know that sp_MSForEachDB is not being supported, but I am wondering if someone would help me find out if I can use sp_MSForEachTable within sp_MSForEachDB.
The code below does not work, and I am wondering if the code is feasible.
Error Message - "cursor hCForEach already exist"
declare @var varchar(1000)
set @var =
'EXEC sp_MSForEachTable @command1='INSERT INTO #tblResults2
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused ''?''''
EXEC sp_MSForEachDB @command1=@var
Is this supported? Has anyone used it with success? Here's the code I'm using -
EXEC sp_Msforeachdb "DBCC shrinkdb ('?')"
sp_msforeachdb @command1= 'USE ? exec sp_Depends "dbo.style"'
So im trying to return everything that uses that dbo.style im getting this error I just want to show where that table is being used and suggestions. I know that feature is undocumented. This also doesnt seem to be searching all the Databases when I search it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'master'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'tempdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'msdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'GoldMine_Sales_and_Marketing'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CustomerConcerns'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Aggregate'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLD_BUDGET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CreditCardData'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'COZUM'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet2'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DYNAMICS'.
Object does not reference any object, and no objects reference it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'HUNTB'.
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEV'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEVSmall'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLDOnlineOrdering'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineRMA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineStore'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Reporting'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SSE'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'STTHO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'TMPLT'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'WHAVL'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'QSCHQ'.
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_NET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_NET'.
I am using SQLExpress for Unit Testing my application. In the Unit Tests, I use a local database file that is attached automatically in SQLExpress when the Unit Test uses it.
FYI, in the Unit Test I use the following connection string :
"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyUnitTestDatabase.mdf;Integrated Security=True;User Instance=False;Pooling=false"
By accident, the MyUnitTestDatabase.mdf file was marked as ReadOnly. So, after executing several times the unit Test, the attached databases appear in grey in the SQL Server Management Studio Express. That's normal ! The problem I want to report here occurs when I execute the following script in SQL Server Management Studio Express:
use master
go
sp_MSForEachDB 'Print ''?'''
go
In .SQLExpress, I currently have 8 databases (The three last databases are those attached by the unit tests. They are ReadOnly):
- master
- model
- msdb
- tempdb
- MyUnitTestDatabase (the original db copied and used by the Unit Tests. It's not ReadOnly)
- 1E6AA4A60F3733D37F016842D4626B8B_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_22_03OUTMYUNITTESTDATABASE.MDF
- ADA9F382DFBC95C8334EF95336C98274_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_13_57OUTMYUNITTESTDATABASE.MDF
- F00BF38C8BB8F07D37FCC4E918CF815E_X34058MYSERVICETESTRESULTSX34058_N17400 2008-03-12 17_10_04OUTMYUNITTESTDATABASE.MDF
When executed, the script mentioned above displays sometimes all the databases and sometimes only the 4 first databases ?!?!
I did a demo to various colleagues here, pressing F5 many times in the Script windows. It's seems taht it displays 4 names or 8 names "at random"... (I always wait for the message "Query executed successfully" before pressing again F5).
I have to understand the problem here because I use sp_MSForEachDB to detach all the databases at the end of the Unit Tests and it also fails from time to time...
Thx in advance for any tip that could help me in finding the origin of this problem
V.
PS. : FYI, here is the stored proc I use to automatically detach the databases at the end of the unit tests
declare @spid int
declare @killstatement nvarchar(10)
IF @database like '%TESTRESULTS%'
BEGIN
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
from sys.dm_tran_locks
where resource_type='DATABASE' AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(3))
exec sp_executesql @killstatement
end
fetch next from c1 into @spid
end
close c1
deallocate c1
exec msdb.dbo.sp_delete_database_backuphistory @database
exec master.dbo.sp_detach_db @database, 'true'
END
END
Possibly I was not detaching the databases in a "clean way" and my system databases are now corrupted ? Is such a case, what should I do in addition to the code here above to correctly detach the databases ?
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).
View 6 Replies View RelatedI am trying to install SQL Server Express - (the non ADV version) and get the following error.
"An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli_x64.msi'".
I have 64 bit Vista Ultimate running on an HP dv9000.
I am using the download from the bottom of the download page for SSX where it says 64 bit version.
Prior to this I got the. "64-bit ASP.net is registered. Required 32-bit ASP.NET to install Microsoft Reporting services 2005(32-bit)." messagd during the pre-install scan. From what I gather in the posts, the reporting issue is different from my installation package problem.
I have been trying to prove a point to friends about how far you can go with SSX but am having no luck installing it. This whole process took 1 hour in XP but I've been stymied for 2 days.
I would appreciate being pointed at the right downloads for what seems to be a simple install.
I can't find 'SQL Server: SSIS Pipeline' performance object in performance monitor on a 64-bit SQL Server. I see it on a 32-bit. Does anybody know why?
Thanks
Is there any way to find Report server database name using T-SQL?
I have SQL server report server instance and authentication credentials.
I can use sys.databases but in case of user had changed ReportDb name at the time of configuration this approach will not work then How I can find?
Hello!
We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).
The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.
During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:
Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)
Any help is greatly appreciated,
Lucio Gayosso
I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.
I have a code to find it in local DB....how can I modify to include all linked servers as well:
ALTER PROCEDURE [dbo].[FindTable]
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
[code]....
My ISP runs MS SQL server for me and does not have experience managing this type of database. For example, the database occassionally crashes and looses data. Our site is not live yet, but once we go live, this is unacceptable.
I need somebody to consult on the database configuration, tuning, security and maintenance plan. How would I find a person with the correct qualifications?
I have the following in my web.config<appSettings> <add key="strConn" value="server=10.100.1.2;uid=sa;pwd=;database=MyDB/></appSettings>and I am getting the following error message --"SQL Server cannot be found or access denied"if I replace the server IP address with server name, it works fine. Am I missing something here? Help!ThanksBugme
View 1 Replies View RelatedI installed MS SQL 2014 Standard. In the Data Tools in 2013 with the connection setup I do not see any server names. When calling sqlcmd -L all servers are visible. In SQL Configuration Manager Network Configuration I set the TCP / IP, Shared Memory and Named Pipes have set enabled. I installed all the server components.
View 6 Replies View RelatedHow to find total number of SQL Server which is running in localnetwork m/cs, through C# or from one of SQL Server? - Thanks in adv.
View 2 Replies View RelatedHello,
When I Installed SQL Server in my PC I didn't choose if I want to connect with Windows Authentication or SQL Server Authentication. So, I can't connect to neither of those, In the Connect to Server box I cannot find any server to connect. How do I connect to the server with the Windows Authentication ?
I need to deploy a multi-user application to several different customers. My app was built on VB.net and uses SQL Server Express. For remote client to connect to the server I understand that I need to use a connection string something like this:
€śServer=ServerName;Database=myDB;Trusted_Connection=True;€?
My question is, how will I know what the server name is? Can that be different for different customers? When I test this on my PC, I use (Server=.SQLEXPRESS;), but what do I use for deployment?
Thanks.
Hello;
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
Thanks
Hi,
I installed already vs 2005. The installation installed automaticaly SQL Server 2005 express. Now , I want to see which databases I have there and tables,views,store procedure etc. I tried the server explorer in the vs but I don't see any database on my pc. I tried also to open somthing like enterprise manager like I have in sql server 2000 but I don't found such a tool. I tried to find it in START=>Programs=>SQL Server 2005 but their I see only the Configuration tool. I want to know if the installation of the vs 2005 does not install Databases for the sql server Express ? Should I have to download this database samples? What about a tool to view those database like the enterprise manager in sql server 2000 ?
Thanks,
David
I need to find out how long my SQL Server has been running and I believe that there is a way of obtaining this info by querying a system table (or executing a procedure).
Does anyone know about this?
Karl
Hello.
I have a few reports that I've published to my reportserver.
Now, my rdl files have been delete from my local computer.
Is there a way to find the rdl files on the server so I can download them to my local pc so I can continue working on them?
I have full access to the report server of course (and the the sql server itself).
I'm useing rs2005.
Thanks.
I installed sql server on my PC, and when I look at the Programs I dofind sql server, but when I click on that I get "configuration tools".I don't see anything that says "enterprise manager" for instance. Howcan I find it so I can make a shortcut to it?Thanks
View 4 Replies View RelatedI am trying to get the CLR user-defined aggregate functions sample working.
I have made the DLL and I can create the assembly,
create assembly sqlutildll from 'D:SQLdatadllSQLutils.dll'
This went OK. But I can't create the function:
CREATE AGGREGATE Concat (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME sqlutildll.Concatenate
this is the error:
Msg 6556, Level 16, State 1, Line 1
CREATE AGGREGATE failed because it could not find type 'Concatenate' in assembly 'SQLutils'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
This looks like a simple error, but the name Concatenate is spelled correctly (it is a copy from books online see "Invoking CLR User-Defined Aggregate Functions" in BOL)
The error is "could not find type", but it is a class, might this be the problem?
Lost as a goose....
View 1 Replies View RelatedSay the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?
View 7 Replies View RelatedDear All,
I have installed SQL Server 2005 Express Edition, Iam not able to find SQL Agent, So anybody help me............................
Regards,
Vinayak
Hello,
My problem is this:
In the phase of installation of my software, how I can know if SQL Server 2005 (anyone edition) is installed in the guest system?
Thank you.