Posts Tagged ‘ SQL ’

Add a FILESTREAM feature to an existing SQL D/B

2017/12/07
By
Modified: 2017/11/05
SQL 2008 SP2 FILESTREAM Problem

SQL Server 2008 allows to store binary data, if Database has a FILESTREAM feature

Read more »


Moving Old ASP Sites to 2008 Server, IIS 7, and SQL 2008

2017/11/13
By
Modified: 2017/11/12
IIS75-ASP-is-not-Installed

It was time to move many sites from W2K3 Server (Windows 2003 Server) and SQL 2000 Server to a new W2K8 Server (Windows 2008 Server) and SQL 2008 Server.  And if I ever have to do it again, I will check with these notes. The greatest secret in technical life (and may be wider) is to change before you have to.  Anticipate and change before circumstances force you.  For a long time I was planning this migration, but I didn’t expect to go through so many hurdles and error messages.  Luckily, I found solutions that seem logical and clean.  So, let’s go into step-by-step details. COPY DATABASE AND SITES – Backup…

Read more »

Allow to Pass Optional Parameters to a Stored Procedure

2017/11/03
By
Modified: 2017/09/16
Mount Carrigain NH 1427m

This article shows how to use string parameters to make selection on Boolean fields. What is it you need to select "true", "false" of "ALL" records. How WHERE statement will look like in that case?

Read more »

How to Re-Install SQL Server 2008 Evaluation

2017/10/28
By
Modified: 2017/11/04
Sandy-Beach-2017

You have 180 days to evaluate.  You can check number of days till expiration:   open your SQL Server Management Studio and widen first line in Help — About box. What if your trial is about to expire, and you need just a few more days to evaluate? You can switch to a free Express edition, or you can follow this cumbersome procedure.   =01= Un-Install SQL Server 2008 Drop all your custom databases Run your Maintenance Plan one last time Save all your maintenance plans (this step was not tested) Go to Control Panel – Programs and Features Uninstall Microsoft SQL Server 2008 – Remove Check Rules – Next Select Features –…

Read more »

SQL Error 80004005 – “Specified SQL server not found” – Solved

2017/10/08
By
Modified: 2017/09/16
SQL CFG Manager - Enable TCPIP

  Environment ASP or ASP.NET site is running on IIS 7.5. SQL Server 2008 R2 Express (10.50.1600.1) running on Windows 2008 Server R2 (W2K8 R2) x64.     Problem You will see one of two errors depending on whether you dealing with ASP or ASP.NET sites. On ASP.NET sites you will see  “Server Error – Error: 26 – Error Locating Server/Instance Specified” Server Error in '/YourApps' Application. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26…

Read more »

Automate Database Backup with SQL Express

2017/10/02
By
Modified: 2017/09/16
Automate Database Backup with SQL Express

This article describes, how to automate database backup in SQL Express with mixed Windows and SQL Authentication running on Windows 2008 Server.   This material was tested with Microsoft SQL Server 2008 R2 Express. SQL Server 2008 Enterprise comes with Maintenance Plan feature.  That feature allows amazing flexibility and auditing.  SQL Express edition lacks this feature.  But you still need a backup. Backup automation with SQL Express consist of 3 steps: (1) Write the script to backup all your databases. (2) Test the script by initiating it from a command prompt. (3) Schedule the script using Task Scheduler. Here is each step in detail. ` (1) Write the Script This script overwrites…

Read more »

How to Convert Julian Date to Gregorian in MS SQL

2017/09/20
By
Modified: 2017/09/16
SQL - Convert JUL to GREG

Julian date is stored in YYYDDD format, for example 12/31/99 will be stored as 99365 and 01/01/2017 will be stored as 117001. If you need to convert the date from YYYDDD to any conventional date format like dd/mm/yyyy in SQL use steps below. The idea is to convert year and date portions separately and them add them together and use CONVERT function to get any date format that you require. Here is a script that shows the conversion process step-by-step. SELECT -- Limit output for 9 rows TOP 9 -- 00 Julian date - 91244 FAEFTB, -- 01 Greg year - 1991 cast(left(CAST(FAEFTB as decimal)+1900000, 4) as char(4)) AS GREGYEAR, --…

Read more »

Adding a new WordPress site to an Existing Installation

2017/07/12
By
Modified: 2017/06/25

Task You already have a working WordPress (WP) site.  Now, you need to add one more site with a new MySQL database.  This document describes how to add one more database and install a new WP site on the same server. Download Download latest WordPress ZIP package (3.X) and unzip it to your future site location. Open IIS Admin Create a new virtual directory pointing to that location Side Notes – Change root password You need to know existing root user password. Login into MySQL Command Line Client. Select database: use mysql; Reset user password: update user set password=PASSWORD(“NewPassword”) where User=’root'; Allpy new password: flush privileges;   Create a New Database…

Read more »

Update Field Based on a Field in Another Table

2017/06/02
By
Modified: 2017/04/30

This article contains 3 examples: - T-SQL; – MS Access; – AS/400.   This example shows a standard T-SQL syntax tested on MS SQL 2005 server: update P set P.CertMemberLevelID = Z.CertLevelTo from   UPM_MemberPermissionMask P INNER JOIN zzzCertRegistryUpdate Z ON P.MemberID = Z. Cert2ID Here we are updating one filed CertMemberLevelID in table UPM_MemberPermissionMask based on field Cert2ID in table zzzCertRegistryUpdate. This next example was generated using MS Access 2007 syntax: UPDATE CRPDTA_F1201 INNER JOIN CatCode7 ON CRPDTA_F1201.FANUMB = CatCode7.XXNUMB SET CRPDTA_F1201.FAFA7 = [CatCode7].[XXFA7] A field FAFA7 in CRPDTA_F1201 is being updated. Table  CatCode7 contains 2 fields: – ID Link field XXNUMB and – Update values field XXFA7 SQL statement JOINs two…

Read more »

How to Get SQL Table Field Structure?

2017/05/15
By
Modified: 2017/04/30
SQL-2008-R2

Sometimes you need to display or print a SQL table structure. This article provides a SQL statement that allows you to do that. We are using MS SQL 2008 Studio interface.

Read more »