SQL Stored Procedure – How to Find Your Data – Lesson 3

It can sometimes be a challenge to find where certain data is stored in your database. It’s not easy remembering where data is stored especially if you don’t touch the database every day.

Most people have a memory span like a goldfish, about 10 seconds. This is why we need to come up with solutions and ideas that will help us in the future.

Good news, you can create a sql stored procedure that can be executed against a specified string or word. Once you have it created and saved, next time you are looking for something just execute the sql stored procedure against what you are looking for and it will search your entire database and give you a return value set for every entry.

This sql server stored procedure example with its syntax is all you need, there is no need for any customization. Simply copy the text into your query editor and execute it. You will now have this procedure saved for future use.

The Code

Copy the text into your query editor, execute and you are done. This is standard code and will work across just about any MSSQL database.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[FindMyData_String]

@DataToFind NVARCHAR(4000),

@ExactMatch BIT = 0

AS

SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)

SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type

FROM Information_Schema.Columns AS C

INNER Join Information_Schema.Tables AS T

ON C.Table_Name = T.Table_Name

AND C.TABLE_SCHEMA = T.TABLE_SCHEMA

WHERE Table_Type = ‘Base Table’

And Data_Type In (‘ntext’,’text’,’nvarchar’,’nchar’,’varchar’,’char’)

DECLARE @i INT

DECLARE @MAX INT

DECLARE @TableName sysname

DECLARE @ColumnName sysname

DECLARE @SchemaName sysname

DECLARE @SQL NVARCHAR(4000)

DECLARE @PARAMETERS NVARCHAR(4000)

DECLARE @DataExists BIT

DECLARE @SQLTemplate NVARCHAR(4000)

SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1

THEN ‘If Exists(Select *

From ReplaceTableName

Where Convert(nVarChar(4000), [ReplaceColumnName])

= ”’ + @DataToFind + ”’

)

Set @DataExists = 1

Else

Set @DataExists = 0′

ELSE ‘If Exists(Select *

From ReplaceTableName

Where Convert(nVarChar(4000), [ReplaceColumnName])

Like ”%’ + @DataToFind + ‘%”

)

Set @DataExists = 1

Else

Set @DataExists = 0′

END,

@PARAMETERS = ‘@DataExists Bit OUTPUT’,

@i = 1

SELECT @i = 1, @MAX = MAX(RowId)

FROM @Temp

WHILE @i BEGIN

SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, ‘ReplaceTableName’, QUOTENAME(SchemaName) + ‘.’ + QUOTENAME(TableName)), ‘ReplaceColumnName’, ColumnName)

FROM @Temp

WHERE RowId = @i

PRINT @SQL

EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

IF @DataExists =1

UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

SET @i = @i + 1

END

SELECT SchemaName,TableName, ColumnName

FROM @Temp

WHERE DataFound = 1

There you go, pretty simple.

Execute the Procedure

In SQL server management studio, expand your database, programmabillity and stored procedures. Find the newly created sql server stored procedure, right click on it and select execute stored procedure.


In the value box, enter in the data you are searching for and click ok.


You will now see in your query editor the following.

USE [YourDataBase]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[FindMyData_String]

@DataToFind = N’sold’

SELECT ‘Return Value’ = @return_value

GO

Then simply wait for the query to complete and find your results in the results window. Copy the results into a spreadsheet for easier management and access when you are writing your stored procedure for your report or whatever it may be.

Note that this search will take some time and may also tax your server, pick a time when the server is less busy if this is of concern to you.

How Else Can I Find My Data?

There are certainly other ways to keep track of all your tables and what data they contain. The above example is just that, an example of how you can automate the search for your data.

You can keep up to date database diagrams, with detailed information about table content etc. These will need to be manually manipulated any time there is a change.

Excel spreadsheets with detaile information is useful as well. If you want an automated way to extract all this information into a spreadsheet look at our post on this topic here.

I don’t know about you, but we do like to automate things to increase efficiency and why do something twice when you have machinery to do it for you. A lot of admins are afraid of automation because they think they might not be needed anymore, and if this is your thought pattern then you are probably right. We need inovators and not protectors of themselves.

Innovation Nation

How do we get there? Anytime you see something that could be better, that could be automated with a script, start thinking about how you can make that happen. You can help a lot of other people out there, not only when it comes to IT, but anything really.

New ideas, new solutions, better ways for all of us.

In the old days, innovators did not gain much or make any type of decent living with their ideas and inventions. Today with the vast amount of communication tools we can make things happen fast and even make a living doing it.

We hope we have at least helped someone out that have had the same challenge with not remembering where their data is.

Conclusion

Today we talked about how to make things easier for yourself and other by implementing a little automation and documentation.

Never underestimate the power of ducumentation. If you are anything like me, a lot of what you learn comes from what others have already done and documented, we put our own spin on it and make it work in a different situation, document it and share it with others to help them.

As always, if you have any questions, comments, feedback or ideas comment below and we will be in touch. We’d love to help you out in your unique situation.

And if you have a better way to accomplish what we are doing here, please share your ideas and we will look at them and make updates as needed.

Einar

SQL Stored Procedure – Lesson 2 – Create Stored Procedure SQL Server

Today we will look at some more procedures, hopefully they will have some more exitement to them. Now with a correctly designed sql server stored procedure syntax, you can accomplish just about anything you need (with your data, that is).

We will look at some more select statements as well as a couple of ways to manipulate your data set results. Manipulating the results is very useful for SSRS reporting, and any type of reporting for that matter.

Left Join on Multiple Fields

A standard join statement will bind tables based on values in one field.

Select Table1.Item,
Table1.Sold,
Table2.Desc

From Table1
Left Join Table2 On Table2.Item = Table1.Item

Where
Table1.Sold ‘Not Sold’

Order By Table1.Item

Now let’s say that Table2 has multiple entries for Item. We have seen this from time to time and in this case you cannot use the where statement since it will give you duplicates in your results.

The only way we have seen to get around this is to join on multiple fields. You will need to find a differenciating field to use.

From Table1
Left Join Table2 On Table2.Item = Table1.Item and Table2.InvDate is not null

Now in an ideal world you would go in and clean up all duplicate entries, or fix your application so duplicates are not allowed. But as we all know this is not always something that can be done in the real world. So for reporting purposes your best option would be to filter it as best as possible.

DateDiff

DateDiff is a magnificient tool. When you need to figure out how long has my current inventory been in stock and has not been sold, how long were they in inventory before getting sold, what is the average time for an item to get sold once in stock? Any situation really, how long does an employee take to complete an work order etc.

Select Table1.Item,
Table1.Sold,
Table2.Desc,
(Case when (Table1.Sold IS null)
then DateDiff(Day,  Table2.InvDate, GetDate() )
else DateDiff(Day,  Table2.InvDate, Sold) end ) as DaysInInv

From Table1
Left Join Table2 On Table2.Item = Table1.Item and Table2.InvDate is not null

Where Table1.Sold is null

This will give you a result set of items that are not sold and still in inventory. It will also tell you how long they have been in inventory. We can expand on this on a little to include averages and whatever else you would like.

Select Table1.Item,
Table1.Sold,
Table2.Desc,
(case when DateDiff(Day,  Table2.InvDate, Sold) > ’90’ or (Table1.Sold IS null) then ‘1’ end) as OverNinetyDays,
(case when DateDiff(Day,  Table2.InvDate, Sold) > ’60’ and (DateDiff(Day,  Table2.InvDate, Sold)  (case when DateDiff(Day,  Table2.InvDate, Sold)  (Case when (Table1.Sold IS null)
then DateDiff(Day,  Table2.InvDate, GetDate() )
else DateDiff(Day,  Table2.InvDate, Sold) end ) as DaysInInv

Just a few examples of what we can do with DateDiff.

Scalar-Valued Function Examples

As an example we will assume that you have a database that has seperate fields for a customers first, middle and last name. To make any sense of this in a report for example, we will need to use a scalar-valued function. You create a function much like you create a procedure. Here is an example.

USE [YourDataBase]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fnFormatName] 
(
@paramNameFirst [varchar](30),
@paramNameMiddle [varchar](30) = null ,
@paramNameLast [varchar](30),
@paramFormat varchar(4)
)
returns varchar(40)
as
begin
DECLARE @FormattedName varchar(40)

if @paramFormat = ‘FML’
begin
SET @FormattedName = IsNull( @paramNameFirst, ‘FNU’ ) + ‘ ‘
SET @FormattedName = @FormattedName + IsNull( @paramNameMiddle, ‘ ‘ ) + ‘ ‘
SET @FormattedName = @FormattedName + IsNull( @paramNameLast, ‘LNU’ ) 
SET @FormattedName = replace( @FormattedName, ‘  ‘, ‘ ‘ )

return( @FormattedName );
end

if @paramFormat = ‘L,FM’ 
begin
SET @FormattedName = IsNull( @paramNameLast, ‘LNU’ ) + ‘, ‘
SET @FormattedName = @FormattedName + IsNull( @paramNameFirst, ‘FNU’ ) + ‘ ‘ 
SET @FormattedName = @FormattedName + IsNull( @paramNameMiddle, ‘ ‘ )

SET @FormattedName = rtrim( @FormattedName )

return( @FormattedName );
end

SET @FormattedName = ‘Unknown Name Format’;

return( @FormattedName );
end

Then in the select part of your procedure you will call the function.

dbo.fnFormatName( [NameFirst], [NameMiddle], [NameLast],’L,FM’ ) AS Customer

It will return a full name rather than three seperate line items.

Scalar-Valued Function Example #2

Calculate and return a usable age of a customer.

You will create the function and then call it from the select statement of your procedure. Function example.

USE [YourDataBase]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[fnFormatAge] (@CustomerAge int)  

RETURNS varchar(25) AS  
BEGIN 

DECLARE
@FormatAge varchar(25),
@AgeYears INT,
@AgeMonths INT,
@AgeDays INT,
@AgeHours INT,
@AgeMinutes INT,
@Return varchar(25)

SET @FormatAge = ”

IF @CustomerAge IS NOT NULL
BEGIN
    IF ISNUMERIC(@CustomerAge) = 1
    BEGIN
        SET @AgeYears = ABS(@CustomerAge / POWER(2, 20) )

        IF @AgeYears > 0
        BEGIN
            –Express age in years only
            IF @AgeYears = 1
                SET @FormatAge = ‘1 Year’
            ELSE
                SET @FormatAge = LTRIM(@AgeYears) + ‘ Years’
        END
        ELSE
        BEGIN
            SET @AgeMonths = ABS( (@CustomerAge & (0xF * POWER(2, 16) ) ) / POWER(2, 16) )
            SET @AgeDays = ABS( (@CustomerAge & (0x1F * POWER(2, 11) ) ) / POWER(2, 11) )

            IF @AgeMonths > 0 OR @AgeDays > 0
            BEGIN
                –Express age in months and days
                IF @AgeMonths = 1
                    SET @FormatAge = ‘1 Month’
                ELSE IF @AgeMonths > 1
                    SET @FormatAge = LTRIM(@AgeMonths) + ‘ Mons’
                
                IF @AgeDays = 1
                    SET @FormatAge = @FormatAge + ‘ 1 Day’
                ELSE IF @AgeDays > 1
                    SET @FormatAge = @FormatAge + ‘ ‘ + LTRIM(@AgeDays) + ‘ Days’
            END
            ELSE
            BEGIN
                –Express age in hours and minutes
                SET @AgeHours = ABS( (@CustomerAge & (0x1F * POWER(2, 6) ) ) / POWER(2, 6) )
                SET @AgeMinutes = @CustomerAge & 0x3F

                IF @AgeHours = 1
                    SET @FormatAge = ‘1 Hour’
                ELSE IF @AgeHours > 1
                    SET @FormatAge = LTRIM(@AgeHours) + ‘ Hrs’
                
                IF @AgeMinutes = 1
                    SET @FormatAge = @FormatAge + ‘ 1 Minute’
                ELSE IF @AgeMinutes > 1
                    SET @FormatAge = @FormatAge + ‘ ‘ + LTRIM(@AgeMinutes) + ‘ Mins’
                ELSE IF @AgeMinutes SET @FormatAge = ‘0 Age’
            END
        END

        SET @FormatAge = LTRIM(@FormatAge)
    END
    ELSE
        SET @Return = @FormatAge 
END

RETURN @FormatAge

END

With this particular function you will get a return value that makes sence when looking at an age, rather than a value based on seconds or however this data is stored in your database.

Conclusion

In todays lesson we learned how to manipulate the data returned from your sql stored procedures. We really hope you found this sql server stored procedure tutorial useful and helpful.

If you have any questions, suggestions or if you have a particular challenge you need help with, drop a comment below.

Einar

SQL Stored Procedure – Lesson 1 – Create Stored Procedure SQL Server 2008

There is always a lot of questions around sql server stored procedure basics. How do I create a stored procedure in sql server 2008 for example. Today we will cover how to create a basic stored procedure, saving it to your database for later use or to be called for reporting. Basic select sql server stored procedure syntax. We will also go into basic update, and delete procedures. These will work for just about any flavor of sql that is not ancient.

As with anything be extremely careful and do not practice on production databases. Even a select statement can cause a server to slow down.

Basic Select SQL Server Stored Procedure Example

If you already don’t have it installed, install Microsoft SQL Server Management Studio. Launch the application and connect to your database engine. Use credentials as specified in your database setup. From the management studio you will be able to create backups as well as performing all the steps we will be learning today.

Once connected, expand databases and right click on your test database. Select new query and a blank query window will open up.

To get sql server stored procedure return value of all values in a table, type in this command:

Select * From YourTable

Then hit the execute button. All data stored in YourTable will be returned.

If you want to narrow down the return value and only see certain column values from the table. You can use the select * to find the column names in YourTable. We narrow it down to ProductID and InvNum.

Select ProductID,

InvNum

From YourTable

Again execute and see the different results.

If you want to return all InvNum from a specific ProductID.

Select ProductID,

InvNum

From YourTable

Where ProductID = ‘B27’

You will now see all InvNum’s for ProductID 27.

Or all ProductID’s in the B20 range.

Select ProductID,

InvNum

From YourTable

Where ProductID like ‘B2%’

Or.

Select ProductID,

InvNum

From YourTable

Where ProductID => ‘B20’

And ProductID < ‘B30

There you have some basic select examples.

Select SQL Server Stored Procedure Example Multiple Tables

Basically the same commands as above, but we will add the key value (we will call it ID) to join the tables together as well as add table names to the select statement.

Select YourTable.ID,

YourTable.ProductID,

YourTable.InvNum,

MyTable.InvDate,

MyTable.SaleDate

From YourTable

Inner Join MyTable ON YourTable.ID = MyTable.ID

Again execute and see the different results.

From here you can narrow down your returns using the where clause to display only items based on your criteria as explained above.

 

Update SQL Server Stored Procedure Syntax

The update command will update a field with the value you specify. Again do not practice on your production database, use a test database. Why would you need to update records? The update procedures are frequently used by applications, a sale happens, a new shipment comes in and product is added to inventory, etc. Even if an entry was made by mistake.

Updates are failrly simple, and we do recommend using two criterias for the update to minimize any risk. Here is a basic update statement.

Update MyTable Set SaleDate = ‘2/3/2018’ Where ID = ‘1’ and SaleDate = ‘2/1/2018’

This will change the sale date for ID 1 only if the sale date was 2/1/2018. You can change any available entry, however keys are usually not available for change.

Delete SQL Server Stored Procedure Syntax

The delete command will delete and entire entry based on your specified criteria. This is when you need to be extra careful. To remove entries for an ID, it has to be removed from all tables it is in.

Delete * from YourTable Where ID = ‘1’ and InvNum = ‘1’

Delete * from MyTable Where ID = ‘1’ and InvDate = ‘1/1/2018’

Do not try this on a production database. If this is needed on a production database, ensure you have a good backup before proceeding. This particular command may or may not be able to be executed against your database, depending on the original setup. If you have trouble with this leave a comment below and we can try to help you work through the issues.

How do I Save The Procedure For Later Use?

Saving your procedures is very useful for future use so you don’t have to recreate your work or remember what you did to get the data for your CEO that one time. Also for SSRS reporting functions, and you can have multiple procedure executing any storedprocedure you have saved already.

You will need to add some lines to your query test, database, name, etc. We will go over a basic select statement and save it. It will be stored under your specified database, programabillity and stored procedures.

USE [DataBaseName]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Author,,Name>

— Create date:

— Description:

— =============================================

CREATE PROCEDURE [dbo].[ProcedureName]

AS

BEGIN

SET NOCOUNT ON;

Select InvNum From YourTable Where ProductID = ‘B21’

END

This can be customized based on your various criteria, using some of the statements from above. You can always use an existing procedure, but make sure you edit the procedure name and the alter statement you should change to create.

Conclusion

We have gone through some of the very basics of sql stored procedures. There is so many things you can do, and we will cover more in future lessons. Have fun collecting data, manipulate your results and get the return values you want.

As stated before, never practice on a production system or database, Always have a test server and test database available, when you get the results you want you can implement them on your production system, but always ensure you have a good backup before you do.

As always please leave comments and suggestions below. And if you need any help or have questions leave a comment below as well. Also if you have any suggestions regarding future lessons leave a comment.

Happy SQL learning.

Einar

 

 

 

 

Veeam Backup Recovery – Home Backup – Enterprise Backup

A little confusing header. But the fact is it is true, Veeam is the complete solution, if you need to back up your home machines or all of your servers in your enterprise, and anything in between. When talking about Veeam people tend to think virtual server backup only. This is not the case, a few years ago now they released the Veeam agents, both for Windows and Linux. It would be awesome to have agents for MAC OSx and IBM OS/2 as well.

Veeam Backup and Replication

The ultimate product when it comes to back up, disaster recovery and high availability. A very affordable functional software, fits perfectly into any business size, small, small/medium, large and enterprise. Most vendors seem to focus on one or the other, but with this product you can complete your disaster recovery plan no matter the size of your business.

Complete control of disaster recovery for your virtual environment. Create a Veeam server, physical server with a lot of storage depending on your storage needs. We do recommend using a physical server even though a virtual will work just as well, but for recovery purposes you want to be in a separate environment. If your virtual host or cluster fails, it would be challenging to make any recovery from a backup server residing there. If you are on a separate physical server, you can spin up your VM’s on that server in case of a meltdown.

Veeam Cloud Backup

With Veeam Cloud Connect you can set up a complete offsite backup solution for small business without breaking the bank. Fully automated offsite sync for disaster recovery and high availability. You can either replicate your VM’s offsite to another VM host or copy your local jobs offsite to another repository server. A perfect solution to meet any disaster recovery requirements for your industry.

One of the coolest features her is that you can fully recover your entire server farm in the cloud and operate your business from there in case of any local disasters or theft. Once the local issues are resolved just fail back to your local farm.

Physical Servers

If you have physical servers in your environment you will need to use the Veeam agent. It is installed locally on the machine and will also do a complete image of the machine or just selected drives and data if so chosen. These backups can be used to restore individual files as well as recover an entire machine into a virtual machine or to new physical hardware if needed.

The agent can be used for either server OS or workstation OS, backing up to a Veeam repository server, network share or locally attached storage.

Even though most of us are operating with virtual servers these days, we all know that there is always a need to back up any legacy physical machines, or maybe just the workstations of some key people in the organization.

Home Data Backup Solutions

We have covered business solutions so far, and the previous paragraph is very much true for home based backup as well. The agent needs to be installed on your machine, configure it to back up to a locally attached storage device like an external USB drive or if you have a NAS at your house you can use it to back up to.

The best part here is that the agent is free to use in a nonbusiness environment.

What Have We Learned

Veeam is an excellent product to use either for home or business. We have successfully restored individual files, servers and viritualized entire farms both locally and in the cloud.

As with any backup solution, do not solely rely on the software to notify you on successful or failed jobs. To maintain your compliance’s you will need to manually check the status of the jobs every day. An additional layer of peace and security.

If you have any questions or want to share any experiences please leave a message below.

Einar

 

 

 

 

Air Printing – IPhone – IPad

Today we are going to talk about how to use the air print functionality in an enterprise environment. This can be a challenging scenario especially when using enterprise class wireless controllers, and as we know centralizing is key, but brings on challenges that makes it fun and makes us earn our pay check.

Local Controller

If your have a controller on each LAN, or better described each site since a LAN can contain multiple subnets or vLAN’s, you do have an advantage from the easy of initial configuration standpoint, but with multiple controllers, in the long run there will be more administrative overhead. In most cases you are in control of all the equipment and all segments are operated by high speed connections and will not suffer from any broadcast or multicast traffic, if properly defined that is.

In this scenario all you will need to do is to define appropriate rules on your routing capable equipment to allow multicast traffic across the subnets.

The steps on how to do this will vary depending on your specific equipment, and would be to cumbersome to go over in one little post. If your are unsure how to configure your equipment, drop a comment and we will try to help you out.

Bonjour

My iphone air print and my apple air print works perfectly in my house with no problems, but when I get to the office, I can’t print. I thought apple was easy and just works, but what happened? My IT guys just don’t know what they are doing…. Sound familiar?

Bonjour. Hello. Buenos dias. They all mean the same thing, hey I’m here, yea, right here, come on, don’t your see me? In the networking world this means a constant flood of advertisements, this can be costly when we are talking bandwidth.

In a smaller network this does not pose a big problem, there are fewer cars waiting at the stop sign. Now when the network is larger, there are more cars waiting to go, the time until it’s your turn will increase. This means that the resources your are trying to access will take more time to load, your travel time will increase. Slowness, lag however your want to describe it. More stuff trying to get through the same outlet. This is partially why subnetting and switching got invented, to only allow traffic to get flooded to a certain point.

Bonjour is utilizing what is called multicast, advertise to as many devices as possible and as often as possible. Open the flood gates.

Centralized Controller

This is definitely the way to go. Change management is easier and faster. Administrative overhead is reduced, and security is increased.

The initial configuration of the WiFi network might seem to be a bit more cumbersome, but as always this depends on what product you have selected. We all know the overpaid sales people of certain overpriced equipment.

Moving forward we will go over two specific scenarios, or two ways to configure your equipment and wireless networks in your enterprise.

Tunnel

WiFi tunnel or tunnel mode is where any wireless client are tunneled back to the controller on a different subnet or vLAN per SSID. Like mentioned above this will work just fine for the local subnet of the controller. When deploying a wireless network across a WAN, this will work as long as there are no requirements for multicast protocols like bonjour that is required for air print.

You could even make this work with multicast if configured properly, but we do not recommend this since the WAN links are slower and will be more affected by multicasting. This is if you actually do control all the equipment in the network. All routers and so on that is required for the connectivity. If the equipment is controlled by your ISP, it will take an act of a higher power to make this happen.

Any guest or visitor SSID’s should always be in tunnel mode for obvious security reasons.

Bridge

Also known as bridge mode. If configured as a bridge, the wireless client will be on the same subnet as the local LAN interface of the access point. A virtual ethernet connection if your like.

This will allow for multicast and broadcast protocols to function without compromising WAN bandwidth.

If your purchased proper equipment from a proper vendor it will allow you to push out SSID’s configured in bridge mode across your enterprise to function in the same manner (as noticed by the clients) as tunnel mode. One SSID across all your sites with the same security so it will be transparent to end users.

Conclusion

We have just covered a couple of different scenarios here, but as you can see these are probably the two major scenarios you will encounter.

There are some choices to be made upfront, both on what equipment you purchase and how you will configure it. As far as the equipment goes, we would recommend a vendor that can be configured both ways and offer the support your may or may not need to accomplish your goals.

The choice of the configuration might seem like a do-or-die scenario and must off course be based on your organizations needs. That being said, in the event that you choose a centralized configuration, hats off. With a controller that meets industry standards, it is just a matter or a few clicks and a little typing to make a change between tunnel and bridge modes.

If you have a few, a couple hundred, a thousand or a hundred thousand access points deployed with a centralized solution this particular change will not be a burden. Simply remove your SSID, reconfigure it with the same name and credentials as it was, either from tunnel to bridge or from bridge to tunnel. Push it back out to your access points, and voila. Your wireless clients will not know the difference besides if you reconfigured into bridge to make air print work for iphones and ipads, they will notice that they now can do something they could not before.

Have fun and if you have any questions or feedback, leave a comment below.

Einar

 

 

 

How to Export All Table Names and Column Names in SQL

It can perhaps sound like an odd request, but we are used to those. It can also be a useful tool for enhancing applications, SSRS report writing (Where is the data I want in the report?) and many more scenarios. Create a new stored procedure as written below.

On your SQL server execute stored procedure:

SELECT o.Name as TableName,

c.Name as ColumnName,

t.name as DataType,

t.length as [DataLength],

c.collation

FROM sysobjects o

JOIN syscolumns c

ON o.id = c.id

JOIN systypes t

ON c.xtype = t.xtype

WHERE o.xtype = ‘u’

ORDER By o.name

Copy the results into a spreadsheet.

And there you go! Short and sweet.

Einar

Active Directory Time Server

There has been a lot of questions and confusion around the Windows time service. A functional NTP service is crucial to Active Directory, and without it issues will appear.

The first step is to ensure that your ADS server VM is not set to synchronize with the virtual host. If your host OS is setting the time on your virtual server, the OS settings on the VM will not matter, the host will over ride. Once you have that squared away you can continue with the following steps to properly configure NTP in your Active Directory environment. A little side note, NTP is also known as Windows Time Service or w32time.

These steps require registry changes, always backup your registry before making any changes. We cannot be held responsible or liable if anything goes wrong, although this is a proven method and any mishaps should not be anticipated.

 

Change the server Type to NTP.

  • Click Start, click Run, type regedit.
  • Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters\Type.
  • Right click Type and click Modify.
  • In edit Value type in NTP.

Set AnnounceFlags to 5.

  • Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config\AnnounceFlags.
  • Right click AnnounceFlags and click Modify.
  • In edit DWord value type in 5.

Enable NTPServer.

  • Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer.
  • Right click Enabled and click Modify.
  • In edit DWord value type in 1.

Specify time source.

  • Locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters.
  • Right click NtpServer and click Modify.
  • In edit Value type in pool.ntp.org,0x1. (If you have a preferred public NTP server you can use it, pool.ntp.org contains a pool of servers and has yet to fail us.)

Restart services.

  • Open a command prompt.
  • Type in net stop w32time and hit enter.
  • Type in net start w32time and hit enter.

If you have any questions or comments, drop a note below.

Einar

 

About Ragnarok IT

Welcome and thank you for visiting, we hope you find the solution you are looking for here.

Our Short Story

We created this site to help people find solutions and answers to everyday IT problems and issues. We try to keep all the content and solutions true with no fluff.

 

Why We Exist

We want to help business owners and IT professionals make educated decisions based on proven standards and best practices. We know it is hard to find sound advice when it comes to IT.

 

What We Do

We are here to make IT easy for you.

If you ever need a hand or have any questions, feel free to leave them below and I will be more than happy to help you out.

All the best,

Ragnarokit.com