SQL Server, select all date but not Saturday and Sunday from given dates

DECLARE @OnlineRegTokenPerDay INT

SET @OnlineRegTokenPerDay=10

declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = GETDATE()+2
Select @dteEnd = GETDATE()+20


CREATE TABLE #working_hours (FullDates SMALLDATETIME);

while @dteStart <= @dteEnd
BEGIN
print @dteStart

   IF    datename(WEEKDAY, @dteStart) <> 'Saturday'
 AND DATENAME(WEEKDAY, @dteStart) <> 'Sunday'
 AND @dteStart not in ('2019-01-14','2019-01-26')--any special date of holiday
 BEGIN
SET @dtStart=@dteStart
insert into #working_hours values (@dtStart)
  END
   Select @dteStart = DATEADD(day,1,@dteStart)
END

Select * From #working_hours

Read More

SQL Server, Get Second and Forth Saturday list

I had a requirement to get second and forth Saturday in a month.

Here is a SQL Server query which allow to get it done.



select *
    from (
    select datein2n4,
               datename( weekday, datein2n4 ) as wkdy,
               row_number( ) over ( partition by datepart( month, datein2n4 ), datename( weekday, datein2n4 ) order by datein2n4 ) as rn_dy_mth
        from (
            select dateadd( day, rn, cast( '2017-05-29 00:00:00' as date ) ) as datein2n4
            from (
                select row_number() over( order by object_id ) - 1 as rn
                from sys.columns
                ) as rn
            ) as dy
        ) as dy_mth
    where rn_dy_mth in ( 2, 4 )
      and wkdy = 'Saturday'
    order by datein2n4

Read More

SQL Server Database connection error on Amazon RDS

Creating Database using Amazon RDS is very eary.

See detailed documentation about creating a Microsoft SQL Server DB Instance and Connecting to a DB Instance

After that you can connect SQL Server from SQL Server Management Studio.

Amazon automatically configured Inbound permission to the IP from which it is created from.
While connecting from other PC or Hosting server you will receive bellow error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

 

Also connection Asp.net website you need to apply Connection String by:




Solution : 
 You need to apply  INBOUND rules for permission to that IP

In Security Group option

You can use WhatIsMyIp.com to get Source IP address.
Amazon has its own Check IP website : http://checkip.amazonaws.com/

Thanks

Read More

SQL Server Database Publishing Wizard

Data Transfer is one kind of headache for some time in various hosting environment, SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into different  hosting environment or in development server to production server on either a SQL Server 2000, 2005 server.

It generates a all required fields SQL script in a single file which can be used to recreate a database there are three best option to generate script.

• Schema Only
• Data Only
• Schema And Data

 

This wizard can also directly upload databases to servers located at the shared hosting provider, but I would like to upload by generating script, It also helps as backup file of database.

You can start this wizard from SQL Server Folder:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe

Now, using with Visual Studio 2010, we can directly start Publishing Wizard from Server Explorer:

Just right click on database name and your will get “Publish to Provider…” menu.

 

So, have a nice stuff in Visual Studio 2010 Ultimate

Read More

Google Map Advance Search by Radius

Google map allows us to create radius in its map, user will drag and drop that radius in different location and he will able to show only those pointers which are search result in his selected criteria.
Visit this example : http://maps.forum.nu/gm_sensitive circle2.html to create radius in Google map.

Now we can get new updated radius value from JavaScript function.
We have to pass that amount to SQL Server stored procedure Below is that sql :

    CREATE PROCEDURE uspAddress_GetByRadius
        @Latitude        decimal(10,8)
        ,@Longitude        decimal(10,8)   
        ,@Radius        decimal(5,3)
    AS
    BEGIN

        DECLARE @R decimal(18,10)
        SET @R = PI()/180

        SELECT
              ACOS((SIN(@Latitude*@R)* SIN(Latitude*@R)) + (COS(@Latitude*@R)* COS(Latitude*@R) * COS(Longitude*@R - @Longitude*@R))) * 6378.137 AS Distance
              ,A.*
        FROM
              (SELECT
                     CONVERT(decimal(10,8), SUBSTRING(LMapCoordonates, 0, CHARINDEX(',',LMapCoordonates))) as Latitude
                    ,CONVERT(decimal(10,8), SUBSTRING(LMapCoordonates, CHARINDEX(',',LMapCoordonates) + 1, 100)) as Longitude
                    ,tblAddress.*
              FROM tblAddress) AS A
        WHERE      
            ACOS((SIN(@Latitude*@R)* SIN(Latitude*@R)) + (COS(@Latitude*@R)* COS(Latitude*@R) * COS(Longitude*@R - @Longitude*@R))) * 6378.137 <= @Radius
    END

We will apply Google map’s new Latitude and Longitude value and radius area to the sql server’s stored procedure and it will return only those records which are satisfy that criteria.

Read More

Change Squience Logic

Create PROCEDURE usp_MoveSequence]

@ID int,

@Sequence int,

@MoveBy varChar(4)

AS

BEGIN—PROCEDURE

SET NOCOUNT ON;

DECLARE @compateTo INT

DECLARE @compateToMax INT

DECLARE @currentSequence INT

DECLARE @NextSequence INT

DECLARE @NextRecordID INT

IF @MoveBy='UP'

Select TOP 1 @NextSequence=Sequence, @NextRecordID=ID from TableName where sequence<@Sequence ORDER BY sequence DESC

ELSE IF @MoveBy='DOWN'

Select TOP 1 @NextSequence=Sequence, @NextRecordID=ID from TableName where sequence>@Sequence ORDER BY sequence

--ENDIF

IF @NextRecordID<>0

BEGIN

UPDATE TableName SET Sequence=@NextSequence WHERE ID=@ID

UPDATE TableName SET Sequence=@Sequence WHERE ID=@NextRecordID

END

END--PROCEDURE

Read More

Fastest Data binding in Grid for Web App

I think that here is the fastest way to bind a data grid for web application.

• Login with SQL Server 2005 with Windows Authentication.

• Open your database,
• Right Click your table and click on Modify menu for which one do you want to bind.
• Select Fields which are do you want to show in browser. user Control key to select.
  

• Press Control + C to copy those column.
• Now Open a web page in devenv.
• And Click to for Control + V data binding.
• See bellow
  

All Selected Columns and Connection string will be automatically

• Press F5 to run

Result in browser.

Done. Isn't it fastest?

If you did not login with "Windows Authentication" so you have to write down password to connect that sql server in Web.config file.

Read More

How to find missing number form a table?

Please Give me the solution,

Today I had a requirement that I have to get missing number form table and start new ID form that missing value...

Ex. I have a customer table.

ID Name
1 Vijay
2 Rajesh
3 Nitin
5 Manish
6 Bipin
8 Jalpesh

Here, 4 and 7 number are missing, some one have deleted that record. and now suppose user click on add new button he want to generate that missing number.

Here a I have create a small cursor to get that value:


DECLARE @lastID int,
@crrID int

SET @crrID=1;

DECLARE curJob CURSOR FORWARD_ONLY READ_ONLY LOCAL
FOR
SELECT ID FROM Customer ORDER BY ID

OPEN curJob

FETCH NEXT FROM curJob INTO @lastID

WHILE @@FETCH_STATUS = 0 AND @crrID=@lastID
BEGIN
SET @crrID=@crrID+1
FETCH NEXT FROM curJob INTO @lastID
END

CLOSE curJob
DEALLOCATE curJob

SELECT @crrID;


Done. This SQL satisfied my requirement.

But Its now a good idea where thousands of records in a table, I think that there should be some in built functions of SQL Server 2005, who can get me those missing values without any coding and quickly also.

Do you know any other solution?

Read More

SQL Server 2005 Specifications

Here are some maximum sizes and numbers of objects in SQL Server 2005 databases or referenced in T-SQL statements.

There are two edition for SQL Server 2005 for 32-bit and 64-bit. I have show here this information regarding 32-bit version. but there are almost same.

• Bytes per short string column 8000.


• Bytes per GROUP BY or in ORDER BY 8060.


• Bytes per primary key, foreign key and index key is 900.


• Bytes in source text of a stored procedure Lesser of batch size or 250 MB.


• Clustered indexes per table 1.


• Columns in GROUP BY, ORDER BY Limited only by number of bytes.


• Columns per base table 1024.


• Columns per SELECT statement 4096.


• Columns per INSERT statement 1024.


• Database size 1048516 terabytes.


• Nested subqueries level 32.


• Nested trigger levels 32.


• No. of Parameters per stored procedure and in user-defined function is 2100 parameters.


• Rows per table all most unlimited (Limited by available storage).


• Tables per SELECT statement 256

Read More

SQL Server 2008!

Are you ready for SQL Server 2008?

"SQL Server 2008 provides a more secure, reliable and manageable enterprise data platform."

It is along with .NET Framework 3.0 will accelerate the development of the next generation of applications.

See video:

• SQL Server Platform Vision
• Enterprise Data Platform Vision
• Beyond Relational
• Dynamic Development
• SQL Server BI Vision

SQL Serer 2008 CTP, book online and Sample download list here.

Read More

Microsoft ISV buddy!

Microsoft ISV Buddy Program is all about connect with us with Microsoft.

Microsoft employee who will be our point person within Microsoft. It's about putting a face to Microsoft and helping us get timely answers to our questions and requests.

Microsoft buddy will be able to guide us to resources and help. it one kind of 1-on-1 relationship.

See Video 1, Video 2

Rajesh Nagpal at Microsoft has been chosen as Microsoft ISV buddy for me.

you can also get Microsoft ISV buddy. Register Here.

Read More

Database Answers

hi, this site provides use 450+ database designing.

there are many many different subjects which are vary interesting database schema are there. and they are also provide "We can design a Database for you." service. they also design database under SQL Server 2005.

we are going to create a ERP for a company so my friend Jalpesh decided to get idea form this one.

Click here to visit this site.

Read More

Download SQL Server Express Edition Learning Video

download 13 learning video for Sql Server Express Edition or u can watch online

it's is g8 for beginners Click Here

Read More

SQLServerDataMining.com

This site has been designed by the SQL Server Data Mining team to provide the SQL Server community with access to and information about our exciting data mining features.

• Downloads
• Newsletter
• Tutorials
• Screenshots Gallery
• Live!Samples

Click here.

Read More

debug sql

i know before this that we can debug all sql query and all kind of transction in sql server 2000 and 2005, but i don't know how to do this today i found some details about this.

We can debug every transction of sql server.

all this tool are avalable in 'Binn' folder of the sql server directory. like:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn

• Start sql server from run : sqlservr.exe
  
• Start sql server query analyzer from run : isqlw.exe
• Monitoring with SQL Profiler : profiler.exe
• show chart view for sql server from : Administrative Tools/Performance

these are important tools. suppose you are working in front end application and you want to know that which query fire to sql server you can show it.

Read More

Stored Procedure with Crystal Report passing from VB.net 2003

last few days i m working with cryatal report 9 and vb.net 2003.

i found some usefull information for crystal report and vb.net. here are some
of that. i have to create a report with some sub report. The report is using
sql stored procedure in back-end. i have to pass it's parameter from vb.net.

there is one primary report and eleven sub report. this is a new experience for
me. i use stored procedure to calculate and retrive complex data from data
base. there was very good use of stored procedure. but finaly i have to work


with crystal report and vb.net for front-end.

'Finaly i create a class to show that report. this are the referance, in that class. Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class rpt_Schema Dim TheReport As Integer Public Sub New(ByVal ReportIDX As Integer) TheReport = ReportIDX End Sub 'i create a function which returm all required informationform database for this report. and pass it in to parameter for crystal report. 'this fucntion return that report document. Private Function generateReport() As ReportDocument Dim tmpCrystalReport As New ReportDocument tmpData = generateBasicInfo() tmpCrystalReport.Load(Application.StartupPath & "\Report\rptMain.rpt") Dim crParameterDiscreteValue As ParameterDiscreteValue Dim crParameterFieldDefinitions As ParameterFieldDefinitions Dim crParameterFieldLocation As ParameterFieldDefinition Dim crParameterValues As ParameterValues ' Get the report's parameters collection. crParameterFieldDefinitions = tmpCrystalReport.DataDefinition.ParameterFields 'not i have to pass all parameters here. as follow: 'there was 36 parameters so i create this procedure "generateReport_child". 'param 1 generateReport_child("@vCenterIDX", tmpData.CenterIDX,crParameterFieldDefinitions, crParameterFieldLocation,crParameterValues) 'param 2 generateReport_child("@vYearIDX", tmpData.YearIDX, crParameterFieldDefinitions, crParameterFieldLocation, crParameterValues) ..... ..... 'finaly generateReport = tmpCrystalReport tmpCrystalReport = Nothing End Function End Function Private Sub generateReport_child( _ ByVal ParamName As String, _ ByVal ParamValue As Integer, _ ByVal crParameterFieldDefinitions As ParameterFieldDefinitions, _ ByVal crParameterFieldLocation As ParameterFieldDefinition, _ ByVal crParameterValues As ParameterValues) 'i have all arguments datatype was integer here Dim crParameterDiscreteValue As ParameterDiscreteValue crParameterFieldLocation = crParameterFieldDefinitions.Item(ParamName) crParameterValues = crParameterFieldLocation.CurrentValues crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue crParameterValues.Add(crParameterDiscreteValue) crParameterFieldLocation.ApplyCurrentValues(crParameterValues) End Sub End Class '----------------------------------- ''in form coding. just three line code. Private Sub rptPreview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'create instance Dim rpt1 As New rpt_Schema(<>) CrystalReportViewer1.ReportSource = generateReport() rpt1 = Nothing End Sub

Crystal Decisions Project Examples

VB.NET Web Sample ApplicationsVB.NET Windows Sample Applications

a ready code for sub report click here.

but i had a problem with, when i attach account report more then one time as sub
report in same primary report. i cant successfully pass parameters in that sub
report. if i will found any result for the same put that here letter.

Read More