Skip to main content

SQL Server – Recuperare il Product Key da una installazione esistente

fonte: http://www.ryadel.com/sql-server-2008-2008-r2-e-2014-recuperare-il-product-key-da-una-installazione-esistente/

A volte può essere necessario recuperare il Product Key del proprio SQL Server da una installazione esistente: il caso più comune è quello di un Server di una certa età del quale si è perso ogni ricordo di configurazione e che si ha improvvisamente necessità di trasferire altrove, ma può anche capitare di smarrire semplicemente il codice seriale e trovarsi nell’esigenza di doverlo recuperare.

Fortunatamente, l’informazione può essere agevolmente ottenuta tramite l’esecuzioe di questo script PowerShell realizzato da Jacob Bindslet:

Lo script funziona con tutte le versioni e le edizioni di SQL Server dalla 2005 in poi: SQL Server 2005, SQL Server 2008 e SQL Server 2008 R2: per utilizzarlo con Sql Server 2012 o Sql Server 2014 dovrete apportare delle piccole modifiche.

Nel caso di Sql Server 2012 dovete modificare la linea 5 nel seguente modo:

E anche modificare la linea 16 nel seguente modo (grazie a gprkns per il suggerimento):

Una versione completa dello script modificato in questo modo per funzionare su Sql Server 2012 è disponibile a questo indirizzo.

Nel caso di Sql Server 2014, Microsoft ha spostato il nodo DigitalProductID  all’interno della chiave di registro relativa all’istanza dell’installazione di Sql Server: per questo motivo dovrete modificare la linea 5 nel modo seguente:

Per eseguire lo script è sufficiente compiere queste operazioni:

  • Lanciate un prompt PowerShell (Avvio > Esegui (oppure Start > Run se il sistema è in lingua inglese), quindi digitate powershell e premete invio.
  • Copiate il testo della funzione di cui sopra e incollatelo direttamente all’interno del prompt.
  • Premete INVIO una o più volte, fino a quando il prompt non tornerà disponibile.
  • scrivete GetSqlServerProductKey e premete INVIO.

Se tutto è stato eseguito correttamente il Prompt si riempirà di una serie di informazioni nel seguente modo:

sql-server-2008-retrieve-product-key

Potrete così visualizzare la versione di SQL Server installata, l’architettura, la build, l’edizione e, soprattutto, il vostro Product Key.

Per il momento è tutto: felice recupero!

How to grant insert in a table that is in another database on SQL Server 2008 R2?

fonte: https://dba.stackexchange.com/questions/96629/how-to-grant-insert-in-a-table-that-is-in-another-database-on-sql-server-2008-r2

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4c12914c-8364-48f4-ad35-db64fbeb0ff1/how-to-give-read-write-permissions-to-a-specific-table-in-a-database-of-sql-2008r2?forum=sqlgetstarted

Use [DatabaseA]
GO

ALTER DATABASE [DatabaseA]
SET DB_CHAINING ON
GO

Use [DatabaseB]
GO
ALTER DATABASE [DatabaseB]
SET DB_CHAINING ON
GO

EXEC sp_grantdbaccess 'UserB';
GO 

GRANT SELECT, UPDATE on [DatabaseB].[dbo].[TableA] TO [UserB]
GO

How to run 32-bit UDL file on a 64-bit Operating System

fonte: https://blogs.msdn.microsoft.com/chaitanya_medikonduri/2008/04/09/how-to-run-32-bit-udl-file-on-a-64-bit-operating-system/

We’ll need to  execute the command below from a command line or Start/Run :

 

C:\Windows\syswow64\rundll32.exe “C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll”,OpenDSLFile C:\test.udl

 

Check the paths of rundll32.exe and oledb32.dll while running this command!

 

Here you have your 32 bit OLE DB Providers.

Rimuove spazi in eccesso da stringa SQL

fonte: http://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql

SELECT REPLACE(REPLACE(REPLACE('some   string    with         many     spaces'
  , '  ', ' ' + CHAR(7) + CHAR(7)), CHAR(7) + CHAR(7) + ' ', ''), ' ' + CHAR(7) + CHAR(7), ' ') AS NewStr
--but it remove CHAR(7) + CHAR(7) from string

How it works: enter image description here

Caution:
Char/string used to replace spaces shouldn’t exist on begin or end of string and stand alone.

Esportazione dati da SQL a file posizione fissa

Fonte: http://gaurangpatel.net/exporting-data-from-sql-server-to-fixed-position-flat-file

Exporting data from SQL Server to Fixed Position Flat file

Just as usual, this post is kind of “Bi-product” of my attempt to create a demo for another post in which I need to use fixed position file. At first I tried to create a fixed position file using BCP. BCP has query out option to export query results into a file, but I was unable to produce the fileformat which uses fixed position. I actually even tried to use format file to generate required format but I ran into many issues so finally I thought that it is more important to get results than means to achieve it (at least it is true in this case Open-mouthed smile) and I fired up Visual Studio to use SSDT.

Actually it is very easy to generate a fixed position file using SSIS. All you have to create is a simple Data Flow task and then add a SQL Server as source and a flat file as destination into that task. Only thing to be sure is configuring Flat File destination settings.

FlatFileConnection Details Preview

In Flat File Connection Manager settings, goto General tab and make sure that FORMAT is “Ragged Right” not “Fixed Width”. What’s confusing is if you want to add row delimiter in the end then you have to use Ragged Right not Fixed Width. And that’s it !!

Simply run the package and you have fixed width flat file for your all Devil experiments  …

I have attached sample SSIS project created with SSDT to give an idea. Of course you will need to modify this project to meet your requirements.

That’s it for now…

It’s Just A Thought … Peace

Gaurang Sign

**************************************************************************************************************************************

fonte: http://stackoverflow.com/questions/15346503/how-do-i-export-data-from-a-query-to-a-text-file-with-fixed-column-widths

I actually just did this last week. I wrote a function that does this then for each field I call the function.

Here is the function

/* USE [Newton-Dev] GO /****** Object: UserDefinedFunction [dbo].[CharPad] Script Date: 1/10/2015 11:38:27 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Script : Character Padding Function; Assist with fixed width file creation Version : 1.0 (01/08/2015) Author : Jeffery Williams */ ALTER FUNCTION [dbo].[CharPad] ( @Input VARCHAR(255) ,@OutputWidth INT ,@OutputAlign VARCHAR(5) ,@PadCharacter CHAR(1) ) RETURNS VARCHAR(255) AS BEGIN DECLARE @Output VARCHAR(255) DECLARE @InputWidth INT SET @InputWidth = LEN(@Input) IF @InputWidth > @OutputWidth BEGIN IF @OutputAlign = 'LEFT' BEGIN SET @Output = LEFT(@Input,@OutputWidth) END IF @OutputAlign = 'RIGHT' BEGIN SET @Output = RIGHT(@Input,@OutputWidth) END END IF @InputWidth < @OutputWidth BEGIN IF @OutputAlign = 'RIGHT' BEGIN SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input END IF @OutputAlign = 'LEFT' BEGIN SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) END END IF @InputWidth = @OutputWidth SET @Output = @Input RETURN (@Output) END

Here is the query that calls the function and provides the fixed width output:

SELECT --dbo.CharPad (DeltaLineId,8,'LEFT',' '), dbo.CharPad (DeltaElgId,8,'LEFT',' ') dbo.CharPad (CARRIER_ID,6,'LEFT',' ') ,dbo.CharPad (GROUP_NUM,7,'RIGHT','0') ,dbo.CharPad (LEFT('0000' + SUB_GROUP_ID, 4),9,'LEFT',' ') ,dbo.CharPad (SVC_TYPE,1,'LEFT',' ') ,dbo.CharPad (FILLER_1,1,'LEFT',' ') ,dbo.CharPad (FILLER_2,5,'LEFT',' ') ,dbo.CharPad (RATE_CODE,2,'LEFT',' ') ,dbo.CharPad (FILLER_3,1,'LEFT',' ') ,dbo.CharPad (ELIG_CODE,1,'LEFT',' ') ,dbo.CharPad (EFF_DATE,8,'LEFT',' ') ,dbo.CharPad (TERM_DATE,8,'LEFT',' ') ,dbo.CharPad (SUBSC_SSN,9,'LEFT',' ') ,dbo.CharPad (INDIV_SSN,9,'LEFT',' ') ,dbo.CharPad (CHNG_SSN,9,'LEFT',' ') ,dbo.CharPad (REL_CODE,2,'LEFT',' ') ,dbo.CharPad (HIRE_DATE,8,'LEFT',' ') ,dbo.CharPad (DOB,8,'LEFT',' ') ,dbo.CharPad (REL_TYPE,1,'LEFT',' ') ,dbo.CharPad (FIRST_NAME,24,'LEFT',' ') ,dbo.CharPad (MID_NAME,24,'LEFT',' ') ,dbo.CharPad (LAST_NAME,24,'LEFT',' ') ,dbo.CharPad (GENDER,1,'LEFT',' ') ,dbo.CharPad (POP_DESC,5,'LEFT',' ') ,dbo.CharPad (ADR_LINE_1,30,'LEFT',' ') ,dbo.CharPad (ADR_LINE_2,30,'LEFT',' ') ,dbo.CharPad (CITY,30,'LEFT',' ') ,dbo.CharPad ([STATE],2,'LEFT',' ') ,dbo.CharPad (COUNTY_CODE,3,'LEFT',' ') ,dbo.CharPad (COUNTRY_CODE,3,'LEFT',' ') ,dbo.CharPad (ZIP,5,'LEFT',' ') ,dbo.CharPad (ZIP_EXT,4,'LEFT',' ') ,dbo.CharPad (FILLER_4,21,'LEFT',' ') ,dbo.CharPad (USER_DEFINED,30,'LEFT',' ') ,dbo.CharPad (WAIT_PERIOD,1,'LEFT',' ') ,dbo.CharPad (CAID,9,'RIGHT','0') ,dbo.CharPad (FILLER_5,9,'LEFT',' ') FROM export.DeltaLine */

How to Setup SQL Server 2008/2008R2 Maintenance Plan and Email Notifications

fonte: http://nideesh.wordpress.com/2012/10/24/how-to-setup-sql-server-20082008r2-maintenance-plan-and-email-notifications/

How to Setup SQL Server 2008/2008R2 Maintenance Plan and Email Notifications

Most of the time we will have good installation and configuration for several systems but when we talk about maintenance and regular health check there should be a delay in talk and response. Database backup and maintenance should consider very important DBA should take responsibility to their assigned database. This article, we will discuss about creating Email notification and alert using out of box notification solutions designed by Microsoft in SQL 2008 /2008R2, we will go deep in maintenance plan and notification task. Backing up database in to tape or disk is not the scope of this articles however to explain SQL Database mail and Email notification ,we will back up the database in to disk and when the backup task is completed we will trigger the notification to the respective admin user for the status of the process. This will become a warning for the admin in case there is a failure in backup or any other task which we have scheduled through maintenance plan with in SQL. Then whole process and step described in this article are done through Microsoft SQL Server Management Studio, of course for email creation you have to go for the mail server.

First step is to configure Database Mail. Open Microsoft SQL Server Management Studio then right-click on Database Mail > select Configure Database Mail

clip_image001

Skip the welcome screen and select Next on the Select Configuration Task window.

clip_image003

Create new profile > fill out Profile name > Select Add under SMTP accounts:

clip_image005

Fill out New Database Mail Account info:

clip_image007

You can create New profile for SMTP Server and Email Address for different jobs and maintenance Plan here by adding more SMTP profile

clip_image009

clip_image011

Consider public and privet profile security, Default Profile and email flow according to your environment and alert requirements. Flowing are the screens for the completion and over view of your Database mail configuration which you have carried out.

clip_image013

clip_image015

clip_image017

After all went right Send test email by Right-click on Database Mail

clip_image019

Fill out test info, by typing the email address and select Send Test Email.

clip_image020

clip_image021

clip_image022

clip_image023

Check your inbox for the test mail .If you do not receive the test mail ,then make sure all the email address and SMTP setting you have configured on the SQL Database Mail configuration and Exchange server incase if your relaying your mail form SQL server.

clip_image025

Also for troubleshooting mail flow, View Database Mail log is a good place for starting.

In order to receive Email we must create at least one operator it will be under SQL Server Agent and right click Operators and click for New Operator

clip_image026

Here you can specify the Name of the Operator then in Notification options, we can set up email, Net send etc…and also the schedule, and these are self-explanatory. After fill all the necessary info and details click OK.

clip_image027

clip_image028

Now we will create a Maintenance Plan, right click on Maintenance Plans àMaintenance Plan Wizard. If you have already one just Modify that plan and add the notify operator task or success and failure.

clip_image030

clip_image032

clip_image034

Write the name and description and schedule it by click the change button.

clip_image036

I have schedule this for daily @12AM with No end date then as soon an you click OK, you will be prompted back to the Select Plan Properties; click Next. You will get help on this and any configuration part by clicking Help button if you have installed the documents and Books while installing SQL Server. This is good practice and you will get a quick reference point.

clip_image037

Here you can select the Maintenance Task for your Maintenance Plan, which is totally based on your requirements Click Next

clip_image039

Move the order if you required then select task and click Next. The few screens below are the backup settings. This is again your choice and requirement.

clip_image041.

clip_image043

clip_image045

clip_image047

Here you can select the report location folder and Email that report to the DB admin or operator you have created earlier in the post. Click Next

clip_image049

This will finish the Maintenance Plan Wizard. Click the + sign and review the settings you have made in this plan.

clip_image050

I fall wend right you will be in Green window and close the Wizard.

clip_image052

Right click on the Maintenance Plan and click Modify this will open the Maintenance Plan Design windows

clip_image054

From the toolbox on your right side drag and drop the Notify Operator Task to any Maintenance Plan you want to notify email alert whether it is success or failure.

clip_image056

Below is the screen for my Maintenance Plan notification design.

clip_image057

Here the red is connector is Failure and Green is Success. You can right click on the connector and change the status to success or failure at any time and also you can edit. Below is the screen for Task connector status.

clip_image058

Now double click on each Notify Operator task and Select the Database Mail which you have created earlier and Type subject and message body according to your need.

clip_image059

Finally, you can test your Maintenance plan and Email notification, If you want to wait for the seclude to be run then fine , otherwise by right click on the Maintenance Plan you have created and click Execute. You will be having a status window like the one shown below close it after finish.

clip_image060

clip_image062

clip_image064

If every this is configured right or not but the Database Mail configuration and Operator email is configured properly you will get notification. There will always be some adjustment and special configuration is required depends upon the environment, that you can sort out with your skill and experience. I have created distribution mail group and add as many administrator you want notify by simply add the user in that AD distribution mail group, this way you can eliminate the errors caused when you fiddle around with SQL management studio and other settings.

This article will help you a better understating for your setup and configuration. Suggestion and questions are very important for me to improve and enhance next article. I always believe in praising of my mistakes.