Tag: database

Create a dynamic query

I have made a stored procedure like this.. CREATE PROCEDURE [dbo].[sp_dataPull] @serverName nvarchar(30), @dbName nvarchar (30), @serverName2 nvarchar(30), @dbName2 nvarchar (30) INSERT INTO sampleDatabase.dbo.WorkFlowCopy ([ServerName] ,[DBName] ,[ID] ,[ActivityDefinitionID] ,[ParentID] ,[Caption] ,[Description] ,[ShortDescription] (SELECT @serverName, @dbName, sdb1.* from OPENDATASOURCE(‘SQLOLEDB’, ‘Data Source=phmnldb16\eaudit;user id=XXXX;password=XXXX’).AUDIT_FSA_170_001.AUD170.Workflow sdb1) UNION ALL (SELECT @serverName2, @dbName2, sdb2.* from OPENDATASOURCE(‘SQLOLEDB’, ‘Data Source=phmnldb16\eaudit;user id=XXXX;password=XXXX’).AUDIT_FSA_170_002.AUD170.Workflow sdb2) This […]

Refreshing Non-Production Environments (Data Warehouse)

My question has to do with regard to refreshing non-prod environments (Dev, Test, Staging). My question: What is typically best practice in terms of how you do it and frequency? A couple options that I thought of: Every night or periodically run the ETL jobs against their respective source environments i.e. in Dev I would […]

Failed to import target model. The element is not supported in Microsoft Azure SQL Database v12

I’m setting up a CI for Azure SQL DB. When running C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130>sqlpackage.exe /SourceFile:”MyPackage.dacpac” /Action:Publish /TargetServerName:”my-server.database.windows.net” /TargetDatabaseName:”db-name” /TargetUser:myuser /TargetPassword:”SomeSecurePassword” /TargetTimeout:120 I’m getting Initializing deployment (Start) Initializing deployment (Failed) An error occurred during deployment plan generation. Deployment cannot continue. Failed to import target model db-name. Detailed message The element SYMMETRIC_KEY_NAME is not […]

How to exclude database objects that include a certain Extended Property using SQL Compare 12?

I’m using SQL Compare to generate a script file to deploy to a production environment, but I can’t get the Include or Exclude command line options to work with extended properties. Here is the commands I’ve tried so far. Let me try to only include only objects with the extended property ‘Feature’ SQLCompare.exe /scripts1:.\ /empty2 […]

SQL – Calling scripts Error SQLCMD query

So i have been writing a data migration script for the past few weeks and have decided it has become too long and is too hard to edit as one long script. So what i wanted to do was to split it up into individual scripts and have one script run the others. From looking […]

How do I save date and time and then sort them from new to old in datagridview, I'm using Visual Studio 2015 and SQL server

In Visual Studio I have: ‘Label15.Text = DateTime.Now.ToString(“dd/M/yyyy(dddd) hh:mm:ss:tt”) Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick Label15.Text = DateTime.Now.ToString(“dd/M/yyyy(dddd) hh:mm:ss:tt”) Label16.Text = DateTime.Now.ToString(” hh:mm:ss tt “) End Sub In sql server currently I’m using a nvarchar data type, what data type should I use? what im trying to do is make a […]

Save data in child table with parent table having composite keys

I wonder why it is not working. I am using ASP.NET MVC having dbcontext to contact with database. I have two tables. Table A and Table B Table A has multiple columns from that there are three columns i.e. Email, Year and Month which are primary keys / composite keys. Table B has a 5 […]

Update/Set multiple column with set and select statement

I have below trigger written which works perfectly fine for single column update (firstname), however we now need to update few extra columns (lastname, mobileNumber etc.) CREATE TRIGGER apl.updateJaWithCandidateDataAfterInsert ON [APL].[tblJobApplications_v2] AFTER INSERT AS SET NOCOUNT ON UPDATE [APL].[tblJobApplications_v2] SET FirstName = ( SELECT FirstName FROM [USR].[tblUserDetails] WHERE inserted.CandidateId = [USR].[tblUserDetails].CandidateId ) FROM [APL].[tblJobApplications_v2] INNER […]

Selected Item – listbox insert to database

I have a small problem associated with adding values ​​to database. C# and SQL Server I have this procedure: ALTER PROCEDURE [AB].[AddMovie] @Actors nvarchar(255), @Director nvarchar(100), @Title nvarchar(255), @Year char(4), @Length nvarchar(5), @Description nvarchar(255), AS BEGIN INSERT INTO AB.Movie (Title, Year, Lenght, Description, Actors, Director) VALUES (@Title, @Year, @Lenght, @Description, @Actors, @Director) END; ADDCATEGORY_TYPE ALTER […]

How to create type from existing table in SQL Server?

I have database with many tables already existing. I need to write stored procedure that takes data identical to some of these tables and process it in stored procedure. I know one can create tables from type. However sometime it’s cumbersome to add new type just to facilitate single stored procedure. Is there any way […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.