How to refactor this deadlock issue?

I ran into a deadlock issue "synchronizing" a table multiple times in a short period of time. By synchronize I mean doing the following:

1. Insert data to be synchronized into a temp table


2. Update existing records in destination table


3. Insert new records into the destination table


4. Delete records that are not in the synch table under certain circumstances


5. Drop temp table

For the INSERT and DELETE statements, I'm using a LEFT JOIN similar to:

INSERT INTO destination_table (fk1, fk2, val1)
FROM #tmp
LEFT JOIN destination_table dt ON dt.fk1 = #tmp.fk1
   AND dt.fk2 = #temp.fk2
WHERE dt.pk IS NULL
;

The deadlock graph is reporting the destination_table's primary key is under an exclusive lock. I assume the above query is causing a table or page lock instead of a row lock. How would I confirm that?

I could rewrite the above query with an IN, EXIST, or EXCEPT command. Are there any additional ways of refactoring the code? Will refactoring using any of these commands avoid the deadlock issue? Which one would be the best? I'm assuming EXCEPT.

Not IN() Innaccuracy

I want to view a list of zip codes that are in list1 but not in list2 so that I can see which area was hit with which mailing (1st or 2nd). I have tried the below query but it is returning 0 records, and after a manual line by line I found at least 80 zips that were in list1 but not in list2. What is the issue with my query that caused the zips to not be returned as not in()

Select marketingtype, zip As [Area Marketed]
From campaignDB.list1
WHERE marketingtype Is Not Null
AND zip NOT IN (Select zip from campaignDB.list2)
Order By zip ASC

WHY? UNION ALL is doing a Calculation

I have two data sets: DS_A and DS_B.

My question is why I have 2 quantity for pro_id 71549 and not 3?

The ALL Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

loc_id        pro_id      quantity    price
------------- ----------- ----------- -----------
2310          5052        1           0
2365          5433        1           0
2310          7694        1           0
2310          9480        1           0
2310          9502        1           0
2310          14413       1           0
2310          31277       1           0
2310          46180       1           0
2310          65233       1           0
2310          68369       1           0
2310          68372       1           0
2310          77396       1           0


loc_id        pro_id      quantity    price
------------- ----------- ----------- -----------
2310          71549       3           0

When I do my UNION ALL

select loc_id
   , pro_id
   , sum(quantity)
   , price
from DS_A
group by loc_id, pro_id
UNION ALL
select 2310
   , 71549
   , sum(quantity)
   , price
from DS_B
group by pro_id, loc_id

Results:

loc_id        pro_id      quantity    price
------------- ----------- ----------- -----------
2310          5052        1           0
2365          5433        1           0
2310          7694        1           0
2310          9480        1           0
2310          9502        1           0
2310          14413       1           0
2310          31277       1           0
2310          46180       1           0
2310          65233       1           0
2310          68369       1           0
2310          68372       1           0
2310          77396       1           0
2310          71549       2           0

Can not find the existing data source for the SSRS report

I am trying to create a SSRS report with sql server 2005 BI. I have to use the existing data source to be consistent with the other reports, but in the SharedDataSources folder when I click add existing item, I cant find the Data source. Can someone tell me where the Data Sources for SSRS reports store physically on a machine?

Thanks

Running child package from script task on windows 2008 server

Scenario: We have an SSIS package which has a script task. This script task has a package.Execute() statement which runs another package.

This child package has an 'Execute Process task' which calls an .exe

Both these packages are built using ssis in visual studio 2005. And the parent package was scheduled to run on a Windows 2003 server.

Problem: Now, we have to migrate this job to windows 2008 server. I got SSIS 2005 installed on this system and gave an explicit path to call the package:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f "\\ustca974\Share\KC-HC\PRINCE\QA\Scheduler_Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V

The script inside the package is like:

Private Sub StartTask()
    Dim _errorMsg As System.Text.StringBuilder = New System.Text.StringBuilder("")
    Dim str_To As String
    Dim str_From As String
    Dim str_Message As String
    Dim str_Subject As String
    Dim isSimulationComplete As Boolean = False

    Try
        Dim app As Application = New Application()
        Dim _PackageVars As Variables = Nothing
        Dim _PackagePath As String = System.IO.Path.Combine(Dts.Variables("User::ExecutionPath").Value.ToString, _
            Dts.Variables("User::PkgName").Value.ToString)
        Dim pck As Package = app.LoadPackage(_PackagePath, Nothing)
        _PackagePath = _PackagePath.Replace(System.IO.Path.GetExtension(_PackagePath), ".dtsConfig")
        If (pck.Configurations.Count > 0) Then
            If System.IO.File.Exists(_PackagePath) Then
                pck.Configurations(0).ConfigurationString = _PackagePath
                pck.ImportConfigurationFile(pck.Configurations(0).ConfigurationString)
            End If
        End If

        pck.VariableDispenser.GetVariables(_PackageVars)
        For Each var As Variable In pck.Variables
            If (Not var.SystemVariable) And Dts.Variables.Contains(var.QualifiedName) Then
                pck.VariableDispenser.LockForWrite(var.QualifiedName)
                var.Value = Dts.Variables(var.QualifiedName).Value
            End If
        Next
        If (_PackageVars.Locked) Then

            _PackageVars.Unlock()
        End If
        UpdateRequestsStatus("3")
        pck.Execute()
        For Each errorObj As DtsError In pck.Errors
            _errorMsg.AppendLine("Source: " & errorObj.Source & vbNewLine & "Desc: " & errorObj.Description)
        Next
        Dts.TaskResult = pck.ExecutionResult

after migration, the job goes long running at the pck.Execute() statement. It does not throw any error. I tried on my local machine access the package on that server and it works fine. I dont have administrator access on that server so cannot run it there. I feel there is some framework or setup issue. Some thing is missing being installed. But not sure what exactly. Does anyone have any idea? Thanks a lot for the help

Update deadlock issue

I'm using SQL server 2005 and running into deadlock issues. I've begun reading up on NO LOCK, but I'm not sure that is the correct way to solve my problem. Any assistance would be greatly appreciated.

I have a batch process that is running every 15 seconds. It generates dynamic UPDATE SQL statements based off a list of foreign keys. To over simplify, imagine the below simple SQL statement:

UPDATE dual
SET val1 = @val1
WHERE fk = @fk
;

Remember this example is over simplified, for each foreign key the SQL statement is actually different, but the table it updates and the values are always the same. I cannot just write a single update statement to deal with all the foreign keys at once.

If I run each statement one at a time everything works fine, but I risk going over my 15 second interval. As a silver bullet, I decided to multi thread the batch application so it would run 25 update statements at once instead of just 1 at a time. After doing this, I begin receiving deadlock errors.

How do I solve this deadlock issue? Three things to remember:

1. The batch is the only application that will ever INSERT, UPDATE, or DELETE records from the table in question


2. Every UPDATE statement uses the foreign key in the WHERE clause, so the batch would never access the same record at once


3. If a record gets bad data, the batch would self correct it in the next run

SQL crashes when transferring a file to the server

We have a server running a few instances of SQL (2005 & 2008)

Just recently we have noticed that whenever we transfer a large file over our internal network to the server...

SQL restarts itself and puts all databases into a 'recovery' state.

Any ideas what may be causing this?

Group element by another element?

The following SQL

with t(a, b) as (select 1, 2 union all select 1, 3)
select  t.a as 'X/A/@Id',
        t.b as 'X/A/b'
from    t
for xml path(''), root('ROOT')

returns

<ROOT>
  <X>
    <A Id="1">
      <b>2</b>
    </A>
  </X>
  <X>
    <A Id="1">
      <b>3</b>
    </A>
  </X>
</ROOT>

Is it a way to group the two <b> into <a> since the Id(a) are the same? (Is it a way to do it without using subquery if possible?) Expected,

<ROOT>
  <X>
    <A Id="1">
      <b>2</b>
      <b>3</b>
    </A>
  </X>
</ROOT>

How do I mirror MS SQL 2005 Express databases to a MS SQL 2012 server Standard?

I am in the process of migrating a server containing several IIS 7.0 sites along with their associated MS SQL 2005 Express databases to a faster server running SQL 2012 and IIS 8. The current server is running Win Server 2008 R2 and the new one will be running Win Server 2012.

Is there an automated built in facility to mirror the databases between the two servers to minimize or eliminate downtime and data loss? Microsoft's documentation is quite confusing about how to do this.

Windows server 2012R2 Classic ASP Microsoft OLE DB Provider for SQL Server error '80040e4d'

I have a classic ASP application which has been moved to windows server 2012R2 two days before and sice then its showing a connection error in some sections of the application.

Microsoft OLE DB Provider for SQL Server error '80040e4d' Login failed for user 'sa'.

Login and some listing functionality are working fine but when Im accessing a billing page its showing the above mentioned error and we are using the same DB connection in the billing page also.

Following is the connection string used

Dim datasource,cnnCompanyDbase
Set cnnCompanyDbase=Server.CreateObject("ADODB.Connection")
cnnCompanyDbase.Open "Provider=SQLOLEDB;Initial Catalog=CompanyDatabase;Data Source=192.168.0.20;User Id=sa; Password=satest;"

Previously the application was running fine on windows server 2003.

Concatenate Columns using ASP, VB, and SQL

I want to select two columns (first and last name) from a database, combine them into one, and stick them into a data set to be displayed in a datagrid. I also need to add a space between them for formatting.

My normal SQL statement:

SELECT first_name + ' ' + last_name as userName from Table

My current VB statement:

strSQL = "SELECT first_name + ' ' + last_name as userName from Table"

When I attempt to do this, my application throws the following error: System.Data.SqlClient.SqlException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

EDIT: For those asking about the database and/or if this is the correct query, if I format my VB query as follows:

strSQL = "SELECT first_name + last_name as userName from Table"

I get the proper results, but then the column looks like FirstLast and is very difficult to read.

I'm guessing there's something small I'm missing on how to do this properly within VB. Can anyone advise?

Datevalue is getting appended if i try to insert only time value into database

The table schema:

CREATE TABLE [dbo].[Sales_main](
[Receipt_no] [decimal](18, 0) NOT NULL,
[Receipt_Date] [datetime] NOT NULL,
[Receipt_Time] [datetime] NOT NULL,
[User_id] [nvarchar](255) NOT NULL,
[Total_amt] [float] NOT NULL,
[Discount] [float] NOT NULL,
[To_pay] [float] NOT NULL,
[Paid] [float] NOT NULL,
[Balance] [float] NOT NULL,
[roundoff_amount] [float] NOT NULL,
[CreatedID] [varchar](50) NULL,
[CreatedDateTime] [smalldatetime] NULL,
[UpdatedID] [varchar](50) NULL,
[UpdatedDateTime] [smalldatetime] NULL)

My sql query looks like this

sql = "insert into Sales_main(Receipt_no,Receipt_Date,Receipt_Time,User_id,Total_amt,Discount,To_pay,Paid,Balance,roundoff_amount,CreatedID,CreatedDateTime) values(" + bosales.Receipt_no + ",'" + bosales.Receipt_Date.ToString("dd-MMM-yyyy") + "','" + bosales.Receipt_Time.ToString("t") + "','" + bosales.User_id + "'," + bosales.Total_amt + "," + bosales.Discount_amt + "," + bosales.Amt_tobe_paid + "," + bosales.Amt_paid + "," + bosales.Balance + "," + bosales.Roundoff + ",'" + bosales.Createdid + "','" + bosales.Createddate.ToString("dd-MMM-yyyy") + "')";

The output of this query is:

43  |27/01/2015 12:00:00 AM |01/01/1900 5:00:00 PM  |admin  |20 |0  |20 |20 |0  |0  |admin  |27/01/2015 12:00:00 AM |NULL   |NULL

I want only time value(5:00:00 PM) to be inserted rather than 01/01/1900 5:00:00 PM.

Please help me out.

I want to make stored procedure for the TABLE1 that have another table column that are not primary key in sql server

I have two table

Maker(maker_id(PK), maker_code, maker_desp)

Model(model_id(PK), model_code, model_desp, maker_id(FK))

Now I have a gridview 2 in asp.net c#

MAKER TABLE (GRIDVIEW 1):

Maker_id, Maker_code, Maker_desp

MODEL TABLE (GRIDVIEW 2):

model_id, model_code, Model_desp, maker_code

Now I want to make crud stored procedure for the MODEL TABLE that have another table MAKER TABLE column that are not primary key in sql server

PLZ... PLZ… help I spent my 3 days for finding and making solution

Meanwhile this is what which I have done

use logistics

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[maker]

(

mkid int identity(1,1) not null,

mkcd char(5) null,

desp char(15) null

CONSTRAINT [PK_maker] PRIMARY KEY CLUSTERED

( [mkid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE model(

[mdid] int identity(1,1) not null,

[mkid] int null,

[mdcd] char(5) null,

[desp] char(15) null,

CONSTRAINT [PK_model] PRIMARY KEY CLUSTERED

(

[mdid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/*******FK******/ use logistics

ALTER TABLE [dbo].[model]

WITH CHECK ADD CONSTRAINT [FK_Model_Maker]

FOREIGN KEY([mkid])

REFERENCES [dbo].[Maker] ([mkid])

GO

ALTER TABLE [dbo].[model]

CHECK CONSTRAINT [FK_Model_Maker]

GO

insert into maker(mkcd,desp)values('asd','qwerty')

select * from maker

select * from model

CREATE PROCEDURE [dbo].[model_pro]

@mdid int,  

@mkid int,  

@mkcd char(5),

@mdcd char(5),  

@desp char(15)

AS

    BEGIN

        SET NOCOUNT ON;

        SELECT

            md.mdid,

            mk.mkid,

            --mk.mkcd,

            md.mdcd,

            md.desp

        FROM model md

        left join 

        maker mk on md.mkid=mk.mkid

        insert into model(mdcd,desp)--mkcd

        values(@mdcd,@desp)--@mkcd  

  END

look now I want to mkcd and @mkcd column add in procedure for enter record in gridview or make like resultset

Sql Server 2005 Not Compatible with Windows 10

I had SQL Server 2005 working when I upgraded to Windows 10. Now it won't work. I read that it is not compatible with Windows 8, according to Microsoft. Windows 10 is but the matured version of Windows 8, to me. Is there any way I can get SQL Server 2005 to work with Windows 10?

RollBack insert to multiple tables in SQL Server 2005

I want to use Transaction and rollback first time but I am confused as if how to in my case?

I want to insert records in multiple tables and I need to use for loop to insert more than one record in one table. And also I want to delete all records from all tables if some error occurred.

Let's take 3 tables as an example:

   Insert into table1 values (a, b, c);

   for(int i = 0; i < gridview1.rows.count; i++)
   {
      Insert into table 2 values (i, b, c);
   }

   Insert into table 3 values (a, b, c);

So this is just a short example of what I want. I tried few tutorials but those seems to be for different cases and pretty easy.

I have to use SQL Server 2005 and cannot go to 2008 or above..

Thanks in advance

Edit

Currently I am doing this using multiple stored procedure (one for each table) And I want to implement a transaction in it. Using Asp.net if possible will also be ok for me.

SQL Server 2005: Confusion using XACT_ABORT=ON with TRY...CATCH

I am slightly confused about using XACT_ABORT ON together with a TRY...CATCH construct to try to rollback a transaction in the CATCH block when there is an error in the TRY block.

I have a stored procedure structured liked this (simplified here of course):

CREATE PROCEDURE dbo.usp_clean_and_re_Insert
AS
   SET XACT_ABORT ON;

   BEGIN TRY

      BEGIN TRANSACTION

      -- first clear the table
      DELETE FROM dbo.table1

      -- re-populate the table
      INSERT INTO dbo.table1
      (col1, col2, col3)
      SELECT  1
              ,dbo.fn_DoSomething('20150101')
              ,dbo.fn_DoSomething('20150123')

      COMMIT TRANSACTION

   END TRY

BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should 
--     be rolled back.
-- XACT_STATE = 0 means there is no transaction and
--     a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;
END CATCH;

So the SP is intended to work like this: if the transaction fails at any point, it should roll back. So when the insert bit fails, the delete bit should be rolled back, i.e. the table should be in the same state as before.

Now, let's say that at run-time dbo.fn_DoSomething() function is not available (it has been dropped by a DBA by mistake). The SP as written above works as expected, i.e. the transaction is rolled back and the table remains intact and the error messages displayed in SSMS look like:

"Msg 208, Level 16, State 1, Procedure usp_clean_and_re_Insert, Line 15 Invalid object name 'dbo.fn_DoSOmething'."

However for some reason the PRINT statements from the CATCH block do not seem to execute, i.e. I cannot see them in SSMS? The Microsoft documentation on TRY...CATCH says that if errors occur during execution in the TRY block, execution is passed to the CATCH block (http://ift.tt/15l960K).

If, however, I remove the XACT_ABORT ON, things get even stranger:

1. 
   

   PRINT statements still do not appear in SSMS

   
   


2. 
   

   the same errors as above are displayed correctly, i.e.

   
   

"Msg 208, Level 16, State 1, Procedure usp_clean_and_re_Insert, Line 15 Invalid object name 'dbo.fn_DoSOmething'."

3. There is a final error which says:

"Msg 266, Level 16, State 2, Procedure usp_clean_and_re_Insert, Line 52 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."

This results in the table being locked until I disconnect SSMS (the query window where the SP has run), after which the table becomes available again with all results intact (so the DB engine must rollback the uncommittable transaction implicitly).

Reading other posts about this error message (such as this one: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0), I understand that I need to check XACT_STATE in the CATCH block and roll back uncommittable transactions (which is the same advice from: http://ift.tt/15l96h4), but this is precisely what I have done in the above SP and yet the transaction does not get rolled back (without XACT_ABORT ON) until I disconnect SSMS?

I am confused! In summary:

1. 
   

   Why do I not see the PRINT statements in SSMS?

   
   


2. 
   

   Why does the ROLLBACK TRANSACTION in CATCH block not get executed when XACT_ABORT ON is removed from the stored procedure?

   
   

How to use group by query here

Id  BatchName      TblName           SubBatchName     Status
1   12032014    Tbl_12032014_raw2_1  raw2           Complete    
2   12032014    Tbl_12032014_raw2_2  raw2           Complete    
3   12182014    Tbl_12182014_raw3_1  raw3           Complete        
4   12182014    Tbl_12182014_raw3_2  raw3           Complete        
5   12182014    Tbl_12182014_raw4_1  raw4           Complete        
6   12182014    Tbl_12182014_raw4_2  raw4           Complete        
7   12062014    Tbl_12062014_raw5_1  raw5           Complete    
8   12062014    Tbl_12062014_raw5_2  raw5           Complete
9   12062014    Tbl_12062014_raw5_3  raw5           InComplete  

i want output like below

BatchName                                                                                 
--------------------
12032014  
12182014                                            

If all rows of batchname are having status complete then and then only BatchName Get select

Thanks

Difference between 'where' and 'And' in in line view query join

I had read in this thread below that there is no difference in the data returned in a query where you either have a Where predicate after the join or an 'And':

description of difference between 'where' and 'And' in join

However I do have different row counts in my query, which is using an in-line view query in my overall statement, when I change AND/WHERE. I have noticed that the issue is happening when I use a row number over statement in my in line query.

The reason I am using this is to restrict records in the in line query to only the first date record, so I can return subsequent but related records from the outer query.

However when I change the WHERE to an AND I get different number of rows.

My query is similar to this:

select distinct a.1 f,
a.2 g,
b.1 h,
b.2 i
From a inner join b ON (a.key = b.key)
inner join (select c.1,
                   c.2,
                   d.1,
                   d.2,
                   ROW_NUMBER() OVER (PARTITION BY c.1 ORDER BY d.2 ASC) Seq
            from c inner join d ON (c.key = d.key)
) e
on e.f = c.1
**and e.g <> c.2 << (this can also be 'WHERE e.g <> c.2')**
and e.Seq = 1
and e.i = d.2

When I change WHERE/AND I get slightly different figures. Where gives me slightly more but not much. the <> is the unique ID of the row so I am trying to exclude the exact same record from inner query to what is returned from outer query.

I also found out that if I remove the last condition (and e.i = d.2) and then change AND/WHERE the row count returned stays the same.

I realise that technically changing the WHERE/AND without the sequence condition is not changing the rows count but it's more that I am wondering why including the sequence and then changing the WHERE/AND then gives me different results.

What is causing this change in row count returned with changing AND/WHERE with the sequence?

EDIT: The problem really just seems to be the last condition that is having an affect on the rows count i.e. when include this in query and change WHERE/AND then I get different row count.

SQL Insert Using Records From Another Insert

I have 3 tables, as follows:

tblAgentVisit (VisitID auto-increments)
VisitID (PK) | StatusID | A bunch of other columns
--------------------------------------------------
1            | 1        | etc.
2            | 1        | etc.

tblAgentVisitAgents
VisitID | AgtID | Prime
-----------------------
1       | 8507  | 1
2       | 56    | 1

tblAgentVisitLoad
AgtID
-----
2077
3068
432

Every quarter, we take our top 20% of agents, and load their IDs into tblAgentVisitLoad.

I need to create a new, unique visit for each agent in the table. I need to INSERT my StatusID and other columns to tblAgentVisit, grab the auto-incremented VisitID (using OUTPUT?), then INSERT VisitID, the AgtID, and Prime = 1 into tblAgentVisitAgents. Again, each visit has to be unique for each agent.

Ideally, this is how my tables would look when I'm done:

tblAgentVisit (VisitID auto-increments)
VisitID (PK) | StatusID | A bunch of other columns
--------------------------------------------------
1            | 1        | etc.
2            | 1        | etc.
3            | 1        | etc.
4            | 1        | etc.
5            | 1        | etc.

tblAgentVisitAgents
VisitID | AgtID | Prime
-----------------------
1       | 8507  | 1
2       | 56    | 1
3       | 2077  | 1
4       | 3068  | 1
5       | 432   | 1

Anyone have any suggestions for doing this within MS SQL Server 2005? I'm totally stumped.

Order By not working on datetime 101 format

Create table #temp
(
OrderDate datetime
)

insert into #temp values ('01/21/2015'),('01/20/2014'),('11/12/2013')

select distinct convert(varchar(10),orderdate,101) as OrderDate  from #temp 
order by convert(varchar(10),orderdate,101) asc

The above query gives me the result like below:

OrderDate
01/20/2014
01/21/2015
11/12/2013

But I want the result like below:

OrderDate
11/12/2013
01/20/2014
01/21/2015

The above is just a sample on which I am trying to do sorting on format 101. In my actual query I need to use distinct keyword and also the columns will come dynamically in the select statement by using parameter.

I can't use group by in my actual query.

Please help.

Migrate PHP app from Mysql to sql server

i'm trying to turn a php-mysql web-app into a php-mssql one, i wonder if there is an equivalent of every single mysql function (i used plenty in ajax file for an editable table) in php for sql server, or is there another automatic way to do the migration. Thanks!

For Each SQL Loop with Output

I will be receiving a list of 250+ Agent IDs from a superior. They will all be input into tblAgentVisitLoad, which is a single column table (AgtID).

I want to take each AgtID in tblAgentVisitLoad and run the following query on them. I have comments within the query on where the loop is to be placed.

DECLARE @varVisit Table (id int)
DECLARE @varVisitID Int
DECLARE @varDate DateTime
DECLARE @varCreated Int
DECLARE @varAssigned Int
DECLARE @varAgtID Int

Set @varDate = '3/31/2015' -- This is the DEADLINE DATE to change quarterly
Set @varCreated = 193 -- Created By ID
Set @varAssigned = 194 -- Assigned staff member ID

-- For each value in tblAgentVisitLoad
Set @varAgtID = tblAgentVisitLoad.AgtID -- This will be what is dynamically set

INSERT INTO tblAgentVisit (StatusID, DeadlineDate, Objective, ScheduledDate, Location, Summary, Created_user_id, assignedStaff)
     OUTPUT Inserted.VisitID INTO @varVisit
     VALUES
       (1 --Scheduled
       ,@varDate
       ,''
       ,''
       ,''
       ,''
       ,@varCreated
       ,@varAssigned)

Set @varVisitID = (Select Id from @varVisit)

INSERT INTO tblAgentVisitAgents (VisitID, AgtID, Prime, Interests, referred, years, bussize)
    VALUES
    (@varVisitID
    ,@varAgtID -- This is where the loop will need to be entered
    ,1
    ,''
    ,''
    ,''
    ,'')

I'm not sure if there's a FOR EACH loop I can use here, or if CURSOR is my best bet. Can anyone assist me?

Query to get the information of Databases used by Stored Procedure in SQL server 2005

Is there any query in SQL server 2005 that returns the list of Stored procedures in the particular database along with the name of databases whose objects are getting used in the stored procedure.

SQL display record that occurs after the most recent of a specific date type in a field [duplicate]

This question already has an answer here:

• Create a SQL query to retrieve most recent records 4 answers

I have a set of records about a van (serialnumber, job number, job type, and current mileage).

select serial_number, call_ref, call_type_description, callm_data19
from calls
left join equipment on link_to_equipment=equipment_code
inner join lu_call_types on call_type=call_type_code
left join call_more on call_ref=callm_link_to_call
where serial_number = 'SH12ZLN'
order by call_ref desc

I want to be to able to just show the most recent "van inspection" record that occurs after the most recent "van service" record.

here is a snapshot of the data

SH12ZLN 3023152 Van Inspection  83980
SH12ZLN 3019319 Van Inspection  83046
SH12ZLN 3016999 Van Servicing   NULL
SH12ZLN 3016346 Van Inspection  81818
SH12ZLN 3012977 Van Inspection  80742
SH12ZLN 3010435 Van Inspection  79909
SH12ZLN 3008528 Van Repairs NULL
SH12ZLN 3006880 Van Inspection  78577
SH12ZLN 3001942 Van Inspection  76974
SH12ZLN 2998209 Van Inspection  75976
SH12ZLN 2994475 Van Inspection  75285
SH12ZLN 2991756 Van Repairs NULL
SH12ZLN 2989642 Van Inspection  74408
SH12ZLN 2985795 Van Inspection  73642
SH12ZLN 2981952 Van Inspection  72838
SH12ZLN 2978257 Van Inspection  72011
SH12ZLN 2975667 Van Inspection  70692
SH12ZLN 2972244 Van Inspection  69732
SH12ZLN 2969157 Van Inspection  68821
SH12ZLN 2959335 Van Inspection  67891
SH12ZLN 2956295 Van Inspection  66994
SH12ZLN 2948516 Van Inspection  66481
SH12ZLN 2946213 Van Inspection  65778
SH12ZLN 2939497 Van Inspection  64408
SH12ZLN 2937538 Van Inspection  63765
SH12ZLN 2934421 Van Inspection  62937
SH12ZLN 2932707 Van Inspection  61645
SH12ZLN 2930711 Van Inspection  60713
SH12ZLN 2930023 Van Inspection  59683
SH12ZLN 2924989 Van Inspection  58372
SH12ZLN 2924830 Van Repairs NULL
SH12ZLN 2922412 Van Inspection  57474
SH12ZLN 2919005 Van Servicing   NULL
SH12ZLN 2918376 Van Repairs NULL

list all ACTIVE databases that are mark for replication

i'm trying for figure out a way to list all ACTIVE databases that are mark for replication. I used two codes but they are not that good. 1.

select * from [distribution].[dbo].[MSpublications]

In this code you will see all databases that have been replicated even that one that are not replicated at the moment but used to be.

2.

select * from sys.databases where is_published=1 or is_subscribed=1 or is_merge_published=1

Well this code is not good for a lot of reasons :)

Thank you for your help !

Dollars Earned after running totals roll over

This is a continuation of my earlier question. After some assistance, I was able to correctly return a running total of carpool trips for each employee. Now, I need to add how money the employee earns for the quarter. For each 20 trips, they earn $30, with anything over mod of 20 rolling over to the next quarter. My old method updated the value in a temp table. Here is my current query:

declare @employeeID NVarChar(100), @year Char(4)
Set     @year = '2014'
------
declare @startDate DateTime, @endDate DateTime
Set     @startDate = '1/1/' + @year
Set     @endDate = DateAdd(d,-1,DateAdd(yyyy,1,@startDate))


;WITH cte
     AS (SELECT Datepart(qq, e.eventDate) AS quarterNum,
     CASE DatePart(qq, e.eventDate) 
                WHEN 1 THEN 'Jan-Mar' 
                WHEN 2 THEN 'Apr-Jun' 
                WHEN 3 THEN 'Jul-Sep' 
                WHEN 4 THEN 'Oct-Dec' END AS quarter,
                e.employeeID,
                Sum(t.value) AS trips
         FROM   events e
         LEFT JOIN types t ON t.typeID = e.eventType
         WHERE e.eventType = 'CP' AND Year(eventDate) = @year
         GROUP  BY Datepart(quarter, e.eventDate), e.employeeID)

SELECT a.quarter, a.employeeID, nta.DisplayName AS employee, trips,  
           (SELECT Sum(trips)
            FROM   cte b
            WHERE  a.employeeID = b.employeeID
                   AND a.quarterNum >= b.quarterNum) AS runningTripsTotal,

           0 AS runningEarned

FROM   cte a 
LEFT JOIN SBAIntranet.dbo.NTAuth nta ON 'SBA\' + a.employeeID = nta.AccountName
ORDER BY a.employeeID, a.quarterNum

running total for each employee

I have the following query which works great to return a running total of carpool reimbursements for an individual staff member. (employee earns $30 for each 20 trips, trips roll over until the end of the year).

-- carpool quarter stats
use TRPTracking

declare @employeeID NVarChar(100), @year Char(4)
Set     @employeeID = 'PSmith'
Set     @year = '2014'
------
declare @startDate DateTime, @endDate DateTime
Set     @startDate = '1/1/' + @year
Set     @endDate = DateAdd(d,-1,DateAdd(yyyy,1,@startDate))

DECLARE @calendar TABLE (Date datetime)

WHILE (@startDate <= @endDate) BEGIN
    INSERT INTO @Calendar VALUES (@startDate)
    SET @startDate = DATEADD(quarter, 1, @startDate)
END

DECLARE @CarpoolTbl TABLE (quarter varchar(250), value decimal(18,1), runningTotal decimal(18,1), earned money)
DECLARE @runningTotal decimal(18,1), @earned money
SET @runningTotal = 0
SET @earned = 0

INSERT INTO @CarpoolTbl 
    SELECT CASE DatePart(q, c.date) 
        WHEN 1 THEN 'Jan-Mar' 
        WHEN 2 THEN 'Apr-Jun' 
        WHEN 3 THEN 'Jul-Sep' 
        WHEN 4 THEN 'Oct-Dec' END AS quarter,
        IsNULL(Sum(t.value),0) AS value,
        null,
        0
    FROM @calendar c
    LEFT OUTER JOIN events e ON (DatePart(q, c.date) = DatePart(q, e.eventDate) AND e.employeeID = @employeeID AND e.eventType = 'CP' AND Year(eventDate) = @year)
    LEFT JOIN types t ON t.typeID = e.eventType
    GROUP BY DatePart(q, c.date)

UPDATE @CarpoolTbl 
SET @earned = earned = Floor((@runningTotal + value)/20) - Floor(@runningTotal/20),
    @runningTotal = runningTotal = @runningTotal + value

FROM @CarpoolTbl

SELECT quarter, value, runningTotal, earned * 30 AS earned
FROM @CarpoolTbl

Now, I want a query that returns this information for all employees. I remove the portion that relates to employeeID and I get what looks to be good. But... what is happening is my running total is running for everyone. I need it to restart for each employee. I can't quite figure out where to add the employeeID grouping in the running total.

-- carpool quarter stats
use TRPTracking

declare @year Char(4)
Set     @year = '2014'
------
declare @startDate DateTime, @endDate DateTime
Set     @startDate = '1/1/' + @year
Set     @endDate = DateAdd(d,-1,DateAdd(yyyy,1,@startDate))

DECLARE @calendar TABLE (Date datetime)

WHILE (@startDate <= @endDate) BEGIN
    INSERT INTO @Calendar VALUES (@startDate)
    SET @startDate = DATEADD(quarter, 1, @startDate)
END

DECLARE @CarpoolTbl TABLE (dateQ int, quarter varchar(250), employeeID varchar(255), value decimal(18,1), runningTotal decimal(18,1), earned money)
DECLARE @runningTotal decimal(18,1), @earned money
SET @runningTotal = 0
SET @earned = 0

INSERT INTO @CarpoolTbl 
    SELECT 
        DatePart(q, c.date),
        CASE DatePart(q, c.date) 
            WHEN 1 THEN 'Jan-Mar' 
            WHEN 2 THEN 'Apr-Jun' 
            WHEN 3 THEN 'Jul-Sep' 
            WHEN 4 THEN 'Oct-Dec' END AS quarter,
        e.employeeID,
        IsNULL(Sum(t.value),0) AS value,
        null,
        0
    FROM @calendar c
    LEFT OUTER JOIN events e ON (DatePart(q, c.date) = DatePart(q, e.eventDate) 
    AND e.eventType = 'CP' AND Year(eventDate) = @year)
    LEFT JOIN types t ON t.typeID = e.eventType
    GROUP BY e.employeeID, DatePart(q, c.date)

UPDATE @CarpoolTbl 
SET @earned = earned = Floor((@runningTotal + value)/20) - Floor(@runningTotal/20),
    @runningTotal = runningTotal = @runningTotal + value

FROM @CarpoolTbl

SELECT c.quarter, c.employeeID, a.DisplayName AS employee, c.value AS trips, earned *  30 AS earned
FROM @CarpoolTbl c
LEFT JOIN SBAIntranet.dbo.NTAuth a ON 'SBA\' + c.employeeID = a.AccountName
ORDER BY dateQ, employeeID

Any thoughts?

Argument data type text is invalid for argument of len

I don't understand why there's a such error there (marked in the code below).

I've tried to put this on comment but the error still occurs at line 64. I'm using sql server 2005.

Error :

Msg 8116, Level 16, State 1, Procedure prSwiftGetMessages, Line 64
Argument data type text is invalid for argument 1 of len function.

Code :

delete from Messages where LOAD_DATE >= @minDate print 'Deleted : ' + convert(varchar, @@ROWCOUNT)

print 'Date MIN = ' + convert(varchar, @minDate,102)

print 'Dropping Indexes.' 
if exists (select 'x' from sysindexes where name = 'idxMessagesReference')
      drop index Messages.idxMessagesReference 
if exists (select 'x' from sysindexes where name = 'idxMessagesLoadDate')
      drop index Messages.idxMessagesLoadDate 
if exists (select 'x' from sysindexes where name = 'idxMessagesBankTo')
      drop index Messages.idxMessagesBankTo 
if exists (select 'x' from sysindexes where name = 'idxMessagesBankFrom')
      drop index Messages.idxMessagesBankFrom 
if exists (select 'x' from sysindexes where name = 'idxMessagesTo')
      drop index Messages.idxMessagesTo -- ERROR HERE !! 
if exists (select 'x' from sysindexes where name = 'idxMessagesFrom')
      drop index Messages.idxMessagesFrom 
if exists (select 'x' from sysindexes where name = 'idxMessagesEnv')
      drop index Messages.idxMessagesEnv print 'Indexes Dropped'

How to replace all numbers of exactly 8 characters in length eg 12345678

I've done a good bit of searching all over so don't berate me yet.

I have a column with string values showing the name of shows.

Eg:

[Titles]

World Cup 2014

Family Guy

UFC Fight Night

9pm News and Weather

2014 Media Awards

Homeland 2524232

Simpsons 2524231

Shameless

Soccer Night 4534232 International

Rugby Live 4534232 HTML5

I wish to use a select statement to strip out the numbers where the numbers are exactly 8 characters in length.

I have only read access so cannot create functions and im using SQL Server 2005.

Cheers.

An unhandled exception of type 'System.StackOverflowException' occurred in System.Windows.Forms.dll during connect to sqlserver

Need help on this error, could not find out where goes wrong..

` Imports System.Windows.Forms Imports System.Data.Sql Imports System.Data.SqlClient Imports System.Configuration

Public Class Celclass

    Dim conn As SqlConnection
    Dim cmd, cmd1, cmd2, cmd3 As SqlCommand
    Dim da, da1, da2, da3 As SqlDataAdapter
    Dim ds, ds1, ds2, ds3 As DataSet
    Dim dt, dt1, dt2, dt3 As DataTable
    Dim dr, dr1, dr2, dr3 As DataRow
    Dim scb, scb1, scb2, scb3 As SqlCommandBuilder

Public Sub conn1(ByVal sql As String, ByVal dsname As String)
    conn = New SqlConnection()
    conn.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")
    cmd = New SqlCommand(sql, conn)
    da = New SqlDataAdapter
    da.SelectCommand = cmd
    ds = New DataSet
    da.Fill(ds, dsname)
End Sub

Public Sub conn2(ByVal sql As String, ByVal dsname As String)
    conn = New SqlConnection()
    conn.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")
    cmd1 = New SqlCommand(sql, conn)
    da1 = New SqlDataAdapter
    da1.SelectCommand = cmd1
    ds1 = New DataSet
    da1.Fill(ds1, dsname)
End Sub

Public Sub conn3(ByVal sql As String, ByVal dsname As String)
    conn = New SqlConnection()
    conn.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")
    cmd2 = New SqlCommand(sql, conn)
    da2 = New SqlDataAdapter
    da2.SelectCommand = cmd2
    ds2 = New DataSet
    da2.Fill(ds2, dsname)
End Sub

Public Sub conn4(ByVal sql As String, ByVal dsname As String)
    conn = New SqlConnection()
    conn.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")
    cmd3 = New SqlCommand(sql, conn)
    da3 = New SqlDataAdapter
    da3.SelectCommand = cmd3
    ds3 = New DataSet
    da3.Fill(ds3, dsname)
End Sub

Public Sub clear()
    txtPart.ReadOnly = True
    txtMpn.ReadOnly = True
    txtdc.ReadOnly = True
    txtQty.ReadOnly = True
    txtPO.ReadOnly = True
    cmbCoo.SelectedIndex = -1
    cmbCoo.Text = ""
    txtPart.Text = ""
    txtMpn.Text = ""
    txtBatch.Text = ""
    txtdc.Text = ""
    txtQty.Text = ""
    txtPO.Text = ""
    chk.Checked = False
End Sub

Private Sub txtJob_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtJob.TextChanged
    txtJob.Text = Trim(txtJob.Text)
    conn1("select p.qty, p.lot, p.mpn, p.dc1, p.dc2, p.dc3, s.raw, s.so_raw, s.prog, s.po, p.job_no from prod_pt p inner join pln_so s on p.so_id=s.so_id where p.job_no='" & txtJob.Text & "'", "pt")
    If ds.Tables("pt").Rows.Count > 0 Then
        dr = ds.Tables("pt").Rows(0)
        If dr("po") = "" Then
            MsgBox("No PO Key in!")
            txtJob.Text = ""
            Exit Sub
        End If
        Dim fulldc As String = ""
        fulldc = fulldc + dr("dc1")
        If UCase(dr("dc2")) <> "NA" And dr("dc2") <> "" Then
            fulldc = fulldc + "/" + dr("dc2")
        End If
        If UCase(dr("dc3")) <> "NA" And dr("dc3") <> "" Then
            fulldc = fulldc + "/" + dr("dc3")
        End If
        txtJob.Text = UCase(dr("job_no"))
        If dr("prog").Equals(System.DBNull.Value) = False Then
            If dr("prog") <> "" Then
                txtPart.Text = UCase(dr("prog"))
            Else
                txtPart.Text = UCase(dr("so_raw"))
            End If
        Else
            txtPart.Text = UCase(dr("so_raw"))
        End If
        txtMpn.Text = UCase(dr("mpn"))
        txtdc.Text = fulldc
        txtQty.Text = dr("qty")
        txtPO.Text = UCase(dr("po"))
        txtBatch.Focus()
    End If
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    Dim m As Form
    m = Main
    Me.Close()
    m.Show()
End Sub

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
    txtPart.ReadOnly = False
    txtMpn.ReadOnly = False
    txtdc.ReadOnly = False
    txtQty.ReadOnly = False
    txtPO.ReadOnly = False
End Sub

Public Sub insert(ByVal qty As String)
    Dim x As Integer
    Dim coo As String
    If chk.Checked = True Then
        x = 2
    Else
        x = 1
    End If

    If cmbCoo.Text = "GERMANY" Then
        coo = "DE"
    ElseIf cmbCoo.Text = "JAPAN" Then
        coo = "JP"
    ElseIf cmbCoo.Text = "MALAYSIA" Then
        coo = "MY"
    ElseIf cmbCoo.Text = "PAPUA NEW GUINEA" Then
        coo = "PG"
    ElseIf cmbCoo.Text = "SINGAPORE" Then
        coo = "SG"
    ElseIf cmbCoo.Text = "UNITED STATES" Then
        coo = "US"
    Else
        coo = cmbCoo.Text
    End If

    For i As Integer = 1 To x
        conn4("select * from label_celclass", "insert")
        dt3 = ds3.Tables("insert")
        dr3 = dt3.NewRow
        dr3("part") = txtPart.Text
        dr3("mpn") = txtMpn.Text
        dr3("batch") = txtBatch.Text
        dr3("dc") = txtdc.Text
        dr3("qty") = qty
        dr3("po") = txtPO.Text
        dr3("coo") = coo
        dt3.Rows.Add(dr3)
        scb3 = New SqlCommandBuilder(da3)
        da3.InsertCommand = scb3.GetInsertCommand
        da3.Update(ds3, "insert")
        ds3.AcceptChanges()
    Next
End Sub

Public Sub delete()
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
    Dim str As String = "DELETE FROM label_celclass"
    Dim cmd As SqlCommand = New SqlCommand(str, con)
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
End Sub

Public Sub printing()
    conn1("select * from label_celclass", "others")
    Dim retval
    retval = Shell(Config.BarTenderFile & " /F=C:\barcode\cele.btw /P /X", vbMinimizedFocus)
    'telford
    'retval = Shell("C:\Program Files\Seagull\BarTender 6.20\Enterprise\bartend.exe /F=C:\barcode\cele.btw /P /X", vbMinimizedFocus)
    'cele
    'retval = Shell("C:\Program Files\Seagull\BarTender\7.75\bartend.exe /F=C:\barcode\cele.btw /P /X", vbMinimizedFocus)
    'jabil
    'retval = Shell("C:\Program Files\Seagull\BarTender\8.00\bartend.exe /F=C:\barcode\cele.btw /P /X", vbMinimizedFocus)
End Sub

Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
    If txtBatch.Text = "" Then
        MsgBox("Please key in Batch No")
        txtBatch.Focus()
        Exit Sub
    End If
    If cmbCoo.Text = "" Then
        MsgBox("Please select COO")
        cmbCoo.Focus()
        Exit Sub
    End If
    Dim qty, part, reel As Integer
    delete()
    conn1("select * from production_oqc where job_no='" & txtJob.Text & "' and status='Accept'", "oqc")
    If ds.Tables("oqc").Rows.Count > 0 Then 'oqc record found
        For i As Integer = 0 To ds.Tables("oqc").Rows.Count - 1
            dr = ds.Tables("oqc").Rows(i)
            qty = dr("qty_per_reel")
            insert(CStr(qty))
        Next
    Else 'no oqc record found
        conn2("select s.fa_ref_no, s.revision, s.pkg, s.lead from pln_so s inner join prod_pt p on s.so_id=p.so_id where p.job_no='" & txtJob.Text & "'", "fa")
        dr1 = ds1.Tables("fa").Rows(0)
        If dr1("fa_ref_no") <> "" Then 'get from fa taping
            conn3("select * from fa_taping where fa_ref_no='" & dr1("fa_ref_no") & "' and revision='" & dr1("revision") & "'", "taping")
            If ds2.Tables("taping").Rows.Count > 0 Then 'fa taping record found
                dr2 = ds2.Tables("taping").Rows(0)
                qty = dr2("qty_per_reel")
                If CInt(txtQty.Text) > qty Then 'more than 1 reel
                    part = CInt(txtQty.Text) Mod qty
                    reel = (CInt(txtQty.Text) - part) / qty
                    For j As Integer = 0 To reel - 1
                        insert(CStr(qty))
                    Next
                    If part <> 0 Then
                        insert(CStr(part))
                    End If
                Else
                    insert(txtQty.Text)
                End If
            Else 'no fa taping record found, use fa taping mast
                conn4("select * from fa_taping_mast where pkg='" & dr1("pkg") & "' and lead='" & dr1("lead") & "' and status='Approved'", "mast")
                If ds3.Tables("mast").Rows.Count > 0 Then 'fa taping mast record found
                    dr3 = ds3.Tables("mast").Rows(0)
                    qty = dr3("qty_per_reel")
                    If CInt(txtQty.Text) > qty Then 'more than 1 reel
                        part = CInt(txtQty.Text) Mod qty
                        reel = (CInt(txtQty.Text) - part) / qty
                        For k As Integer = 0 To reel - 1
                            insert(CStr(qty))
                        Next
                        If part <> 0 Then
                            insert(CStr(part))
                        End If
                    Else
                        insert(txtQty.Text)
                    End If
                Else 'no fa taping mast record found
                    insert(txtQty.Text)
                End If
            End If
        Else 'get from fa taping mast
            conn3("select * from fa_taping_mast where pkg='" & dr1("pkg") & "' and lead='" & dr1("lead") & "' and status='Approved'", "taping_mast")
            If ds2.Tables("taping_mast").Rows.Count > 0 Then 'fa taping mast record found
                dr2 = ds2.Tables("taping_mast").Rows(0)
                qty = dr2("qty_per_reel")
                If CInt(txtQty.Text) > qty Then 'more than 1 reel
                    part = CInt(txtQty.Text) Mod qty
                    reel = (CInt(txtQty.Text) - part) / qty
                    For l As Integer = 0 To reel - 1
                        insert(CStr(qty))
                    Next
                    If part <> 0 Then
                        insert(CStr(part))
                    End If
                Else '1 reel
                    insert(txtQty.Text)
                End If
            Else 'no fa taping mast record found
                insert(txtQty.Text)
            End If
        End If
    End If
    printing()
    clear()
    txtJob.Text = ""
    txtJob.Focus()
End Sub

Private Sub btnPaper_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPaper.Click
    Dim p As Form
    p = Celclass_papertaping
    Me.Close()
    p.Show()
End Sub

End Class

error on

Private Sub txtJob_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtJob.TextChanged conn1("select p.qty, p.lot, p.mpn, p.dc1, p.dc2, p.dc3, s.raw, s.so_raw, s.prog, s.po, p.job_no from prod_pt p inner join pln_so s on p.so_id=s.so_id where p.job_no='" & txtJob.Text & "'", "pt")

How to get unique records in the SQL Server 2000

I have 4 tables

 Employeetab
Employeeid     EmployeeName
   1               xyz
   2               abc
   3               mno
   4               pqr
PurchaseRequesttab
PRID    Employeeid     PRNR
1          1           pr1

 Statustab
StatusID         Statusmsg
  1          approve by manager
  2          approve by CC manager
  3          approve by designer
  4          approve by BU head
 ApprovalStatustab
PRID    StatusID   Employeeid     ApprovalDate
1           1           3           jan 1 2015
1           2           3           jan 3 2015
1           4           4           Jan  5 2015

Result should be

        PRNR           EmployeeName           Statusmsg 
        1                 mno             approve by CC manager 

Below is my query, which gives duplicate

 Select distinct P.PRNR, EmployeeName, Statusmsg from  Employeetab as E 
 inner join PurchaseRequesttab AS P  ON  E. Employeeid   = p. Employeeid  
 inner join  ApprovalStatustab  as A ON P. PRID    = A. PRID    
 inner join  Statustab as S  on A. StatusID    = S. StatusID   

How to get unique records in the result set

insert into existing temp table in sql server

I have created a temp table in sql server and inserted values to it. If the values of a particular column are null i need to fill using some values from another table. How to query this?

sample data given below.

select 'name' as name,3 as age,'email' as email into #tmp1 from table1

Now if the column age is empty i need to insert a value to the column age to all the existing records in tmp1.

INSERT INTO #tmp1 (age)SELECT age AS [age] FROM table2 WHERE name=@name 

But it inserts a new record.

please help.

inno setup sql server 2005 and after updating database

I want to use silent install of sql server 2005 express and after I want to update database. I used inno setup script wizard and I selected sqlserversetup.exe as installing application and the sql file which I want to update database after installing the sql server 2005 express."emin.sql" is a file that I want to update database after installing sql server.

Here is my code which is generated by wizard;

; Script generated by the Inno Setup Script Wizard.
; SEE THE DOCUMENTATION FOR DETAILS ON CREATING INNO SETUP SCRIPT FILES!

#define MyAppName "My Program"
#define MyAppVersion "1.5"
#define MyAppPublisher "My Company, Inc."
#define MyAppURL "http://www.example.com/"
#define MyAppExeName "SQLEXPR.EXE"

[Setup]
; NOTE: The value of AppId uniquely identifies this application.
; Do not use the same AppId value in installers for other applications.
; (To generate a new GUID, click Tools | Generate GUID inside the IDE.)
AppId={{B6B8AFF5-6C3D-4673-8179-EE9C5C0A5072}
AppName={#MyAppName}
AppVersion={#MyAppVersion}
;AppVerName={#MyAppName} {#MyAppVersion}
AppPublisher={#MyAppPublisher}
AppPublisherURL={#MyAppURL}
AppSupportURL={#MyAppURL}
AppUpdatesURL={#MyAppURL}
DefaultDirName={pf}\{#MyAppName}
DefaultGroupName={#MyAppName}
OutputBaseFilename=setup
Compression=lzma
SolidCompression=yes

[Languages]
Name: "english"; MessagesFile: "compiler:Default.isl"

[Tasks]
Name: "desktopicon"; Description: "{cm:CreateDesktopIcon}"; GroupDescription: "{cm:AdditionalIcons}"; Flags: unchecked

[Files]
Source: "C:\SQL Server 2005 Express\SQLEXPR.EXE"; DestDir: "{app}"; Flags: ignoreversion
Source: "C:\emin.sql"; DestDir: "{app}"; Flags: ignoreversion
; NOTE: Don't use "Flags: ignoreversion" on any shared system files

[Icons]
Name: "{group}\{#MyAppName}"; Filename: "{app}\{#MyAppExeName}"
Name: "{commondesktop}\{#MyAppName}"; Filename: "{app}\{#MyAppExeName}"; Tasks: desktopicon

[Run]
Filename: "{app}\{#MyAppExeName}"; Description: "{cm:LaunchProgram,{#StringChange(MyAppName, '&', '&&')}}"; Flags: nowait postinstall skipifsilent

The options to replicate a secondary read-only copy of a big database with limited network connection?

There is a big database on remote server. A read-only replicate is required on a local server. The data can only be transferred via FTP, etc. It's ok to replicate it once a day.

Logshipping is an option. However, it need to kill all the connections when doing restoring. What's the other options (pros/cons)?

SSIS AcquireConnection Error during Package Installation Validation

I'm trying to release a couple of SSIS packages to production but I'm getting an error on the AquireManager method of one of the ConnectionManagers.

Details:

• 2005 SSIS package extract info from Sybase and stage into SQL Server 2005 (x64)


• Connection Manager details: Provider : Native OLE DB\Sybase ASE OLE DB Provider. Server Connection : SQL Server Authentication. DelayValidation : true


• Server Confg: Windows Server 2008 R2 (64bits)


• Deployment Path: C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSIS


• Getting the following error when SSIS Installation Wizard is doing the final Validation:

Error: SSIS Error Code DTS_E_CANNOTAQUIRECONNECTIONFROMCONNECTIONMANAGER. The AquireConnection method call to the connection manager "" failed with error 0x0202009.

Error: component "Database Table" (1798) failed validation and returned error code 0xC020801C.

Any clue of what I'm missing on the release process? Anything really stupid in front of my eyes I'm not realizing?. All the contributions are welcome.

Thanks in advance,

Script or SQL Query to export SQL Server tables to Excel 2010

I have to extract data from around 5 databases to excel 2010. Each data base having around 300 tables. I want each tables to be 1 tables. So I will have around 300 sheets in one excel file. I will have 8 excel files.

I tried the query but ended up with error:

Query:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:\ExcelExport\testing.xlsx;', 
'SELECT * FROM [Sheet1$]') 
select * from dbo.BUDGET

Error:

 OLE DB error trace [Non-interface error:  Provider not registered.].
 Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.

Note: I refered this to get connection string; http://ift.tt/1ypqcFe

How can I reslove this?

Then,I tried this using improt export wizard. However, I'm getting below error.

Operation stopped...

 - Initializing Data Flow Task (Success)

 - Initializing Connections (Success)

- Setting SQL Command (Success)

 - Setting Source Connection (Success)

  - Setting Destination Connection (Success)

 - Validating (Success)

 - Prepare for Execute (Success)

 - Pre-execute (Error)
Messages
* Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE       DB error has occurred. Error code: 0x80040E21.
 (SQL Server Import and Export Wizard)

* Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
 (SQL Server Import and Export Wizard)

* Error 0xc004701a: Data Flow Task 1: component "Destination - ACCOUNT" (145)      failed the pre-execute phase and returned error code 0xC0202025.
 (SQL Server Import and Export Wizard)


  - Copying Rows (Warning)
Messages
* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)

* Warning: Preparation SQL Task 1: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (SQL Server Import and Export Wizard)


 - Post-execute (Stopped)
Messages
* Information 0x4004300b: Data Flow Task 1: "component "Destination - ACCOUNT" (145)" wrote 0 rows.
 (SQL Server Import and Export Wizard)

* Information 0x4004300b: Data Flow Task 1: "component "Destination 1 - ACCOUNTADDR" (426)" wrote 0 rows.
 (SQL Server Import and Export Wizard)

* Information 0x4004300b: Data Flow Task 1: "component "Destination 2 - ACCOUNTASSO" (547)" wrote 0 rows.
 (SQL Server Import and Export Wizard)

* Information 0x4004300b: Data Flow Task 1: "component "Destination 3 - ACCOUNTCON" (653)" wrote 0 rows.
 (SQL Server Import and Export Wizard)

* Information 0x4004300b: Data Flow Task 1: "component "Destination 4 - ACCOUNTTYPE" (752)" wrote 0 rows.

I want to export all data at a time to one excel file very quickly.

How Can I slove this? Please help.

sql server 2005 connection open error - An existing connection was forcibly closed by the remote host

I am getting following error when trying to open a connection to SQL Server 2005. I am using .Net SqlConnection class and the error is thrown when open() is called. My code runs properly from local machine but throws error from the our test server. The test server is running IIS 6.0.

Error:

System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Portal.Data.SqlDataAccess..ctor(String ConString) in...

The connection string I am using is

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=servername;Packet Size=4096" providerName="System.Data.SqlClient"

Thanks

How to create a new SQL Database on different drive

I have installed SSMS 2014 recently on my new machine, installations are in C: but my C: is not having that much enough space, so I need to create the new database on D:. Please help me on how can I do that ?

compare a varchar column with a varchar variable does not work

I have two block of SQL statements (I am using sql server 2005):

-- 1

SELECT *
FROM Invoice
WHERE CustomerId = 'ITTEST01' AND StoreId = '01'

-- CustomerId and StoreId have VARCHAR(16) data type.

--2

BEGIN
DECLARE @pCustomerId AS VARCHAR(16)

DECLARE @pStoreId AS VARCHAR(16)

SET @pCustomerId = 'ITTEST01'
SET @pStoreId = '01'

SELECT *
FROM Invoice
WHERE CustomerId = @pCustomerId AND StoreId = @pStoreId
END

The 1st statement returns what I expect, but the 2nd block returns nothing. I am not sure why this happens as the two SQL blocks should return the same result to me? Could someone give me a hint?

Thank you.

How to perform a TRY_CAST in SQL SERVER 2005?

I'm try to perform a TRY_CAST in SQLSERVER 2005. The query will look like: SELECT TRY_CAST((select bla bla bla expression) as int),

The expression inside the try_cast might return some strings or something which cannot be converted as INT, that's why I need it (obviously :) ). Unfortunately I wrote my big query for sql server 2012, but now I need to use it on a sql server 2005.

Is there a workaround?

Thank you!

How can i select a datetime as ddMMyy, so a two-digit year?

I'm replacing my C# code for performance reasons with a sql query:

CreatedAt.ToString("ddMMyy", System.Globalization.CultureInfo.InvariantCulture)

returns the creation-date as 140114(for today). Simple enough in C#, but how do i get this in T-SQL?

I have tried:

REPLACE(CONVERT(CHAR(10), ChargeArrival.CreatedAt, 103), '/', '')

which is almost correctly, but the year has four digits instead of two, so 14012014 instead of 140114.

php access mssql 2005 database, sqlsrv drivers

I am attempting to access a mssql 2005 database using Microsoft's supplied sqlsrv drivers. I have tried every driver version from 3.1 to 2.0, installing a different version of XAMPP with different versions of PHP installed.

• version 3.1 = php version 5.4.7 error:

This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86

• version 3.0 = php version 5.3.1 error:

This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server. Neither of those ODBC Drivers are currently installed. Access the following URL to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver for x86:

• version 2.0 = php version 5.2.1 and 5.2.4

Php drivers are unrecognized and do not appear in phpinfo().

I am under the impression the extension should support ALL EDITIONS of SQL Server 2005 and greater...

I installed both the SQL Server 2012 Native Client and the SQL Server 2008 Native Client (SP3). According to this article I can test the ODBC connectivity to a SQL Server.

Entering odbcping at a command prompt returns:

'odbcping' is not recognized as an internal or external command....

I checked the ODBC Data Source Administrator and found the ODBC Driver 11 for SQL Server is listed in the drivers tab.

Any assistance as to what I may be missing?

How to access database from another PC in LAN using C#

I am making a Windows application using C# .I have two machines connected on lan. On one machine database is stored and i want to access that database from other machine .How can i do that??Please help me

Insert multiple rows into SQL Server 2005 using a stored procedure

I am working on a ASP.NET MVC / C# application.

I have a list of objects lets say List<Student> students that were imported from a CSV.

I want to insert them into a SQL Server 2005 database using stored procedures and transactions. In case a row fails it should rollback.

Any suggestions?

Sql 2005 + w7 HALP [on hold]

I'm installing SQL Server 2005 on windows 7. When the installation execute the System Configuration Check, it give some warnings: - SQL Server Edition Operating System Compatibility (Warning) Messages SQL Server Edition Operating System Compatibility

Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.

My OS is Windows 7 Home Premium SP1 x64, spanish My version of SQL Server 2005 is Standard

Beside this, im in a remote computer. So if i reboot (haven't rebooted) im not sure if i will be able to connect again.

I have seen that if i can install SQL 2005 SP3 first, then reinstall sql server that could work... but not in my case, it didn't work. so Please, help

Joining virtual tables in SQL server 2005

I have a simple procedure that uses two identical virtual tables:

declare @t table
(T datetime,  Berth5 int, BerthOther int)

declare @t2 table
(T datetime,  Berth5 int, BerthOther int)

Now after populating those tables I can select from any of those i.e. SELECT * FROM @t but I can't select from join

SELECT * FROM @t inner join @t2 on @t.T=@t2.T

or

SELECT * from [@t] inner join [@t2] on [@t].T= [@t2].T

I receive Must declare the scalar variable "@t"/@t2 (in second example its "Invalid object name '@t') "

EDIT: select * from @t t inner join @t2 t2 on t.T= t2.T Works fine

Using JOIN and SUM in SQL-Server Create View without "DISTINCT"

I'm using SQL Server 2005.

I'm trying to make an index view based on a query of these tables joined:

Table Name: CallRecords

TN         Carrier CallDate     Calls
----------------------------------------------
5558675309 10      2014-12-30   3
5558675309 2       2014-12-30   1
5551457868 13      2014-12-30   14

TableName: PhoneNumbers

TN            CustomerCode
----------------------------------------------
5558675309    205
5551457868    118

Here's my original query and create view:

--query
SELECT DISTINCT PN.CustomerCode,
                CR.CallDate,
                CR.Carrier,
                Sum(CR.Calls) as SumCalls
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
         CR.CallDate,
         CR.Carrier

--create view
CREATE VIEW CallsByCustCodeByCarrierByDay
WITH SCHEMABINDING
AS
SELECT DISTINCT PN.CustomerCode,
                CR.CallDate,
                CR.Carrier,
                Sum(CR.Calls) as SumCalls,
                COUNT_BIG(*) AS CountLines
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
         CR.CallDate,
         CR.Carrier

I am able to create the view but when I try to create an index on the view I get this error:

Cannot create index on view "dbo.CallsByCustCodeByCarrierByDay" because it contains the DISTINCT keyword. Consider removing DISTINCT from the view or not indexing the view. Alternatively, consider replacing DISTINCT with GROUP BY or COUNT_BIG(*) to simulate DISTINCT on grouping columns.

But if I remove "DISTINCT" then my SUM result is incorrect. I think it's because it is multiplying the true sum by the number of times a CustomerCode shows up in the PhoneNumbers table.

Is there a way I can still use this query with an indexed view or am I out of luck?

New fields added to table migrated from SQL2005 to SQL2008R2 remain NULL after INSERT

We have upgraded from SQL Server 2005 to SQL Server 2008R2. For some reason, when I add a new field to a table on the new server, an insert query will populate every field but the new one, leaving it NULL. I am using a field list along with my values list. All of the detailed column properties are the same as adjacent fields which continue to work fine.

It's interesting to note that an UPDATE query will successfully update the new field after the insert. Also interesting is that, if you create a new table from a script (after adding the new field), the new table doesn't experience this problem, so I'm wondering if it has to do with how the DBs were migrated over to the new server (a process I wasn't involved in)... if some flag or parameter did not set properly on the table after it was brought over, and insert queries are ignoring new fields somehow.

SUM Every row as Total

I was looking for before posting but I don´t find anything. I don´t know if is possible what I want.

I want the sum of every column in the same row. For a better explanation, I attach a picture. I am using SQL Server 2005

Example:

Thanks for your time.

'DateValue' is not a recognized built-in function name Sql Server 2005

What is the alternate of MS Access DateValue function in Sql Server Here is the Query

SELECT DISTINCT ss.FileName 
FROM SubmitSheets AS ss 
WHERE 1=1 AND 1=1 AND 1=1 AND 
ss.UpdateTimeA >= DateValue('2014/11/03') AND
ss.UpdateTimeA < DateAdd("d", 1, DateValue('2016/01/06')) AND
(1=1) AND 1=1 ORDER BY ss.FileName

And Here is the Error

Msg 195, Level 15, State 10, Line 4
'DateValue' is not a recognized built-in function name.

This query works fine in MS Access

Including NULL results after join

I am trying to do a report which shows all payments we have received and for the report I have to show names of patients who pay, but this table also contains checks from payers (insurance companies) and after I do a join all of the payers are excluded. I have tried every join version I know left, right, outer, inner, and combinations of the two. SQL Server 2005.

select 

pay.patient_id,
p.lname + ', ' + p.fname as 'Name',
pay.source_type,
pay.instrument,
pay.doc_reference,
pay.instrument_date,
pay.payment_amount,
pay.user_id,
pay.entry_chron,
pay.payor_id


from payment pay
join (select p.*, max(episode_id) over (partition by patient_id) as maxei from patient p) p
on p.patient_id = pay.patient_id

where episode_id = maxei and (pay.instrument_date between '2014-11-01' and '2014-11-30')
order by pay.payment_amount

This is what the results look like for patients with some fields commented out for confidentiality.

These are the fields that are being excluded

count mismatch in sql browser and java JDBC, due to high transaction

As im using sql server 2005 r2. There is more concurrent access on this table1 . when i execute the following query in sql browser im getting 2000 records, but when the same query is executed with java, no of records r varied.

EX: 1st execution --> 2000 records,
    2nd execution --> 2005 records, 
    3rd execution --> 1990 records


 select REGISTRATION_NO as TOTAL_ASSETS_COUNT from Contable1 a 
 inner join table2 b on a.REGISTRATION_NO = b.Registration_no and a.System_id=b.System_id 
 inner join table3 c on b.Registration_no=c.VehicleNo and b.System_id=c.System_id 
 where a.CLIENTID = ? and a.System_id = ? and b.User_id=?

im using isolation level as READ COMMITTED.

Due to high transaction on table1 the count is varied. please help to resolve why the count is varied in java.

thanks in advance.

.net application hangs in iis7.0 at peak time randomly

we have our own webserver in that internal applications are hosted in that one of the application will be used at peak time by around 300 employees

this application get hangs some times in peak time

server details as follows: windows server 2008 standard IIS 7.0 .net framework 4.0 sql server 2005

I have created one application pool for 2 .net internal applications in same configuration as specified above. the other application will not get hangs in peak time and running smooth with similar number of users.

I have one doubt is some one forcefully stopping the application to run slow and get hangs .

Please let us know what will be the exact solution as i am getting this issue since 2 years and i googled and done changes in queries, IIS settings and code standards. but still the same problem persists making my life disaster.

some times i get errors as 1. network tcp_errors server may busy or server is down 2. in event logs login failure for sa 3. some times CPU usage will be 100% at peak time

SQL report using financial periods

I have created a report for management that will total everything up by month with in a date range. Management has now decided that rather than by month they would like to go by period. we have 13 periods in a year each is 28 days except the last one is 29 or 30 depending on if its a leap year. The beginning of the first period is always 1-1-YYYY So now I will need to figure out what the beginning and end of each period is and total up each period. I am not really sure how to do this since every year the dates will change and they may want to look at periods from 2013 through the current period. the code and results I am currently using are enclosed

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) AS 'Date' 
    ,COUNT(*) AS Lots
    ,sum(flotSize) as 'Lot Size'
    ,sum(LReject) 'Lots Rejected'
    ,sum(fnumreject) as Rejected
    ,sum(fsampleSize) as 'Sample Size'
    ,sum(BDueDate) as 'Before Due Date'
FROM 
    ReportData
WHERE 
    finspecteddate >= '01-01-2014' 
    AND finspecteddate <= '10-15-2014' 
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) 
ORDER BY 
    date

migrating ms sql database to new server

Scenario: We have MS SQL 2005 installed on a windows 2003 server with 9GB database. We acquired a new Windows 2012 server and installed MS SQL 2014 on it.

My expertise level: Partial knowledge of SQL server and databases

What I need: I am given a task to migrate the 9GB database (SQL 2005) to a SQL 2014.. DB names and all should be identical to old one..

I would like to know the steps involved from experts, so I can achieve this job w/o any issues.

I am thinking of below steps:

1) Full Backup of 2005 database... 2) Create a new database with same name on the new server 3) Restore the full backup (taken on old server) on the new server 4) Change the compatibility level to suit to the new sql server

pls. let me know if this works or do I need to follow other approach. any queries, pls. feel free to ask.

sql_learner

DATEPART(ww,Date) with SET DATEFIRST

I´m using SQL Server 2005

I´m trying to get the week with DatePart(ww,date) function

My code

SELECT datepart(ww,'2012-01-08 00:00:00')

Return 2

But I want ...

Return 1

According with IS0-8601 and this table from this website

YEAR 2012 Week-01 From 2012-1-2 to 2012-1-8 ...

Am I wrong?

There is any trick with SET DATEFIRST 1, I´m trying without success.

Thanks for your time

How to change the date datatype format in SQL Server 2005

I am using SQL Server 2005 it is accepting datetime as '2014-10-31 20:46:00.0' only

What should I do to make the format as '30-10-2014 22:14'?

How to find static/constant values and expression used against a column(s) in a view in SQL server?

Is there any way to check what static/constant value(s) used for which column(s) in views, This needs to be checked in 100s of views so it will be very time consuming if we do it manually by reading a defination of a view.

Similarlly, Another part of question is, can we capture/find what expression/calculation used against computed/dependent column(s) of a view.

Example:

CREATE VIEW dbo.Test
select distinct top 100 percent 
    NULL AS C1, NULL AS C2, NULL AS C3, NULL AS C4,
    NULL AS C5, NULL AS C6, NULL AS C7, NULL AS C8,
    NULL AS C9, NULL AS C10, NULL AS C11, NULL AS C12,
    NULL AS C13, NULL AS C14, NULL AS C15, NULL AS C16,
    'Termination_Category_' + CGSTAT + '_' + RSSTAT as Employee, 

    CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y' 
        ELSE 'N' END AS Invol,
    'N' AS Inactive,
    CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
        ELSE 'N' END As Retire,
    'Y' AS Manager
from dbo.xyzs
    inner join [dbo].[vw_Ref_Test2] 
        on dbo.xyzs.cgstat = [dbo].[vw_Ref_Test2].[CDCODE]
    left join [dbo].[vw_Ref_Test3] 
        on dbo.xyzs.RSSTAT = [dbo].[vw_Ref_Test3].[CDCODE]
where CGSTAT<>'' or RSSTAT<>''

I need below for the above view:

S.No. View_Name   Column_Name    Value
1.     dbo.Test     Employee      'Termination_Category_' + CGSTAT + '_' + RSSTAT
2.     dbo.Test     Manager        Y   
3.     dbo.Test     Retire         CASE WHEN LEFT(RSSTAT,1)='I' OR CGSTAT = 'INVOLT' THEN 'Y' 
                                       ELSE 'N' END AS Invol,
                                             'N' AS Inactive,
                                             CASE WHEN CGSTAT = 'RETIRE' THEN 'Y'
                                       ELSE 'N' END

Any usefull guidance will be really helpful.

query to get following output

this is my table

BatchName    SubBatch   records     
12032014     raw1_0     2  
12032014     raw1_0     2  
12032014     raw1_1     2  
12032014     raw1_1     2     
12302014     raw7_0    150  
12302014     raw7_0    150  

want to get output like BatchName SubBatch records

12032014 raw1_0 4

12032014 raw1_1 4 12302014 raw7_0 300