Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday 3 December 2019

TSQL to create Stored procedures as separate script in folder-

-------TSQL to create Stored procedures as separate script in folder-----------------------


select name
from sys.procedures


 select   b.name,a.definition into #1
 from sys.sql_modules a inner join sys.procedures  b on a.object_id=b.object_id



 
 while (1=1)
 Begin
 if (select count(*) from #1)=0
break
 Declare @name as varchar(1000)
 Declare @s as varchar(1000)

select top 1 * into ##2 from #1

select top 1 @name=name from ##2

set @s  = ' bcp "  select definition from ##2 " queryout "E:\records\separate SP list\' + @name + '.sql "  -c -t, -T -S'




select @s

exec xp_cmdshell @s

drop table ##2
 
Delete from #1 where  @name=name

 End

Thursday 28 November 2019

migrate 32 bit to 64 bit sql server

Pre Implementation Steps

Script out logins
Script out Linked Servers
Script out jobs
Take system database(master,model,msdb) Full Backup
Take user database Full Backup

Steps

Install new VM with Windows Server with required RAM and partition 4 drives as system and data/log and backup and tempdb
add hostname as newserver
Copy application files from oldserver to newserver
Install SQL Server 2016 Enterprise edition 64 bit  with latest SP(Service Pack) as per existing collation(Required collation)
Create logins from oldserver which we scripted out earlier
Create Linked Servers from oldserver which we scripted out earlier
Test Linked server connections
Restore user database from oldserver which we backedup
Restore msdb database from oldserver which we backedup would create jobs and schedules
Check jobs whether running or not
Configure DB mail and check connectivity
Test applications connections and functionality

Post Implementation Steps


Install ODBC/OLEDB Drivers for excel to be worked for import/export
Configure SQL Server memory as per RAM size
Configure Error log files
Create tempdb secondary files as per no of cores/processors
Configure SQL Agent history
Implementation of 32 bit to 64 bit SQL Server instance
Take Backup on system and user databases on oldserver
Stop SQL Services on old server
copy backup file  to newserver local
Restore user databases on newserver
change the IP/hostname from newservername to oldservername
restart server machine
verify hostname
Change SQL Service instance name from newservername to oldservername
Restart SQL Service
Verify SQL Server name
Check application connectivity and functionality
Check Report working fine

Monday 14 October 2019

Waiting for SQL Server to recover database 'msdb' and SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)


[393] Waiting for SQL Server to recover database 'msdb'... [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 10004, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [393] Waiting for SQL Server to recover database 'msdb'... [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 10004, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [393] Waiting for SQL Server to recover database 'msdb'... [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 10004, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [393] Waiting for SQL Server to recover database 'msdb'... [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 10004, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [393] Waiting for SQL Server to recover database 'msdb'... [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 233, Communication link failure [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01] (ConnCheckIfDBIsOnline) [393] Waiting for SQL Server to recover database 'msdb'...

Impact:

SQL agent job shows next run column as not scheduled even if you run job it shows "SQLServerAgent is not currently running so it cannot be notified of this action"

but SQL configuration manager service status shows as running and mode is automatic


Sometimes you would get this error even if the SQL Server Agent is running. In those cases, you need to check SQLAgent.out file to check if it’s started
completely.

Solution:

Something SQLAgent.out file from E:\Program Files\Microsoft SQL Server\MSSQL14.InstanceName\MSSQL\Log


we can start SQL Agent services to fix this error.

Friday 20 September 2019

Bulk load failed


Msg 5301, Level 16, State 1, Line 1

Bulk load failed. User does not have ALTER TABLE permission on table 'Table_2'. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, but the  'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified. ALTER TABLE permission is also required if the 'KEEPIDENTITY' bulk hint is specified.



Issue:

While we perform Bulk insert operation on table it will through error as requires bulkadmin and alter table permission for user

When you use bulk insert on main table it ignores constraints and disables triggers.





Permissions:
Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:
·         Constraints exist and the CHECK_CONSTRAINTS option is not specified
·         Triggers exist and the FIRE_TRIGGER option is not specified.
·         You use the KEEPIDENTITY option to import identity value from data file.


  • By default, the BULK INSERT statement ignores constraints and disables triggers.
  • By default, the INSERT…INTO statement executes check constraints and triggers.

Fix for this issue:

We can create temp table first and use this temp table in bulk insert operation then we can get the row from temp table will resolve disable triggers and permission requirement on user.






   bulk insert Table_2 from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')



create table #temp(tid int,tname varchar(50))

bulk insert #temp from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')

insert into Table_2


select * from #temp 

Friday 6 September 2019

How to find Missing index on all database on TSQL query

Missing index on all database on single TSQL query


SELECT

mid.database_id dbid,DB_NAME(mid.database_id) [DB_Name], mid.[object_id],mid.statement,

  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

  + ' ON ' + mid.statement

  + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (mid.inequality_columns, '')

  + ')'

  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

  migs.*

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
and migs.avg_user_impact >=90.00

ORDER BY DB_NAME(mid.database_id),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Monday 12 August 2019

How to install SQL Server 2017 with Docker

---------------Docker with SQL-------------


Docker commands:

docker run -d -p 8080:1433 --name docker_sql2017 -e sa_password=Test123456test -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer:2017-latest

To See docker images:

docker images

For ex:

PS C:\Users\usename> docker images

To see docker all containers:

docker ps -a

To see docker container ip address:

docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" containername

docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" docker_sql2017

To see logs of container:

Docker container logs containername

Docker container logs docker_sql2017

To login to the sql cmd:

PS C:\Users\jar> sqlcmd -U sa -S Ipaddressofserver(which we got from inspect command above)

PS C:\Users\jar> sqlcmd -U sa -S 172.31.240.15

Once logeed into to we can see version and servername as below

PS C:\Users\jar> sqlcmd -U sa -S 172.31.240.15
Password:Test123456test
1> go
1> select @@version
2> go
1> select @@servername
2> go

To create loginname with sysadmin as below:

1> create login testlogin with password='test@123';
2> go
1>
2> exec master..sp_addsrvrolemember @loginame='testlogin',@rolename='sysadmin';
3> go

Now we can go with loginto the SSMS as see below

Servername: Ipaddress,portno

Ex:
172.31.240.15,1433

Username:testlogin
Password:test@123
-------------------------------------------------------

ref:




https://www.digitalocean.com/community/tutorials/how-to-remove-docker-images-containers-and-volumes



Purging All Unused or Dangling Images, Containers, Volumes, and Networks

Docker provides a single command that will clean up any resources — images, containers, volumes, and networks — that are dangling (not associated with a container):
  • docker system prune
To additionally remove any stopped containers and all unused images (not just dangling images), add the -a flag to the command:
  • docker system prune -a

Removing Docker Images

Remove one or more specific images

Use the docker images command with the -a flag to locate the ID of the images you want to remove. This will show you every image, including intermediate image layers. When you've located the images you want to delete, you can pass their ID or tag to docker rmi:
List:
  • docker images -a
Remove:
  • docker rmi Image Image

Remove dangling images

Docker images consist of multiple layers. Dangling images are layers that have no relationship to any tagged images. They no longer serve a purpose and consume disk space. They can be located by adding the filter flag, -f with a value of dangling=true to the docker imagescommand. When you're sure you want to delete them, you can use the docker images purgecommand:
Note: If you build an image without tagging it, the image will appear on the list of dangling images because it has no association with a tagged image. You can avoid this situation by providing a tagwhen you build, and you can retroactively tag an images with the docker tag command.
List:
  • docker images -f dangling=true
Remove:
  • docker images purge

Removing images according to a pattern

You can find all the images that match a pattern using a combination of docker images and grep. Once you're satisfied, you can delete them by using awk to pass the IDs to docker rmi. Note that these utilities are not supplied by Docker and are not necessarily available on all systems:
List:
  • docker images -a | grep "pattern"
Remove:
  • docker images -a | grep "pattern" | awk '{print $3}' | xargs docker rmi

Remove all images

All the Docker images on a system can be listed by adding -a to the docker images command. Once you're sure you want to delete them all, you can add the -q flag to pass the Image ID to docker rmi:
List:
  • docker images -a
Remove:
  • docker rmi $(docker images -a -q)

Removing Containers

Remove one or more specific containers

Use the docker ps command with the -a flag to locate the name or ID of the containers you want to remove:
List:
  • docker ps -a
Remove:
  • docker rm ID_or_Name ID_or_Name

Remove a container upon exit

If you know when you’re creating a container that you won’t want to keep it around once you’re done, you can run docker run --rm to automatically delete it when it exits.
Run and Remove:
  • docker run --rm image_name

Remove all exited containers

You can locate containers using docker ps -a and filter them by their status: created, restarting, running, paused, or exited. To review the list of exited containers, use the -f flag to filter based on status. When you've verified you want to remove those containers, using -q to pass the IDs to the docker rm command.
List:
  • docker ps -a -f status=exited
Remove:
  • docker rm $(docker ps -a -f status=exited -q)

Remove containers using more than one filter

Docker filters can be combined by repeating the filter flag with an additional value. This results in a list of containers that meet either condition. For example, if you want to delete all containers marked as either Created (a state which can result when you run a container with an invalid command) or Exited, you can use two filters:
List:
  • docker ps -a -f status=exited -f status=created
Remove:
  • docker rm $(docker ps -a -f status=exited -f status=created -q)

Remove containers according to a pattern

You can find all the containers that match a pattern using a combination of docker ps and grep. When you're satisfied that you have the list you want to delete, you can use awk and xargs to supply the ID to docker rmi. Note that these utilities are not supplied by Docker and not necessarily available on all systems:
List:
  • docker ps -a | grep "pattern
Remove:
  • docker ps -a | grep "pattern" | awk '{print $3}' | xargs docker rmi

Stop and remove all containers

You can review the containers on your system with docker ps. Adding the -a flag will show all containers. When you're sure you want to delete them, you can add the -q flag to supply the IDs to the docker stop and docker rm commands:
List:
  • docker ps -a
Remove:
  • docker stop $(docker ps -a -q)
  • docker rm $(docker ps -a -q)

Removing Volumes

Remove one or more specific volumes - Docker 1.9 and later

Use the docker volume ls command to locate the volume name or names you wish to delete. Then you can remove one or more volumes with the docker volume rm command:
List:
  • docker volume ls
Remove:
  • docker volume rm volume_name volume_name

Remove dangling volumes - Docker 1.9 and later

Since the point of volumes is to exist independent from containers, when a container is removed, a volume is not automatically removed at the same time. When a volume exists and is no longer connected to any containers, it's called a dangling volume. To locate them to confirm you want to remove them, you can use the docker volume ls command with a filter to limit the results to dangling volumes. When you're satisfied with the list, you can remove them all with docker volume prune:
List:
  • docker volume ls -f dangling=true
Remove:
  • docker volume prune

Remove a container and its volume

If you created an unnamed volume, it can be deleted at the same time as the container with the -vflag. Note that this only works with unnamed volumes. When the container is successfully removed, its ID is displayed. Note that no reference is made to the removal of the volume. If it is unnamed, it is silently removed from the system. If it is named, it silently stays present.
Remove:
  • docker rm -v container_name

Thursday 25 July 2019

The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server was unable to begin a distributed transaction.


Issue:
When we tried to update table data, which uses triggers with linked servers
It will through error.




Solution:

To resolve this we should need to configure DTC(Distributed Transaction Coordinator)  Settings like below in both Source and Destination
RUN------------> dcomcnfg.exe
Expand "Component Services"
Expand "computers" >
Expand "My computer"
Expand "DTC"
Right click on the server and make the same config as below step for the "local DTC"
Place a check in the Network DTC Access and the Allow Inbound and Allow Outbound under the Transaction Manager Communication option.  We also selected No Authentication Required, as this was the way the other servers in the environment were configured.

Restart the DTC services from Services.msc





We should need to same config on source and destination
Issue fixed now

We can update data using linked server inside triggers as see below




Friday 5 July 2019

Troubleshooting - Getting error A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

[QODBC-ALL] Troubleshooting - Getting error A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Troubleshooting - Getting error A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Problem Description:

I am using QODBC to fetch QuickBooks Data in SQL Server. I have created Linked Server in SQL Server.

We are receiving the following error when trying to fetch data.

The SQL is:
SELECT * FROM [QREMOTE].[QODBC]..[Company]

The error is:
Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for linked server "QREMOTE" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Line 4
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "QREMOTE". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Solution:

You have used wrong SQL statement. You need to use LinkedServerName & table name in the query.

For Example:
Select * from YourLinkedServerName...TableName
SELECT * FROM [QREMOTE]...[Company]
 

 (0 vote(s))
 Helpful
 Not helpful