Getting error when Executing rake db:migrate
rake aborted!
Unable to connect: Adaptive Server is unavailable or does not exist
to solve this issue check your database configuration on config\database.yml (remove unnecessary property)
development: adapter: sqlserver
host: sharon database: SharonBlog_development
username: Usersharon
password: fill in the password
verify that TCP/IP port is Enabled (restart the MSSQLSERVER service):
open SQL Server configuration Tools - > SQL Server configuration manager-> SQL Server Network configuration
create ruby project http://guides.rubyonrails.org/getting_started.html
on Configuring a Database section if you decide to connect to MSSQL try this steps:
1)
open SQL Server and add your database
create database SharonBlog_development
2)
Open ROR config\database.yml set the connection string(simple connection property):
development: adapter: sqlserver
host: sharon database: SharonBlog_development
username: Usersharon
password: fill in the password
3)A dd 'tiny_tds' to Gemfile
open Gemfile and add the new lines after gem 'rails', '3.2.3' :
source 'https://rubygems.org'
gem 'rails', '3.2.3'
gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'
4) Execute rake:migrate from command line:
open project config command line and run rake db:migrate
D:\eclipse\Workspace\SharonBlog\config>rake db:migrate
In order to verify that the backup SQL file is ok before restoring the data , we can use the following syntax :
restore verifyonly from disk='C:\Test\TestBackUP.bak' - Checks to see that the backup set is complete and that all volumes are readable
restore headeronly from disk='C:\Test\TestBackUP.bak'-Returns a result set containing all the backup header information for all backup sets on a particular backup device
restore
filelistonly from disk='C:\test\TestBackUP.bak' - Returns a result set containing a list of the database and log files contained in the backup set.
For the all list
sample for error message:
restore verifyonly from disk='C:\Test\Test_Error_BackUP.bak'
Msg 3203, Level 16, State 1, Line 1
Read on "C:\test\Test_Error_BackUP.bak' " failed: 38(Reached the end of the file.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
-----------------------------------------------------------------------------------------------------
restore headeronly from disk='C:\Test\TestBackUP.bak'
![]()
have a nice day from
sharon cohen shahen tov
Problem: I tried to load a system table from Microsoft Access from a C# application and got the error message "Record(s) cannot be read; no read permission on 'MSYSObjects'".
Solution: To add permission to the systable:
1) Tools -> Options, on the view tab check 'system object'.
2) Tools -> Security -> User and group permissions, on each of the new tables give it 'administrator' access .
Have a nice day. Sharon
Using randomly records can be benefit for testing two or more application with different data
simply add to the Order by column NEWID()
SELECT TOP 200 * FROM mytbl ORDER BY NEWID()
sharon cohen
The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them.
SQL Server 2008 System Views Map
Cumulative Update 3 contains hotfixes for the Microsoft SQL Server 2008 issues that have been fixed since the release of SQL Server 2008.
http://support.microsoft.com/kb/960484
If your Database is marked as Suspected, here are the steps to fix it :
EXEC sp_resetstatus 'DBName'
GO
ALTER DATABASE DBName SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBName', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBName SET MULTI_USER
GO
-- Rebuild the index
ALTER INDEX ALL ON [ TableName] REBUILD
Good luck
sharon cohen shahen tov
When you are involved with a group of DBA developers and you want to retrieve the objects that were updated or created in the last week to check permissions, performance issues, etc., the fastest way is to run this script.
SELECT name
FROM sys.objects
WHERE (type = 'P' OR type = 'V' ) AND DATEDIFF(D,modify_date, GETDATE()) < 7
best regards ,
sharon
One of my colleagues had a problem. His user defined error message didn’t reach the application side.
After a quick look, I noticed that he didn’t put the SET NOCOUNT ON statement at the beginning of his stored procedure, something I intuitively add.
I asked him to add it and try again, and to my surprise it solved the problem.
To satisfied my curiosity, after a short search in Google I found that it’s a known issue and it occurs only in the SQLOLEDB driver.
http://support.microsoft.com/kb/254304
If you don't have DataCompare or Other Third-party tool and you still want to compare the data between tables consider of using TableDiff Utility.
Description
The TableDiff Utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:
1. A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.2. Perform a fast comparison by only comparing row counts and schema. 3. Compare destination tables at multiple destination servers simultaneously. 4. Perform column-level comparisons. 5. Generate Transact-SQL scripts to fix discrepancies at the destination server to bring the source and destination tables into convergence. 6. Log results to an output file or into a table in the destination database.
Location
C:\Program Files\Microsoft SQL Server\90\COM.
Options
For Getting TableUtility all options run the TableDiff /-? In the Command Prompt
C:\Program Files\Microsoft SQL Server\90\COM.\TableDiff /-?
Using the utility
fill in the missing parameters and execute the tablediff.exe .
C:\Program Files\Microsoft SQL Server\90\COM.\TableDiff -sourceserver SourceServer -sourcedatabase sharon -sourceuser MyUser -sourcepassword MyPass -sourcetable MyTableNameToComper -destinationserver MyDestComputer -destinationdatabase DBsub -destinationuser sa -destinationpassword MyDestPass -destinationtable MyDestTableToComper -c -f –q
For using a GUI Aplication:http://devintelligence.com/blogs/netadventures/archive/2007/08/13/ui-for-ms-sql-server-tablediff-tool.aspx
Have a nice day
sharon cohen shahen-tov
EXCEPT and INTERSECT are new operators in SQL Server 2005, which returns distinct values by comparing the results of two queries.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:
* The number and the order of the columns must be the same in all queries.
* The data types must be compatible.
| TableA (col1 int) |
TableB (col1 int) |
TableC (col1 int) |
|
NULL |
NULL |
2 |
|
NULL |
1 |
2 |
|
NULL |
3 |
2 |
|
1 |
4 |
4 |
|
2 |
4 |
4 |
|
2 |
|
|
|
2 |
|
|
|
3 |
|
|
|
4 |
|
|
|
4 |
|
To return all rows in tableA that do not match exactly in tableB
select * from tableA except select * from tableB
To return all rows in tableA that match exactly in tableB
select * from tableA intersect select * from tableB
The INTERSECT operator takes precedence over EXCEPT. For example, the following query uses both operators:
SELECT * FROM TableA EXCEPT SELECT * FROM TableB INTERSECT SELECT * FROM TableC
The results for the last query:
Col1
-----------
NULL
1
2
3
Happy Holiday
Sharon Cohen Shahen Tov
A basic tutorial that examines how Profiler can be used in SQL Server 2005
http://www.sqlservercentral.com/articles/Video/63814/
sharon
This error message could occur while trying to call a stored procedure that contains Alias Data Types, if the current user does not have permission to use the current types.
To avoid this error, create the new type and add REFERENCES permission.
Sample:
CREATE TYPE dtInstrumentID FROM VARCHAR(15) NULL
GRANT REFERENCES ON TYPE::dtInstrumentID TO My_UserRole
Have a nice day
sharon cohen shahen-tov
In my work I frequently need to exeute groups of SQL files such as Stored Procedures (SP), Triggers, Functions, and Views.
Using the SQL Server 2005 tool SQLCMD simplifies the task.
For this task I created two windows folders:
1. CleanFolder
2. CreateFolder
In the CleanFolder I wrote SQL scripts to drop all objects except the tables (data).
File name : DROP_SP_AND_VIEW.SQL
-----------------------------------
DECLARE @sqlstring nvarchar(1000)
DECLARE @name sysname
DECLARE @xtype char(2)
DECLARE ObjectList CURSOR FOR
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
WHERE xtype = 'V' OR xtype = 'P' OR xtype = 'TR' OR
xtype = 'FN' OR xtype = 'TF'
OPEN ObjectList
FETCH NEXT FROM ObjectList into @name, @xtype
WHILE @@FETCH_status = 0
BEGIN
-- Drop view
IF @xtype = 'V'
BEGIN
SET @sqlstring = 'DROP VIEW ' + @name
EXECUTE sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- Drop procedure
IF @xtype = 'P'
BEGIN
SET @sqlstring = 'DROP PROCEDURE ' + @name
EXECUTE sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- Drop Trigger
IF @xtype = 'TR'
BEGIN
SET @sqlstring = 'DROP Trigger ' + @name
EXECUTE sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- Drop Function
IF @xtype = 'FN' OR @xtype = 'TF'
BEGIN
SET @sqlstring = 'DROP FUNCTION ' + @name
EXECUTE sp_executesql @sqlstring
SET @sqlstring = ' '
END
FETCH NEXT FROM ObjectList INTO @name, @xtype
END
CLOSE ObjectList
DEALLOCATE ObjectList
In the Second folder (CreateFolder) I put all file scripts for adding the objects.
File name : vDEMO1.SQL - vDEMO1000.SQL
CREATE VIEW dbo.vDEMO1
AS
SELECT * FROM sysobjects
....
In each folder I put a batch file that contain the folowing line:
File name : RunScripts.BAT
-----------------------------------------------
for %%X in (*.SQL) do SQLCMD -S ServerName -d DatabasesName -U UserName -P UserPassword -I -i "%%X" >> ResultScript.txt
This batch file uses a for loop that runs the SQLCMD on all of files that match *.SQL that reside in the same folder.
yours
Sharon Cohen Shahen Tov
More Posts
Next page »