I have installed SQL Server 2012 Express edition SP1 in my system. I have created a database in the instance. Now when I try to generate scripts of tables using Right click on database > Click on Task > Click on Generate Scripts, it is showing Error in Action 'Getting the list of objects from <database>'.
View 4 RepliesHi, How are you?
I have a problem generating command objects from SqlCeCommandBuilder. The problem is about the UpdateCommand and DeleteCommand. In the where clause, the method GetUpdateCommand and GetDeleteCommand is returning all the parameters. I just need the primary key parameters.
I'm using Microsoft SQL Server 2005 Compact Edition and the table's specification that i'm getting the commands is the following:
CREATE TABLE Table1 (
Column1 nvarchar(2) NOT NULL,
Column2 nvarchar(10) NOT NULL,
Column3 nvarchar(30) NOT NULL
)
;
ALTER TABLE Table1 ADD CONSTRAINT PK_Table1
PRIMARY KEY (Column1, Column2)
;
This is the code:
string commandText = "Select Column1, Column2, Column3 From Table1";
SqlCeDataAdapter da = new SqlCeDataAdapter(commandText, m_dbCon);
SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(da);
da.InsertCommand = cmdBuilder.GetInsertCommand();
da.DeleteCommand = cmdBuilder.GetDeleteCommand();
da.UpdateCommand = cmdBuilder.GetUpdateCommand();
and this is the result of the CommandText property in the UpdateCommand:
UPDATE [Table1] SET [Column1] = @p1, [Column2] = @p2, [Column3 ] = @p3 WHERE (([Column1] = @p4) AND ([Column2] = @p5) AND ([Column3 ] = @p6))
As you can see, the where clause has all the parameters and I just need the primary key parameters
I appreciate your help!!
We are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or
Example 1 (Works):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).
Example 2 (trying to accomplish):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).
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]....
how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?
View 2 Replies View RelatedI am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.
I am running the following query
select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb
[Code] ....
Following are the results:-
DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....
Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.
I am trying to generate a hex value automatically at time of insert. The column used for the hex # is an identity column. I am getting an error that the trigger fails because the field doesn't accept nulls. My guess is that the identity column is not populated yet.
Below is the table and trigger.
CREATE TABLE [dbo].[scancode](
[scancode_id] [int] IDENTITY(1,1) NOT NULL,
[scancode] [varchar](50) NOT NULL,
[parent_object] [varchar](150) NOT NULL,
[parent_object_id] [int] NOT NULL,
[Created] [datetime] NOT NULL,
[code].....
I tried to open the reports for activityof all blocking transactions under the
reports-->standardreport-->Activity-All Blocking Transactions
but it is throwing error
Unable to retrieve data for this report. Following error occured.
Msg 8115, Level 16, State 2
Arthimetic overflow error converting expression to datatype int
I'm trying to set up a SQL Alert to run when a Failed login error is generated. For whatever reason, I can't seem to get this working. I have it set up to run on error 18456 (Failed login), I have the server set to log failed login attempts and I can see the entry in the log file, but the alert never occurs. Basically, I want a way to detect when someone is trying to hack into my database using a brute force approach.
View 8 Replies View RelatedI am attempting to create a snapshot replication publication.
When the snapshot is generated, any table that consists of all numeric columns creates a script with SET ANSI PADDING OFF
I googled around a bit and found that if a DDL trigger is in place on the database, it will cause this to happen.
I dropped the triggers, re-created the publication and subscription, same thing.
Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
[URL] ....
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....
I would like to maintain version control of the all the sql objects (sp, view , tables ) and maintain source code versions control. Any way to use TFS to maintain versions of sql objects. Also the folder structure when using TFS.
View 1 Replies View RelatedI need to do some clean up activities in my databases... So i intended to drop unwanted tables and views.
for that I need to find, whether the table being used by any other objects ?
How could I achieve it?
I would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.
I would like to be able to able to automate this process....
Need to find the list of objects that are referenced from the outside of current database. Example
Use TestDB1
Create Proc TestProc as begin
select * from TestDB1..Sysobjects
select * from sys.indexes
end
Need a query which able to return the following
LinkedServername, Databasename, TableName
null ,TestDB1 , Sysobjects
Any method on creating baselines for your stored procedures/objects.
View 4 Replies View RelatedI'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...
I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:
dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents
I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..
View 4 Replies View RelatedHi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/
(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)
But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#
Does anyone know how to do this?
Cheers,
Matt.
Hi,
is there a way in SQL Server 7, to use some sql to extract the entire database schema?
I know that with the Enterprise manager there is All Tasks- Generate SQL Scripts.
But I need to connect to the database and use some sql to do this.
Any suggestions?
Thankyou
RH
Hi All,
I require to compare two database structures.This is what I went for:
I generated scripts (right click on the database --> All Tasks--> Generate Scripts) for all tables in the two given databases and compared them.
The problem is that the order of the tables in the generated scripts are different. Can I control this order by any possible means?
Thanks in advance.
I recently upgraded to on 2012 SP1 CU5 and have found the SSDT gui for SSIS to be almost unusable. I can't drag or resize items. Any time i try they either automagically shrink to the tiniest possible size, shoot off to some extreme or just shake uncontrollably I didn't have these problems on previous versions (dont remember what It was).
Is there a fix for this?
Hello,
there are a tools, or a "ways" to generating "database creation scripts" from a MDF files?
Thank you for help.
Bye
Toni
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
I want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
Using the scripting wizard in SQL Server 2005 database engine, I have been able to script all my DDL out to a flat file which is great; however, when I scripts for instance all views I would like to have the script in alphabetical order by view name, is there a value I can set to accomplish this?
Thanks
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 RelatedHello,
I would be grateful if you could mail/point me to a TSQL script that can show me the Data Definitions of my SQLserver database objects. In fact, I have found a Visual Basic script that may be of help but it demands the installation of the Visual Basic software, which I do not possess.
Thanks,
Albert.
I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:
1. If I run this command
EXEC sp_MScheck_uid_owns_anything 5
I get this weird error message:
"The user owns objects in the database and cannot be dropped."
Not sure why that is since I'm just trying to list the objects, not drop them.
2. I tried running a simple query to get the objects from the system table instead:
SELECT * from [dbo].[sysobjects] where uid = 5
This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:
The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.
Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!
-Cliff
I have an ActiveX script which I need to convert to a T-SQL Stored Procedure. As a part of it, I need to loop through all the tables in a Database and check whether the particular Table/View exists? If doesn't exist, I need to create one with Primary Key, Foreign Keys, Indexes (Clustered & Non-Clustered), and Check Constraints.
Here is the ActiveX Script I have:
Code:
Dim sNextMonthTable
Dim oServerName
Dim oTables
Dim CheckDate
sNextMonthTable = "Jan_2012"
[Code] .....