Using CURSOR in SQL

Declare @AppId as varchar(50)

Declare @InvoiceNo as varchar(50)

declare @DueDate as datetime

DECLARE AppCURS CURSOR FOR

select cit_applicationno,new_InvoiceNo,cit_DueDate from ZmZm_MSCRM.dbo.cit_application where statuscode=912660015

OPEN AppCURS

fetch next from AppCURS into @AppId,@InvoiceNo,@DueDate

WHILE @@FETCH_STATUS = 0

BEGIN  

update AX2012R2_DB.dbo.PURCHTABLE 

set 

CRMINVIOCENUM=@InvoiceNo,

ACCOUNTINGDATE=@DueDate,

ISINVOICERECEIVED=1

where VENDORREF=@AppId

fetch next from AppCURS into @AppId,@InvoiceNo,@DueDate

END   

CLOSE AppCURS   

DEALLOCATE AppCURS

How to retrieve the value of an Optionset using SQL

OptionSet labels are stored in stored in the StringMapBase table.

SELECT 

      [ObjectTypeCode]

      ,[AttributeName]

      ,[AttributeValue]

      ,[LangId]

      ,[OrganizationId]

      ,[Value]

  FROM [StringMap]

  WHERE AttributeName = 'OptionsetName'

SQL Server Error: The instance name must be the same as computer name

To solve this proble goto this url:

http://mscrmuk.blogspot.com/2008/10/sql-server-instance-name-must-be-same.html
.......................................................................................................................................................
****************************************************************************
To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0

If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.


To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ -- where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name
Restart you computer.

How to Select from stored procedure in SQL?

We can't execute a Stored Procedure in a Select statement.You can use a User-defined function or a view instead of a procedure

 but you can copy output from Stored Procedure to temporary table as blow:

CREATE TABLE #Result

(

  Column_Name1 Nvarchar(50),  

  Column_Name2 Int

)

INSERT #Result exec Stored_Procedure_Name @Parameter1,@Parameter2

SELECT Sum(Column_Name2) as Total  FROM #Result

where "Condition Here"

DROP TABLE #Result

Sql Server Error:A connection was successfully established with the server, but then an error occurred during the login proces

To Solve this Error do the following:

1-Open SQL Server Management Studio and login

2-From Server Name right click and Select properties

3-Select Security in left menu and set server authentication to SQL Server and Windows Authentication.

3-Stop SQl service(MSSQLSERVER) and Start it again.
4- Open SQL Server Management Studio. Do not login, cancel the login dialog.
5- From the file Menu select New->Database engine query, and login(windows Authentication).
6-Execute the query ALTER LOGIN sa WITH PASSWORD = ''; to reset the password (if the sa is not enabled then type ALTER LOGIN sa ENABLE to do so).
7-Login with the sa user and add the Administrator user.