Here is what the table looks like

As you can see some rows start with spaces and some end with spaces, one row has the NULL value
The output should be a Z followed by the right 6 characters from the column + the length of the column (not the length of the 6 characters that you are returning)

Here is what the output should look like

ZAAAAAA7
ZBBBBBB7
ZCCCCC 7
ZDDDDD 7
Z0
Z      7

As you can see spaces should remain and when the value is NULL then there should be a blank. The length for the row with the NULL value should be 0, you cannot hardcode 7 for the length, you have to calculate it.

Post your solution(s) in the comment section, remember no RIGHT function. Let's see how many different ways you can come up with to do this

Cannot show requested dialog.

Additional information:
Cannot show requested dialog.(SqlMgmt)
Property Owner is not available for Database'[Your Database Name]'. This property may not exist for this
object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

What does this mean, was the server hacked and someone changed the owner? No, what this means is that the owner of the database was a windows account which no longer exists, the person probably left and the account was removed.

All you have to do is change the owner to a valid login

If you want it to be sa or a sql login, this example is for sa, change sa to something else if you want a different sql login

If you want it to be a windows login, you can do the following

If you run sp_helpdb now, you will see that the owner has been changed

If you are old school, you can also use sp_changedbowner to make the change

In the error log I saw the following messages

Server Error: 17190, Severity: 16, State: 1.
2013-04-30 12:42:06.11 Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.
2013-04-30 12:42:06.11 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2013-04-30 12:42:06.11 Server Error: 17182, Severity: 16, State: 1.
2013-04-30 12:42:06.11 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2013-04-30 12:42:06.11 Server Error: 17182, Severity: 16, State: 1.
2013-04-30 12:42:06.11 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2013-04-30 12:42:06.11 Server Error: 17826, Severity: 18, State: 3.
2013-04-30 12:42:06.11 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2013-04-30 12:42:06.11 Server Error: 17120, Severity: 16, State: 1.
2013-04-30 12:42:06.11 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

We are not using encryption, VIA is disabled as well. After trying all kinds of different things I notice that when logging on with this profile on the box there was an error. Something about that the profile couldn't be created and a temporary profile would be used instead

In the end I had to wipe out the user profile from the box

You do that by following these steps

Right click on computer, select Properties
Click on Advanced System Settings
Select the Advanced Tab
On the user profiles section click on Settings

Select the profile and hit Delete

If you try to log in as that user, the profile will be recreated.

Now when I tried to start SQL Server again, the errors were not there and SQL Server started up without a problem

Object type. Can be one of the following object types:
AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

However there is no table in SQL Server that holds this info....or is there?

I answered this question today and decided to share here as well

Here is how you can find that info, you can use my favorite table spt_values

This is the output

AF: aggregate function
AP: application
C : check cns
D : default (maybe cns)
EN: event notification
F : foreign key cns
FN: scalar function
FS: assembly scalar function
FT: assembly table function
IF: inline function
IS: inline scalar function
IT: internal table
L : log
P : stored procedure
PC : assembly stored procedure
PK: primary key cns
R : rule
RF: replication filter proc
S : system table
SN: synonym
SQ: queue
TA: assembly trigger
TF: table function
TR: trigger
U : user table
UQ: unique key cns
V : view
X : extended stored proc

Now if you want to split it into two columns, you can use the LEFT and RIGHT functions together with the PATINDEX function

Here is the result

xtype	Description
AF	 aggregate function
AP	 application
C 	 check cns
D 	 default (maybe cns)
EN	 event notification
F 	 foreign key cns
FN	 scalar function
FS	 assembly scalar function
FT	 assembly table function
IF	 inline function
IS	 inline scalar function
IT	 internal table
L 	 log
P 	 stored procedure
PC 	 assembly stored procedure
PK	 primary key cns
R 	 rule
RF	 replication filter proc
S 	 system table
SN	 synonym
SQ	 queue
TA	 assembly trigger
TF	 table function
TR	 trigger
U 	 user table
UQ	 unique key cns
V 	 view
X 	 extended stored proc

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

So what do you do? You can connect from within SSMS, you need to click on the Database Engine Query icon

Prefix the servername with Admin: Instead of (local), you would do Admin:(local)

Another way to connect is by using sqlcmd with the command-line option A
Here is an example with a username and password

sqlcmd -S ServerName -U sa -P Password –A

You can also use a trusted connection

sqlcmd -S (local) -E -A

If you want to know more about DAC, check out Using a Dedicated Administrator Connection

1) we need to grab all the tables in the database
2) we need to make sure that the table names are valid
3) we need to specify the output directory
4) we need to make sure that the file names are valid
5) we need to specify how we are connecting to SQL Server

We need to grab all the tables in the database
The query to grab all the names is

However if you use schemas then that won't work, you need to do the following

The reason for this is that you can have the same table name in different schemas, you would only get the one in the default schema

We need to make sure that the table names are valid
Sometimes you have table names that have spaces in them or start perhaps with a number. If you have tables like that, you have to put brackets around it. One way to put brackets around tables names is to just do something like this '[' + name + ']' another way is to use the QUOTENAME function

We need to specify the output directory
You need to tell bcp where to dump the files

We need to make sure that the file names are valid
Windows does not allow for certain characters in file names

< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
If you have to give the files to be imported on a Linux/unix systems then you want to eliminate spaces as well
In that case you end up with something like this

We need to specify how we are connecting to SQL Server
You can use password and username to connect to SQL Server or you can use a trusted connections

Putting it all together

Here is the complete query

Running that query will give you something like the following

You can now take that and run it. When you run it, you will see the following output

NULL
Starting copy...
NULL
32 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (32000.00 rows per sec.)
NULL

Sometines you don't want to see that output,In that case we need to suppress xp_cmdshell output, you do this by adding ,no_output at the end

Now you get something like the following

There you have it, a quick and dirty version to dump all the tables into files.

You can of course enhance this by creating a proc where you can specify only a certain schema, delimiters, how to connect etc etc

If you don't want to use xp_cmdshell, you can also dump the results without the xp_cmdshell part into a BAT file and call it from DOS or PowerShell

That query would look like this

The first thing we are going to do is to spoof the host and program name. This is easy to do. Click on Connect, choose Database Engine, you will see the following box

Click on options >>

Click on the Additional Connection Parameters tab and paste in the following

Application Name=TOAD;Workstation ID=LarryEllison-PC

Now you can verify that what you have entered is returned from SQL Server

host_name	    program_name
LarryEllison-PC	    TOAD

If the DBA monitors the connection he might notice.....if not, time for plan B

Kick it up a notch....or two

Time to become real evil :-)
Find out what the biggest table is in your company. Create a database with the same name on your local instance and also create the same table. Now it is time to create a panic.
Open another connect dialog box, add the real server in the Server Name box, for example I entered PDW\SQLServer2015

Click on options >>, click on the Additional Connection Parameters tab and paste in the following Data Source=localhost

Just to verify, run the following query

That returns you the local servername. Look what you see everywhere else (highlighted in yellow)

I see PDW\SQLServer2015 everywhere else.
Now run your query which returns 0 rows

Call your DBA to stop by and then ask him if he deleted all 3 billion rows from this table? Look at his face..let him run sp_spaceused 'HugeTable'. Once the panic sets in tell him he has be pranked.......

Of course there is a chance that all your permissions will be taken away.....

In order to use the Data Profiling Task in SSIS you need to do a couple of things. First thing you have to do is adding a connection, I decided to use my local machine. Next drop a Data Profiling Task on the Control Flow designer pane.

Once you do that you will see a red circle with an x, if you hover over this icon, you will see the following text: The "Destination" property is invalid: Missing destination for profile output. Double click on the Data Profiling Task, leave DestinationType as FileConnection, click on Destination, in the pop up window, choose New File from the Usage Type option, navigate to the folder where you want the file and give the file a name.

Next we have to look what profile requests we want to include. Here is what is available according to Books On Line

The following three profiles analyze multiple columns or relationships between columns and tables.

Profiles that analyze multiple columns	Description
Candidate Key Profile	Reports whether a column or set of columns is a key, or an approximate key, for the selected table. This profile also helps you identify problems in your data, such as duplicate values in a potential key column.
Functional Dependency Profile	Reports the extent to which the values in one column (the dependent column) depend on the values in another column or set of columns (the determinant column). This profile also helps you identify problems in your data, such as values that are not valid. For example, you profile the dependency between a column that contains United States Zip Codes and a column that contains states in the United States. The same Zip Code should always have the same state, but the profile discovers violations of this dependency.
Value Inclusion Profile	Computes the overlap in the values between two columns or sets of columns. This profile can determine whether a column or set of columns is appropriate to serve as a foreign key between the selected tables. This profile also helps you identify problems in your data, such as values that are not valid. For example, you profile the ProductID column of a Sales table and discover that the column contains values that are not found in the ProductID column of the Products table.

I decided to pick a handful of requests. Here is what it looks like.

As you can see, you need to specify the connection, the table as well as the columns
After you hit okay, run your SSIS package
In order to see what the task generated, you need to use the Data Profile Viewer. You can find it under SQL Server, Integration Services.

Run the tool, click on open and navigate to the file that the Data Profiling Task created. Now you will see something like this

As you can see you get the percentage of NULL values as well as some graphical indication of the distribution.

What do you think, useful or do you prefer writing your own queries?

When running SQL Server on Amazon's Relational Database Service it is done a little different. While you can use the command above to take the database offline, you can't use the command to bring the database online.

I you have a database name test and you execute the following it will work

You can verify this by running the following

You get the following error

Msg 942, Level 14, State 4, Line 1
Database 'test' cannot be opened because it is offline.

If you try running the following command to bring the database online

Now you get this error

Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'test', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

So what can you do? RDS has a special stored procedure that will bring the databse online, the name of this stored procedure is rdsadmin.dbo.rds_set_database_online

Here is how you use it

Now you can use the test database again

See, simple, you just have to be aware of these differences

If you want to use OPENROWSET, you can run the following

When running SQL Server on Amazon's AWS RDS, you can't do it like that. If you try running it, you will get the following error.

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.

Here is how you have to do it. From the dashboard click on DB Parameter Groups. Click on Create DB Parameter Group, pick the version of SQL Server and fill in the text boxes. Here is what it looks like

Check the DB Parameter Group you just created and click on Edit Parameters

You will see a screen like this pop up

Modify the ones that you are interested in and click Save Changes

Click on DB Instances, Click on the Instance Actions dropdown

Select Modify from this dropdown

You will see a screen like this

In the Parameter Group dropdown, pick the Parameter Group you just created, hit Continue and then Modify DB Instance.
In order for this to take effect, you need to reboot your instance
Click on DB Instances, click on the Instance Actions dropdown

Select reboot from this dropdown

Once the instance is available again, connect from SSMS, right click on the Server, select Properties and click on the Advanced page. It should now match what you have edited in the DB Parameters Group

That is it for this post, as you can see it is pretty simple to do
Also check out my other AWS RDS SQL Server posts: Trying out Amazon Relational Database Service and How to read the error log on an Amazon RDS SQL Server instance