SQL2005Help

PowerShell for DBAs: Automating Backups and Alerts

noreply@blogger.com (ted) — Tue, 27 May 2025 09:00:00 +0000

Database Administrators (DBAs) are constantly under pressure to ensure databases are healthy, secure, and backed up regularly. PowerShell can be a game changer when it comes to automating these daily, repetitive tasks. In this article, we’ll look at how you can use PowerShell to automate SQL Server backups and set up simple alerts to monitor your environment.

Why Use PowerShell?

PowerShell is a powerful scripting language that allows DBAs to:

Interact with SQL Server via SMO (SQL Management Objects)
Automate repetitive tasks like backups and alerting
Integrate with Windows Task Scheduler
Send emails or log messages
Create dynamic scripts that work across multiple servers or databases

Automating SQL Server Backups with PowerShell

Here’s a simple script that connects to a SQL Server instance and performs a full backup for each user database:

# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

# Define SQL Server instance
$serverName = "localhost"
$backupDirectory = "C:\SQLBackups"

# Create SMO server object
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName

# Loop through each user database
foreach ($db in $server.Databases) {
    if (!$db.IsSystemObject) {
        $backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
        $backup.Action = "Database"
        $backup.Database = $db.Name
        $backup.Devices.AddDevice("$backupDirectory\$($db.Name)_$(Get-Date -Format yyyyMMddHHmmss).bak", "File")
        $backup.Initialize = $true
        $backup.SqlBackup($server)
        Write-Output "Backed up database: $($db.Name)"
    }
}

Tips:

Run this script via Task Scheduler for full automation.
Use parameters for server name, backup type, and destination.
Include error handling and logging for production scripts.

Setting Up Alerts with PowerShell

You can use PowerShell to monitor specific conditions and send email alerts. For example, the script below checks SQL Server service status and sends an email if it’s stopped:

$serviceName = "MSSQLSERVER"
$service = Get-Service -Name $serviceName

if ($service.Status -ne 'Running') {
    $subject = "ALERT: SQL Server service is not running"
    $body = "The SQL Server service '$serviceName' is currently '$($service.Status)' on $(hostname)."
    Send-MailMessage -To "dba@example.com" -From "sqlalerts@example.com" -SmtpServer "smtp.example.com" -Subject $subject -Body $body
}

Combine this with database size checks or error log monitoring.
Use dbatools PowerShell module for more advanced features.
Always test alerting scripts thoroughly before deployment.

Final Thoughts

PowerShell allows DBAs to take control of their environment through automation. Backups, health checks, and alerts are just the beginning. As your familiarity with PowerShell grows, you can automate deployments, patching, indexing strategies, and more.

Start small and build confidence. Even simple scripts can save hours each week and help prevent outages.

Do you have a favorite PowerShell script for DBA tasks? Share it in the comments or reach out on social media!

Day 1: Getting Our Hands Dirty – Setting Up Our DBA Workbench!

noreply@blogger.com (ted) — Sun, 25 May 2025 09:37:00 +0000

Alright, after yesterday's introduction to the "Data Guardian" role, it's time to equip ourselves for the journey ahead. A DBA needs their tools, just like an ethical hacker needs their Kali Linux setup!

For our adventure into database administration, we're going to set up two of the most popular and widely used database systems in the world, along with their essential management tools. Why two? Because the real world of databases is diverse, and while concepts are similar, the implementations and tooling can vary significantly.

Today, we installed:

MySQL Server & Client Tools:
- What it is: MySQL is an incredibly popular open-source relational database management system (RDBMS). It's the backbone of countless web applications (think WordPress, many e-commerce sites), and a favorite for startups due to its flexibility and cost-effectiveness.
- Why we installed it: It's versatile, widely used, and gives us a great starting point with a different flavour of SQL compared to what we'll see with Microsoft.
- The "Client": When we talk about MySQL "clients" or "client tools," these are applications that allow us to connect to and interact with the MySQL server. This could be a command-line interface or a graphical tool like MySQL Workbench.
Microsoft SQL Server (MSSQL Server) & SQL Server Management Studio (SSMS):
- What it is: Microsoft SQL Server is a powerful, enterprise-grade RDBMS from Microsoft. It's heavily used in corporate environments, especially in Windows-centric infrastructures, for everything from critical business applications to data warehousing.
- Why we installed it: It represents a huge segment of the corporate database world. Learning MSSQL is a critical skill for any aspiring DBA.
- SQL Server Management Studio (SSMS): This is the indispensable graphical user interface (GUI) tool that Microsoft provides for managing SQL Server. Think of it as a DBA's mission control center. From SSMS, we can write queries, design databases, manage users, monitor performance, and much, much more. You simply can't be an effective MSSQL DBA without it.
Toad for SQL Server (or similar multi-database tool):
- What it is: Toad (Tools for Oracle Application Developers, though it supports many databases now) is a third-party database management tool. While SSMS is specific to SQL Server, Toad offers a unified interface for working with various database systems, including SQL Server, Oracle, MySQL, and others.
- Why we installed it: Many DBAs in mixed environments use tools like Toad (or DBeaver, DataGrip, etc.) because they offer a consistent experience across different database platforms. It shows us that there are specialized tools beyond the vendor's own. It often has advanced features for development, administration, and analysis.

Why is setting up these tools so important for a DBA?

These aren't just programs; they are our "workbench." They allow us to:

Create and manage databases: Build tables, define relationships, and structure our data.
Write and execute SQL queries: The language we use to interact with data.
Monitor performance: See how our databases are running and identify bottlenecks.
Implement security: Grant and revoke user permissions.
Perform backups and restores: Our ultimate safety net!

For Kai, getting comfortable with these environments is crucial. As an ethical hacker, understanding the various interfaces DBAs use (and sometimes, abuse!) can reveal different attack vectors or insights into how a system is managed. Knowing how to use these tools even on the "defensive" side will make him a more well-rounded cybersecurity professional.

Kai's Challenge for Today: Open up both SSMS and your MySQL client (like MySQL Workbench). Just connect to your local server instance. Take a look around each interface. What are some immediate differences you notice in their layout or options? Don't worry about understanding everything yet, just observe!

🔥 From Bug Bounty Hunter to Data Guardian: Kai's Next Level Up! 🔥

noreply@blogger.com (ted) — Sun, 25 May 2025 09:21:00 +0000

Hey everyone! For those of you following along, you know my son, Kai, has been diving deep into the world of ethical hacking. We just wrapped up his "27-Point Challenge" – an intensive crash course designed to get him hands-on experience with real-world vulnerabilities and even sign up for HackerOne. I'm incredibly proud of his dedication and the insights he's already gained!

We're definitely going to keep that ethical hacking journey going, because the best way to secure something is to truly understand how it can be broken.

But now, it's time for Kai to look behind the curtain.

While finding vulnerabilities is exciting, the true power lies in understanding the systems you're trying to penetrate (or protect!). And at the heart of almost every major application, website, and digital service, lies something critical: Data.

That's why our next adventure takes us into the core of infrastructure and the pivotal role of a Database Administrator (DBA).

Over the coming days, I'll be sharing daily insights into what DBAs do – the guardians of data, the performance wizards, the recovery experts, and the security gatekeepers. We'll explore:

How databases are built and kept running smoothly.
The magic behind making queries lightning-fast.
Why backups are a DBA's absolute superpower.
And, most importantly for Kai's journey, how DBAs protect data from the very threats ethical hackers are looking for (including things like SQL Injection, which we'll definitely revisit!).

This isn't just about learning SQL commands; it's about understanding the entire ecosystem of data management. For Kai, this will be invaluable. The more he understands how systems are designed and secured by DBAs, the sharper his ethical hacking skills will become. He'll learn to think like a defender, which makes him an even more effective "attacker" (for good, of course!).

So, get ready to explore the fascinating world where data lives, breathes, and needs constant care. Join us daily as we peel back the layers of database administration!

Stay tuned for Day 1, coming right up!

📊 How to Spot When a Table’s Statistics Need Updating

noreply@blogger.com (ted) — Sat, 24 May 2025 19:22:00 +0000

In the world of database performance tuning, statistics play a crucial role. They help the query optimizer make smart decisions about how to access data. But when those statistics become outdated, your queries can suffer dramatically in performance.

So how do you know when stats are stale? In this post, we’ll explore how to detect outdated statistics and what you can do about it.

🧠 What Are Table Statistics?

Statistics describe the distribution of data in your tables and indexes—things like:

Number of rows
Data distribution (e.g., how many NULLs, distinct values)
Column histograms

The query optimizer uses this data to estimate row counts and choose the most efficient query plan.

⚠️ Signs That Table Statistics Need Updating

Here are some red flags that suggest your stats might be stale:

1. Sudden Query Performance Degradation

A query that used to be fast suddenly becomes slow. Often, this means the execution plan has changed due to outdated stats.

2. Table Has Undergone Massive Changes

If you've added or deleted a large number of rows, the statistics may no longer represent the data.

3. Estimated Rows ≠ Actual Rows

Use the execution plan to compare the Estimated Number of Rows vs the Actual Number of Rows. A big mismatch is a clear sign the statistics are outdated.

4. Execution Plan Uses Table Scan Instead of Index Seek

If your query starts using a table scan instead of a more efficient index seek, it's worth checking the stats.

5. High CPU or IO on Simple Queries

If normally light queries start using more CPU or IO, it could be due to bad estimates caused by stale statistics.

🔎 How to Check for Stale Statistics

👉 SQL Server

Use this query to check when statistics were last updated:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    STATS_DATE(i.object_id, i.index_id) AS StatsLastUpdated
FROM
    sys.indexes i
JOIN
    sys.tables t ON t.object_id = i.object_id
JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.is_ms_shipped = 0;

Also, track how many modifications a table has had:

SELECT
    OBJECT_NAME(object_id) AS TableName,
    modification_counter,
    [rows]
FROM
    sys.dm_db_stats_properties (OBJECT_ID('YourTableName'), 1);

👉 MySQL

Use the ANALYZE TABLE command to check/update stats. MySQL doesn't expose as much info, but running:

SHOW TABLE STATUS LIKE 'your_table';

can give you row count estimates.

👉 PostgreSQL

Use pg_stat_user_tables to find when tables were last vacuumed or analyzed:

SELECT
    relname,
    last_analyze,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM
    pg_stat_user_tables
WHERE
    schemaname = 'public';

🛠 How to Update Statistics

SQL Server:

-- Update stats for one table
UPDATE STATISTICS your_table;

-- Update all stats
EXEC sp_updatestats;
MySQL:
ANALYZE TABLE your_table;

PostgreSQL:

ANALYZE your_table;

✅ Best Practices

Enable Auto Update Statistics (on by default in SQL Server).
Schedule manual updates during off-peak hours if you expect large data loads.
Use incremental statistics for large partitioned tables.
Monitor and trend stats update dates as part of your health checks.

📌 Summary

Outdated statistics are a silent performance killer. By monitoring stats health and updating them regularly, you ensure the optimizer makes smart decisions, keeping your queries fast and efficient.

Stay tuned for our next post on index fragmentation vs stale stats – which is worse?

🧠 SQL Query Performance and Tuning: A Practical Guide

noreply@blogger.com (ted) — Sat, 24 May 2025 19:20:00 +0000

In today's data-driven world, efficient database queries are critical for application speed, user satisfaction, and overall system performance. Whether you're a developer, DBA, or a data enthusiast, understanding how to tune SQL queries can make a significant difference.

In this post, we’ll explore what impacts SQL performance, common pitfalls, and how to optimize your queries for better performance.

🔍 Why Query Performance Matters

Slow SQL queries can lead to:

Delays in application response time
Increased load on servers
Poor user experience
Costly cloud resource consumption

Tuning queries not only saves time and resources, but also improves scalability and reliability.

🚧 Common Causes of Poor SQL Performance

Here are some typical reasons queries run slowly:

Missing Indexes – Without indexes, the database may perform full table scans.
Poorly Written Queries – Using SELECT *, unnecessary joins, or subqueries can be problematic.
Outdated Statistics – SQL optimizers rely on table statistics for decision-making.
Blocking and Locking – Long-running transactions may block others.
Large Result Sets – Retrieving more data than needed consumes time and bandwidth.
Improper Joins – Cartesian joins or joining on non-indexed columns can be costly.

🧰 SQL Query Tuning Tips

Here are actionable strategies to improve your SQL performance:

1. Use Indexes Wisely

Ensure key columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses are indexed.

-- Create an index on the 'last_login' column
CREATE INDEX idx_last_login ON users(last_login);

2. Avoid SELECT *

Fetch only the columns you need.

-- Bad
SELECT * FROM orders;

-- Good
SELECT order_id, customer_name, order_date FROM orders;

3. Use Joins Efficiently

Avoid unnecessary joins and ensure join conditions are correct and indexed.

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

4. Use Query Execution Plans

Execution plans show how SQL Server/MySQL/PostgreSQL will execute your query. Use them to find table scans, missing indexes, or expensive operations.

5. Avoid Functions in WHERE Clauses

Using functions on indexed columns disables index use.

-- Avoid this
SELECT * FROM users WHERE YEAR(birth_date) = 1990;

-- Do this
SELECT * FROM users WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';

6. Use LIMIT or TOP for Large Tables

Fetching only what you need saves time and resources.

-- MySQL / PostgreSQL
SELECT * FROM logs LIMIT 100;

-- SQL Server
SELECT TOP 100 * FROM logs;

🔄 Updating Statistics and Rebuilding Indexes

In SQL Server, you can run:

-- Update statistics
UPDATE STATISTICS table_name;

-- Rebuild index
ALTER INDEX ALL ON table_name REBUILD;

ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

🧪 Tools for Performance Tuning

SQL Server Management Studio (SSMS) – Query Analyzer & Execution Plan
MySQL Workbench – Visual Explain Plans
PostgreSQL EXPLAIN ANALYZE
SolarWinds DPA, Redgate SQL Toolbelt, New Relic, pgBadger, etc.

🏁 Final Thoughts

SQL tuning is a mix of art and science. The more you understand your data, workload, and indexes, the better your queries will perform. Always test changes in a safe environment before applying them in production.

What are your favorite SQL tuning tricks? Share them in the comments!

noreply@blogger.com (ted) — Sat, 24 May 2025 19:17:00 +0000

USE [test]

GO/****** Object: Table [dbo].[companylist] Script Date: 08/04/2012 18:51:47 ******/SET ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
SET
ANSI_PADDING OFFGO
CREATE
TABLE [dbo].[companylist]([symbolid] [int] NOT NULL,[ticker] [nvarchar](305) NULL,[exchange] [nvarchar](255) NULL,[coname] [nvarchar](255) NULL,[target] [nvarchar](255) NULL,[PCoName] [varchar](50) NULL,[ErrorInd] [varchar](50) NULL,[Market Cap] [decimal](18, 6) NULL,[bid] [decimal](18, 6) NULL,[ask] [decimal](18, 6) NULL,[open] [decimal](18, 6) NULL,[high] [decimal](18, 6) NULL,[low] [decimal](18, 6) NULL,[ch] [varchar](50) NULL,[52 week Hi] [decimal](18, 6) NULL,[52 week Lo] [decimal](18, 6) NULL,[%ch50dmav] [varchar](50) NULL,[%ch200dmav] [varchar](50) NULL,[volume] [decimal](18, 6) NULL,[AvDvol] [decimal](18, 6) NULL,[shortratio] [decimal](18, 6) NULL,[DivShare] [decimal](18, 6) NULL,[DivYield] [decimal](18, 6) NULL,[ExDivDate] [varchar](50) NULL,[DivPayDate] [varchar](50) NULL,[BookV] [decimal](18, 6) NULL,[Psales] [decimal](18, 6) NULL,[Pbook] [decimal](18, 6) NULL,[PEratio] [decimal](18, 6) NULL,[Cash] [decimal](18, 6) NULL,[Debt] [decimal](18, 6) NULL,[EV] [decimal](18, 6) NULL,CONSTRAINT [companylist1] PRIMARY KEY CLUSTERED ([symbolid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
SET ANSI_PADDING OFFGO

stocks

noreply@blogger.com (ted) — Mon, 06 Mar 2017 22:58:00 +0000

creating a stocks and shares database I will be looking to build a stocks database and asp frontend

USE [stock]GO/****** Object: Table [dbo].[techdbo$] Script Date: 07/22/2012 09:29:03 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON TABLE [dbo].[techdbo$]([SymbolID] [float] NULL,[Ticker] [nvarchar](255) NULL,[Exchange] [nvarchar](255) NULL,[ComGroup] [nvarchar](255) NULL,[Continent] [nvarchar](255) NULL,[Stage] [nvarchar](255) NULL,[MI] [float] NULL,[Inf] [float] NULL,[PP] [float] NULL,[Production] [float] NULL,[Cash Cost] [float]
)NULL ON [PRIMARY]GOUSE [stock]GO/****** Object: Table [dbo].[symboldbo$] Script Date: 07/22/2012 09:28:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON TABLE [dbo].[symboldbo$]([SymbolID] [float] NULL,[Ticker] [nvarchar](255) NULL,[Exchange] [nvarchar](255) NULL,[CoName] [nvarchar]
)(255) NULL ON [PRIMARY]GOUSE [stock]GOUSE [stock]GO/****** Object: Table [dbo].[Exchange] Script Date: 07/22/2012 09:27:36 ******/USE [stock]GO/****** Object: Table [dbo].[Symbol] Script Date: 07/22/2012 09:28:23 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Symbol]([SymbolID] [int] NULL,[Ticker] [varchar](50) NULL,[Exchange] [varchar](50) NULL,[CoName] [varchar](max) NULL,[Target] [varchar](50) NULL,[PConame] [varchar](50) NULL,[ErrorInd] [nchar](10) NULL,[MarketCap] [decimal](18, 0) NULL,[Bid] [decimal](18, 0) NULL,[Ask] [decimal](18, 0) NULL,[Open] [decimal](18, 0) NULL,[High] [decimal](18, 0) NULL,[Low] [decimal](18, 0) NULL,[Ch] [nvarchar](50) NULL,[52high] [decimal](18, 0) NULL,[52low] [decimal](18, 0) NULL,[ch50dmav] [varchar](50) NULL,[ch200dmav] [varchar](50) NULL,[vol] [decimal](18, 0) NULL,[AvDvol] [decimal](18, 0) NULL,[shortratio] [decimal](18, 0) NULL,[DivShare] [decimal](18, 0) NULL,[DivYield] [decimal](18, 0) NULL,[Exdivdate] [date] NULL,[DivPaydate] [date] NULL,[BookV] [decimal](18, 0) NULL,[PSales] [decimal](18, 0) NULL,[PBook] [decimal](18, 0) NULL,[PEration] [decimal](18, 0) NULL,[Cash] [decimal](18, 0) NULL,[Debt] [decimal](18, 0) NULL,[EV] [decimal]
)(18, 0) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFFUSE [stock]GO/****** Object: Table [dbo].[Stage] Script Date: 07/22/2012 09:28:07 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Stage]([StageID] [int] NULL,[Stage] [varchar]
)(50) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFF[stock]GO/****** Object: Table [dbo].[Provider] Script Date: 07/22/2012 09:27:51 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Provider]([ProviderID] [int] NULL,[Provider] [varchar]
)(50) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFFSET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Exchange]([ExchangeID] [int] NOT NULL,[Exchange] [varchar](50) NOT NULL,[ProviderD] [int] NULL,[Yahoo] [varchar](50) NULL,[Google] [varchar](50) NULL,[Reuters] [varchar](50) NULL,[CurrencyID] [int]
)NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFF/****** Object: Table [dbo].[Currency] Script Date: 07/22/2012 09:27:18 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Currency]([CurrencyID] [int] NULL,[Currency] [varchar](50) NULL,[ProviderID] [int] NULL,[Yahoo] [varchar](50) NULL,[Google] [varchar](50) NULL,[Reuters] [varchar]
)(50) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFFUSE [stock]GO/****** Object: Table [dbo].[continent] Script Date: 07/22/2012 09:26:54 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[continent]([continentID] [int] NULL,[Continent] [varchar]
)(50) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFFUSE [stock]GO/****** Object: Table [dbo].[comgroup] Script Date: 07/22/2012 09:26:30 ******/SET
GO
SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[comgroup]([comgroupID] [int] NULL,[ComGroup] [varchar]
)(50) NULL ON [PRIMARY]GO
SET
GOANSI_PADDING OFFinsert into dbo.Symbol values ('1, [Exxon Mobil Corpo], [N/A], 67.03, [N/A], [N/A], 86.02, 86.38, 85.62,0.00,87.94,67.03,'+4.17%','+1.95%', 14902213,18142400,1.60,1.98,2.30, '2012-07-20', '2012-07-20',33.577,0.91,2.57,10.41')select @@GETDATE())declare
set @date datetime @date = getdate()select @date
insert into Currency values ( 1,'AUD',1,'AUDUSD=X','CURRENCY:AUD','AUD/USD')insert into Currency values ( 2,'USD',1,'USDUSD=X', 'CURRENCY:USD','USD/USD')insert into Currency values ( 3,'CAD',1,'CADUSD=X','CURRENCY:CAD','CAD/USD')insert into Currency values ( 4,'GBP',1,'GBPUSD=X','CURRENCY:GBP','GBP/USD')insert into Currency values ( 5,'EUR',1,'EURUSD=X','CURRENCY:EUR','EUR/USD')insert into Currency values ( 6,'HKD',1,'HKDUSD=X','CURRENCY:HKD','HKD/USD')insert into Currency values ( 7,'CNY',1,'CNYUSD=X','CURRENCY:CNY','CNY/USD')insert into Currency values ( 8,'BRL',1,'BRLUSD=X','CURRENCY:BRL','BRL/USD')insert into Currency values ( 9,'ZAR',1,'ZARUSD=X','CURRENCY:ZAR','ZAR/USD')insert into Currency values ( 10,'SGD',1,'SGDUSD=X','CURRENCY:SGD','SGD/USD')go
insert into Provider values (1,'Yahoo')insert into Provider values (2,'Google')insert into Provider values (3,'Reuters')insert into Provider values (4,'Bloomberg')go
insert into Exchange values (1, 'ASX',1,'.AX','ASX:','.AX', 1)insert into Exchange values (2, 'NYSE', 1, '','NYSE:','.N', 2)insert into Exchange values (3,'OTC',1, '' ,'PINK:','.PK',2)insert into Exchange values (4,'NASDAQ', 1, '' ,'NASDAQ:','.O',2)insert into Exchange values (5,'TSX', 1, '.TO', 'TSE:','.TO',3)insert into Exchange values (6,'TSXVCDNX', 1, '.V', 'CVE:','.V',3)insert into Exchange values (7,'LSE', 1, '.L', 'LON:','.L',4)insert into Exchange values (8,'AIM', 1, '.L', 'LON:','.L',4)insert into Exchange values (9,'HKSE', 1, '.HK','HKG:','.HK',6)insert into Exchange values (10,'Bovespa', '','NS','NS','.SA',8)insert into Exchange values (11,'JSE', '','NS','NS','.J',9)insert into Exchange values (12,'SGX', 1,'.SI','SGX:','.SI',10)go
insert into stage values (1,'Production')insert into stage values (2,'Development')insert into stage values (3,'Feasibility')insertinto stage values (4,'Exploration')insert into comgroup values (1,'DivMaj')insert into comgroup values (2,'DivMin')insert into comgroup values (3,'CopperMaj')insert into comgroup values (4,'CopperMin')insert into comgroup values (5,'GoldMaj')insert into comgroup values (6,'GoldMin')insert into comgroup values (7,'Silver')insert into comgroup values (8,'IronOre')insert into comgroup values (9,'Steel')insert into comgroup values (10,'Royalty')insert into comgroup values (11,'Diamonds')insert into comgroup values (12,'CoalCok')insert into comgroup values (13,'CoalTherm')insert into comgroup values (14,'Uranium')insert into comgroup values (15,'Potash')insert into comgroup values (16,'Nickel')insert into comgroup values (17,'Zinc')insert into comgroup values (18,'Oil')insert into comgroup values (19,'Gas')insert into comgroup values (20,'Test')go
insert into continent values (1,'Africa')insert into continent values (2,'FSU')insert into continent values (3,'NAmerica')insert into continent values (4,'SAmerica')insert into continent values (5,'Asia')insert into continent values(6,'Aus')insert into continent values(7,'Europe')insertinto continent values(8,'Global')

Should a DBA have SA rights all the time?

noreply@blogger.com (ted) — Tue, 15 Jan 2013 00:48:00 +0000

I was having a look around and I have not seen many questions or Answers on this topic.

I did come across a post from Ted Krueger and also Brian Kelley on Should DBAs have local admin rights, this question above is a little different but I guess should cover both Local Admin and SA rights to the SQL server.

I have worked as a SQL Server DBA for many years working for the UK government to Banking. In all these years I have seen so many changes to the DBA job, when I first started out I was a Network Engineer, DBA and SA Slowly this has changed to being a glorified F5 pusher.

Should a DBA have SA rights all the time?

DBAs would almost defiantly argue yes as it makes their jobs easier. It defiantly does, no the other hand users, security risk and other admins may argue that it will make there jobs harder to monitor this.

As a DBA I am now in the thought on how we can manage risk and I think there is no right or wrong answer to the above question but now I am leaning towards the Risk and Security aspects of a DBA and the systems they could be in charge of, If you work in banking should you have full control of a database which may hold thousands of user account numbers and balances. You may also argue that the finance crisis of late was never caused by a DBA stealing the money it was the traders. but yet again would you want the blame, I once did a migration as a DBA in a large intuition that involved 100 billion pounds being shipped to Hong Kong from London, the money went missing from he database and ended up in another bank who was the first person they came to me. Not the person who sent the money to the wrong location they asked do you have a large amount in your bank account, I said now but do you really think I would be here if I did sarcastily as they never even knew the money was not in there account for 3 days. I did manage to track down where the money had gone through the database and they found the right person.

Need less to say taking away a DBAs rights and then asking them to elevate on a task will never stop the above scenario happening. But it can be controlled, through an elevation procedure and audit scripts that audit what everyone does.

This would only really work in a Medium to Large company you could never impose such large restrictions on a small company usually were the DBA is the SA and so on.

How can we monitor what users are doing?

Audit scripts, DML, DDL audits

Check out the post here for table audit scripts, I will post one for database audit and changes a little later.

audit storeproc

I will work on a script for elevating a user to SA this way you only need to rights to do your day to day jobs, IE backup check jobs failed, run sp_who2 active and so on enough to diag performance issues and so on without seeing customer data.

on to local admin rights I also do not think a DBA should have these, if a drive feels up then the windows admin should ask the user or DBA which files to delete, I think logging on to a server could be a bad thing, I once knew a DBA who logged into a server and restarted it and also deleted files that he should not have done as they were on the same drive as the MDF, and the database was full. This is better for the apps team to do or the windows admin.

However, again one may argue that this makes the DBAs job easier and that the DBA should be trained, however, I go for the segregation of duty aspects and let others do a job they are meant to do and leave us to do our job.

To conclude on what I am saying here, is that we should not lock everyone down; it should just depend on your organization. It should depend on the data in question. It should depend on the controls you have in place. I will also try to add some above that you may like to implement, IE audit and elevation.

Even if you’re a small company it is good to add audits I once worked in a small company where someone run out of space and did not know that the master, msdb and so on were for starting MSSQL and deleted them. Needless to say the server was down a while having an audit would have shown who did this obviously the person who did it deninded it and it was hard to prove.

You should always look at every situation in these cases too and choose the best practices you should also look at what type of DBA you have dev or prod, dev DBAs may not be able to access prod servers you may need some sort of application support that works with a DBA on this you may not its up to the ORG HR and so on.

You may have HR Data and intellectual property that you don’t want people to see and it makes sense to lock these down to the DBA. Nevertheless you may come to the conclusion that the DBA needs these rights anyway, that would be fine if its what your org needs and there should be nothing wrong I would just suggest to look at the question and consider it.

Thanks for your time please check back in a few days for audit scripts.

audit storeproc

noreply@blogger.com (ted) — Thu, 12 Jul 2012 08:21:00 +0000

Here is a good storeproc to audit your database tables for changes

first create table for your audit list

****** Object: Table [dbo].[Auditlist] Script Date: 07/12/2012 15:10:35 ******/ ANSI_NULLS ON QUOTED_IDENTIFIER ON ANSI_PADDING ON TABLE [dbo].[Auditlist]([sz30tablename] [varchar]
)(128) NOT NULL ON [PRIMARY]GO
SET
GO
ANSI_PADDING OFF

then insert into this the tables you want to audit

insert into auditlist values ('tablename')

then create the proc

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_addaudittable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_addaudittable]
GO
USE [databasename]
GO
/****** Object: StoredProcedure [dbo].[sp_addaudittable]    Script Date: 07/12/2012 14:46:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_addaudittable] @tablename varchar(40)
as
declare @databasename varchar(30)
declare @status  smallint
declare @statement varchar(8000)
declare @id  int
declare @audittable char(50)
declare @identity tinyint
set nocount on
if not exists(select name from sysobjects where name = 'Auditlist' and type = 'U')
begin
  print '--Auditlist table does not exist'
  return 1
end
if not exists(select name from sysobjects where name = @tablename and type = 'U' and uid = 1)
begin
  print '--Source table does not exist'
  return 1
end
if not exists(select sz30tablename from dbo.auditlist where sz30tablename = @tablename)
  insert into dbo.auditlist (sz30tablename) values (@tablename)
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid = 1)
begin
  print '--Audit table already exists - please drop first'
  return 1
end
if exists(select name from sysobjects where name like @tablename + '_a' and type = 'U' and uid <> 1)
begin
  print '--Invalid audit table. Not dbo.'
  return 1
end
SELECT @databasename = db_name(),@audittable = @tablename+'_a'
SELECT @status = (status & 4)
FROM master..sysdatabases
WHERE name = db_name()
if @status = 0
begin
  exec sp_dboption @databasename, 'select into/bulkcopy',TRUE
end
if exists(
SELECT *
FROM syscolumns
WHERE (status & 128) = 128 and
  object_name(id) = @tablename )
  SET @identity = 1
else
  SET @identity = 0
if @identity = 0
  SET @statement = 'select * into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' where 1>2'
else
  SET @statement = 'select t1.* into ' + 'dbo.' + @tablename + '_a from ' + @tablename + ' t1 join ' + @tablename + ' t2 on t1.identitycol = t2.identitycol where 1>2'
execute (@statement)
SELECT @statement = 'alter table dbo.'+ @tablename + '_a ' + 'add sz30auditusername varchar(30) null, sz1auditType char(1) null, sz30audithostname char(30) null, dtaudittimestamp datetime null'
execute (@statement)
-- Now we need to get rid of the timestamp column
declare @TimestampColname varchar(100)

SELECT @TimestampColname = syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name like 'Timestamp' and id = object_id(@tablename)

if @@rowcount > 0
begin
  SET @statement = 'alter table dbo.' + @tablename + '_a drop column ' + @TimestampColname
  exec(@statement)
end
-- Now get a list of columns
declare @cols table (colid int, colname varchar(100))
INSERT
INTO @cols
SELECT colid,
  syscolumns.name
FROM syscolumns
JOIN systypes on syscolumns.xusertype = systypes.xusertype
WHERE systypes.name not like 'timestamp' and id = object_id(@tablename)
declare @colname varchar(100)
declare @colnames varchar(8000)
declare @first tinyint
SET @colnames = ''
SET @first = 1
while (1>0)
begin
  SELECT @colname = colname
  FROM @cols
  WHERE colid = (SELECT min(colid)
    FROM @cols)
  if @@rowcount = 0
  begin
   break
  end
  else
  begin
   if @first = 1
   begin
    SET @first = 0
   end
   else
   begin
    SET @colnames = @colnames + ','
   end
  end
  SET @colnames = @colnames + '[' + @colname+']'
  DELETE
  FROM @cols
  WHERE colname = @colname
end
declare @addcols varchar(1000)
SET @addcols = ',[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]'
select @id = deltrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
  print '--Warning this table has no delete trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
  print @statement
  print 'go'
end
else
begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s delete trigger does not access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s delete trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Delete on ' + @tablename + ' for delete as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''D'',host_name(),getdate() from deleted'
  print @statement
  print 'go'
end
select @id = Updtrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
  print '--Warning this table has no update trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
end
else
begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s update trigger does access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s update trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Update on ' + @tablename + ' for update as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''U'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
end
select @id = Instrig from sysobjects where name = @tablename and type = 'U'
if @id = 0
begin
  print '--Warning this table has no insert trigger - Printing please run'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
end
else
begin
  if not exists(select * from sysdepends where depid = object_id('auditlist') and id = @id)
   print '--Warning this table''s insert trigger does access audit log'
  if not exists(select * from sysdepends where depid = object_id(@audittable) and id = @id)
   print '--Warning this table''s insert trigger does not insert into audit log'
  SELECT @statement = 'Create trigger tr' + @tablename + 'Insert on ' + @tablename + ' for insert as if exists(select sz30tablename from auditlist where sz30tablename=''' + @tablename + ''') insert into ' + @tablename + '_a (' + @colnames+ @addcols + ') select ' + @colnames +',user_name(),''I'',host_name(),getdate() from inserted'
  print @statement
  print 'go'
end
if @status = 0
begin
  exec sp_dboption @databasename, 'select into/bulkcopy',FALSE
end

GO

then run proc

exec sp_addaudittable 'tablename'

This will create a table tablename_a

it will then print the code to create the triggers, copy this out and run it into the database you now have the triggers for audit.

SET
GO
SET
GO
SET
GO
CREATE

how to create an audit trigger for sql agent jobs

noreply@blogger.com (ted) — Thu, 19 Apr 2012 01:06:00 +0000

Tired of jobs being changed without your knowledge, here is a sample of a audit of the sysjobs view in MSSQL 2008 this will audit update, delete and insert and tell you the time and the hostname and user who changed the table. You can add more audits too.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sysjobs_a](
[job_id] [uniqueidentifier] NOT NULL,
[originating_server_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[start_step_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[owner_sid] [varbinary](85) NOT NULL,
[notify_level_eventlog] [int] NOT NULL,
[notify_level_email] [int] NOT NULL,
[notify_level_netsend] [int] NOT NULL,
[notify_level_page] [int] NOT NULL,
[notify_email_operator_id] [int] NOT NULL,
[notify_netsend_operator_id] [int] NOT NULL,
[notify_page_operator_id] [int] NOT NULL,
[delete_level] [int] NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[version_number] [int] NOT NULL,
[sz30auditusername] [varchar](30) NULL,
[sz1auditType] [char](1) NULL,
[sz30audithostname] [char](30) NULL,
[dtaudittimestamp] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Auditlist](
[sz30tablename] [varchar](128) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into auditlist values ('sysjobs')
go

Create trigger trsysjobsDelete on sysjobs for delete as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from deleted
go
--Warning this table has no update trigger - Printing please run
Create trigger trsysjobsUpdate on sysjobs for update as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from inserted
go
--Warning this table has no insert trigger - Printing please run
Create trigger trsysjobsInsert on sysjobs for insert as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from inserted
go

c# totals

noreply@blogger.com (ted) — Fri, 16 Mar 2012 13:33:00 +0000

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private int value1 = 0;

private int value2 = 0;

private int value3 = 0;

private int value4 = 0;

private void textBox1_TextChanged(object sender, EventArgs e)

{

int i = 0;

if(int.TryParse(textBox1.Text, out i))

{

value1 = i;

DoIt();

}

else

{

textBox1.Text = value1.ToString();

}

}

private void textBox2_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox2.Text, out i))

{

value2 = i;

DoIt();

}

else

{

textBox2.Text = value2.ToString();

}

}

private void textBox3_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox3.Text, out i))

{

value3 = i;

DoIt();

}

else

{

textBox3.Text = value3.ToString();

}

}

private void textBox4_TextChanged(object sender, EventArgs e)

{

int i = 0;

if (int.TryParse(textBox4.Text, out i))

{

value4 = i;

DoIt();

}

else

{

textBox4.Text = value4.ToString();

}

}

private void DoIt()

{

textBox5.Text = (value1 + value2 + value3 + value4).ToString();

}

}

}

exporting sql statement to excel in c#

noreply@blogger.com (ted) — Thu, 01 Mar 2012 02:10:00 +0000

a sample of an export from c# using a sql statement to excel

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

namespace WindowsFormsApplication6
{
public partial class excelout : Form
{
public excelout()
{
InitializeComponent();
}

private void excelout_Load(object sender, EventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
string data = null;
int i = 0;
int j = 0;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "data source=192.168.20.3;initial catalog=hct;user id=ted;password=ted;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT * FROM Product1";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}

xlWorkBook.SaveAs("C:\\ted.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file c:\\ted.xls");

}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}

}

}
}

Table ' does not have the identity property. Cannot perform SET operation.

noreply@blogger.com (ted) — Thu, 23 Dec 2010 05:30:00 +0000

Error Message:

System.Data.SqlClient.SqlException: Cannot find the object "table" because it does not exist or you do not have permissions.

try again
System.Data.SqlClient.SqlException: Table table does not have the identity property. Cannot perform SET operation.
at table.DatabaseIO.UpdateKey(Key key, KeyGroup keyGroup)
at table.Interface.AddKey.btnSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
Description:
This error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column, for which the IDENTITY property was declared.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and are corrigible by the user. The SET IDENTITY_INSERT setting cannot be used on such a table.

Versions:
All versions of SQL Server.

Example(s):
USE test
GO

CREATE TABLE test2
(
test1 int)
GO
SET IDENTITY_INSERT t ON
INSERT INTO test1 SELECT 1
SET IDENTITY_INSERT test1 OFF
DROP TABLE test1
GO

Remarks:
In the above example we try to turn on the IDENTITY_INSERT setting für the table test1 to insert an explicite value into a column for which the IDENTITY property was declared. Because there no such column in table test1, the error is raised.

How to enable access to sql proflier without sysadmin

noreply@blogger.com (ted) — Thu, 23 Dec 2010 05:27:00 +0000

If a member of your team wants to have trace rights to diag a issue you can grant alter trace.

grant Alter trace to username
go

to stop this right

deny alter trace to username
go

Setting up transactional replication without a snapshot

noreply@blogger.com (ted) — Sat, 04 Dec 2010 13:05:00 +0000

We had a case where a client needed replication from London to Hong Kong and New York, needless to say we setup the transaction replication we a snapshot and due to the latency issues the snapshot never worked. I decedied to set up replication without the snapshotm heres how to do it if you need to know.

Normally its very easy to setup the publisher and subscriber by default. Thanks to the handy Wizard interface. However, we faced a tiny bit of a problem when using the wizard.

If you are replicating on the same domain and in the same country using the wizzard is great but this is probably only a small about of situations.

I had a problem with the initial snapshot creation as said above when setting up the transactional replication. This is using SQL Server 2008. The initial snapshot creation took a very long time and would not complete. Even after running it over a weekend, for 48 hours, it did not complete. The thing is, while the snapshot agent was running, the database was not accessible to other users. Is there a way to make the snapshot run under low priority?

Since I had to allow users access to the database, I had to stop the snapshot job. Will it start from scratch if restarted or will it continue from where it stopped? I tested and it seemed to start from scratch once again. Another 8+ hours? I don’t think so. Thus, I set out to find a way to create a transactional replication without a snapshot.

I did a little bit of checking around to find out how to do this as we are all DBAs but a DBA does not know everything hence why you are also reading this site. I found a stored procedure called sp_addsubscription(), this will allow one to initialize a subscription without the need to create a snapshot of the publishing database. sp_addsubscription()you will be able to run and create replication with a backup with this and without the snapshot ment not many hours of waiting. I said to myself. So I did. I did the first test in dev and then in prod after it worked,

Here is how to do it
Following are the steps to create a SQL Server 2005 Transactional Replication without an initial snapshot.

1. Create a new publication using the New Publication Wizard. you are able to still do this by using the wizard to create the publication. Just apply the right information in the fields and settings as required according to your environment. However, when you reach the SnapShot Agent dialog, you will need to leave all the checkboxes unticked. The reason being is that you dont want a snapshot remember this? That’s the only thing to note while going through the New Publication Wizard dialogs.

2. Upon completion of the New Publication Wizard, open the property dialog to your publication. You now need to modify a property setting on the publication. You need to set the publication to allow initialization from backup files. Yeah, that’s all there is to it. Just set this setting to True. It really would have been even more dandier if this could be performed within the New Publication Wizard though.

3. you will now need to Perform a full backup of your database. Backup the database to a directory or drive accessible by the SQL Server, such as e:\ted\ted.bak in my case.

4. Restore the backup into the subscriber sql server instance. Don’t know
how to restore from a SQL backup? Google it up. Easy way to restore is from the managemt studio.

5. Run the following stored procedure in the SQL Server Management Studio Query Window on your publisher server.

sp_addsubscription
@publication ='YourPublicationName', --ie tedpub
@subscriber='SubscriberServerDBInstance', --servername in my case tedserver02@destination_db='SubscriberDatabase', -- teddb
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = d:\ted.bak'
go

You will need to disable or change the job of the cleanup job to run as SA, I would just disable this job before you run the above command as you may encounter an error.

you will now see a new job in the sql agent just go to the job and right click go to properties and then set the job to run as sa.

round up
backup publication database
copy backup to subscriber server
restoring subscriber database
The transactional replication is now happily running between the two servers. Of course, the above process creates only a basic full database transactional replication. If your requirements are a litte bit more complicated, you might want to take a look at the options you can configure in the sp_addsubscription stored procedure.

checking sql2005 replication

noreply@blogger.com (ted) — Tue, 29 Jun 2010 03:29:00 +0000

select la.name,la.publisher_db,
case lh.runstatus
when 1 then 'Start'
when 2 then 'Succeed'
when 3 then 'In progress'
when 4 then 'Idle'
when 5 then 'Retry'
when 6 then 'Fail'
else 'Unknown'
end as runstatus
, lh.time, lh.comments
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
inner join (
select lh.agent_id, max(lh.time) as LastTime
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
group by lh.agent_id) r
on r.agent_id = lh.agent_id
and r.LastTime = lh.time
where lh.runstatus not in (3,4) -- 3:In Progress, 4: Idle

select * from distribution..MSlogreader_history

SIZE OF THE DATABASE FILES

noreply@blogger.com (ted) — Wed, 09 Jun 2010 06:11:00 +0000

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

OUTPUT
File Name file_id Physical Name Total Size in MB Available Space In MB
-------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------
master 1 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 80 71.625000
mastlog 2 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 1 0.640625

(2 row(s) affected)

How to find the authentication methord used

noreply@blogger.com (ted) — Wed, 09 Jun 2010 06:03:00 +0000

This script will help you find the authentication method of logins in sql 2005.

SELECT 'Authentication Method'=(
CASE
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
END),
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name

quick and dirty shrinklogs for all databases

noreply@blogger.com (ted) — Mon, 31 May 2010 05:18:00 +0000

This is a real quick and dirty way to shink all the database logs in all the databases please makesure you want to shrink the master, msdb,tempdb and so on before running this.

EXEC SP_MSFOREACHDB 'dbcc shrinkfile(2,1)'

Script to check whats running in tempdb and want statements its running

noreply@blogger.com (ted) — Thu, 27 May 2010 06:08:00 +0000

This will help deturming what is running in tempdb this will give you the sql text.

use tempdb
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'
ORDER BY [Log Bytes Used] DESC;

Check which tables have triggers and compare

noreply@blogger.com (ted) — Tue, 18 May 2010 03:01:00 +0000

This is useful if you have 2 or more databases in different sites.

Script to check which tables have triggers

SELECT s1.name as tablename, s2.name as triggername FROM sysobjects s1
JOIN sysobjects s2 ON
s1.id =s2.parent_obj
AND s2.xtype = 'TR'
WHERE s1.xtype = 'U'
ORDER BY s1.name

output

tablename triggername
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AccruedDivis trAccrueddivisAuditQueueInsert
AccruedDivis trAccrueddivisAuditQueueUpdate
AccruedDivis trAccrueddivisAuditQueueDelete
AlertMessages trAlertMessagesInsert
Run this on both of the servers and in the databases required
copy the data to an excel worksheet
I have two databases one in London and one in Hong Kong in this sample

use a vlookup to compare like this and the copy the formula down

=VLOOKUP(B115,'sheet.name'!B:B,1,FALSE)

How to start service broker on a database

noreply@blogger.com (ted) — Mon, 03 May 2010 02:52:00 +0000

enable service broker
-- Alter the database to enable ServiceBroker functionality

DECLARE @dbname VARCHAR(30)

SELECT @dbname=db_name()

PRINT 'Setting up service broker on ' + @dbname

EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

EXEC ('ALTER DATABASE ' + @dbname + ' set NEW_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET ENABLE_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER')

IF (SELECT is_broker_enabled FROM sys.databases WHERE name = @dbname) = 0

BEGIN

-- PRINT 'The Service broker wasn''t enabled !!'

RAISERROR ('The Service broker wasn''t enabled !!', 1, 1)

END

a few ways to check server uptime

noreply@blogger.com (ted) — Mon, 26 Apr 2010 05:33:00 +0000

Here are a few ways to check your sqlserver2005 uptime

SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM master..sysprocesses WHERE spid = 1
go

SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM master..sysprocesses
WHERE spid = 1
go

SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM master..sysprocesses
WHERE spid = 1
) a

How to restart your sqlserver inside of SQL server

noreply@blogger.com (ted) — Sun, 25 Apr 2010 05:09:00 +0000

Restarting your SQL service within SQL

we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.

declare @sqlcmd varchar(8000)select @sqlcmd = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start SQLSERVERAGENT'EXEC xp_cmdshell (@sqlcmd)

Creating a automated backup and restore with litespeed

noreply@blogger.com (ted) — Sat, 24 Apr 2010 00:55:00 +0000

Copy this code into a sqljob for and set time to whatever time you would like the backup taken this will work for daily backups and delete weekly.

SET QUOTED_IDENTIFIER ON

DECLARE @lastweekfiletime char(10)
, @dircmd varchar(500)
, @delcmd varchar(500)
, @filetime char(19)
, @sqlcmd varchar(500)

SELECT @lastweekfiletime = convert(char(4),dateadd(dd,-7,getdate()),112)+ '-' + substring(convert(char(8),dateadd(dd,-7,getdate()),112),5,2) + '-'
+ substring(convert(char(8),dateadd(dd,-7,getdate()),112),7,2);

PRINT @lastweekfiletime;

SELECT @dircmd = 'EXEC master..xp_cmdshell ''DIR d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp''';

EXEC(@dircmd);
-- deletes older files
SELECT @delcmd = 'EXEC master..xp_cmdshell ''DEL d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp /Q''';

print @delcmd;

EXEC(@delcmd);

EXEC(@dircmd);

SELECT @filetime = (
SELECT convert(char(4),getdate(),112) + '-'
+ substring(convert(char(8),getdate(),112),5,2) + '-'
+ substring(convert(char(8),getdate(),112),7,2) + '_' +
CASE len(datename(hh, getdate()))
WHEN 1 THEN
'0' + datename(hh, getdate())
ELSE
datename(hh, getdate())
END + '-' +
CASE len(datename(mi, getdate()))
WHEN 1 THEN
CASE datename(mi, getdate())
WHEN 0 THEN
datename(mi, getdate()) + '0'
ELSE
'0' + datename(mi, getdate())
END
ELSE
datename(mi, getdate())
END + '-' +
CASE len(datename(ss, getdate()))
WHEN 1 THEN
CASE datename(ss, getdate())
WHEN 0 THEN
datename(ss, getdate()) + '0'
ELSE
'0' + datename(ss, getdate())
END
ELSE
datename(ss, getdate())
END);

SET @sqlcmd = 'exec master..xp_backup_database @database = ''database'', @filename = ''d:\dir\database_' + @filetime + '_1of1.lsb.dmp''';

EXEC(@sqlcmd);

Restore if you would like to restore daily to another database on the same server you can us this job to pick up the latest full database.
Or create a copy job to copy the backup accross to another server and use this to restore daily.
SET NOCOUNT ON
declare @RestoreDateTime varchar(20)
declare @stmt varchar(500)
declare @restore_stmt varchar(1000)
declare @latest_full varchar(255)

create table #SrcFilesRAW
(
SrcFName varchar(200)
)

select @stmt = 'dir D:\dir\*database_*.dmp /b'

insert into #SrcFilesRAW
exec master..xp_cmdshell @stmt

select * from #SrcFilesRAW
set rowcount 1
select @latest_full = SrcFName from #SrcFilesRAW
where SrcFName like 'database_20%' order by SrcFName desc
set rowcount 0
select * from #SrcFilesRAW
drop table #SrcFilesRAW

select @restore_stmt = 'exec master..xp_restore_database @database = ''database1''
,@FILENAME = ''D:\dir\'+ @latest_full + '''
,@WITH = ''REPLACE'''

exec (@restore_stmt)

SQL2005Help

PowerShell for DBAs: Automating Backups and Alerts

Why Use PowerShell?

Automating SQL Server Backups with PowerShell

Tips:

Setting Up Alerts with PowerShell

Final Thoughts

Day 1: Getting Our Hands Dirty – Setting Up Our DBA Workbench!

🔥 From Bug Bounty Hunter to Data Guardian: Kai's Next Level Up! 🔥

📊 How to Spot When a Table’s Statistics Need Updating

🧠 What Are Table Statistics?

⚠️ Signs That Table Statistics Need Updating

1. Sudden Query Performance Degradation

2. Table Has Undergone Massive Changes

3. Estimated Rows ≠ Actual Rows

4. Execution Plan Uses Table Scan Instead of Index Seek

5. High CPU or IO on Simple Queries

🔎 How to Check for Stale Statistics

👉 SQL Server

👉 MySQL

👉 PostgreSQL

🛠 How to Update Statistics

✅ Best Practices

📌 Summary

🧠 SQL Query Performance and Tuning: A Practical Guide

🔍 Why Query Performance Matters

🚧 Common Causes of Poor SQL Performance

🧰 SQL Query Tuning Tips

1. Use Indexes Wisely

2. **Avoid SELECT ***

3. Use Joins Efficiently

4. Use Query Execution Plans

5. Avoid Functions in WHERE Clauses

6. Use LIMIT or TOP for Large Tables

🔄 Updating Statistics and Rebuilding Indexes

🧪 Tools for Performance Tuning

🏁 Final Thoughts

stocks

Should a DBA have SA rights all the time?

audit storeproc

how to create an audit trigger for sql agent jobs

c# totals

exporting sql statement to excel in c#

Table ' does not have the identity property. Cannot perform SET operation.

How to enable access to sql proflier without sysadmin

Setting up transactional replication without a snapshot

checking sql2005 replication

SIZE OF THE DATABASE FILES

How to find the authentication methord used

quick and dirty shrinklogs for all databases

Script to check whats running in tempdb and want statements its running

Check which tables have triggers and compare

How to start service broker on a database

a few ways to check server uptime

How to restart your sqlserver inside of SQL server

Creating a automated backup and restore with litespeed

2. Avoid SELECT *