Transact SQL :: Read All DB Objects In Dependency And Need Object Definition
Jun 16, 2015
I need a solution (script) where I can pull all objects(DB's incudes tables, views, synonyms, stored procedures, primary key, foreign key, triggers etc) definitions from the server in a dependency order. I want to generate a script from these objects, where if I run script all objects should be created the way it is..
.All dependencies should be automatically available in script in a dependency order. What is the best way to bring all objects (definition) in form of scripts.
These are the fields am looking - databsename, schemaname, objectname, typeofobject, depenedencyorder (nothing but level)...
View 6 Replies
ADVERTISEMENT
Aug 17, 2007
Hello All
Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.
I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:
There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.
[stored procedure name] and [trigger name] are where the actual names appear in the message.
I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).
Has anyone seen this, and if so, how can I fix it?
Thanks in advance for your help!
Jay
View 4 Replies
View Related
Sep 8, 2014
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
View 3 Replies
View Related
Oct 12, 2006
1. Create table and 2 triggers with script below (*)
2. Run the following script:
use testjrl;
begin transaction;
update testtable
set workphone = '333' where id = 1;
rollback;
Get following error message:
(1 row(s) affected)
Msg 2801, Level 16, State 1, Procedure trgTestTableInsertUpdate, Line 7
The definition of object 'trgTestTableInsertUpdate' has changed since it was compiled.
The statement has been terminated.
3. Change trgTestTableInsertUpdate trigger to
alter table TestTable disable trigger trgTestTableDelete
select 1
alter table TestTable enable trigger trgTestTableDelete
4. Run the script from step 2 again - works fine.
Questions:
a. Why does first error occur?
b. Why does error go away when you specify trigger name to disable?
Thanks
Jim
*Script to create table and triggers:
CREATE TABLE [dbo].[TestTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[WorkPhone] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[trgTestTableInsertUpdate] ON [dbo].[TestTable]
FOR INSERT, UPDATE
AS
alter table TestTable disable trigger all
select 1
alter table TestTable enable trigger all
GO
CREATE TRIGGER [dbo].[trgTestTableDelete] ON [dbo].[TestTable]
FOR DELETE
AS
select 1
GO
View 6 Replies
View Related
Nov 25, 2015
I am facing a very weird issue in our SSAS database. We have several roles with 'Read definition' access. None of these roles are able to see data in the cubes. I have checked these roles over and over and there is no problem with the definition. Each of the roles have been given read access to Data Source, Cube and Dimensions.
The users are able to access the definition and structure of the cube i.e. they can see the measures & dimensions available but when they drag measures and dimension attributes in the browser (SSMS) OR execute an MDX - they get null values. The roles with 'Full Control (administrator)' access are able to see all the data without any issues.
I have tried the following:
1. Deleted all roles and re-created.
2. Created roles directly on SSAS DB.
3. Deployed all objects and processed the DB.
Each time only the admins are able to see the data.
View 7 Replies
View Related
Jan 12, 2012
i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies
View Related
Jun 25, 2015
Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
View 2 Replies
View Related
Sep 15, 2015
I have two tables (i.e. Table1 and Table2).
SELECT
* FROM [dbo].[Table1]
Date
id
9/15/2015
[code]...
Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-
declare @Startdate
datetime
DEclare @enddate
datetime
[code]...
Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns?
View 2 Replies
View Related
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
May 12, 2015
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
View 2 Replies
View Related
Aug 31, 2006
My dillema is this.
I have a table with the simplified sturcture like this:
Teacher
TeacherID
Name
Etc...
YearlyTeacherRecord
TeacherID
Year
Phone Number
blah
blah
I need a list of teachers with their most recent yearly teacher record. How would I do that
View 3 Replies
View Related
Feb 11, 2008
Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions. This was possible in 2000.
Thanks, Dave
View 5 Replies
View Related
Jul 27, 2015
We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs. I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically.
Error Log Entry:System objects could not be updated in database 'x' because it is read-only.
Problem: After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated.
Solution: Simply set the db to read-write and the system objects will get updated, long after SP1 was installed.
ALTER DATABASE [x] SET READ_WRITE WITH NO_WAIT
Then set it back to read-only:
ALTER DATABASE [x] SET READ_ONLY WITH NO_WAIT
You should then see these log entries:
System objects could not be updated in database 'x' because it is read-only.
Setting database option READ_WRITE to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.
Synchronize Database 'x' (129) with Resource Database.
Setting database option READ_ONLY to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.
View 0 Replies
View Related
Nov 29, 2007
We are in the process of trying to automate our production releases (what a concept ;-)
The database is SQL server 2005
All objects are being stored in VSS
Using Nant and Cruise Control for the actual migrations.
I have two directories - Create (for a brandnew database) and Change (db object changes)
In my 'Change' script, I do the following -
1 - Take backup of database
2 - Migrate objects from 'change' directory to production
3 - Script out all objects of database and save in the 'Create' directory
For the #3, I was hoping I could create an SSIS package that would
script out all database objects and save them on the VSS server.
I'm new to SSIS and want to verify it's something that can be done before I start down that path.
If anyone has any examples or references, it would be much appreciated.
Thanks
View 4 Replies
View Related
Apr 2, 2008
I was wondering if it is possible to use a generic list object to use as the datasource/data for a report? Right now we use these business objects to display information on our website and would be great it we could re-use those objects and create reports based on them. We are doing some calculations etc in the business layer to build these objects, so if we could re-use, that would be best.
Thanks!
View 2 Replies
View Related
Aug 16, 2004
I have to build application Excel (interface) - SQL Server (backend)
I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.
And error message I am geting is "Cannot update. Database or object are read-only".
Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingspdDesktopDevelopment est.xls;" & _
"Extended Properties=Excel 8.0"
strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"
cn.Execute strSQL
cn.Close
Set cn = Nothing
Read-only of databse properties is not selected.
View 2 Replies
View Related
Apr 19, 2006
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
View 11 Replies
View Related
Mar 19, 2008
Hello everybody. When I start to test SQL 2008 Febuary CTP I detect that sume of new options work incorrectly. This is description of this bug from Microsoft Connect:"I don`t know, may be somebody detect this issue too. Sorry If I create duplicate. I start SQL Server Managment Studio & click new query. After that I try to create simple query like this
Uses Master
Select "column name" from "table".
I was surprised because when start write column name I saw the pop-up objects list. But this was very strange list, because I didn`t find necessary columns. This list show only "common" objects(such as database nemes, functions & ect). I think that such list(after first select) should show only children objects(columns, keys, triggers, indexes & ect.), after that is you write "from" such list should show only parents objects (Such as table name, database name & ect).
PS. Sorry for my english." After that(today) I get the answer from MS SQL Team "Hi,
Your observation is correct. Selection list (column names) can suggests correct candidates once user specifies FROM clause.
Regrads,
Eric Kang
SQL Server Program Manager" & bug was closed as "By Design". Then I reopen this bug with such comments "
But It`s lokks very strange. If I want to select column name from the list I should: 1. write SELECT 2. Press Space 2. Write FROM "Table name" 3. Returt to free space after SELECT 4. Start Write "Column name". 5. Choose the necessary Column name from the list. There are too much for such simple operation.
Posted by stanisluv on 3/19/2008 at 11:45 AM
Too much steps." Sorry I make a typo. I mean: "looks". If anybody else argee with me please validate & vote the feedback. This is the link for it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=333047
View 1 Replies
View Related
Aug 26, 2015
We have an interface where we receive data from an external supplier. One of the fields in the interface is of type BLOB (the source is an Oracle database), which would be read into our MSSQL database as image. This can also be converted to varbinary, and a typical field value looks something like:
0x70697A5F8F000000789C0DCCBD0DC2301....etc. etc.
However, we know that the origin only contains text, and we even know the text from the GUI they supply us with. The text could typically be "Delayed by 3 minutes because of water damage" or something like that.
What I want to do, is to extract that text from the field.
First, I have stored the incoming data stream in a table, where one column is of type varbinary(max). It looks like this goes swell. But I don't know which command to use in order to get the text extracted.
I have tried these:
1) select master.dbo.fn_varbintohexstr(Myfield) from Mytable
-> Returns just the text "0x70697A5F8...." which I have no interest in
2) select cast(Myfield as varchar(max)) from Mytable
-> Returns just Chinese signs.
3) select cast(Myfield as nvarchar(max)) from Mytable
-> Returns just Chinese signs.
4) declare @ptrval varbinary(16)
select @ptrval = TEXTPTR(MyField) from MyTable -- with MyField defined as image
READTEXT MyTable.MyField @ptrval 1 30
-> Returns just the text "0x697A5F8...." which I have no interest in
Of course, since only text is stored in this field, the field should never have been defined as BLOB in the first place. But the source system is external, and it's a standard system, so we may not alter it in any way.
View 2 Replies
View Related
Aug 26, 2011
i have a data base sql Server .I wrote a stored procedure or attach my database but it is attached in read only mode how can remove read-only.
this is my stored procedure.
create procedure attache
as
declare @trouvemdf int
declare @trouveldf int
if exists (select name from sysdatabases where name='Gestion_Parc')
[Code] ....
i try this EXEC sp_dboption 'Gestion_Parc', 'read only', 'FALSE' but it causes those error
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C: Gestion_Parc.mdf". Operating system error 5: "5 (Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C: Gestion_Parc_log.ldf". Operating system error 5: "5 (Access is denied.)".
Msg 945, Level 14, State 2, Line 1
[Code] ....
View 3 Replies
View Related
Jun 27, 2012
I am reading text file using OPENROWSET command. The read is successful. But the UNICODE characters in the file is not showing properly in the output.I am using a sql like below:
select
*
FROM
OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=ppa20-igloo-fsMODELSTORECR106_GIM_UKGISourceFilesProcess;HDR=Yes;Format=Delimited(|)',
'SELECT * FROM [RUW_P_Reinsurers_Aviva_and_Others_Map.txt]')
how should I read the unicode characters correctly.
View 8 Replies
View Related
May 14, 2015
declare @inputXml xml
set @inputXml='<root>
<row>
<col>start_date</col>
<col>1</col>
<col>rpton#on#13-May-2015|$|rpton#on#13-May-2015</col>
[Code] ...
I get below output ....
But I need below output ....
View 7 Replies
View Related
May 6, 2015
I have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
View 9 Replies
View Related
Sep 16, 2015
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount
[code]....
View 19 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Mar 24, 2015
How to identify whether the files are in read write or read only?
View 1 Replies
View Related
Aug 26, 2015
I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write.
I tried from
GUI or ALTER DATABASE [WSS_Content] SET
READ_WRITE WITH NO_WAIT
but I received the below error:
Database WSS_Content is in Warm Standby
View 9 Replies
View Related
Jan 18, 2008
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
thanks
View 7 Replies
View Related
Nov 26, 2007
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
View 1 Replies
View Related
Aug 14, 2007
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 2 Replies
View Related
Jun 27, 2014
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
View 2 Replies
View Related
