Recent Posts | - July, 2022-7,(2)
- March, 2022-3,(1)
- November, 2021-11,(2)
- August, 2021-8,(2)
- July, 2021-7,(2)
- June, 2021-6,(1)
- May, 2021-5,(1)
- March, 2021-3,(1)
- February, 2021-2,(2)
- January, 2021-1,(7)
- December, 2020-12,(3)
- March, 2020-3,(2)
- February, 2020-2,(1)
- December, 2019-12,(2)
- November, 2019-11,(1)
- October, 2019-10,(1)
- September, 2019-9,(1)
- August, 2019-8,(1)
- May, 2019-5,(1)
- April, 2019-4,(2)
- March, 2019-3,(2)
- December, 2018-12,(1)
- November, 2018-11,(4)
- July, 2018-7,(1)
- May, 2018-5,(3)
- April, 2018-4,(2)
- February, 2018-2,(3)
- January, 2018-1,(3)
- November, 2017-11,(2)
- August, 2017-8,(1)
- June, 2017-6,(3)
- May, 2017-5,(5)
- February, 2017-2,(1)
- December, 2016-12,(1)
- October, 2016-10,(2)
- September, 2016-9,(1)
- August, 2016-8,(1)
- July, 2016-7,(1)
- March, 2016-3,(2)
- February, 2016-2,(3)
- December, 2015-12,(5)
- November, 2015-11,(5)
- September, 2015-9,(1)
- August, 2015-8,(2)
- July, 2015-7,(1)
- March, 2015-3,(2)
- February, 2015-2,(1)
- December, 2014-12,(4)
- July, 2014-7,(2)
- June, 2014-6,(2)
- May, 2014-5,(3)
- April, 2014-4,(3)
- March, 2014-3,(1)
- December, 2013-12,(2)
- November, 2013-11,(1)
- July, 2013-7,(1)
- June, 2013-6,(2)
- May, 2013-5,(1)
- March, 2013-3,(3)
- February, 2013-2,(3)
- January, 2013-1,(1)
- December, 2012-12,(3)
- November, 2012-11,(1)
- October, 2012-10,(1)
- September, 2012-9,(1)
- August, 2012-8,(1)
- July, 2012-7,(6)
- June, 2012-6,(1)
- April, 2012-4,(1)
- March, 2012-3,(3)
- February, 2012-2,(3)
- January, 2012-1,(4)
- December, 2011-12,(3)
- October, 2011-10,(3)
- September, 2011-9,(1)
- August, 2011-8,(10)
- July, 2011-7,(2)
- June, 2011-6,(7)
- March, 2011-3,(2)
- February, 2011-2,(3)
- January, 2011-1,(1)
- September, 2010-9,(1)
- August, 2010-8,(2)
- June, 2010-6,(1)
- May, 2010-5,(1)
- April, 2010-4,(3)
- March, 2010-3,(2)
- February, 2010-2,(3)
- January, 2010-1,(1)
- December, 2009-12,(3)
- November, 2009-11,(3)
- October, 2009-10,(2)
- September, 2009-9,(5)
- August, 2009-8,(3)
- July, 2009-7,(9)
- June, 2009-6,(2)
- May, 2009-5,(2)
- April, 2009-4,(9)
- March, 2009-3,(6)
- February, 2009-2,(4)
- January, 2009-1,(10)
- December, 2008-12,(5)
- November, 2008-11,(5)
- October, 2008-10,(13)
- September, 2008-9,(10)
- August, 2008-8,(7)
- July, 2008-7,(8)
- June, 2008-6,(12)
- May, 2008-5,(14)
- April, 2008-4,(12)
- March, 2008-3,(17)
- February, 2008-2,(10)
- January, 2008-1,(16)
- December, 2007-12,(6)
- November, 2007-11,(4)
|
|
|
| Honda CTX 700 2014 | 7/25/2022 10:52:38 AM |
| New to me ! Honda CTX 700 2014
|
| | SQL Server Management Studio MFA hanging not prompting | 7/12/2022 5:58:15 AM |
|
We have certain "zones" / "restrictions" that cause us too use a common "jump box" (server).
Sometimes SQL Server Management Studio SSMS hangs when using Multi Factor Authentication to access Azure SQL resources. The prompts for logging in never appear. ( Make sure the URL's for Microsoft are white listed).
This appeared to only affect some users.
Internet Explorer is "retired", but it appears that SSMS still uses some calls to IE underneath the hood to call MFA.
The team was able to find a workaround that users can
compete to clean up a bad cookie that seems to be the ultimate culprit. Details
on this fix are below:
Executing the below commands in a
Powershell window. – If you have never ran Internet explorer on the server
before, do that before running the command. E.g open IE and close it, the run
the below.
RunDll32.exe
InetCpl.cpl,ClearMyTracksByProcess 1
start-sleep
2
RunDll32.exe
InetCpl.cpl,ClearMyTracksByProcess 2
start-sleep
2
RunDll32.exe
InetCpl.cpl,ClearMyTracksByProcess 8
start-sleep
5
[Net.ServicePointManager]::SecurityProtocol
= [Net.SecurityProtocolType]::Tls12
Invoke-WebRequest https://login.microsoftonline.com
|
| | Postgres to SQL Quick Tips | 3/4/2022 7:23:55 AM |
| Very First make sure your working with a great Postgres DBA.
1. Convert the schema from Postgres to SQL Server 2. Install ODBC Drivers 3. Use SSIS or it's watered down cousin DTSWizard 4. If really necessary you can drop down to custom code to transfer data. 5. Edit the ProvidersDescriptors.xml 6. Out of Memory Reading tuples ? (add UseDeclareFetch=1 to connect string).
For data type compatibility https://www.convert-in.com/docs/mss2pgs/types-mapping.htm For: sql server odbc postgres column attribute colum_size is not valid providerdescriptors.xml MaximumLengthColumnName , NumericPrecisionColumnName , and NumericScaleColumnName attribute values to "LENGTH" , "PRECISION" , and "SCALE" , respectively.
The column attribute "COLUMN_SIZE" is not valid. The column attribute "DECIMAL_DIGITS" is not valid. The column attribute "COLUMN_SIZE" is not valid.
<dtm:ColumnSchemaAttributes
NameColumnName = "COLUMN_NAME"
OrdinalPositionColumnName="ORDINAL_POSITION"
DataTypeColumnName = "TYPE_NAME"
MaximumLengthColumnName = "COLUMN_SIZE"
NumericPrecisionColumnName = "COLUMN_SIZE"
NumericScaleColumnName = "DECIMAL_DIGITS"
NullableColumnName="NULLABLE"
NumberOfColumnRestrictions="4"
/>
... to ... <dtm:ColumnSchemaAttributes
NameColumnName = "COLUMN_NAME"
OrdinalPositionColumnName="ORDINAL_POSITION"
DataTypeColumnName = "TYPE_NAME"
MaximumLengthColumnName = "LENGTH"
NumericPrecisionColumnName = "PRECISION"
NumericScaleColumnName = "SCALE"
NullableColumnName="NULLABLE"
NumberOfColumnRestrictions="4"
/>
sql server postgresql out of memory while reading tuples
"Driver={PostgreSQL Unicode};Server=ip;Port=port;Database=db_name;Uid=user;Pwd=pass;UseDeclareFetch=1"
https://stackoverflow.com/questions/22532149/vba-and-postgresql-connection
|
| | Stored procedure can not return BIGINT | 11/2/2021 2:02:07 PM |
| SQL Server stored procedures can not return a big integer.
Table has identity value with primary key as big integer.
Stored procedure is running Return Scope_Identity() which works until the identity values exceeds the value of the implicit integer conversion, 2,147,483,646.
This is documented, but rarely run into.
The correct best practice is too use an output parameter for the stored procedure. This requires changing the procedure and calling code, which can be difficult if this happens in production environment during a busy period.
The temporary solution is too reset the identity value to a negative values, as the range of an integer, -2,147,483,646 to +2,147,483,646.
Watch when you do this, as checkident('tablename') will reset an identity value, so be sure to use noreseed as part of the command.
For monitoring purposes:
dbcc checkident ([table name],reseed,-2147483646) select max([column identity]),min([column identity]) from [table name] with (nolock) select count(*) from [table name] with (nolock) where [column identity] < 0
|
| | Grant Create Table Permissions to a Schema, but not DBO | 11/2/2021 1:48:31 PM |
|
Granting Create Table permissions to a specific schema requires, granting create table at the database level and alter at the schema level (doing no grants to the dbo schema).
Create user [some user] from external provider go
Grant alter,select,insert,update,delete on schema::[schema name] to user [some user] go
-- if control or references is needed -- grant control,references on schema:: [schema name] to user [some user]
grant create table, create view, create function,create type to [some user] go
Always confuses me to remember the grant alter and the create table portions. |
| | SQL Server Walk the Dog | 8/18/2021 6:07:33 AM |
|
Recently I was given several text files with 500k to 1 million update statements in each that needed to be run.
Try loading and running this in SQL Server Management studio and you'll find that if it does load and run, sometimes it fails part through with memory error or other issues (all related to the client, not the engine).
It would have been better for the teams to work together and write this as a table join update as opposed to generating so many individual statements, but that ship had sailed and now these needed to be run.
I ended up "Walking the Dog" or RBAR (Row by agonizing Row).
Load the text files to a table, use a cursor to read each row and dynamically execute it. I threw in a global counter i could query to figure out where it was at in the process. In total it took about 2-3 hours to run.
Here is how to walk the dog.
SET
NOCOUNT
ON;
DECLARE @sqlstmt
nvarchar(4000)
create
table ##Global_Count
(current_rownum
int)
insert
into ##Global_Count
values
(0)
PRINT
'-------- starting --------';
DECLARE cur_statements
CURSOR
FOR
SELECT sqlstmt
FROM DocumentId_3
OPEN cur_statements
FETCH
NEXT
FROM cur_statements
INTO @sqlstmt
WHILE
@@FETCH_STATUS
= 0
BEGIN
Exec
sp_Executesql
@sqlstmt
update ##Global_Count
set current_rownum
= current_rownum
+ 1
FETCH
NEXT
FROM cur_statements
INTO @sqlstmt
END
Close cur_statements
Deallocate cur_statements
select
*
from ##Global_Count
drop
table ##Global_Count |
| | SQL Server Drop Constraints unknown name | 8/12/2021 6:01:02 AM |
| SQL Server will create constraints with a default name, that is not consistent across implementations if you do not specify a name.
Best practice would be to always specify a name.
But if you inherit a database across 1000's of locations and have to write update scripts, you may come across the need to drop a constraint for a column when you don't know the constraint name.
This script will find the constraint name based off the column and dynamically generate a drop statement for it.
SET @SelectStatement
= (SELECT
'ALTER TABLE [Machines] DROP CONSTRAINT '
+
name
FROM
sysobjects WHERE parent_obj
=
object_id('TableName')
AND xtype
=
'D'
AND
name
LIKE
'%ColumnName%')
EXECUTE
sp_executesql
@SelectStatement
|
|
|