IT and business subjects
16-Apr-1313 Leave a comment
PROBLEM
Opened SSMS 2012 and got error viewInfo. Unable to see database list.
On Refresh databases same error.
SOLUTION
For me it was to do a restore back 2 days
BACKGROUND
Others have had this.
Sysinternal Tools and filters to “Process Name is ssms.exe” and “Path contains AppData\Local\Temp”
It seemed to not be finding an Addin.
https://connect.microsoft.com/SQLServer/feedback/details/573771/value-cannot-be-null
14-Mar-1313 Leave a comment
Imported some data from Excel in form “156 x 54 x 16mm”. Parsed the text to get the 156. However this would not convert to a numeric.
Error converting data type nvarchar to numeric
After some exploration I discovered that the cursor required to steps through before the number. So a character was there that SQL Server could not display. To find this I used:
SELECT Code, UNICODE(LEFT(DimensionsTextImport, 1)) AS UnicodeTest FROM Product.<tablename>
And discovered text which would not convert had Unicode 65279
Create a new column and fill this with the data from the first column after a CASE statement.
UPDATE Product.Dimensions
SET DimensionsText = CASE
WHEN UNICODE(LEFT(DimensionsTextImport, 1)) = 65279 THEN SUBSTRING(DimensionsTextImport, 2, 50)
ELSE DimensionsTextImport END
With reference to:
http://stackoverflow.com/questions/6441208/unable-to-replace-char63-by-sql-query
http://www.fileformat.info/info/unicode/char/feff/index.htm
http://en.wikipedia.org/wiki/Byte_Order_Mark
11-Mar-1313 Leave a comment
My SQL 70-461 study reminded me about the T-SQL CONTINUE statement to cause execution to jump back to the beginning of the loop. Re. Querying Microsoft SQL Server 2012 – MS Press – p 479
http://msdn.microsoft.com/en-us/library/ms178642.aspx – SQL Server
I keep needing this in VB.Net and for some reason it is missing from my VB.Net vocabulary. Not any longer…
http://msdn.microsoft.com/en-us/library/801hyx6f(v=vs.100).aspx – VB.Net
Continue transfers control immediately to the next iteration of a loop.
15-Jan-1313 Leave a comment
Have an old Excel file that connects to a database list using Microsoft Query. An existing user was moved to a new Windows 7 machine and then got this error.
[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified
Tried various things. In the end.
In Administrative Tools > ODBC > Drivers on his machine SQLNCLI was missing.
I did not want to install this old version, so initially changed the connection string to “SQL Server Native Client 10.0” but “SQLNCLI10” may work
14-Dec-1212 Leave a comment
VB.Net dynamic SQL WHERE DateField = ‘2012-12-13’ converts to US date and possibly throws error if then invalid
Was only happening on Windows XP, possiby 32-bit computers.
Windows 7 64-bit were ok.
In this case it was against a view. Solved by changing view:
SELECT ts.JobSheetID
, CAST(js.DateMJSAASdatetime2(0)) AS DateMJSA
,js...
FROM
13-Dec-1212 Leave a comment
Various error messages received including:
Description: ADO NET Source has failed to acquire the connection DataReaderSrc with the following error message: “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”
Solution:
13-Dec-1212 Leave a comment
In SQL Server 2005 32-bit > Linked Servers > Providers > VFPOLEDB was present, (with in process ticked)
This provider was not available in SQL Server 2012 64-bit
Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider “VFPOLEDB” for linked server “”. [SQLSTATE 42000] (Error 7303) OLE DB provider “VFPOLEDB” for linked server “” returned message “Invalid path or file name.”. [SQLSTATE 01000] (Error 7412). The step failed.
10-Dec-1212 Leave a comment
With thanks to
http://aartemiou.blogspot.co.uk/2011/02/no-global-profile-is-configured-specify.html
”
Even though the first three tasks were executed successfully, when I checked the history of the job that undertakes the execution of the maintenance plan, there was an error message on the last task that is the “Notify Operator” Task.
A part of the error message is the following:
“No global profile is configured. Specify a profile name in the @profile_name parameter.”
So, what does the above error message tell us? It actually says that upon the task’s execution, it cannot find a Database Mail profile in order to use it for sending the email notification.
To overcome this problem, you can set one of the available Database Mail profiles as the default one.
In SQL Server 2005 or 2012 you can do this in the following way through SSMS:
“
16-Nov-1212 Leave a comment
Use a CTE. Problem could occur in
WITH myCTE AS
(
SELECT WO, QuoteNo, EntryNo = ROW_NUMBER() OVER (PARTITION BY QuoteNo ORDER BY WO)
FROM dbo.Orders2006
WHERE QuoteNo IS NOT NULL
)
UPDATE dbo.Orders2006
SET EntryNo = myCTE.EntryNo
FROM dbo.Orders2006
INNER JOIN myCTE ON myCTE.WO = dbo.Orders2006.WO
With thanks to:
http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause
05-Oct-1212 Leave a comment
During study for Exam 70-461 looked at Synonym
Could be useful. For instance could use synonym to point at a data table in linked “Live” database. Then restore your project to “Test” version, change the synonym to point at the linked “Test” database and you have changed what all objects now point at.
Stephen Shakespeare 