Manish Pansiniya's Blog

.NET, C#, Javascript, ASP.NET and lots more…:)

Archive for the ‘SQL Server’ Category

Finding Max values of column for all the tables for SQL Server

leave a comment »

 

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DataType VARCHAR(50)

–Create Temp Table to Save Results
IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,MaxValue VARCHAR(50)
    ,MinValue VARCHAR(50)
    )

DECLARE Cur CURSOR
FOR
SELECT DB_Name() AS DatabaseName
    ,s.[name] AS SchemaName
    ,t.[name] AS TableName
    ,c.[name] AS ColumnName
    ,'[‘ + DB_Name() + ‘]’ + ‘.[‘ + s.NAME + ‘].’ + ‘[‘ + T.NAME + ‘]’ AS FullQualifiedTableName
    ,d.[name] AS DataType
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE  is_identity = 1 –and WHERE i.is_primary_key = 1

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ‘ Select ”’ + @DatabaseName + ”’ AS DatabaseName, ”’ + @SchemaName + ”’ AS TableName,
      ”’ + @TableName + ”’ AS SchemaName,
      ”’ + @ColumnName + ”’ AS ColumnName,
      ”’ + @DataType + ”’ AS ColumnName,
      (Select MAX(‘ + @ColumnName + ‘) from ‘ + @FullyQualifiedTableName + ‘ with (nolock))
      AS MaxValue,
      (Select MIN(‘ + @ColumnName + ‘) from ‘ + @FullyQualifiedTableName + ‘ with (nolock))
      AS MaxValue’

    PRINT @SQL

    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END

CLOSE Cur

DEALLOCATE Cur

SELECT *
FROM #Results
order by TableName
    –drop table #Results

Advertisements

Written by Manish

November 1, 2017 at 2:57 pm

Posted in SQL Server

Create Temporary Table in SQL Server

leave a comment »

if you Wanted to create temporary tables without create and all stuff.

Solutins:

Select * into #TEMPTABLENAME from TABLE

Written by Manish

September 7, 2013 at 4:47 pm

Posted in SQL Server

Microsoft.Practices.ServiceLocation.ActivationException–EnterpriseLibrary The type Database cannot be constructed

with 15 comments

While moving code first time to live server, we started to get following error. I found that the issue in my case is, I do not put providerName="System.Data.SqlClient"  in following connection string.

    <add name="XYZ" connectionString="Data Source=localhost;Initial Catalog=LondonDreams;Persist Security Info=True;User ID=dream;Password=dream" providerName="System.Data.SqlClient"/>

Once I put that in above part, it worked fine for below error.

Microsoft.Practices.ServiceLocation.ActivationException: Activation error occured while trying to get instance of type Database, key "XYZ"
—> Microsoft.Practices.Unity.ResolutionFailedException: Resolution of
—> the dependency failed, type =
"Microsoft.Practices.EnterpriseLibrary.Data.Database", name = "XYZ".
Exception occurred while: while resolving.
Exception is: InvalidOperationException – The type Database cannot be constructed. You must configure the container to supply this value.
———————————————–
At the time of the exception, the container was:
Resolving
Microsoft.Practices.EnterpriseLibrary.Data.Database,XYZ
—> System.InvalidOperationException: The type Database cannot be constructed. You must configure the container to supply this value.

Smile

Written by Manish

November 12, 2010 at 10:49 am

Countries/States/City list for development

with 2 comments

I have just needed the countries/states/city list for the development purpose. After searching a bit, I found 2 good sites for the same.

http://download.geonames.org/export/dump/

Another is for many used lists

http://www.toolsjar.com/list_generator.php

Written by Manish

September 19, 2010 at 12:46 pm

SQL Server 2005 installation problem – Error 1053 (Error in setup) – Multicore CPU issue

with one comment

Follow

http://kbalertz.com/954835/cannot-install-Server-server-number-processors-server-power.aspx

or

http://support.microsoft.com/kb/954835

For resolution to this specific error.

You need to install first SQL Server 2005 on blank machine. Install SP3.

Now, goto Binn directory of installation, take both the file mentioned in KB and keep it.

Now, start installation on the machine and when error occurs, dont quit. On dialog of retry, just overwrite both the file on Binn directory and then retry. It works for me.

If you dont have two PC, i think you should install blank SQL Server SP3 and copy the files.

Written by Manish

September 4, 2010 at 11:49 pm

float to string sql cast or convert

leave a comment »

If you directly use convert it gives you solution in terms of +e. But if we convert it to bigint first then it provide the correct conversion to string.

CONVERT(nvarchar,  CAST(floattoconvert as bigint))

You can use str function but you need to be careful as it appends 0 or space while converting the same. So it would be bit tricky to use str function.

Written by Manish

August 10, 2010 at 7:29 pm

Service Dependency deleted in Sql Server when enabling Full Text Search

leave a comment »

To resolve this error:

1. Open the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet
\Services\msftesql
2. Rename the value DependOnService to anything
3. Restart the server

Written by Manish

June 24, 2010 at 1:16 pm