Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 10 February 2020

Msg 7321, Level 16, State 2, Line 68 An error occurred while preparing the query

Msg 7321, Level 16, State 2, Line 68
An error occurred while preparing the query "

Solution:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/083229e7-58f2-44cf-ae5d-c9acf366818c/sql-2005-ldap-query-error-msg-7321-level-16?forum=transactsql

It normally succeedes test connection generally but when you run query using this linked server to pull data from AD(Active Directory) server it will throw above issue. so issue will be between db server of linked server and AD server.

we need to make sure logins should be same on both AD server and db server
of linked server created.

LDAP means Lightweight Directory Access Protocol


First execute your query:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject'

, 'adsdatasource'

GO

Like daw21 explain before follow the detail steps:

Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

Select the option :         Be made using this security context:

and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.



(or)

First you have to make sure that Ad Hoc Distributed Queries is enabled ...

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so
SELECT



TOP 100 *

FROM



OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

Monday, 3 February 2020

To change file name of text files from SQL QUERY

----------------To change file name of text files from SQL QUERY-------------------------------------------------------------

create table #test(col   varchar(1000))
create table #files(name varchar(1000),rename varchar(1000))

Declare @path as varchar(500)='\\sharepath\filesfolder\test\'
Declare @ssql as varchar(500)='dir ' + @path
Declare @col as varchar(500)=''
Declare @col1 as varchar(500)=''

insert into #test
Exec xp_cmdshell @ssql


--------Tables

insert into #files
select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.Table','')

-------SPs

--insert into #files
--select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
--rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.StoredProcedure','')


from #test where PATINDEX('%.%',col) > 0

delete
from #files where  name in (' .',' ..')

select @col =name , @col= rename
from #files

while (1=1)
Begin
select  top 1 @col =name , @col1= rename from #files
if @col = ''
begin
break
return
End

set @ssql = 'rename  ' + @path+'\'+ ltrim(rtrim(@col))+' '+ltrim(rtrim(@col1))+''
print @ssql

Exec xp_cmdshell @ssql
delete  from #files  where @col =name
set @col = ''
End


------------file name be like dbo.Test2.Table
-------output of result dbo.Test2

Wednesday, 8 January 2020

Fix issues of line split from SQL to Excel while copy and paste

---------------------------------Fix issues of line split from SQL to Excel while copy and paste

SELECT replace(replace(columnwhichhaveissueswhilecopy, char(10), ''), char(13), '')
FROM [dbname].[dbo].[tablename]

Ref:
https://www.mssqltips.com/sqlservertip/3416/line-split-issues-when-copying-data-from-sql-server-to-excel/

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