|
I'm not entirely sure but I think you are trying to
a) determine if there are any tables in a schema
b) if there are tables then drop them
c) then drop the schema
I'm guessing that you are currently getting the error Quote: Cannot drop schema 'InsertYourSchemaName' because it is being referenced by object
The following code steps through all the tables (if any) in a named schema (I've included views as well). It will drop the table (or view) then drop the schema at the end
declare @schemaName varchar(255) = 'Test'
declare @thingsToDrop table (TABLE_NAME varchar(255), TABLE_TYPE varchar(255))
insert into @thingsToDrop (TABLE_NAME, TABLE_TYPE)
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @schemaName
DECLARE @name varchar(255)
DECLARE @type varchar(30)
IF CURSOR_STATUS('global','myCursor')>=-1 DEALLOCATE tb_cursor
DECLARE tb_cursor CURSOR FOR
SELECT TABLE_NAME, TABLE_TYPE FROM @thingsToDrop
OPEN tb_cursor
FETCH NEXT FROM tb_cursor INTO @name, @type
DECLARE @sql nvarchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @thing varchar(10)
SET @thing = (SELECT CASE WHEN @type = 'BASE TABLE' THEN 'table'
WHEN @type = 'VIEW' THEN 'view'
END)
SET @sql = CONCAT('DROP ',@thing, ' ',@schemaName, '.', @name)
EXEC sp_executesql @sql
FETCH NEXT FROM tb_cursor INTO @name, @type
END
CLOSE tb_cursor
DEALLOCATE tb_cursor
set @sql = 'DROP SCHEMA ' + quotename(@SchemaName)
exec sp_executesql @sql I tested it with these examples
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO
;CREATE VIEW Test.ViewTest AS
SELECT * FROM Test.test UNION ALL SELECT * FROM Test.test1
GO
;CREATE SCHEMA Test;
GO
;CREATE VIEW Test.ViewTest AS
INFORMATION_SCHEMA.TABLES
GO
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO
;CREATE SCHEMA Test;
GO
|
|
|
|
|
|
I have a piece of legacy software that I have successfully used on modern Windows systems up to and including Windows 10.
I have just installed it on a laptop running Windows 7 (64-bit) and on that machine only, I get the problem described below.
One component is written in VB6 and uses DAO 2.5/3.51 for database access. It all works fine except for SQL statements of the form:
Alter Table xxxx Add Column yyyy Integer.
The data type of integer is merely an example. Irrespective of the data type, all Add Column requests throw an exception "Can't load DLL: MSJTER35.DLL"
I have checked in C:\Windows\System32 and MSJTER35.DLL is definitely installed on the machine.
As I said, it's only Add Column statements that fail. All other database access, Select, Update etc. work fine.
I know this is all old hat, VB6 etc. but I don't have the resources to reengineer in VB.net and, as previously noted, the whole system is working fine on several Windows 10 boxes.
Any suggestions why I am getting this message and how to fix it?
Keith
|
|
|
|
|
|
Problem solved. The key was that it wasn't saying it couldn't find msjter35.dll (the file was clearly there) it was saying it couldn't load it. After some digging, I discovered that msjter35 has a dependency on msjint35.dll and it was that latter file that was missing.
I have no idea how the machine was delivered to me in this state, but copying msjint35.dll to SysWow64 solved the problem.
Thanks for your interest.
Keith
|
|
|
|
|
Hi,
I have below query which giving 8 records.
declare <a href="https://www.codeproject.com/Members/T">@t</a> table (id int)
declare <a href="https://www.codeproject.com/Members/t2">@t2</a> table(id2 int)
insert into <a href="https://www.codeproject.com/Members/T">@t</a> values(1),(1),(1)
insert into <a href="https://www.codeproject.com/Members/t2">@t2</a> values(1),(1),(1)
select t1.id from <a href="https://www.codeproject.com/Members/T">@t</a> t1 inner join <a href="https://www.codeproject.com/Members/t2">@t2</a> t2 on t1.id=t2.id2
I think we should get 9 records.
Please help
Thanks
|
|
|
|
|
That code produces 9 records here. There must be something else going on.
Execute your query in a new SSMS query document. Try adding SET ROWCOUNT 0 at the top, just in case you've picked up some weird settings from somewhere.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I tested your script in SSMS-2017.
It gives me 9 records.
BTW, perhaps you should scroll the "result" window to see the last row?
|
|
|
|
|
hi All,
Is there any setting in SQL Server when I am creating a stored procedure, it can automatically generate documentation for me that who is creating who is updating, date time of creation and update etc instead me typing all that by myself. Just like in the below example.
/****************************************************************************************************/
/* NAME : [Usp_Get_vvvvvvById] /
/ PURPOSE : THIS STORED PROCEDURE RETRIEVES LIST OF vvvvv codes /
/ TABLES USED: /
/ [dbo].[xxxxxxxxxx] , [dbo].[xxxxxxxxxxxxxxx] /
<h2>/ VERSION HISTORY:- */</h2>
<h2>/* VERSION NUMBER| DATE | AUTHOR | CHANGES */</h2>
/* 1.0 | 10/18/2017 | xxx | INITIAL VERSION */
<hr />
I want it can be generated automatically instead of me typing every-time, anybody can help me please, any help can be a great help - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
I presume that you mean when you are in an editor and start a new proc.
Rather certain that Visual Studio provides that straight out of the box. Certainly has for a very long time for other languages like C#. There is or at least was something named something like 'template' which was invoked every time one created a file of a new type.
|
|
|
|
|
Hello All,
I have a job which runs for one hour is now running for 3 hours.
I looked at the sql server logs, error logs, event viewer for any errors or anything out of ordinary. I could not find anything.
Is there a way to know why the is taking longer?
thanks!
|
|
|
|
|
Maybe profiller would help?
|
|
|
|
|
Simple guess; there's now more data in you database.
If you have a lot of functions in your query, then those will slow it down. If you do lot of character-manipulations, it will slow down. Convert time often, and it will slow down.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Or someone dropped an index.
|
|
|
|
|
Or someone took half the RAM out of the machine.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
or your 1Gb connection is now 10Mb....
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
I meant that dropping an index is less likely. Just like a sudden hardware change. Still possible, just less likely.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I have run across this in stored procedures, one of the more esoteric problems was "parameter sniffing", basically it comes down to moving your input parameter values to local variables.
Create proc ....
@InputID int
as
Declare LocalID int
Set @LocalID = @InputID
And use @LocalID int the body of the procedure
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to find one employee belongs to max dept
|
|
|
|
|
Please think about what your question says. We have no idea what your table structures are, or what fields are associated with an employee record.
|
|
|
|
|
What is "employee"?
What is "dept"?
What is the relationship between them?
|
|
|
|
|
With as little information as you have supplied this may give you some ideas.
select *
from Employee
where DepartmentID =(
select top 1 DepartmentID from(
select DepartmentID , count(*) C
from Employee
group by DepartmentID) x order by C desc)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi I have a SQL Agent task which sends an email to all staff. The email is different because it sends it to a group which has "require that all senders are authenticated" ticked in their settings. This task works fine from one node in our availability group. I've copied the job to a secondary node and it says successful but no email is sent. We've investigated and can see a blocked email with the error: "Your message can't be delivered because delivery to this address is restricted".
I have restarted the SQL Agent service which didn't make any difference. The database mail looks to be configured in exactly the same way it is on the other node where it works fine. Any ideas why it is not authenticating properly please?
A reboot is an option but we'd rather not do this.
|
|
|
|
|
I suspect that message is originating from your email service not your application. So your service is attempting to send email via an API call to a server, and that server is telling you that it doesn't like what you are doing.
That is a configuration issue and probably one that can only be fixed via the email service. As a guess perhaps there is a security restriction based on IP/DNS (box of agent) and it needs to be updated.
|
|
|
|