I have SQL server 2014 database installed on a Windows 2012 server and SSDT also got installed ( 2013 shell)
I can connect to a database and see all objects using SSDT , but I cannot write any queries or modify any of the objects,
I am new to the SSDT, and this is the first time I am going to configure SSDT to work with SQL Server.
SQL server 6.5 stopped responding. The last error messages in the SQL Server Error Log / NT Event log suggested that I increase the Open Objects / Open Databases values. I rebooted the server, increase the configuration values, and rebooted the server again for the new configuration to take affect. I suspect that the front-end application does not release the objects properly when it should; therefore increasing Open Objects / Databases configuration values will only be a temporary fix until they reach the new maximum in some time. However to prove that I need to be able to determine how many open objects / databases are on the server at a point of time. Is there a procedure or a method to determine how many open objects / dtabases are on server?
Any comments or suggestions are greatly appreciated.
Thank you
Marina Somers
SQL 6.5
NT 4.3.3
Recently in the error.log the following message appeared:
Warning: Open Objects paramter may be too low
attempt was made to free up descriptors in localdes()
Run sp_configure to increase parameter value
Please indicate why this message appears and what measures must be taken to prevent it from re-occuring.
Thank You in advance!
Hi, is there a way to find all the objects that are currently open on the server, that includes:
Tables, Views, SPs, Extended SPs, Triggers, Rules, Defaults, Indexes on the tables?
I can get some information from syslockinfo table but I can find a way to get the full picture.
Any help on that would be appreciated.
Dim
Currently I must manually complete the following commands to set a new valuefor the "open objects" and "open indexes". I need a way script this processin a batch file.COMMANDS RAN: This is nothing to script, these commands obtain the currentvalues for open objects and open indexes.isql -Usa -Pmanager -SCOP1_DSexecute sp_countmetadata "open objects"execute sp_countmetadata "open indexes"goRESULTS: below are the results, there are 5124 user objects and 2122 userindexes. I need a way to be able to multiple these numbers by a set valueand then use the new value and reset the user objects and user indexes. Thequestion is how do I isolate the results for each result so I can run thecalculations below. Here is the calculation I need:current user objects * 1.05 = new valuecurrent user indexes * 1.1 = new valueThere are 5124 user objects in all database(s), requiring 6985 Kbytes ofmemory.The 'open objects' configuration parameter is currently set to 500.(return status = 0)There are 2122 user indexes in all database(s), requiring 5902 Kbytes ofmemory.The 'open indexes' configuration parameter is currently set to 5000.(return status = 0)SETTING THE NEW VALUES: Once I have the new values, I can set the ojects andindexes as seen below.1> execute sp_configure "open objects", 53802> execute sp_configure "open indexes", 23343> go
View 2 Replies View RelatedI have a sql server project where I have added 1 stored procedure (named StoredProcedure1) and 1 class (named MyClass). MyClass has two functions Func1() and Func2(). The stored procedure simply instantiates MyClass and calls Func1().
Func1() creates a SqlConnection object using "context connection=true" - opens the connection and then calls Func2(). Func2() also creates a SqlConnection object using "context connection=true" - and attempts to open the connection. However when attempting to the Open() call inside Func2(), the code just exits. What gives? The code is pasted below, thanks!
*******THE STORED PROCEDURE**************
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlServerProject1;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{
// Put your code here
MyClass obj = new MyClass();
obj.Func1();
obj = null;
}
};
*******THE CLASS "MYCLASS"**************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class MyClass
{
public MyClass()
{
}
public void Func1()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
conn.Open();
Func2();
conn.Close();
}
}
public void Func2()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
if ( conn.State != ConnectionState.Open )
conn.Open();
if ( conn.State == ConnectionState.Open )
conn.Close();
}
}
}
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 RelatedI was wondering if there is a way to take backup or restore the database using SSDT.
View 6 Replies View RelatedI have a SSDT project that intermittently fails with the following error when I try to publish via Visual Studio by double-clicking the publish.xml file...
(330,1): SQL72014: .Net SqlClient Data Provider: Msg 1205, Level 13, State 68, Line 5
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(326,0): SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
END
This is a hyperV client running on my laptop. I'm connecting to the client's VPN inside the hyperV client and using the "runas" method to open Visual Studio to spoof the domain credentials i use on the clients network. This allows me to connect to servers on the client's domain via apps as if I was logged in with appropriate credentials (not sure how to phrase that better). I can elaborate if deemed necessary, but even the client's FTEs are having same problem and they're sitting in the building.
Windows Server 2012 R2 StandardSQL Server 2014 CU6 (12.0.2480)Visual Studio Premium 2013 (12.0.31101.00) Update 4
Windows Server 2012 R2 (VMWare)SQL Server 2012 SP2 (11.0.5058).Here are some observations...
Happens about 60% of the time during the day (I just keep re-running until it deploys successfully)...late at night and on weekends, it doesn't seem to happen with anywhere near as high a frequency.Only seems to happen when I use "Always re-create database" option (don't recall incrementals having this problem...but admittedly I'm not doing many of those as we're still in the full-speed-ahead-development phase)This does NOT happen when I publish to my local SQL Server instance..Deadlock xEvent trace on target instance. Unfortunately it didn't seem to be able to capture all the info and the deadlock graph doesn't render (error below) checking the Single User Mode during the publish. This seems to increase the frequency with which this issue occurs.
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]....
We are getting regular SSDT/VS crashes when doing schema compares (source: project, target: server). Sometimes the compare succeeds but around 80% of the time we get a crash.
We are using VS2013 Update 3 and the latest version of SSDT.
Application : devenv.exe
Version du Framework : v4.0.30319
Description : le processus a été arrêté en raison d'une exception non gérée.
Informations sur l'exception : System.Runtime.InteropServices.COMException
Pile :
à System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32, IntPtr)
[code]....
Just wanted to find if there is a way i can install SSDT without a Visual studio Install in the machine?I intend to use sqlpackages.exe which is from SSDT for publishing a dacpac, and do not intend to install any Visual Studio for this purpose.
View 3 Replies View RelatedWe're trying to perform an upgrade test against a copy (backup/restore) of our customer database as target. There are some tables with timestamp column in the database. The way we do this is by having a database project with a publish profile targeting that copy of customer database and then with TFS build server is used to build the database but only to generate a publish script (/p:UpdateDatabase=False) set in the build definition - msbuild argument.
Example of table definition:
CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(6)
,[Timestamp] TIMESTAMP NULL);
We would like to have the "Code" column to have CHAR(7), so in the project we modify the table definition:
CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(7)
,[Timestamp] TIMESTAMP NULL);
Expecting SSDT build will generate alter script:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
To our surprise the generated script was:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
ALTER TABLE dbo.CodeTable1 ALTER COLUMN [Timestamp] TIMESTAMP NULL;
Which will cause error when the script is executed: "Cannot alter column 'TIMESTAMP' to be data type timestamp."
Why is SSDT generating the change script for that timestamp column??
We then try a local build in VS, the issue is not happening, SSDT correctly generates alter script only for the "Code" column to CHAR(7);
Both local machine and TFS Build server are having VS 2013 Update 4- SSDT 12.0.50318.0 installed.
As we tried to troubleshoot further, we found out that it seems it only happens on a restored database from a backup copy of our customer database. It doesn't happen for databases created by SSDT build from scratch or that we manually created. We've tried make sure all database properties are the same as the database that correctly built.
But still if the target database is the one we restored from a customer's copy, SSDT always tries to alter timestamp column (on server build).
I am working in a secured company which doesn't allow live internet connection, therefore I can't install SSDT using Web Platform Installer and we also don't want to install the intire 2012 client tools.
View 2 Replies View RelatedWe have a BI-Stack configuration for server SQL 2012 that includes a gateway server that houses the app, a second server which houses SQL Server DB and SSIS, and a third server that houses SSAS and SSRS. Where should SSDT tools be installed? I assume on the gateway? This is a production environment, so should VS and/or SSDT not be here at all?
View 1 Replies View RelatedI have a file named abc-72015.txt.
I have a package that renames the file from abc.txt to abc-72015.txt
I want to do the reverse. I wan to rename it from abc-72015.txt to abc.txt.
I have a variable named myFileValue with a value of abc.txt.
Can I do this by setting the variable as an expressions and use a function to rename it as abc.txt?
What would the syntax be?
In database projects, VS2015, I want to create some views for me database that will reference another database table using 3 part naming reference.
Works fine in SSMS but when I try and build me project I is throwing up a reference error.
I can't import the other database into this project, so is there a way to suppress the error? I don't really want to exclude these view from the project.
In previous versions of BIDS we were able to customize the DataFlow toolbox to incorporate the Oracle Source and Oracle Destination items - however, it appears this is no longer the case in SSDT (no option to customize toolbox). I've installed the 3.0 Microsoft Connectors for Oracle - when I create a new connection in the Connection Manager I can see the Attunity drivers, but under Other Source/Other Destinations in the Toolbox I cannot find the Oracle Source/Destination components.
View 5 Replies View RelatedURL>..n how to deploy (AKA publish) without dropping the users with the DropObjectsNotInSource enabled. With the SSDT September 2012 release I have been testing I seem to be able to ignore permissions and role memberships but not users.In my situation and that of several other folks I have talked to it is a problem trying to manage this. I am thinking of scenarios where there could be (1..n) databases needing the current build (where n is big and not a static number) and need to be updated but the database Users are different for several reasons.
don't have to manually use schema compare to identify objects to drop when we deploy and not drop database users at the target? Because of the dynamic nature of these databases (i.e. 1-n might be on a different release than the current build) we would like to manage security at the source and not the project or post deployment script level.From the MSFT folks is there any hope that we will get a parameter to ignore users when DropObjects otInSource=true in an soon to be released version of SSDT?
In VisualStudio 2012 in C# projects when you double-click on a identifier (variable name, class name, etc) then all occurrences of that sting in the current file are highlighted.
It doesn't work in SQLServer projects in sql files. It is a shame that although we are in VisualStudio we are still limited in functionality.
Is there a way to enable this feature?
I Can't find the Data Flow Task in SSDT 2012.
Has it been replaced by another task or do I need to do something to make it visible?
Edit: It popped up under my favorite tasks.
[URL]
I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.
If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.
To recreate the issue:
CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL
[code]....
-- Test to confirm
EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')
In Visual Studio 2012:
1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger
When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
I get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express
Hi there,
I got an approach like that:
1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc.
Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that?
The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2).
Thanks for the input!
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
I encountered the following error while attempting to preview an RDL report I was developing in VS2010 using SSDT:"The size necessary to buffer the XML content exceeded the buffer quota"
View 3 Replies View RelatedThe 6:th of march Sql server data tools for Visual Studio 2012 was released.
[URL]
I seem to be unable to install this using the link provided on the blog page. I'm getting a "Same architecture installation" error. Running on the machine is Visual Studio 2012 Premium & Sql Server 2012 (64bit).
why I'm getting this installation error.
Hi,
I am using SSRS Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00. I want to open linked report in new window.
I tried whats mentioned in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240172&SiteID=1 but i get an error on Window.Open method.
How do I solve the problem?
Thanks
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies View RelatedHi, there,
I use VB to make an dll file and register on my local PC, then use sp_OACreate to create OLE object on local SQL server, it works fine. But when I register that dll on other SQL server, it dosen't work, anybody have idea about this? Thank you.
Tony
I see these type of objects in Books on Online. I was just wondering
if anyone knows how to use these objects, seems to have lot of good
information. Could these objects be called in VB?
--------------------------------
CreateDate Property (SQL-DMO)
The CreateDate property indicates the date and time the referenced SQLServer object was created.
Applies To
Database Object Table Object
DBObject Object TransactionLog Object
Default Object Trigger Object
Rule Object View Object
StoredProcedure Object
Syntax
object.CreateDate
Part Description
object Expression that evaluates to an object in the Applies To list
Remarks
The string returned is formatted by using the locale setting of the workstation.
Data Type
String
Modifiable
Read-only
Prototype (C/C++)
HRESULT GetCreateDate(SQLDMO_LPBSTR pRetVal);
--------------------------------------------------------
Anyone know how to grant a user to see all data in sys.objects view?
Thanks.