MD5 value mismatch between SQL server and PostgreSQL

In order to write some code to do consistency check of data stored in both sql-server and PostgreSQL, I plan to calculate the MD5 on table data for both the databases, and verify if they are equal. This works fine as long as data is plain text ( ANSI ) as below:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', 'a'));
0x0cc175b9c0f1b6a831c399e269772661


postgres=# select MD5('a');
0cc175b9c0f1b6a831c399e269772661

Now, If I try to use some hangul(korean) characters, MD5 match fails:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', '무'));
0x0cc175b9c0f1b6a831c399e269772661


postgres=# select MD5('무');
cb3e9be1a3a28b355eabae1fa1e291b3

As per my understanding, reason of mismatch is that unicode characters are stored as UCS-2 encoding (fixed 16 bits encoding) in sql-server and UTF-8 encoding in PostgreSQL. And as MD5 works on character bits, the character bits sequence would be different in both SQL server and PostgreSQL.

AS I have been dealing mostly with hangul character-set, the workaround I used in PostgreSQL is to convert the encoding from UTF-8 to UHC ( Universal Hangul Character-set) before calculating hash as below:

postgres=# select MD5(CONVERT('무'::bytea,'UTF8','UHC'));
7827b52f65d9f7777d37071cbbbf7f2d

As you can see, the above hash value is same as that for SQL server.

All is fine as long as I am dealing with Hangul characters. But some tables contains mix of Hangul and Chinese characters, and the conversion fails in that case:

postgres=# select MD5(CONVERT('무么'::bytea,'UTF8','UHC'));
ERROR:  character 0xe4b988 of encoding "UTF8" has no equivalent in "UHC"
postgres=# 

The error makes sense as there are no equivalent of Chinese characters in UHC character-set.

How can I make it work? Basically, I need to find way to convert UCS-2 to UTF-8 in SQL server, or to convert UTF-8 to UCS-2 in PostgreSQL before calculating MD5. I want to perform all these operations within database engine, and not load data in external application to calculate MD5, as some tables has huge data set.

Querying a View with IN vs UNION performance

I have a view that the purpose is to create a regular table instead of a attribute-based representation. In doing so, it casts the varchar field into other varchars and some ints.

Today I'm running into an issue and not sure how to troubleshoot it.

If I try:

select * from vNormalTable where ItemId in (...query to get the affected item ids...)

it never completes (at least, not after 5 minutes). I've run the subquery, and it loads 9 numbers in less than a second.

If I try:

select * from vNormalTable where ItemId in (1, 2, 3, 4, 5, 6, 7, 8, 9)

It's the same thing. Nothing loads.

If I try:

select * from vNormalTable where ItemId = 1
union
select * from vNormalTable where ItemId = 2
union
....

It returns all 9 rows in less than a second.

The view itself is a bunch of left joins (well, just 10) with the item_attributes table (ie dbo.item_attributes as color where item_id = i.item_id = color.item_id and attribute_id = 10001), I don't see any special things happening other than the previously mentioned casting.

I don't know the inner workings of IN () (I always assumed it was the equivalent of = each item and union results, but that doesn't seem to be the case here). Is there anything to look for that causes it to fail only in the IN case? It was working fine before today but may have to do with a combination of bad data (doesn't seem to be the case here) and/or more records, don't know.

SQL Server 2005: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint

Here is my table Structure:

CREATE TABLE [dbo].[Invoice](
    [InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [SubTotal] [numeric](18, 2) NULL,
    [Tax] [numeric](18, 2) NULL CONSTRAINT [DF_Invoice_Tax]  DEFAULT (0.0),
    [InvoiceTotal] [numeric](18, 2) NOT NULL CONSTRAINT [DF_Invoice_InvoiceTotal]  DEFAULT (0.0),
    [Remark] [text] NULL,
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
(
    [InvoiceNumber] ASC
)
GO

CREATE TABLE [dbo].[InvoiceDetail](
    [InvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceNumber] [int] NULL,
    [Amount] [numeric](18, 2) NOT NULL,
    [Tax] [numeric](18, 2) NOT NULL CONSTRAINT [DF_InvoiceDetail_Tax]  DEFAULT (0.0),
    [TransactionType] [varchar](2) NULL,
    [Remark] [text] NULL,
 CONSTRAINT [PK_InvoiceDetail] PRIMARY KEY CLUSTERED 
(
    [InvoiceDetailID] ASC
)
GO

ALTER TABLE [dbo].[InvoiceDetail]  WITH NOCHECK ADD  CONSTRAINT [FK_InvoiceDetail_Invoice] FOREIGN KEY([InvoiceNumber])
REFERENCES [dbo].[Invoice] ([InvoiceNumber])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Here is my code snippet for inserting fresh invoice and invoice details data:

    try
    {
        using (TransactionScope transactionScope = new TransactionScope())
        {

            //... Lots of other insert / update / delete operations ...

            invoice.Add(); //Adds invoice to DB and sets the PK value in invoice.InvoiceNumber;

            foreach (InvoiceDetail invoiceDetail in invoice.InvoiceDetails.Values)
            {
                invoiceDetail.InvoiceNumber = invoice.InvoiceNumber;
                invoiceDetail.AddInvoiceDetail(); //Randomly fails.
            }

            //... Lots of other code + Payment Gateway integration
            transactionScope.Complete();
        }
    }
    catch (Exception ex)
    {
        errorType = ErrorType.General;
        if (HttpContext.Current != null)
        {
            Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
        }
        else
        {
            Elmah.ErrorLog errorLog = Elmah.ErrorLog.GetDefault(null);
            errorLog.ApplicationName = "Billing Engine";
            errorLog.Log(new Elmah.Error(ex));
        }
    }

I use Enterprise Library for DB operations.

I get random exceptions at line# invoiceDetail.AddInvoiceDetail();

I have advised my staff to re-try whenever failure occurs. Re-try of the same operation without any changes passes. We have hundreds of transactions happening daily via this code. And daily I see one or two transactions failing with the below error.

ELMAH logs the following error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_InvoiceDetail_Invoice". The conflict occurred in database "XXXX", table "dbo.Invoice", column 'InvoiceNumber'. The statement has been terminated.

I modified this code to serialize and store invoice + invoicedetails when an exception is caught. Strangely, I was able to see invoicenumber was correctly generated and set in the invoice object. Also, first invoice detail insertion operation succeeded (because serialized data had invoicedetailid set for one invoicedetail but zero for all others). So I assume the second invoicedetail insertion failed with the FK error.

This is totally random, 98% of times it passed and all failures pass upon re-try.

Things that I have tried:

1. I have checked there is no corrupt data in InvoiceDetails table.

2. I have executed sp_updatestats

3. Someone suggested this is a bug in SQL Server 2005 http://ift.tt/1N2pyFg http://ift.tt/1N2pANm

So we got the updates installed on SQL Server 2005. Upto SP4 (Version : 9.00.5057)

But still no good. I keep getting these random failures.

I suspect this is some sort of concurrency issue or may be some transactionscope problem. But I have no clue how to debug further.

Truncate multiple table at a time using single sql query

i am using sql server 2005 and i have 20 tables in my database. now what i want is how to truncate( not delete) selected 10 tables in my database in single sql query. i know it is possible and i do not know how to do that. please any one can help me?

Sum across columns and rows

Consider a table like this

table
+--------+---------+-----------+---------+-----------+
| BookId | ItemId1 | Quantity1 | ItemId2 | Quantity2 |
+--------+---------+-----------+---------+-----------+
|      1 |       1 |         2 |       2 |         1 |
|      1 |       3 |         1 |       2 |         1 |
|      2 |       1 |         1 |       2 |         1 |
+--------+---------+-----------+---------+-----------+

Now I want to get the sum of columns quantity for each item grouped by book. How can I take the sum across different columns then? right now I use an awkward solution like building a temporary table and then querying this one, but it must be possible in a more elegant way!?

select
    BookId 'BookId',
    ItemId1 'ItemId',
    Quantity1 'Quantity'
into #temptab
from table
union all
select
    BookId,
    ItemId2,
    Quantity2
from table

and after that

select 
    BookId,
    ItemId,
    sum(Quantity)
from #temptab
group by ItemId, BookId

How can I get rid of this intermediate step?

Desired output:

+--------+--------+----------+
| BookId | ItemId | Quantity |
+--------+--------+----------+
|      1 |      1 |        2 |
|      1 |      3 |        1 |
|      1 |      2 |        2 |
|      2 |      1 |        1 |
|      2 |      2 |        1 |
+--------+--------+----------+

How to Find the Database Views Which are not executed or Accessed for more than 6 Months in SQL Server 2005

I would like to clean up my database by identified & removing the views & stored procedures which were not in use or not accessed for a longer period (May be for last 6 months or 1 year) in SQL Server 2005.

Please help.

SQL Server Filtering by DateTime column, when TIME portion is provided sometimes

In an SSRS report, the user searches based on start date and end date.

The challenge is, as I discovered recently, he sometimes, not always, provides the time component while searching.

Currently, the filter is done like this:

if @pEndDate is null  
  SET @pEndDate = getdate()  
SET @PEndDate = DateAdd(dd,1,@PEndDate) 

SELECT ........
FROM .....
WHERE ( Createdon >= @PStartDate AND Createdon < @PEndDate)

This is fine when he searches without time (example - @PStartDate = 2/23/2015 and @PEndDate = 2/24/2015)

How should I structure the query to deal with the time portion when he provides it? (example - @PStartDate = 2/23/2015 15:00 and @PEndDate = 2/24/2015 15:00)

If this is answered elsewhere, please point me to it. Thank you.

Row as column in Sql server 2008

I am working with sql server 2008 i have a temp table which returns the below result

Location Month value US January 10 US February 10 US March 10 US April 10 US May 10 US June 10 UK January 10 UK January 10 UK February 10 UK February 10 UK March 10 UK March 10 UK April 10 UK April 10 UK May 10 UK May 10 UK June 10 UK June 10

I want to get the result as below

Location January February March Q1 April May June Q2 US 10 10 10 30 10 10 10 30 UK 20 20 20 60 20 20 20 60

how to query to get the above result using sql server 2008?

How can I test the syntax on SQL2005 without install it

I am using SQL server 2008. Everything go right in my local development. But when I deploy the program and the stored procedure into client workstation which is using SQL server 2005, errors are coming out. I believe it is caused by the syntax problem between SQL2005 and 2008 because I have fixed some of the parts and it can be fixed. It is quite time consuming when I using SQL2008 and search the error one by one in the forum.

So is there any way I can use the SQL server 2005 editor to write stored procedure without install it.

Thanks for your help

SSMS Run a specific Query in SQL Query Editor Based on Highlighted Table / View

When writing SQL, I constantly find myself checking the structure of a table / view or selecting the top 100 rows to get a preview of the data.

For instance, when I have something like this (and I have forgotten the structure of table2)...

SELECT
FROM table1 INNER JOIN table2 ON table1.fieldX = table2.???

I would like to highlight a table / view name and then pass it to a SQL query by passing a parameter, say table2 to either of the following 2 queries

SELECT TOP 100 * FROM table2

or

SELECT o.Type, o.Type_Desc, o.name AS ObjectName, c.name AS ColumnName
FROM sys.objects AS o INNER JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE o.name = N'table2' AND o.Type IN (N'U', N'V')
ORDER BY o.name,c.column_id,c.name

I use SSMS Boost (it's my favorite SSMS addin) but can't figure out how to use functionality (Auto Replacement, Shortcut, Macros) in order to accomplish what I want here.

Has anyone ever set this up?

How can i read more rows in same select sentence?

I am working on SQL server 2005 and Delphi xe7 and using adoquery.

We have sales invoices for our customer. As the goverment wish we have to report totals of customers every month over 5000 (excluded from VAT).

This can seems easy but we have different VAT values in invoices so i have to get correct values If the invoice has One VAT value or Two or Three.

Here is the basic select sentence of me.

(mf.fis_aciklama1 like '%U SFA%')or (mf.fis_aciklama1 like '%U ÝAF%')  

This where clause is enough to getting Sales invoices. This is ok. At the below select ;

I have read next row for 600 accounting number for shipment total and one next row for get the VAT Value. You can see the ABS function lines and subselects for this.

Now the question;

"How can i learn if i have to read next 2 row" or not?

How can create select sentence like this with "if".

Actually i have an idea but i dont have pratice how the create sentence for select this.

IF the fis_meblag0 of first row>fis_meblag0 of next+ fis_meblag0 next+ next;

Then we have to read more row but but to write this in sql.

select mf.fis_tarih as Tarih,mf.fis_hesap_kod as HesapKod,    
mh.muh_hesap_isim1 as Unvan,                                  
mf.fis_aciklama1 as Aciklama,                                 
mf.fis_yevmiye_no as Yno,                                     
mf.fis_sira_no as Sno,                                         
mf.fis_meblag0 as Tutar,                                       
ABS((Select top 1 mf1.fis_meblag0 from  dbo.MUHASEBE_FISLERI mf1 where 
mf.fis_yevmiye_no=mf1.fis_yevmiye_no and                          
mf.fis_tur=mf1.fis_tur and                                          
mf1.fis_RECno=mf.fis_RECno+1 and left(mf1.fis_hesap_kod,3)='600')) as     Matrah,  

Abs((Select top 1 mf2.fis_meblag0 from  dbo.MUHASEBE_FISLERI mf2     where        
mf.fis_yevmiye_no=mf2.fis_yevmiye_no and                                 
mf.fis_tur=mf2.fis_tur and                                                 
mf2.fis_RECno=mf.fis_RECno+2 and left(mf2.fis_hesap_kod,3)='391')) as KDV,  
cr.cari_VergiKimlikNo as Vno 
  from dbo.MUHASEBE_FISLERI mf                                              
  left join dbo.MUHASEBE_HESAP_PLANI mh on     mf.fis_hesap_kod=mh.muh_hesap_kod  
  left join dbo.CARI_HESAPLAR cr ON mf.fis_hesap_kod=cr.cari_kod   
where                                                                      
mf.fis_tarih>='20130501' and mf.fis_tarih<='20130531'  
and                                                                          
((mf.fis_aciklama1 like '%U SFA%') or (mf.fis_aciklama1 like '%U ÝAF%'))                                                                          order by mf.fis_hesap_kod                                                 

Error converting string to GUID in c#

I am using the following code for performing a delete operation via EF code first using a inline query internally

 void IRepository<T>.Delete(params Guid[] ids)
    {
        var sql = string.Format("UPDATE {0} SET [IsDeleted] = 1 WHERE [Id] IN (@ids) ", GetTableName());
        string sep = String.Join(", ", ids.Select(x => "'" + x + "'"));
        var sqlParams = new Object[]
        {
            new SqlParameter("ids", string.Join(",",sep)), 
        };
        DataContext.Database.ExecuteSqlCommand(sql, sqlParams);
    }

Now when I execute the command it gives me

conversion failed when converting from a character string to uniqueidentifier

error.

Hiwever when I run the query in sql say.

   UPDATE [dbo].[Table] SET [IsDeleted] = 1 WHERE [Id] IN ('20Cr0BCA-6EBB-E411-A04B-BC305BA8C713','506c79c1-6ebb-e411-a04b-bc305ba8c733') 

it works fine.

Is this possible to do this way ?Or what am I doing wrong ?

Inserting concatenate Identity column with other column

I have a identity column and i have other column while inserting a new row in 
 table i need to insert into third column with concatenate of two columns result 

For reference please see below table

------------------------------------------------
 A    |   B   |  c  
----------------------------------------------
 1    |  33   |   133(1 [identity result] + 33)    
 2    |  112  |   2112

Please help me to solve this issue.

vb.net service not responding ,

Dears I have built this service in Microsoft visual studio 2008 and there is no errors show in the code , and when i deploy the service in the IIS manager it does not work ?? Please guide me what to do me...??

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient



Namespace WebService1

'To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
  ' <System.Web.Script.Services.ScriptService()> _
   <System.Web.Services.WebService(Namespace:="http://ift.tt/1B8JO4U")> _
    <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
    <ToolboxItem(False)> _
    Public Class Service1
        Inherits System.Web.Services.WebService

        <WebMethod()> _
        Public Function HelloWorld() As String
            Return "Hello World"
        End Function



        <WebMethod()> _
        Public Function GetDataTable(ByVal ConnectionString As String, ByVal SqlSt As String) As DataTable
            Dim result As DataTable

            Dim res As Integer
            res = 0

            Dim mn As Integer
            mn = 1
            'returninfi(Context.Request.UserHostAddress)

            If mn = 1 Then
                Try
                    Dim conn As SqlConnection = New SqlConnection(ConnectionString)
                    Dim ds As DataSet = New DataSet()
                    Dim a As New SqlDataAdapter() With {.SelectCommand = New SqlCommand(SqlSt, conn)}
                    a.Fill(ds, "Account")
                    result = ds.Tables("Account")
                    Return result
                Catch ex_44 As Exception
                End Try
            End If

            Return result
        End Function

    <WebMethod()> _
        Public Function RunSQL(ByVal ConnectionString As String, ByVal queryString As String) As String

            Dim a As Integer
            a = 1
            ' returninfi(Context.Request.UserHostAddress)
            Dim result As String
            result = ""

            If a = 1 Then

                Try
                    Using connection As SqlConnection = New SqlConnection(ConnectionString)
                        Dim command As SqlCommand = New SqlCommand(queryString, connection)
                        connection.Open()
                        command.ExecuteNonQuery()
                        connection.Close()
                        result = "1"
                    End Using
                Catch ex As Exception
                    result = ex.Message.ToString()
                End Try
            End If


            Return result
        End Function

    
        Public Function returninfi(ByVal ip As String) As Integer
            Dim result As DataTable
            Dim res As Integer
            Try
                Dim ConnectionString = "Data Source=WIN-DFSIR3RC74G\SQLEXPRESS;Initial Catalog=SMS;User 

ID=sa;Password=22334"
                Dim conn As SqlConnection = New SqlConnection(ConnectionString)
                Dim ds As DataSet = New DataSet()
                Dim Sqlstr As String
                Sqlstr = "select * from sec_users where ipadress ='" & ip & "'"


                Dim a As New SqlDataAdapter() With {.SelectCommand = New SqlCommand(Sqlstr, conn)}
                a.Fill(ds)


                'if (ds.Tables.Count > 0) and ds.Tables[0].Rows.Count > 0) then
                ' If (ds.Tables.Count > 0) And True Then
                If (ds.Tables.Count > 0) And (ds.Tables(0).Rows.Count > 0) Then

                    res = 1
                Else
                    res = 0
                End If

                'result = ds.Tables("Account")

                Return res

            Catch ex_44 As Exception
            End Try
            res = 0
            Return res
        End Function







    End Class

End Namespace

SQL Query to SUM data from 3 different tables

I have 3 2005 SQL tables, an order table, quote table and invoice table. Structured like this:

SO Table

Rep Code SO Amount SO Date

1         100     1/2/2015
2         50      2/15/2015
3         20      2/20/2015
2         50      2/20/2015
1         85      2/20/2015

SQ Table

Rep Code SQ Amount SQ Date

1        50        2/2/2015
2        14        2/18/2015
3        67        2/19/2015
1        96        2/20/2015
2        54        2/20/2015

INV Table

Rep Code INV Amount INV Date

1         654      2/2/2015
2         312      2/18/2015
3         54       2/19/2015
1         6        2/20/2015
3        48        2/20/2015

What I want to do is write a query which will sum up each amount by rep code, so it would look something like this:

Rep Code SO Total SQ Total Inv Total

1        185      146      660
2        100       68      312
3        20        67      102

I would also like it to only pull the totals if the appropriate date for each one is lets say in the last week. (So Today -7)

Let me know if this needs any clarification.

How to Calculate Gap Between two Dates in SQL Server 2005?

I have a data set as shown in the picture. I am trying to get the date difference between eligenddate (First row) and eligstartdate (second row). I would really appreciate any suggestions. Thank you

How to run a subquery based on results of a query SQL

I have 2 queries i'd like to run. The idea here is to run a query on the transaction table by the transaction "type". Based on these results, I want to run another query to see the customers last transaction based on a specific type to see if the service ID was the same. If it's not the same, I want to flag it as "upgraded"

Here is the initial query that Pulls the results from a transactions table based on a transaction type:

Select customerid, serviceid
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save')

The output for this is:

Customerid          ServiceID
    1                   11
    2                   21
    3                   21
    4                   11
    5                   12
    6                   11

What i'd like to do next is run this query, matching the customerID to see what the customers last charge was:

Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
Where (transactiontype = 'Cust Purchase')
Group By serviceID

My Final output combining the two queries would be:

Customerid          ServiceID          Last Purchase      Upgraded?
    1                   11                  11               No
    2                   21                  11               Yes
    3                   21                  12               Yes
    4                   11                  10               Yes
    5                   12                  12               No
    6                   11                  11               No

I thought this might work but it doesn't quite give me what I want. It returns too many results, so the query is obviously not correct.:

Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
WHERE     Where (transactiontype = 'Cust Purchase') AND EXISTS
                          (Select customerid, serviceid
                           from Transactions
                           where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save'))
GROUP BY serviceid

How to generate script to retrieve schema and only top 100 records from each table from SQL Server 2005

How to generate script to retrieve schema and only top 100 records from each table from SQL Server 2005. I need to generate scripts for 15-16 databases and each database has more than 50-60 tables. Is it possible to generate script to retrieve schema only some data in SQL Server 2005? If yes, how can I do this? Any help appreciated.

SQL Insert with Select and OUTPUT to create a temp copy table

How do I make a copy of the inserted records and keep the original id of the record it copied and put it in the Temp table. When I add Docs.DocID to the OUTPUT I get. "The multi-part identifier "Docs.DocID" could not be bound."

DECLARE @CopiedDocIDs TABLE(NewDocID int, CurrentDocID int)

INSERT INTO Docs (Filename,Backup)
OUTPUT INSERTED.DocID, Docs.DocID INTO @CopiedDocIDs 
SELECT Filename, 1
FROM Docs 
WHERE Filename like 'MyDoc%'

Oh yeah I'm working with: Microsoft SQL Server 2005 - 9.00.4035.00 (X64)

EDIT: Ok somewhat of a hack but it works. Here's it working but I'll add in another column to the RowKey just make sure it's unique.

DECLARE @Docs TABLE (DocID int IDENTITY(1, 1), [FileName] varchar(10), FileDate datetime)
INSERT INTO @Docs VALUES('Doc1','01-01-2011 12:21:12:003')
INSERT INTO @Docs VALUES('Doc2','01-01-2013 02:41:32:120')
INSERT INTO @Docs VALUES('Doc3','01-01-2014 09:30:12:023')
INSERT INTO @Docs VALUES('Doc','01-01-2014 09:30:12:111')
INSERT INTO @Docs VALUES('Doc','01-01-2014 09:30:15:123')

DECLARE @NewDocIDs TABLE(NewDocID int, CopyDocID int, RowKey Varchar(50))

INSERT INTO @Docs ([FileName],FileDate)
OUTPUT INSERTED.DocID, null, (INSERTED.[FileName]+'-'+CONVERT(varchar(50),INSERTED.FileDate,126)) INTO @NewDocIDs
SELECT [FileName],FileDate
FROM @Docs 

UPDATE @NewDocIDs SET CopyDocID=(SELECT TOP 1 DocID FROM @Docs WHERE [FileName]+'-'+CONVERT(varchar(50),FileDate,126)=RowKey) 

select * from @Docs
select * from @NewDocIDs --## output I need

Is It possible to a rebuild Index with out taking instance offline?

I have this one index that's 85.71% total fragmentation and 55.35% page fullness...why not lets start with an index rebuild, but I get this error.

Can this be done without taking my instance offline?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Rebuild failed for Index 'idx_last_success_download'.  (Microsoft.SqlServer.Smo)

For help, click: http://ift.tt/1ALkNN3

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://ift.tt/1ALkO3i

------------------------------
BUTTONS:

OK
------------------------------

Query returning two different result sets

I have two lengthy queries, but when I try to merge them I get two different result windows. When I try to reference query 1 as a temp table into query 2, i get an error that field cannot be bound. How can I merge two different queries to make one result set? (I can paste the queries but they are both quite long)

SQL Server ignores given format with CONVERT()

I believe this should be a fairly easy task but somehow it fails. I have dates stored in a text-field like dd-mm-yyyy and I'm converting them to DateTime-types using CONVERT(datetime, [MyDateAsTextField], 105). But the output I''m getting is yyyy-dd-mm 00:00:00.000 and I'm not sure why. What am I missing?

Difference in Join syntax between SQL 2005 and SQL 2008

I am upgrading my SQL database from 2005 to 2008. I am having difficulty with LEFT OUTER JOINS. In 2005 it joins the first table to each matching record on the second table. In 2008 it is only returning each row from the first table once and returning NULL values for the second table as there are multiple matches. Can anyone help please?

script to identify all the tables that are not been used in stored procedure

Can I have a script to identify all the tables that are not been used in stored procedure.

Thanks much!

Struggling with faulty date fields in MSSQL

I have two tables, a parent table and a child-table. The child-table is a vertical designed table (meaning it stores and Id, ParentId, Property and PropertyValue). Naturally the PropertyValue can hold all types of data.

I'm trying to filter this set but I'm struggling with faulty dates and empty fields. I'm unable to create functions due to read-only access so I have to do everything in the actual query. I tried using a subquery but I'm experiencing I'm not getting the results from the subquery to work with in the outer query.

So far I've got this:

DECLARE @Year Int 
SET @Year = 2015

SELECT      COUNT(Parent.ID), YEAR(PropertyValue), MONTH(PropertyValue)
FROM        Parent
INNER JOIN  Child
ON          Parent.ID = Child.ParentID
WHERE       Parent.ID IN (
    SELECT ParentID
    FROM    Child
    WHERE   Child.Property = 'MyDateField'
    AND ISDATE(Child.PropertyValue) = 1
)
AND Child.Property = 'MyDateField'
AND YEAR(Child.PropertyValue) = @Year
GROUP BY YEAR(Child.PropertyValue), MONTH(Child.PropertyValue)

Any suggestions on how to cut out the faulty date rows and proceed with the desired dataset?

SQL help needed to select certain rows using a WHERE CASE

My first post here, so please bear with me.

My Dataset

t1.c1    t1.c2   t2.c1   t2.c2   t2.c3   t3.c1
2551770  87772   87772   82272   TEST    P
2551770  87772   87772   K0328   TEST    P
2551770  99396   99396   99396   PREV    P
2809700  93000   93000   93000   ELEC    M
2809700  99397   99397   99397   PREV    M
2809700  99397   99397   G0439   PREV    M

Desired Output

t1.C1     t1.C2  t2.C1  t2.C2  t2.C3  t3.C1  t1.C3          
2551770   87772  82272  82272  TEST    P     82272 (from t1.C2)
2551770   99396  99396  99396  PREV    P     99396 (from t1.C2)
2809700   93000  93000  93000  ELEC    M     93000 (from t1.C2)
2809700   99397  99397  G0439  PREV    M     G0439 (from t2.C2)

I know this must use some sort of Case in the Where clause, but I am having a difficult time with the syntax. I need some help to get the desired output! I listed the logic below.

The logic is as follows:

1. If t3.C1 != 'M'
a. If t1.C2 = t2.C2 then select t1.C2
b. If t1.C2 != t2.C2 then do not retrieve this row
2. If t3.C1 = 'M'
a. If t1.C2 = t2.C2 And t2.C2 is not like 'PREV' then select t1.C2
b. If t1.C2 = t2.C2 And t2.C2 is like 'PREV' then do not retrieve this row
c. If t1.C2 != t2.C2 And t2.C2 is like 'PREV' then select t2.C2

>

And, I'm thinking of code like this, but I am getting lost with my cases:

Select Distinct*
From t1 Inner Join t2 On t1.C2 = t2.C1
Where  
  Case When t3.C1 != 'M' Then 
         Case When t1.C2 = t2.C2 Then t1.C2
              When t1.C2 != t2.C2 then /* do not retreive this row */
  Case When t3.C1 = 'M' Then
       Case When t1.C2 = t2.C2 And t2.C2 is not like 'PREV' Then t1.C2
        When t1.C2 = t2.C2 And t2.C2 is like 'PREV' then /* do not retreive this row */
        When t1.C2 != t2.C2 And t2.C2 is like 'PREV' Then t2.C2
End     

How to Make JDBC Connection With SQL Server Express 2005 [on hold]

Help me to solve my issue

my code is

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class PrintFileToPrinter{

public static void main(String[] args){
    try{
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    String dbURL = " jdbc:sqlserver://TOSHIBA-PC/SQLEXPRESS:1433;databaseName=COSEC;user=sa;password=22484;";
    Connection conn = DriverManager.getConnection(dbURL);
    if (conn != null) {
        System.out.println("Connected");
    }
    }catch(ClassNotFoundException | SQLException exception){
        exception.printStackTrace();
    }

}

}

and the error showing is

java.sql.SQLException: No suitable driver found for  jdbc:sqlserver://TOSHIBA-PC/SQLEXPRESS:1433;databaseName=COSEC;user=sa;password=22484;
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:233)
at textfilemaker.PrintFileToPrinter.main(PrintFileToPrinter.java:19)

i am using sqljdbc4-2.0.jar my IDE is netbeans.

please help me in this issue and thanks in advance

MS-SQL: Getting error "Error converting data type varbinary to numeric"

I've got a trouble with deleting/updating the particular row in my table. The table is small and simple:

CREATE TABLE dummy (
    "numemv" NVARCHAR(50) NOT NULL DEFAULT NULL,
    "control_summ" DECIMAL NOT NULL DEFAULT NULL,
    "active" CHAR(1) NOT NULL DEFAULT '1',
    "departid" INT NOT NULL DEFAULT NULL,
    UNIQUE INDEX "UNIQUE" ("numemv")
)

The row that I can't delete/update:

numemv   control_summ active departid
00499038 400000       1      0

I tried to manage in this ways:

UPDATE dbo.atm_control_summs SET departid = 13 WHERE departid = 0
DELETE FROM dbo.atm_control_summs WHERE departid = 0
UPDATE dbo.atm_control_summs SET departid = 13 WHERE numemv = '00499038'
DELETE FROM dbo.atm_control_summs WHERE numemv = '00499038'

But always I get the error: Error SQL (8114): Error converting data type varbinary to numeric.

How can I fix this error?

Thanks in advance :)

Subqueries are not allowed in this context -- SQL statement from 2008 to 2005

I have a SQL statement which run in a window form application. I am using SQL server 2008 to develop and test. Everything is alright here.

            SqlCommand command1 = new SqlCommand("insert into payment_detail ( " +
                                                "transaction_id, enrolment_id, hkid_1, section_r, " +
                                                "classes, section_fee, assist_amt, additional_assist_amt, " +
                                                "discount_amt, coupon_amt, coupon_amt_no_number, " +
                                                "paid_amt, last_updated_by, last_updated_date) values " +
                                                "(@transaction_id, @enrolment_id, @hkid_1, @section_r, " +
                                                "@classes, @section_fee, (select section_fee - adjusted_section_fee from coursenew where coursecode = @courseCode and section_r = @section_r), @additional_assist_amt, " +
                                                "@discount_amt, @coupon_amt, @coupon_amt_no_number, " +
                                                "@paid_amt, @last_updated_by, GETDATE())"
                                                , myConnection);

But when moving to another work station which is using SQL server 2005. It prompts out an error like below

Subqueries are not allowed in this context. Only scalar expressions are allowed.

I am sorry that since a computer can only install one version of SQL server. I cannot test the script with a SQL server 2005.

Please help and thanks.

SQL Server 2005: Preserve parent-child relationships when copying hierarchical data

We have a table representing a tree of values associated with an entity (call it a project), where the ParentID column refers to the id column of a row's parent. The id column is an auto-incremented IDENTITY column and primary key. Root nodes have a ParentID of 0.

We want to be able to clone the data for a given project and have the resulting ParentIDs refer to the appropriate new ids for the copied values, in a way that meets the restrictions described below the example.

For example, copying the data for ProjectID 611 in the below table:

    id      ProjectID    Value         ParentID
--------------------------------------------------
     1      611           Animal        0
     2      611           Frog          1
     3      611           Cow           1
     4      611           Jersey Cow    3
     5      611           Plant         0
     6      611           Tree          5
     7      611           Oak           6

Should result in:

    id      ProjectID    Value         ParentID
--------------------------------------------------
     1      611           Animal        0
     2      611           Frog          1
     3      611           Cow           1
     4      611           Jersey Cow    3
     5      611           Plant         0
     6      611           Tree          5
     7      611           Oak           6
     8      612           Animal        0
     9      612           Frog          8
     10     612           Cow           8
     11     612           Jersey Cow    10
     12     612           Plant         0
     13     612           Tree          12
     14     612           Oak           13

Restrictions:

• Solution must work for SQL Server 2005. That is, we can't use MERGE (alas).


• We'd rather not add an additional column to the table. (My current solution adds an "OldId" column, which the copy procedure sets when copying rows. So I'm currently using a combination of INSERT-SELECT and UPDATE-FROM, joining the OldId column on the ParentID column to get the new id.) We'd rather not pepper all of our hierarchical tables with OldId columns just to support this copy operation.


• Solution must be reasonably performant; my initial solution was going to be a complicated set of rescursive function calls and loops processing one item at a time. I quickly abandoned that route!

SSRS 2005 Subscription changes

OK this is my question(s) and its SSRS 2005 and SQL Server 2005/2008

I had been tasked with rebuilding a dozen or so reports that our users use on their data systems. We just build them and since every DB instance is schematically the same for all our clients, we push the reports out to their report servers for use.

So modified a great many reports, but the reports have blown away the clients subscriptions. So every user that uses these reports, that can be a great many seeing as how everyone can have their own set of parameters, has to run the reports manually or redo their subscriptions.

My company would very much like to avoid that, but I can not figure out how to change a report, and even with the same parameter set going in as the last report, keep the subscription there.

Even when I copy the report down to their report server and replace the old with the new using same name. The subscription is still there, but it gets modified.

1. I am looking either for a way to push down a subscription as part of the report, so that they will have minimal input to their subscriptions in order to tailor it to their needs --OR ideally--


2. Upload a new version of the report to their report server and just have the subscription apply to the newest report that I have put on their server

It doesn't really matter which one but the second is best seeing as how individual users use the reports with individual names as a parameter

Many thanks in advance for anyone that can point me to the way to manage out subscriptions on my side, or enable my reports to assume the subscriptions of same named reports on their server. --edit-- Want to put a clearer picture out there I have a master copy of a report. The users use the report on their own systems. I do some heavy modifications to the master copy of the report, and upload it to their systems. using the same name and same parameter set as the original report. I want the subscriptions on their report server to find this report using the same name. so XXX.report has a subscription. I change it to XXX.report locally, and upload to their servers. The subscriptions are not synching though.

Thanks

Comparing today's Date to Date columns

I have a SQL Server 2005 table with a column named CheckTime of datatype DateTime.

I need to run queries based on the date. For example:

1. Select * where DatePart of Checktime (MM/DD/YYYY) = Today


2. Select * where it was last week (assume Sunday - Saturday)


3. Select * where it was last month (assume 1st - last day of month)

I can't figure how how to specify the inputs for comparison and compare those inputs to today. Can anyone please help me understand all the date comparison concepts and how to specify the date ranges?

Thanks in advance, DetRich

SQL Server 2005- "Copy Direction must be in, out or format"

I wrote a program in early 2010 that ran a command line function that pulled data from SQL Server 2005. The program worked at the time, but does not seem to work now (for various reasons, it was not used for about a 1 year).

BCP "EXEC emdb..Patch2"" QUERYOUT D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t "~" -c

The error message says "Copy direction must be in, out or format." I'm trying to figure out what I need to change to make this work; Please note that I don't actually have any programming background (it was extensive research into books that my boss no longer has). Thanks,

SQLServer untrusted foreign keys

I have several SQLServer 2005 databases with untrusted foreign keys. These keys are marked as "Not for Replication" (is_not_for_replication=1) in the sys.foreign_keys object catalog view. I have run a script that captures these untrusted keys and runs "ALTER TABLE [tablename] WITH CHECK CHECK CONSTRAINT [keyname]" against each key. For all but one the results say that these keys have been validated (re-trusted?), but the sys.foreign_keys view still has them marked as untrusted. Why is this object catalog view not reflecting the "re-trusted" keys as is_not_trusted=0? Is a "Not for Replication" foreign_key always untrusted? If not, how do I get the catalog view to reflect the "trusted" state of hte foreign key? Also, am I correct in assuming that as long as the foreign key is marked as "untrusted" it will not be factored in to any SQLServer execution plan? Thanks for any comments or suggestions.

T-SQL Dynamic Pivot table by Sunday Date only

I have a data set that looks like the following below in sample data. I need to break out the data as a summary of the data on a per week basis with the weekend ending on Sunday. I have tried various attempts at a dynamic pivot table but have had no success. Any thoughts or suggestions.

Sample Data

EmpID    ClientID   Contact_Date   Hrs
12000    1234         1/5/2015      1.67
12001    1235         1/6/2015      2.00
12002    1236         1/7/2015      4.00
....... 
etc

Desired Result

EmpID    ClientID   Wk 2/8/2015   Wk 2/1/2015  Wk 1/25/2015   Wk 1/18/2015 ....etc
12000      1234          4             3            2              0 

I have this query to GET Every Sunday within the date range of dates from all entries in the table with a certain status code.**

DECLARE  @StartDate     DATETIME
         ,@EndDate      DATETIME

SET @STARTDATE  = (SELECT MIN(contact_date) FROM dbo.vw_hrs_entered)
SET @ENDDATE    = (SELECT MAX(contact_date) FROM dbo.vw_hrs_entered)

SELECT *
FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                        [Date] = dateadd(DAY,ROW_NUMBER()
                  OVER(ORDER BY c1.name),
                  DATEADD(DD,-1,@StartDate))
        FROM   [master].[dbo].[spt_values] c1 ) x
WHERE  datepart(dw,[Date]) = 1

RETURNS

1/11/2015 12:00:00 AM
1/18/2015 12:00:00 AM
1/25/2015 12:00:00 AM
2/1/2015 12:00:00 AM
2/8/2015 12:00:00 AM

Database connection error with MSSQL 2005

I've got the following code and I'm trying to establish a connection to a data base. I have MSSQL 2005 as the database and trying to connect though ODBC connection.

Importantly I'm trying to use 'Windows authentication' instead of 'SQL authentication' to login to the database. (Note that SQL auth is NOT an option for me!)

<%

Dim Conn
Set Conn = CreateObject("ADODB.Connection")


Dim ConnectionString

Conn.ConnectionString = "Server= CLMSAWN002; Database= mohan_db; Integrated     Security=True;"
Conn.Open ConnectionString


Conn.CommandTimeout=120

Sub CloseConn
    Conn.Close
     Set Conn = Nothing
End Sub

%>

In the live environment I get the following error.

Microsoft OLE DB Service Components error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB 
status value, if available. No work was done.

/CustomerMarketing/_db.asp, line 10

Can you help me understand what cause this and a possible solution?

MS SQL Query criteria - need to extract date from datetime

I can't for the life of me get this to work. I've tried just about every example I've seen on here and all over the internet. I'm trying to query based on a month's worth of data. The data in the view I'm querying is in this format: 2012-03-20 00:00:00.000. Using the following criteria it's bringing back all of the dates (or nearly all of them). Any ideas?

  AND cast(convert(varchar(10),lag.postingdate,112) as datetime) between '2015-01-01' and '2015-01-31'






Original Query:
     SELECT prov.pgrp_id AS PERFORMING_PROV_ID
     , prov.pgrp_prov_name AS PERFORMING_PROV_NAME
     , lag.chgno AS CHARGE_NUM
     , lag/countcharges AS LAGTIME
     , lag.chgamt
     , lag.postingdate
     FROM dbo.Providers prov
     RIGHT JOIN dbo.LagTime_Charges_Calcs lag ON prov.pgrp_prov_cd =       lag.provcode 
AND prov.pgrp_practice = lag.px 
LEFT JOIN dbo.PlaceofService_union_v pos ON lag.px = pos.px 
AND lag.poscode = pos.poscode
WHERE pos.posid='1' Or pos.posid ='2' AND prov.Laginclude ='y'
and MONTH(lag.postingdate) = 1
and   YEAR(lag.postingdate) = 2015 

--and lag.postingdate between '2015-01-01 00:00:00.000' and '2015-01-31 23:59:59.000'
--AND cast(convert(varchar(10),lag.postingdate,112) as datetime) between '2015-      01-01' and '2015-01-31'

SQL - Add Data to Existing Data From Another Table

I have a temp table @table in my stored procedure that looks like this:

AgtID | Bonus
-------------
5063  | 0
1104  | 0

And a table bonus that looks like this:

AgtID | Contest | Points | Event
--------------------------------
5063  | 21      | 1000   | 1
5063  | 21      | 3000   | 3
1104  | 21      | 1000   | 1
1104  | 21      | 500    | 2

And an update query (currently) like thus:

UPDATE tt
SET Bonus = b.Points
FROM @table tt
INNER JOIN tblContestData_Bonus b
ON tt.AgtID = b.AgtID
where ContestID = 21;

Currently, when I run this query, it'll overwrite whatever data is in @table.Bonus with the data in bonus.Points. This is fine for a single record, but as we start getting more bonus point events, I need to have it add to my value.

I know some languages have a i += value... does SQL have something similar?

Calculating Time Difference Between each 2 rows in SQL Server

I have db records like this,

Connected                 UpdatedTime
1                         2015-02-05 13:13:23.420
0                         2015-02-05 23:40:50.360
1                         2015-02-05 23:40:50.357
0                         2015-02-05 23:34:41.250

1 is when I connect external device to pc and 0 is when I disconnect it. I want to find how many seconds usb device was connected and how many seconds it was disconnected.

My Question:

Can I do that using MSSQL Query?

FULL TEXT SEARCH

I want to run this statement:

USE [dbname]
EXEC sp_fulltext_database 'enable'

in a SQL Server 2005 instance, but I'm not sure if I have to stop the service before run it, or if I have to restart the service after run it.

regards.

SQLServer 2005 Full text search component reported error while indexing

Earlier today we had an unexpected server restart and since this time the full text crawl log has a lot of errors and the outstanding batches count is growing quite high, currently at 10K+

2015-02-09 14:45:10.46 spid27s     Error '0x80004005' occurred during full- text index population for table or indexed view '[MYDB].[dbo].[FullTextTable]' (table or indexed view ID '1172251281', database ID '6'), full-text key value 0x981EB6385A396244B7AF363247022651. Attempt will be made to reindex it.
2015-02-09 14:45:10.46 spid27s     The component 'DwgFilter.dll' reported error while indexing. Component path 'C:\Program Files\IFilterShop\DwgFilter\DwgFilter.dll'.

2015-02-09 14:45:10.46 spid27s     Error '0xc000014b' occurred during full-text index population for table or indexed view '[MYDB].[dbo].[FullTextTable]' (table or indexed view ID '1172251281', database ID '6'), full-text key value 0xB95B9A61BF969248A64E363247022489. Attempt will be made to reindex it.
2015-02-09 14:45:10.46 spid27s     The component 'thawbrkr.dll' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\thawbrkr.dll'.

Most of these are of the above 2 types. I've tried searching for a solution but for the most part I can't find anything that refers to the above dlls so would appreciate either some pointers or solutions to the issue.

I don't know if these errors were occurring prior to the restart as the logs only start at this time.

SQL Replication status

I want to check the if the publisher's status when it stop for reinitialisation can I reinitialisation the publication using C# or SQL command

SqlClient.SqlException (0x80131904) The identifier that starts with

I am using stored procedure as below anyhow for some files i am retreiving following error. What could be the reason?:

 System.Data.SqlClient.SqlException (0x80131904): The identifier that starts with 'D:\DISKSEC\TETRAIBMSolution\TETRA\Data\IDJSKDOSD\Parsed\IOPDCVv3995ReportErgfdgfgdfIOPShun_e6c5b926-fb91-484e-bfe0-9c13a96f' is too long. Maximum length is 128.

Stored procedure i am using:

 ALTER PROCEDURE [dbo].[BulkInsertDelimeted] 
                @InputFileName Varchar(700), 
                @TableName Varchar(200) 
        AS 
        BEGIN  
        declare @query varchar(6000) 
          set @Query ='BULK INSERT "' + @TableName + '" FROM "'+ @InputFileName+'" 
                WITH ( DATAFILETYPE =  ''char'', FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'; 
        print @query 
        exec (@query) 
        END

Sending an email from a stored procedure in SQL Server 2005

I am trying to get a stored procedure to work in SQL Server 2005 that was written for SQL Server 2000. It sends an email when called from an application. I tried to run the first part of the stored procedure that creates an OLE object. The 2005 Server is 32-bit. I get the error:

OLE Automation error Information HRESULT: 0x80040154 Source: ODSOLE Extended Procedure Description: Class not registered

This is the query that I am running:

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)

EXEC @hr = sp_OACreate '{DB486713-E4A9-11D3-AC19-00C04FA308B2}', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

I don't know what kind of object that is created from the CLSID. There is no documentation.

Does anyone know why I am getting this error?

THanks.

asp.net Sql Server 2005 how to Combine Multiple Record Values into One Column

I have 5 tables ( movies – categories - movie-catecories - directors-Movie_Directors )

movies movie_Id int title varchar (50)

image varchar(100)

categories cat_Id int cat_name varchar(50)

movie-catecories MovieCatrgory_ID int cat_Id int movie_Id int

directors director_Id int Name varchar(50)

Movie_Directors

MovieDirectors_ID int movie_Id int director_Id int DirectorName varchar(50)

movies

movie_Id title image 1 Hyde Park on Hudson movies/1.jpg 2 Playing for Keeps movies/2.jpg 3 Cheerful Weather for the Wedding movies/3.jpg 4 Heleno movies/4.jpg 8 Deadfall movies/6.jpg

categories

Cat_Id Cat-name 1 Drama 2 Romance 3 Comedy 4 Romance 5 Crime

movie-catecories

MovieCatrgory_ID Cat_Id movie_Id 1 1 1 2 1 2 3 2 3 4 2 4 5 3 8

Directors

Director_ID Name 1 Roger Michell 2 Gabriele Muccino 3 Donald Rice 4 José Henrique Fonseca 5 Sung-Hee Jo

Movie_Directors MovieDirectors_ID movie_Id director_Id DirectorName 1 1 1 Roger Michell 2 1 2 Gabriele Muccino 4 1 3 Donald Rice 5 2 4 José Henrique Fonseca 6 2 5 Sung-Hee Jo

SELECT DISTINCT movies.movie_Id, movies.title, movies.image, Movie_Directors.DirectorName, Movie_Directors.director_Id

FROM movies INNER JOIN Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN directors WHERE (movies.title LIKE '%' + @title + '%')

**Result

Movie_ID tittle image DirectorName director_Id 1 Hyde Park on Hudson movies/1.jpg Roger Michell 1 1 Hyde Park on Hudson movies/1.jpg Gabriele Muccino 2 1 Hyde Park on Hudson movies/1.jpg Donald Rice 3

I want to combine the DirectorName in one record like this

Movie_ID tittle image DirectorName director_Id 1 Hyde Park on Hudson movies/1.jpg Roger Michell, Gabriele Muccino, Donald Rice 1-2-3

I have problem in this code to combine the DirectorName in one record Please help me **

SELECT DISTINCT movies.movie_Id, movies.title, movies.image, 

substring( (select ‘; ‘ +Movie_Directors . DirectorName from Movie_Directors inner join movies on Movie_Directors.movie_Id = movies.movie_Id

for xml path(”)), 3, 1000) as CombineDirectorName 

FROM movies INNER JOIN Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN directors WHERE (movies.title LIKE '%' + @title + '%')

and display the result in Repeater:

<asp:Repeater ID="Repeater1" runat="server" >
<HeaderTemplate> </HeaderTemplate>
<ItemTemplate>
<div style="width:100%;">

<div class="excerpt">
 <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="thumb" title="An image"><img src="<%# DataBinder.Eval(Container.DataItem, "image")%>" alt="Post" style="opacity: 1; float:left; width:80px ; height:100px; border:3px solid #fff ; margin:5px;"></a>
        <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="header"><h6><%# DataBinder.Eval(Container.DataItem, "title")%>

<%# DataBinder.Eval(Container.DataItem, " Movie_Directors . DirectorName ")%>

</div>
</div><br />


<hr />
</ItemTemplate>
</asp:Repeater>

C#

protected void LinkButton1_Click(object sender, EventArgs e) {

        string cat_id = DropDownList1.SelectedValue;
        string keyword = TextBox1.Text;
        int? cid = int.Parse(cat_id);

        Repeater1.DataSource = dc.StoredProcedure1(TextBox1.Text, TextBox1.Text, cid);
        Repeater1.DataBind();

}

Does SqlDataReader transfer 2nd set before, or as part of, .NextResult

When using the SqlDataReader class in .NET 4.0 to return multiple sets of data from a stored procedure, does it transfer ALL the data from SQL Server for ALL the sets from the start - or does it only transfer subsequent sets of data on each call to .NextResult()?

Or to put it another way: If I failed to call .NextResult() would it still go through the process of transferring the data for the 2nd data set?

I have a stored procedure that potentially returns thousands of rows of data, and I would like to be able to split the stored procedure into two parts:

• Data set 1: a scalar value which denotes the number of rows that will be returned


• Data set 2: the actual rows

In an ideal world I would like to be able to call this stored-procedure, and if the scalar value is under a pre-set number then call .NextResult() and then process the rows from the 2nd data set. And if the scalar value is over the pre-set, then not calling .NextResult() will not require the data to be transferred from the SQL Server.

The simple solution is to have two stored procedures (one to return the number of rows, the other to return the rows)... or have a flag on a single stored procedure to return the number of row or actual rows, and call it twice.

Finding job owner of a SQL Agent job

I want to find all the jobs which are created by anyone other than sa.

I'm using below query:

select s.name,l.name
from  msdb..sysjobs s 
      left join master.sys.syslogins l on s.owner_sid = l.sid

But owner name comes as null for users who are not part of server log in. Will the name or user id will be stored in any sys tables?

How to add numeric value as seconds to a datetime field and report data in new column

I need to convert the ElapsedTime field into hours/minutes/seconds and add that to the creationtime field and have the results reported into a new column that I'll call EndTime. Here is the query I have to gather the data:

select ElapsedTime, ChannelUsed, documents.creationtime from historytrx (nolock) inner join history on historytrx.handle = history.handle inner join documents on history.owner = documents.handle inner join DocFiles on documents.docfiledba = docfiles.handle where creationtime > '2015-02-02 20:00:00.000' and creationtime < '2015-02-02 20:01:00.000'  and RemoteServer = 'DMG4120-01668' and ElapsedTime != '0'

Here is the current output:

ElapsedTime   ChannelUsed    creationtime
1042          1              2015-02-02 20:00:03.000
27            35             2015-02-02 20:00:05.000
57            50             2015-02-02 20:00:05.000

Here is my desired output:

ElapsedTime   ChannelUsed    creationtime             EndTime
1042          1              2015-02-02 20:00:03.000  2015-02-02 20:17:39.000
27            35             2015-02-02 20:00:05.000  2015-02-02 20:00:32.000
57            50             2015-02-02 20:00:05.000  2015-02-02 20:01:03.000

Thanks to everyone in advance for any assistance.

sql query is giving repeatative data

select a.invoice_no, 
       a.item_no ,
       b.item ,
       (a.invoice_qty * b.per_qty),
       c.std_purchase_price 
from shp..dms_t_invoice_item a
left outer join pmddb..bom_stock_tb b 
on b.fg = a.item_no 
left outer join pomdb..pur_vendor_stock c
on c.stock_no = b.item  
where a.acsd_date between '2014-04-01 00:00:00.000' and '2014-04-30 00:00:00.000'
order by a.invoice_no

SQL statement to select from 2 different tables, from two different databases (same server)

How do I select from multiple tables in different databases on the same server?

Also, Is there a way to have an identifying marker so I could see where the results came from?

So for example:

SELECT db1.table1.name, db2.table2.name, fromTbl
FROM db1.table1, db2.table2
WHERE db1.table1.name LIKE '%j%' OR db2.table2.name LIKE '%j%'

So in this case, I'm selecting the names from 2 different databases and tables. I'm doing a wildcard search on those names and the fromTbl would let me know where the results came from?

4    john smith    4    555.555.5555    table1
17   joe schmoe    17   555.555.5555    table2
11   james doe     11   555.555.5555    table1

I'm using SQL Server 2005. Thanks for the input!

I've lost a scalar function in Access 2010 - how do I put it back? (I have the function in SQL)

I have a form in Access 2010 (.adp, not .accdb) that references a function titled dbo.search which seems to be missing. I am getting this error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.search", or the name is ambiguous.

I cannot find the function "search" in the navigation pain, so I think I need to add it back. I have the function in a SQL 2005 database - what do I do?

One Query 2 Joins

From my main table, I want to show all applicable dates from the calendar table and only show data that does not exist in the Branch table. This is the query I am trying, but it is returning way more results that it should be. Did I do a join incorrectly? Should I be using a sub-query or CTE instead?

Select
'Master' As [Type],
Main.EmployeeID,
Main.BankName,
Main.BankEmployeeName,
Main.Title,
Main.loan_status,
Cal.[Month],
Cal.[Year],
Main.LoanOfficer
From dbo.Calendar Cal
Inner Join MainBankInformation Main
On Cal.[Month] = Main.LoanMonth
Left Join BranchBankInformation Branch
On Main.EmployeeID = Branch.EmployeeID
And Main.loan_status IN ('approved', 'complete', 'final')

Build Calendar With Quarters Included

I know how to build a SQL Calendar with month & Year. But I need to also add Quarters into the calendar which I'm not sure how to set-up. For example, sample data output would need to be Q1 January February March Q2 April May June Q3 July August September Q4 October November December

What is the best syntax for creating a calendar with quarter information, or how could my syntax be tweaked to add in the quarter information as well? This is what I am currently using

Declare @startdate DateTime, @enddate DateTime;
Select @startdate = '2014-01-01', @enddate = '2016-12-31'
;With Num1 (n) As (
Select 1 as n
Union All Select n+1 as n
From Num1 Where n<101),
Num2 (n) As (Select 1 From Num1 As X, Num1 As Y),
Nums (n) As (Select Row_Number() Over(Order By n) From Num2)

,myDateCTE As
(Select Cast(dateadd(day,n-1,@startdate) as date) dt
from Nums)

Select dt, datename(month, dt) from myDateCTE
Where dt <= @enddate

remote connection to sql database

What is the connection string which connect remotely to sql database via visual studio c. Net