Stored Procedures and asp.net programmability; variable or SQL

Trying to display a users Lastname, Firstname --- Website And I need to insert a comma and space after Lastname to a GridView. I am trying to add a CASE statement in SQL and having trouble figuring it out.

Perhaps I need to use @parameter (scalar variable?) to abstract the memory read from CASE statement; or my syntax is wrong and I just don't understand.

SELECT 
CASE
          WHEN IsNull(people_Table.firstName, '') = ''
          THEN CONCAT(people_Table.lastName, ', ',
          people_Table.firstName) 
          ELSE people_Table.lastName
          END as fullName, people_Table.website
FROM people_Table INNER JOIN membership_Table on people_Table.ID =
membership_Table.personID
WHERE rectype = 'Master'
AND membershipType = 'Business'
AND expirationDate > GetDate()
ORDER BY people_Table.lastName

Edit: Getting SqlServer error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'people_Table'.

Otherwise I suppose I should use an asp databoundevent in the template. What is better for performance and security?

Passing One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Procedure 1:

EXEC Parse
@Part = '0123,4567'
@Qty = '1,1';

returns the following:

Part        Qty
0123         1
4567         1

This procedure simply takes a part and quantity input and parses the strings at each instance of ",".

Procedure 2:

EXEC PA
@Part = '0123'
@Qty = '1';

returns the following:

Top-Level Assembly     TotalQty      MaterialPart     Qty
      0123                1             12A            2
      0123                1             13A           21
      0123                1             14A            5

My overall goal is to have a user enter an assembly part or list of assembly parts (delimited by a comma) and their appropriate quantities. The first procedure creates a result list of all the assembly parts. The second procedure should run off of the result set from the first procedure to get all of the pieces that make up the assembly part.

How can I run my second procedure based off of the result of the first procedure? Any help is greatly appreciated!!

How to show all database objects that operate on a given object

Is there a command to show all the jobs and functions and stored procedures etc. in a database where operations involving a specific table are executed? For example, all the jobs that INSERT into Tablex, all the jobs that CREATE Tablex, and any other operations on that table.

Thank you!

Stored Procedure in cursor firing only once

I am getting an output from a stored procedure but in my cursor it only returns the first value.

SP 1

ALTER PROCEDURE [dbo].[register_system_email_audits]
    @UserId int,
    @EmailFor varchar(500),
    @DateSent datetime,
    @UniqueKey varchar(20) output
AS
BEGIN
    INSERT INTO [SystemEmailsAudit]
           ([UserId]
           ,[EmailFor]
           ,[DateSent]
           ,[UniqueKey]
           )
     VALUES
           (@UserId 
           ,@EmailFor
           ,@DateSent
           ,(SELECT CAST( CAST(RAND() * 100000000 AS int) as varchar(20)))
           );
     SELECT @UniqueKey=s.UniqueKey FROM [SystemEmailsAudit] s 
        WHERE s.RecordId=SCOPE_IDENTITY();

END

SP2

ALTER PROCEDURE [SendNewsletterMails]
(
    @nLID int,
    @Category VARCHAR(50)
)
as
DECLARE
  @html varchar(max),
  @Description VARCHAR(100),
  @Subject varchar(50),
  @Email varchar(100),
  @listID   int,
  @DLC smalldatetime,
  @Date DATETIME = NULL
    set @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 BEGIN
    --Add Beacon
    DECLARE @UniqueKey varchar(20)
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END
  CLOSE crsEmailList
  DEALLOCATE crsEmailList
GO

The stored procedure returns the proper @UniqueKey but only for the first record in the cursor. I have been contemplating a while loop or a temp table but settled on the cursor route for now.

Which SQL Query is the site running?

The websites (intranet sites or extranet sites - sometimes web portals) at my company return certain results (which is obtained via SQL queries/commands in the back-end systems). I"m trying to find out which queries are being run in the background and how I could track back the query results onto the tables where they come from. How can I achieve that? I tried looking at the "source" but found no queries there. Back-end uses SQL Server if that matters.

SQL Server Query Aid

I have a query in SQL Server to return a list of Reports, it has to return either a string representing a location, or a string representing the store it's referencing.

The issue is my query is only returning reports that references a store id, instead of returning all reports and the relevant location information. I'm convinced its a stupid syntax issue, but I haven't done database work for a while, and can't seem to pick it out. I've tried several different ways to get this to work, but it simply refuses.

SELECT rep.rep_id AS "RepId", ISNULL(rep.rep_status, 'C') AS "RepStatus", ISNULL((loc.location_street + ' ' + loc.location_city), store.Description) AS "Location", rep.date_reported AS "DateReported", rep.reported_by AS "ReportedBy"
FROM Report rep JOIN Report_Location reploc ON reploc.rep_id = rep.rep_id
JOIN Location loc ON loc.location_id = reploc.location_id
LEFT JOIN Store store ON store.StoreID = loc.store_id;

I've tried removing the left join and just adding a where loc.store_id = store.StoreID or loc.store_id IS NULL. Neither worked. Thanks in advance for your help.

delete millions records using partition tables?

We write daily about 1 million records into a sql server table. Records has a insertdate and status fields, among others of course. I need to delete records from time to time to free space on the volume but leaving the last 4 days records there. The problem is the deletion takes hours and lots of resources.

I have think about partition tables setting the partition field on the insertdate, but I never used that kind of tables.

How can I archieve the goal using the less cpu/disk resources and having the solution the less drawbacks possible? (I assume any solution has its own drawbacks, but please explain them if you know).

Thank you in advance

SQL Lookup table & Entity Framework 6

I have 2 tables in a SQL database 'tbl_Job' and 'tbl_JobType', I added a FK relationship to tbl_Job pointing to tbl_JobType but when I reverse engineer using Entoty Framework 6 code first it reads as

 this.HasRequired(t => t.JobType)
     .WithMany(t => t.Jobs)
     .HasForeignKey(d => d.JobTypeId);

And it has completely through me, I appreciate SQL doesn't understand 1 to 1 relationships and neither does EF6 but i wasn't expecting the foreign key relationship to create a navigation property pointing in the wrong direction.

Does anyone have a suggestion on what the best way to use a lookup table in SQL with a relationship or constraint with an example?

Thanks in advance.

Using TRY / CATCH to perform INSERT / UPDATE

I have this pattern in a number of stored procedures

-- Table1
[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL
[count] INT NULL

-- 'data' is unique, with a unique index on 'data' in 'Table1'
BEGIN TRY 
    INSERT INTO Table1 (data, count) SELECT @data,1;
END TRY
BEGIN CATCH
    UPDATE Table1 SET count = count + 1 WHERE data = @data;
END CATCH

I've been slammed before for using this pattern

You should never have exception "catching" in your normal logic flow. (Thus why it is called an "exception"..it should be exceptional (rare). Put a exists check around your INSERT. "if not exists (select null from Data where data = @data) begin /* insert here */ END

However, I can't see a way around it in this instance. Consider the following alternative approaches.

INSERT INTO Table1 (data,count) 
SELECT @data,1 WHERE NOT EXISTS 
    (SELECT 1 FROM Table1 WHERE data = @data)

If I do this, it means every insert is unique, but I can't 'catch' an update condition.

DECLARE @id INT;  
SET @id = (SELECT id FROM Table1 WHERE data = @data)

IF(@id IS NULL)
    INSERT INTO Table1 (data, count) SELECT @data,1;
ELSE 
    UPDATE Table1 SET count = count + 1 WHERE data = @data;

If I do this, I have a race condition between the check and the insert, so I could have duplicates inserted.

BEGIN TRANSACTION
   DECLARE @id INT;  
   SET @id = (SELECT id FROM Table1 WHERE data = @data)

   IF(@id IS NULL)
       INSERT INTO Table1 (data, count) SELECT @data,1;
   ELSE 
       UPDATE Table1 SET count = count + 1 WHERE data = @data;
END TRANSACTION

If I wrap this in a TRANSACTION it adds more overhead. I know TRY/CATCH also brings overhead but I think TRANSACTION adds more - anyone know?.

People keep telling me that using TRY/CATCH in normal app logic is BAD, but won't tell me why

Note: I'm running SQL Server 2005 on at least one box, so I can't use MERGE

Create a function for generating random number in SQL Server trigger

I have to create a function in a SQL Server trigger for generating random numbers after insert. I want to update the column with that generated random number please help what I have missed in my code.

If you know other ways please suggest a way to complete my task.

This my SQL Server trigger:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT 
AS 
    declare @EnquiryId int;
    declare @ReferenceNo varchar(50);
    declare @GenReferenceNo NVARCHAR(MAX);

    select @EnquiryId = i.enquiry_id from inserted i;
    select @ReferenceNo = i.reference_no from inserted i;
BEGIN
     SET @GenReferenceNo = 'CREATE FUNCTION functionRandom (@Reference VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        As
        Begin
        DECLARE @r varchar(8);
        SELECT @r = coalesce(@r, '') + n
        FROM (SELECT top 8 
        CHAR(number) n FROM
        master..spt_values
        WHERE type = P AND 
        (number between ascii(0) and ascii(9)
        or number between ascii(A) and ascii(Z)
        or number between ascii(a) and ascii(z))
        ORDER BY newid()) a

        RETURNS @r
        END
        '

        EXEC(@GenReferenceNo)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- update statements for trigger here
    UPDATE enquiry_master 
    SET reference_no ='updated' 
    WHERE enquiry_id = @EnquiryId
END   

SQL to find rows with a similar numeric value

I have a table in a database which lists the similarity of an item to another, where each row(s) is essentially a search result, where similarity is a numeric value.

A row is either a parent (no similarity level) which may have "children" results

Or a child, where a numeric similarity percentage is given of its parent

What I need to do is identify all the items which are similar. This can be done as if two items have a near identical similarity score to a parent, then those two items can be said to be similar.

However; I'm having trouble accomplishing this with SQL. I'm using Access, and can split the table into parents and children if need be, but can't do much more

An example of my table is below:

id, parent, score
aaa,,
aab,,
cas,aab,97
cad,aab,96
agd,aab,70
aac,,
aad,aac,100

In the above example, I'd like to pick out items "cas" and "cad" as the results.

Conversely, I can pick out all the results which are similar to a parent (such as aab and aac) via a simple SELECT query.

Thanks for the help.

ASP.net get max value from Profile field

I have a classic ASP website containing a users table with a ID_USER field (int, primary key, auto increment). The ID_USER value is used to track user's activity and is saved in other tables as part of the "Saved by","Saved date" logic.
Now, the website was updated and moved to ASP.NET, with the authentication rewritten using the ASP.NET membership and Profile providers. The old user's table was imported in the new structure. The ID_USER field became a Profile value in the new Membership/Profile logic.
All the other tables remained the same becouse of the compatibility between the two websites.

Question:
When creating new users I need to set the value for the ID_USER field too.
How can I do this? Can I somehow get the max value of the ID_USER profile field?
Thanx

How to run single select statement across all the databases in the same schema

I need to run a simple select statement across all the databases in the schema(SQL Server). I have around 30-40 databases. This table has same structure in all the databases.

select * from table1 where condition

Can you please let me know how to get the records from all databases??

Can't use addslashes on PHP SQL Server ODBC

EDIT I'm using MS SQL, I query to MySQL then Insert it to MS SQL

I wrote a script that will query a data from a databse and upload it to another database. So the script is like this

    $id                       = $row["id"];
    $title                    = $row["title"];
    $firstname                = safe($row["firstname"]);
    $surname                  = safe($row["surname"]);
    $dob                      = $row["dob"];
    $phone                    = $row["phone"];
    $addr1                    = safe($row["addr1"]);
    $addr2                    = $row["addr2"];
    $towncity                 = $row["towncity"];
    $postcode                 = $row["postcode"];
    $user_platform            = $row["user_platform"];
    $user_browser             = $row["user_browser"];
    $user_browser_ver         = $row["user_browser_ver"];
    $user_ip                  = $row["user_ip"];
    $terms                    = $row["terms"];
    $privacy_policy           = $row["privacy_policy"];
    $column_header            = $row["column_header"];
    $column_header_response   = safe($row["column_header_response"]);
    $column_header_response_2 = safe($row["column_header_response_2"]);
    $column_header_response_3 = safe($row["column_header_response_3"]);
    $column_header_response_4 = safe($row["column_header_response_4"]);
    $column_header_response_5 = safe($row["column_header_response_5"]);
    $filename                 = $row["filename"];
    $cpl                      = $row["cpl"];
    $rejected                 = $row["rejected"];
    $reject_reason            = $row["reject_reason"];
    $email                    = $row["email"];
    $created_at               = $row["created_at"];
    $updated_at               = $row["updated_at"];

$values = "
            $id,
            '$title',
            '$firstname',
            '$surname',
            '$dob',
            '$phone',
            '$addr1',
            '$addr2',
            '$towncity',
            '$postcode',
             $age,
            '$user_platform',
            '$user_browser',
            '$user_browser_ver',
            '$user_ip',
            '$terms',
            '$privacy_policy',
            '$column_header',
            '$column_header_response',
            '$column_header_response_2',
            '$column_header_response_3',
            '$column_header_response_4',
            '$column_header_response_5',
            '$filename',
            '$cpl',
            '$rejected',
            '$reject_reason',
            '$email',
            '$created_at',
            '$updated_at'
            ";

 function safe($value){ 
   return addslashes($value); 
} 

Then this

$query = "INSERT INTO forms VALUES(".$values.");";

Then I have some error

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Neill'., SQL state 37000 in SQLExecDirect 

which is likely to be the unescapped string but I already have it. When I tried to echo the query:

INSERT INTO forms VALUES( 122, 'Miss', 'John', 'O\'Neill', '1973-08-16', '+447939161234', '31w Red Square', '', 'Johnstone', 'PA5 8AD', 44, '', '', '', '', '', '', 'washing_machine', '5yrs and above', 'zanussi', '', '', '', '', '0.17', '', '', 'john.oneill@gmail.net', '2015-08-04', '2015-08-04' );

Looks like the O'Nielll is not escapped or there's a problem with my quotes? How to fix this? thanks

cakePHP find("list") returns empty array

I am trying to make a drop down list of users by using the foreign key [UserID]. In the controller, I have find("list"). When I debug $this->Order->SalesAgent in the controller, it prints the User Object. However, in the view page, when I debug the result of $this->Order->SalesAgent->find("list"), shows and empty array.

Heres the Controller:

    public function edit_sales_agent ($id=null) {
        debug($this->Order->SalesAgent);
        $this->set("users",$this->Order->SalesAgent->find("list"));
        debug($this->users);
    }

and heres the View:

debug($users);
echo $this->Form->create("Order");
    echo $this->Form->input("UserID");

$users is the result of find("list")

Could anyone help me out? Thanks!

Association:

class Order extends AppModel{
    public $useTable = 'CustomerOrder';
    public $primaryKey = 'OrderID';
    **public $belongsTo = array(
        "SalesAgent"=>array(
            "className"=>"User",
            "foreignKey"=>"UserID"**
        ),

Sales Agent Model:

<?php
class User extends AppModel{
    public $useTable = 'UserAccount';
    public $primaryKey = 'UserID';
    public $order = array(
        "User.LastName"=>"asc",
        "User.FirstName"=>"asc"
    );
    public function __construct($id = false, $table = null, $ds = null) {
        parent::__construct($id, $table, $ds);
        $this->virtualFields['full_name'] = sprintf("(%s.FirstName+' '+%s.LastName)", $this->alias, $this->alias);
    }
    public function login($data){
        return $this->find("first",array("conditions"=>$data['User']));
    }
}

UPDATE:

Alright, so I figured out what the problem is but I dont know how to fix it. When I type find(list), this is the query it runs:

SELECT [SalesAgent].[UserID] AS [SalesAgent__0], [SalesAgent].[UserID] AS [SalesAgent__1] FROM [UserAccount] AS [SalesAgent] WHERE 1 = 1 ORDER BY [User].[LastName] asc, [User].[FirstName] asc

THis is the error it proposes:

SQL Error: The column prefix 'User' does not match with a table name or alias name used in the query. [APP/Model/Datasource/Mssql.php, line 749]

The SalesAgent uses class User, which uses table UserAccount

SQL Server snapshot replication: error on table creation

I receive the following error (taken from replication monitor):

The option 'FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME' is only valid when used on a FileTable. Remove the option from the statement. (Source: MSSQLServer, Error number: 33411)

The command attempted is:

CREATE TABLE [dbo].[WP_CashCenter_StreamLocationLink]( [id] [bigint] NOT NULL, [Stream_id] [int] NOT NULL, [Location_id] [numeric](15, 0) NOT NULL, [UID] [uniqueidentifier] NOT NULL ) WITH ( FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UC_StreamLocation] )

Now, for me there's two things unclear here.

1. Table already existed on subscriber, and I've set @pre_creation_cmd = N'delete' for the article. So I don't expect the table to be dropped and re-created. In fact, table still exists on subscriber side, although create table command failed to complete. What am I missing? Where does this create table command come from and why?

2. I don't understand why does this FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME option appear in creation script. I tried generating create table script from table in SSMS and indeed, it's there. But what's weird, I can't drop and re-create the table this way - I get the very same error message.

Difficulty printing one particular query in MSSQL

I'm trying to construct a small query which will pull data from individual fields in a DB and print them in a human readable list format (it's what the operators are used to seeing). The code I have here is far from complete but It seems to me that it should work.

DECLARE @PSUCARD VARCHAR(20)
DECLARE @EQUIPMENT VARCHAR(50)
DECLARE @T1 VARCHAR
SET @PSUCARD = 'PSU-888'
SET @EQUIPMENT = '123_POUCH'

PRINT @PSUCARD + ':'
PRINT @EQUIPMENT
PRINT ''

IF (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND     PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)IS NOT NULL  BEGIN
    SET @T1 = (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
    PRINT 'Temperature: ' + @T1
    --(SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
END

If I execute the code as is, @T1 returns a * rather than a value. If I remove comments from the line below I am reassured that there is indeed a value there. I have other code very similar to this which works fine. Any ideas?

Also, I don't know if this helps in diagnosing the problem, but despite the temperature field in the DB being an INT, I get a conversion message if I try to treat @T1 an an INT.

Updating table by using self joining the same table

I have a @table like below. I need to calculate the values for the row "Apple left in warehouse after".

For this I use this query. But I am not getting the final counts correctly for TA.Left_Counts. Could you please correct the query if I am wrong.

UPDATE TA
SET TA.Left_Counts = TA1.Left_Counts + TA2.Left_Counts +  
                     TA3.Left_Counts - TA4.Left_Count
FROM @TableA TA
INNER JOIN @TableA1 TA1 ON (TA.offsetNumber-1) = TA1.offsetNumber
                        AND TA1.DataField = 'Apple left in Warehouse After' --> To get the Apple's left in warehouse from previous month.
INNER JOIN @TableA TA2 ON TA.DateField = TA2.DateField
                       AND TA2.DataField = 'Apple in Stock' 
INNER JOIN @TableA TA3 ON TA.DateField = TA3.DateField
                       AND TA3.DataFields = 'Apple in Production'
INNER JOIN @TableA TA4 ON TA.DateField = TA4.DateField
                       AND TA4.DataFields = 'Apples Sold'
WHERE 
    TA.DataFields = 'Apple left in Warehouse After' 
    AND TA.offsetNumber <> 0

SQL server, data integrity - table referenced by many tables

I have a table which has some generic data, that must be referenced by a multiple number of other tables. The referenced table can't be simplified to fit columns of the referencing tables. How do I enforce data integrity and relationships in such a scenario?

Update or delete splitted data

In customers table I have Email column which could contain multiple emails separated by (;).
I used split function to separate emails for each customer:

Cust1 --->email1
cust1 --->email2
cust1 ---> emailN

And I could add more emails to the same customer.
I want to be able to update or delete the splitted emails, in other words if email2= abc@company.com I want to change it to xyz@company.com or delete it.
Is it possible to do using split function? or any other way?

Here is my split function

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Calling the function to split emails:

select tb1.custId, split.splitdata from customers tb1
outer apply [dbo].[fnSplitString] (tb1.email,';') split
where tb1.Email like '%;%'

To add new email to the same customer:

UPDATE Customers set Email=Email+';new Email' Where CustId='customerId'

for updating or deleting existing emails, any suggestions?

Thanks in advance

How to connect to (localdb) database on server for Asp.Net membership?

I'm trying to output a list of users membership.GetAllUsers(), but I get an exception:

When establishing a connection to SQL Server error has occurred that is associated with the network or with a specific instance. Server not found or unavailable. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error upon detection of the specified server or instance)

My connection string :

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-SDC-20150804171038;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-SDC-20150804171038.mdf" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Create new dimension using values from another dimension in SQL?

I currently have a SQL table that looks something like this:

   RuleName      | RuleGroup
---------------------------
Backdated task   | DRFHA  
Incorrect Num    | FRCLSR
Incomplete close | CFPBDO
Appeal close     | CFPBDO
Needs letter     | CFPBCRE
Plan ND          | DO
B7IND            | CORE

I am currently writing SSMS procedure that pulls these dimensions from the existing table. However, I also want the procedure to create a new dimension that will create a "SuperGroup" dimension for each rule based on the text in it's RuleGroup (and an other column for the rest). For example:

   RuleName      | RuleGroup | SuperGroup
--------------------------------------------
Backdated task   | DRFHA     | Other
Incorrect Num    | FRCLSR    | Fore
Incomplete close | CFPBDO    | DefaultOp
Appeal close     | CFPBDO    | DefaultOp
Needs letter     | CFPBCRE   | Core
Plan ND          | DO        | DefaultOp
B7IND            | CORE      | Core

I have currently tried used the "GROUP BY" function, as well as using SELECT with several "LIKE" statements. However, the issue is that this needs to be scaleable - although I only have 21 groups right now, I want to automatically sort if new groups are added.

Here is the SSMS procedure as well:

CREATE PROCEDURE [Rules].[PullRulesSpecifics]
AS
BEGIN
SELECT
    ru.RuleName
    ru.RuleGroup
FROM RuleData.groupings ru
WHERE 1=1
AND   ru.ActiveRule = 1
AND ru.RuleOpen >= '2015-01-01'

Server managment studio Export/Import Tables: The Ordering of Execution in SSIS Package

im making some SSIS package to import data from production servers to my own local databases.

The question is, is there a way to order the execution of the tables?

Problem: Table1: PK Table2Key FK Table1Key Table2: PK Table2Key

It seams the ordering gets chosen based on the names of the tables. So here it will try Table1 first, this will fail couse there are forign key constraints to Table2

I want Table2 to get fetched before Table1. I tried editing the order of the components in the ssis package, this did not work :(

Kind regards Tor Einar

Why am I getting a converting data type error when selecting a decimal column?

I am trying to SELECT a column from a view as shown.

The datatype of the column is stored as a decimal(18, 7), null.

SELECT DecimalPercentageColumn 
FROM dbo.DetailsView

However when I try to do this an error message is returned:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

SQL query to list of all lg and lt based upon rectangular polygon points

I have two tables one having longitude and latitude points (2 columns) and other having gps coordinates which define a rectangular polygon (8 columns , 4 GPS co-ordinate , 2 point each)

Here is TABLE A

zip   state city                 lat    lng
35004   AL  Acmar             33.584132 -86.515570
35005   AL  Adamsville        33.588437 -86.959727
35006   AL  Adger             33.434277 -87.167455
35007   AL  Keystone          33.236868 -86.812861
35010   AL  New Site          32.941445 -85.951086
35014   AL  Alpine            33.331165 -86.208934
35016   AL  Arab              34.328339 -86.489638
35019   AL  Baileyton         34.268298 -86.621299
35020   AL  Bessemer          33.409002 -86.947547
35023   AL  Hueytown          33.414625 -86.999607

and TableB is as follows

locationID  lt_Upleft   lg_UpLeft   lt_UpRight  lg_UpRight  lt_DownLeft lg_DownLeft lt_DownRight    lg_DownRight
        1   41.7127     -75.0059     41.7127    -73.0059    39.7127        -75.0059  39.7127         -75.0059

Now I want to get result form TableA Which falls inside 8 points of rectangular polygon. And Same way I want to get result of TableB which falls under longitude and latitude points.

I am new to google maps things. Not sure how I will get results. I have gone through some sample eg :

DECLARE @POLIGON geography; 
SET @POLIGON = geography::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 4326);
DECLARE @LLTABLE TABLE
(
    POINT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
    ,LAT FLOAT NULL
    ,LON FLOAT NULL
)


;WITH NN1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N))
,NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM NN1 N1,NN1 N2, NN1 N3
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (20) ROWS ONLY)

INSERT INTO @LLTABLE (LAT,LON)
SELECT
   LATN.N  AS LATITUDE
   ,LONN.N  AS LONGITUDE
FROM NUMBERS LATN, NUMBERS LONN;

SELECT 
     LT.POINT_ID
    ,LT.LAT
    ,LT.LON
FROM @LLTABLE LT
WHERE geography::STPointFromText(CONCAT
        (
            'POINT('
           ,CAST(LT.LON AS VARCHAR(12))
           ,CHAR(32)
           ,CAST(LT.LAT AS VARCHAR(12))
           ,')'
        ), 4326).STWithin(@POLIGON)  = 1

But don't how to use it.

I need help SELECT querying all ISNULL or empty values in SQL [on hold]

I need help SELECT querying all ISNULL or empty values in all tables of a SQL ArcSDE database. I need this functionality in SQL management studio if possible. The SQL database is 2008R2, the SQL server is SQLEXPRESS locally.I have access to arcCatalog also if it is easier (10.3) and ArcServer 10.3.1.

So far I can highlight to see what I need (null verses not null in image below) and I can see each NULL column highlighted.

SELECT *
FROM [database].[user].[table]

quick query showing all fields in a single table:

SELECT TOP 1000 [OBJECTID]
      ,[EventID]
      ,[GlobalID]
      ,[TagID]
      ,[AlternateAssetID]
      ,[ISATID]
      ,[OriginEventID]
      ,[CreatedBy]
      ,[CreatedDate]
      ,[EffectiveFromDate]
      ,[EffectiveToDate]
      ,[LastModified]
      ,[ModifiedBy]
      ,[HistoricalState]
      ,[ProcessFlag]
      ,[SourceCL]
      ,[Remarks]
      ,[Description]
      ,[InServiceDate]
      ,[OperationalStatus]
      ,[SiteEventID]
      ,[Measure]
      ,[SeriesEventID]
      ,[Station]
      ,[SymbolRotation]
      ,[POINT_X]
      ,[POINT_Y]
      ,[POINT_Z]
      ,[RouteEventID]
      ,[VisualOffset]
      ,[TypeCL]
      ,[ManufacturerCL]
      ,[MaterialCL]
      ,[DiameterInletCL]
      ,[DiameterOutletCL]
      ,[WallThicknessInCL]
      ,[WallThicknessOutCL]
      ,[SpecificationCL]
      ,[PressureRating]
      ,[DateManufactured]
  FROM [test].[test].[REDUCER]

SQLCMD results to Excel

I am using SQLCMD command in powershell to run SQL scripts. When i pipe the output to TXT ,the formatting is all messed up. i want to export the results to excel file. how can i do that.

My command Sqlcmd -Q 'select top 10 * from people' -S serverName -d testDB -o c:\output.txt | format-table

Thanks in advance.

Starting problems with OTL 4.0 MS SQL, ODBC and Code:Blocks

I have to insert some values into a MS SQL-Database on a Windows Machine, but also have to insert some values in a MySQL-Database on Linux soon. So I looked around and found SOCI and OTL 4.0.

I decided to use OTL 4.0 because it looked more easy for one, who haven't worked with C++ and databases before.

I tried to compile a modification of an example ( made it easier) and stumbled serveral times.

My Code so far:

    #include "db.h"
#include <iostream>
#include <fstream>
#include <stdio.h>

#include "plc.h"

#include <sqlncli.h>
#include <oledbdm.h>

using namespace std;


#define OTL_ODBC_MSSQL_2008 // Compile OTL 4/ODBC, MS SQL 2008
//#define OTL_ODBC // Compile OTL 4/ODBC. Uncomment this when used with MS SQL 7.0/ 2000
#include "otlv4.h" // include the OTL 4.0 header file

otl_connect db; // connect object

void insert(TIMESTAMP_STRUCT tm, string table)
{

 string sql_statement;
 //sql_statement = "insert into " << table << " values (:"
 otl_stream o(50, // buffer size
              "insert into test_tab2 values(:f1<float>,:f2<timestamp>)",
                 // SQL statement
              db // connect object
             );

 for(int i=1;i<=10;++i){
  tm.year=1998;
  tm.month=10;
  tm.day=19;
  tm.hour=23;
  tm.minute=12;
  tm.second=12;
  tm.fraction=0;
  o<<(float)i<<tm;
 }
}



void getDataIntoDB(plcdata &data)
{
    otl_connect::otl_initialize(); // initialize ODBC environment
    try{

        db.rlogon("UID=scott;PWD=tiger;DSN=mssql2008"); // connect to ODBC

        }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from Oracle


}

Build log:

-------------- Build: Debug in TimeLogger (compiler: GNU GCC Compiler)---------------

mingw32-g++.exe -Wall -fexceptions -g -DOTL_ODBC -IC:\Users\bauermar\GTK -IC:\Users\bauermar\Documents\Sources\EN3 -I"C:\Program Files\Microsoft SQL Server\90\SDK\Include" -c C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp -o obj\Debug\db.o In file included from C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp:8:0: C:\Program Files\Microsoft SQL Server\90\SDK\Include/sqlncli.h:17:0: warning: ignoring #pragma warning [-Wunknown-pragmas] In file included from C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp:8:0: C:\Program Files\Microsoft SQL Server\90\SDK\Include/sqlncli.h:133:19: fatal error: oledb.h: No such file or directory compilation terminated. Process terminated with status 1 (0 minute(s), 0 second(s)) 1 error(s), 1 warning(s) (0 minute(s), 0 second(s))

I installed the Microsoft SQL Native Client with SDK, I included all libs and headers in the IDE of Code::Blocks I also included the sqlncli.h like it is said here

But I have no idea how to continue

I'm using Windows 7, Code::Blocks and MinGW32

Does anybody knows a good tutorial for working on DBs with C++ on several OS? Had someone the same problem? Is there a easier way to handle that with C++?

Thanks in advice!

SQL Server: How to get the column name which causes the SQLException?

Q: How can i get the column name which causes the SQLException?

C: I am working with SQL Server. My data which i get from my database is visualized with C#/WPF datagrid/DataTable.

If i want to insert an new datarecord to the database where i for example forgot to put some data in a cell which can not be NULL, i will get an SQLException with the message:

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

'%.*ls' is replaced automatically by the columnname which causes the SqlException.

Is there a way to get this column name mentioned in the error message. Or does i have to extract it from the message string myself?

Add columns dynamically based on a condition in SQL

I'm using SSMS 2012. Consider a table A with columns A, B, C. I would like to dynamically add a new column, D to this table based on a condition in one of the existing columns. How can this be achieved?

SQL Server iterate in select query and update upon condition

I have the following result from query

empId   totalPoints  addPointsDate  incidentDate
-------------------------------------------------
 1           11        2015-06-04    2015-07-11
 2           12        2015-07-04    2015-08-16
 3           10        2015-08-04    2015-06-14
 4           9         2015-06-14    2015-09-11

I have to update the total points if the addPointsDate was 5 weeks ago and I didn't have any incidentDate during this period.

Can I do that with stored procedure or I have to use sql function

update multiple table refrencing new table data

I have a table called ADSCHL which contains the school_code as Primary key and other two table as RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. i Have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables. can someone please help me with this. i tried using Merge Update function but wasnt able to do it. it errored out.

the best way to handle

I am writing a module(frontend extjs 4.2.1, backend asp.net mvc with the EF). I meet a little problem: when the user clicks the search button, a panel of extjs will be displayed and he/she can fill some blanks inside the textfield. after the filter information was submitted, the sever side will use appropriate c# code to deal with it to filter some records from the mssql database, here is my problem:

if the user inserts nothing into the field, the standard and best practice will be that this field will be neglected, however, the blank textfield's value will be '' which I can not use this as the filter string, for instance: there is a textfield named "sex", if the use types nothing into the field, the value passed to server will be '', if i write the lambda expression in this way: var filter = x=>x.sex ==""; Apparently it will not work. You may say that i can use if-else to condition out the stuff. but if i have numerous fields, using the if-else will be really waste of time. so, what is the best practice to do this

SQL - Knocking off Accordingly

I have an issue in SQL where all transaction just come into one giant messy tables.

Example:

1 | Invoice | $300
2 | Invoice | $250
3 | Payment | $100
4 | Invoice | $200
5 | Payment | $300

So i will have 3 invoices and 2 paymentsbut

the Payment at line 3 can only be knocking off the Invoice on line 1 and Payment on line 5 is for the Invoice in line 2.

I want to Net Off the Payment and Find rather it is an Overpayment or Underpayment or it is knocking off entire invoice.

How can i do this?

How to controll which rows were sent via SSIS

I'm trying to create SSIS package which will periodically send data to other database. I want to send only new records(I need to keep sent records) so I created status column. I want my package to update this column after successfuly sending data, but I can't update all rows wih "unsent" status because during package execution some rows may have been added, and I also can't use transactions(I mean on isolation levels that would solve my problem: I can't use Serializable beacause i musn't prevent users from adding new rows, and Sequence Container doesn't support Snapshot).

My next idea was to use recordset and after sending data to other db use it to get ids of sent rows, but I couldn't find a way to use it as datasource.

I don't think I should set status "to send" and then update it to "sent", I believe it would be to costly.

Now I'm thinking about using temporary table, but I'm not convinced that this is the right way to do it, am I missing something?

Reverse Concat - Split function

I have a table and it has a value column that lists data as: Row 1: '00','01','02','03' Row 2: '03','02','09','08'

I have a couple of split functions

     FUNCTION [dbo].[udf_Split](@String varchar(MAX), @Delimiter char(1))   
    returns @temptable TABLE (Item varchar(MAX))       
    as       
    begin      
      declare @idx int       
      declare @slice varchar(8000)       

select @idx = 1       
    if len(@String)<1 or @String is null  return       

while @idx<>0       
begin       
    set @idx = charindex(@Delimiter,@String)       
    if @idx!=0       
        set @slice = left(@String,@idx - 1)       
    else       
        set @slice = @String       

    if(len(@slice)>0)  
        insert into @temptable(Item) values(@slice)       

    set @String = right(@String,len(@String) - @idx)       
    if len(@String) = 0 break       
end  return end;

I'm trying to create a view of the table, with that column and then I'd like my view results to be a list of rows that have each value broken to its own row (and distinct) So would look like: (the tics can stay or go, don't care about them right now) Row 1: 00 Row 2: 01 Row 3: 02 Row 4: 03

My view is pretty much a:

    SELECT DISTINCT VALUE FROM TABLE
    cross apply dbo.split(Value, ',') as Item

But it's not working. Can someone lend me some direction on how I should work this?

SQL - new column with the same cell value in every row for the same id without subquery

Sorry for bad title, I don't know how do describe better if you have a better one please tell me ;)

Please look at these small sql fiddle: http://ift.tt/1M93WZ6

I need the "Value" from a specific "Title" in a new column ("Age"). But I need the "Value" in any row for the same "SN_Main". The first query was my first try. It is fast and ok but I get the "Value" only for the row with the same "Title".

The second query is wat I want but the subquery is to slow, so I wanna solve this without subquery. The productive tables are bigger and I need this like 10 times and with subquery it become incredible slow.

So is there any way to get this output with other sql statements???

I hope you understand me, I'm so sorry about bad explanation :)

Regards Martin

Select all second highest values only from temp table

Using T-SQL (SQL Server 2008 R2), I'm trying to list only the rows with the second highest value in a particular column from a temp table and then place the results into a new temp table. The PK is the ID, which can have increasing version numbers and then unique codes.

Example:

 ID | Name| Version | Code
------------------------
 1  | A   | 1       | 10
 1  | A   | 2       | 20
 1  | A   | 3       | NULL
 2  | B   | 1       | 40
 2  | B   | 2       | 50
 2  | C   | 1       | 60

The desired outcome of the query is

ID |  Version | Code
------------------------
1  |  2       | 20
2  |  1       | 40

To achieve this I need the below query to be adapted to pull the second highest value as long as the result gives a version number greater than 1. These results come from a temp table and will then be placed into a final results temp table. EDIT: Please note this will be applied over 33000 rows of data so I would prefer something neater than INSERT VALUES. Thanks.

Current query:

SELECT
    ID
    ,Version
    ,Code
INTO 
    #table2
FROM
    #table1

SELECT * 
FROM #table2
WHERE Version > 1
ORDER BY ID asc

DROP TABLE #table1
DROP TABLE #table2

I have tried running the where clause WHERE Version < (SELECT MAX(VERSION) FROM #TABLE 2) but this has no effect, presumably due to the unique code values and in any case wouldn't work where I have more than 3 Versions.

Ideas would be gratefully received.

Thanks in advance.

Release a WPF app with local database

OK, so I am quite lost. This is my first real application written for windows with a local database.
I have written an WPF application in VS2012 with a local database, which has all CRUD written as stored procedures.
The app is working as I run it in VS. But as I build the app and install it (on any machine) the database is not accessible. I have a feeling the connection string is causing this issue.

First I used this connection string:

SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename='D:\LH\Personalitytest win\DB.mdf';Integrated Security=True;Database=DB")

But as I got "Error: 26 – Error Locating Server/Instance Specified" I guessed that the Data Source of the connection is not correct (although the AttachDbFilename is definitly incorrect). So, I changed the connection string to:

SqlConnection conn = new SqlConnection(@"Server=(localdb)\v11.0;AttachDbFilename=" + AppDomain.CurrentDomain.BaseDirectory + "DB.mdf;Database=DB;Trusted_Connection=Yes;");

And now I get an error which basically says that the file DB.mdf is not found.
My database file type is Microsoft SQL Server.

My question is basically, how should the database be implemented in the final release?

mapping row to column names in sql

i have two tables Table A and Table B

A.

channelname  variablename
------------------------------
TCO1           INPUT CHANNEL  
TC02           OUTPUT CHANNEL
TCO3           TEST CHANNEL

B.

TC01    TC02   TC03
--------------------
100     443    322
200     566    990
376     453    342
45      422    856

I want the output of table B as :

inputchannel   outputchannel   testchannel
--------------------------------------------- 
100            443             322
200            566             990
376            453             342
45             422             856

SQL Server - Derived Table Data Storage

In SQL Server while writing a Query , I noticed that the data in Inner Query which is a derived table when joined with another table is taking long. The keys joined to the outer table is on Primary Key. So I was surprised since the data was about 10,000 records and 15 Columns. But if we store the data from derived table in a temp table and then JOIN the Performance was less than 2 Seconds. It made me wonder what the reason would be ?

SQL Number - Row_Number() - Allow Repeating Row Number

I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder...

ID   Day  ManualOrder  Lat     Lon    
1    Mon  0            36.55   36.55  
5    Mon  1            55.55   54.44  
3    Mon  2            44.33   44.30  
10   Mon  3            36.55   36.55  
11   Mon  4            36.55   36.55  
6    Mon  5            20.22   22.11  
9    Mon  6            55.55   54.44  
10   Mon  7            88.99   11.22  
77   Sun  0            23.33   11.11  
77   Sun  1            23.33   11.11  

What I'm trying to do is get this data ordered by Day, then ManualOrder...but I'd like a row counter (let's call it MapPinNumber). The catch is that I'd like this row counter to be repeated once it encounters the same Lat/Lon for the same day again. Then it can continue on with the next row counter for the next row if it's a different lat/lon. We MUST maintain Day, ManualOrder ordering in the final result.

I'll be plotting these on a map, and this number should represent the pin number I'll be plotting in ManualOrder order. This data represents a driver's route and he may go to the same lat/lon multiple times during the day in his schedule. For example he drives to Walmart, then CVS, then back to Walmart again, then to Walgreens. The MapPinNumber column I need should be 1, 2, 1, 3. Since he goes to Walmart multiple times on Monday but it was the first place he drives too, it's always Pin #1 on the map.

Here's what I need my result to be for the MapPinNumber column I need to calculate. I've tried everything I can think of with ROW_NUMBER and RANK, and going insane! I'm trying to avoid using an ugly CURSOR.

ID   Day  ManualOrder  Lat     Lon     MapPinNumber
1    Mon  0            36.55   36.55   1
5    Mon  1            55.55   54.44   2
3    Mon  2            44.33   44.30   3
10   Mon  3            36.55   36.55   1
11   Mon  4            36.55   36.55   1
6    Mon  5            20.22   22.11   4
9    Mon  6            55.55   54.44   2
10   Mon  7            88.99   11.22   5
77   Sun  0            23.33   11.11   1
77   Sun  1            23.33   11.11   1

Unable to Modify User-Defined Table Type

I have a SQL User-Defined Table Type. It used in more than fifty stored procedures.Now i need to change a column in that table type. I tried to drop and recreate the User-Defined Table Type.But SQL Server doesn't Allow that. It shows up following error.

Msg 3732, Level 16, State 1, Line 3
Cannot drop type 'dbo.UserDefinedTableType' because it is being referenced by object 'SP_DoSomething'. There may be other objects that reference this type.
Msg 219, Level 16, State 1, Line 3
The type 'dbo.UserDefinedTableType' already exists, or you do not have permission to create it.

How to alter the User-Defined Table Type without modifying all the Stored procedure that uses User-Defined Table Type ?

SQL Server async procedure execution: how to pass varbinary(MAX)?

I read and know this great solution from Remus Rusanu for the async stored procedures execution

http://ift.tt/1SHARI5

What is the good way to exec SP with parameter varbinary(MAX)? How to implement this kind of async procedures calls?

Grant all tables (except 1) Read permission on a Role while not knowing which tables there are every day

Every day in our datawarehouse (that wil be dynamicly) the tables are dropped and rebuild. Also is it possible that some developer in our organisation wil create more tables in that database. Because of that i can not give permissions to one database that are persistent.

Question: I want to make some kind of a job that runs every day, that lists all de table names (that are existing at that time) in a database like 'Select * form sys.tables' Then i want the tables names as an input value to a script that runs trough all table names and places them in a scentense like

GRANT SELECT TO [Tablename1] TO [ROLE_READALLTABLES Except 1 table],
GRANT SELECT TO [Tablenaam2] TO [ROLE_READALLTABLES Except 1 table] 

en so go on in a loop until all existing tables are read So all tables (except 1 table )in the entire database should get de GRANT SELECT permission.

I have looked beween all the answers, but i can not seems to get a good idea how to get this to work. I hope someone can help me with this.

UPDATE I use MS SQL Server 2014, i work with SQL management studio 2014

sp_getapplock not working? I would expect the following code to make a table inaccessible for other sessions however this is not the case

run the following in a sql server session

    CREATE DATABASE locktest
    GO

    CREATE TABLE locktest.dbo.lock_this_table (id INT)
    BEGIN TRAN
        EXEC locktest..sp_getapplock @Resource = 'lock_this_table',@LockMode = 'Exclusive'

In a separate window, execute the following code, I would expect it to hang whilst the first transaction remains open, any idea why it doesn't?

SELECT * FROM locktest.dbo.lock_this_table

UPDATE column for a large table with a single value

I have a table with 2 billion rows. The recovery mode is simple.

I have a simple task to do:

UPDATE myTable SET columnA = 'X'

The task seems easy at first, but everywhere I read that it is better to make a SELECT INTO another table, or do a batch update.

Is a batch update neccesary ? Will the query above blow up the transaction log ?

If I create another table I will for sure run out of space.

Any help would be appreciated.

EDIT: I do not have a primary key. Also, no indexes, no constraints, anything.

Why the SQL query giving a bool(false) but running on the sql server.?

I have the SQL query that is fetching data from multiple columns and fields, with the primary and foreign keys. i am trying to run the code on the search script but on var_dump of the query, i get the query as follows:

SELECT `vendor`.`v_id`,`vendor`.`v_name`,`vendor`.`v_img`,`contacts`.`cont_id`,`contacts`.`cont_addr`,`contacts`.`cont_phn`,`fooditem`.`fi_id`,`fooditem`.`item_name`,`fooditem`.`fi_price`,`fooditem`.`fc_desc`,`vendor`.`v_id`,`vendor`.`v_name`,`vendor`.`v_img`,`contacts`.`cont_id`,`contacts`.`cont_addr`,`contacts`.`cont_phn`,`fooditem`.`fi_id`,`fooditem`.`item_name`,`fooditem`.`fi_price`,`fooditem`.`fc_desc` FROM vendor LEFT JOIN `stfood`.`contacts` ON `vendor`.`cont_id` = `contacts`.`cont_id` LEFT JOIN `stfood`.`fooditem` ON `vendor`.`v_id` = `fooditem`.`v_id` WHERE `vendor`.`v_name` LIKE '%ven%' OR `fooditem`.`item_name` LIKE '%ven%'

and when i proceed forward with the var_dump of the above query i get a bool(false) but this query runs flawlessly on PHP MyAdmin Can anyone tell me why is this so?? I am confused on this... Thanks in advance.

SQL Argument data type datetime is invalid for argument 1 of substring function

I'm trying to run an sql query to move data from one table into another but I'm having trouble. Here is my query:

TRUNCATE TABLE LIQ_ACCT_EOD_SIF

DECLARE @filedt varchar(32), @repdate int;
SET NOCOUNT ON


SET @filedt = CAST(getdate() as varchar);

select @repdate=CAST(right(S_ReportingDate, 4) + SUBSTRING(S_ReportingDate,4,2) + LEFT(S_ReportingDate, 2)
AS int)
FROM AllocationJunLLv2;

DELETE FROM dbo.LIQ_ACCT_EOD_SIF
WHERE REPORT_DATE=@repdate;


INSERT INTO dbo.LIQ_ACCT_EOD_SIF
(
 FILE_NUM
,FILE_DATE_TIME
,ROW_NUM
,FILE_TYPE
,MIGRATION_DATE
,INSTITUTION_NAME
,RESULT_TYPE_NAME
,SCENARIO_NAME
,REPORT_DATE
,ASSET_CLASS_NAME
,INSTRUMENT_TYPE_NAME
,INSTRUMENT_SUBTYPE_NAME
,INSTRUMENT_NAME
,COUNTERPARTY_NAME
,CORPORATE_RETAIL_FLG
,CLIENT_NAME
,DEPOSIT_FLG
,POSITION_FLG
,LIQUID_FLG
,LOCATION_NAME
,PORTFOLIO_NAME
,ACCOUNT_TYPE_NAME
,ACCOUNT_NUMBER
,ACCOUNT_OPENING_DATE
,INSTRUMENT_ISO_CURRENCY
,ISO_CURRENCY_CODE
,CURRENT_VALUE
,MATURITY_DATE
,MATURITY_VALUE
,DRAWN_AMOUNT
,UNDRAWN_AMOUNT
,LIMIT_AMOUNT
,EXPOSURE_CD
,EXPECTED_CASH_FLOW
,EXPECTED_CASH_FLOW_DATE
,PASSED_DUE_IND
,INT_TYPE
,INT_FIXED_RATE
,INT_VAR_BASENAME
,INT_REL_VAR_RATE
,RATING_NAME
,RATING_AGENCY
,BASEL_OPTION
,RATING_TYPE
,REPORT_LINE_NUMBER
,REPORT_LINE_NAME
,DELTA
,GAMMA
,NETTING_NUMBER
,COLLATERAL_ACCOUNT_NUMBER
,BUFFER_ELIGIBLE
,ALTERNATIVE_YIELD_RATE
,FTP_PORTFOLIO
,REPO_FLAG
,LIMIT_EXPIRY_DATE
,AL_DEFINITION
,REPO_AMOUNT
,TRANSACTION_NUMBER
,UNENCUMBERED_AMOUNT
,MARGIN_ACCOUNT_NUMBER
,CASH_FLOW_TYPE
,value_of_house
,security_value
,security_type
,int_rate_floor
,int_rate_ceiling
,lcr_line_number
,AMM_line_number
,BASEL_APPROACH
,CCR_portfolio
,CCR_sector
,CCR_region
)

SELECT
 1 AS FILE_NUM
,@filedt AS FILE_DATE_TIME
,0 AS ROW_NUM
,'EOD' AS FILE_TYPE
,getdate() AS MIGRATION_DATE    
,'UBNL' AS INSTITUTION_NAME 
,LEFT(b.STBITEM, 32) AS RESULT_TYPE_NAME
,'Unknown' AS SCENARIO_NAME
,@repdate AS REPORT_DATE
,ISNULL(rl.ASSET_CLASS_NAME, 'Unknown') AS ASSET_CLASS_NAME
,'Unknown' AS INSTRUMENT_TYPE_NAME
,'Unknown' AS INSTRUMENT_SUBTYPE_NAME
,'Unknown' AS INSTRUMENT_NAME
,LEFT(b.S_InstitutionCode, 32) AS COUNTERPARTY_NAME
,ISNULL(rl.CORPORATE_RETAIL_FLG, 'U')
,b.S_CustomerName AS CLIENT_NAME
,'U' AS DEPOSIT_FLG
,'U' AS POSITION_FLG
,'U' LIQUID_FLG
,'Unknown' AS LOCATION_NAME
,b.S_Ref1 AS PORTFOLIO_NAME
,'Unknown' AS ACCOUNT_TYPE_NAME
,b.Unique_ID AS ACCOUNT_NUMBER
,CAST( right(CAST(S_StartDate AS varchar(8)),4) + SUBSTRING(CAST(S_StartDate AS varchar(8)),4,2) + LEFT(CAST(S_StartDate AS varchar(8)), 2)AS int) 
AS ACCOUNT_OPENING_DATE

,'USD' AS INSTRUMENT_ISO_CURRENCY
,'USD' AS ISO_CURRENCY_CODE
,CASE
    WHEN rl.CURRENT_VALUE_FLG='Y' THEN 
    CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
    ELSE 0
 END AS CURRENT_VALUE
,ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231) AS MATURITY_DATE
,CASE
    WHEN rl.MATURITY_VALUE_FLG='Y' THEN
    CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
    ELSE 0
 END AS MATURITY_VALUE
,0 AS DRAWN_AMOUNT
,CASE
    WHEN rl.UNDRAWN_AMOUNT_FLG='Y' THEN 
    CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
    ELSE 0
 END AS UNDRAWN_AMOUNT
,CASE
    WHEN rl.LIMIT_AMOUNT_FLG='Y' THEN 
    CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
    ELSE 0
 END AS LIMIT_AMOUNT
,ISNULL(rl.EXPOSURE_CD, 'Unknown') AS EXPOSURE_CD
,CASE
    WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN 
    CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
    ELSE 0
 END AS EXPECTED_CASH_FLOW
,CASE
    WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231) 
    ELSE 0
 END AS EXPECTED_CASH_FLOW_DATE
,'U' AS PASSED_DUE_IND
,'U' AS INT_TYPE
,0 AS INT_FIXED_RATE
,'Unknown' AS INT_VAR_BASENAME
,0 AS INT_REL_VAR_RATE
,'Unknown' AS RATING_NAME
,'Unknown' AS RATING_AGENCY
,'B2L' AS BASEL_OPTION
,'LT' AS RATING_TYPE
,ISNULL(CAST(SUBSTRING(b.STBITEM, 5, 2) AS int), 0) AS REPORT_LINE_NUMBER
,ISNULL(rl.REPORT_LINE_NAME, 'Unknown') AS REPORT_LINE_NAME
,0 AS DELTA
,0 AS GAMMA
,'Unknown' AS NETTING_NUMBER
,'Unknown' AS COLLATERAL_ACCOUNT_NUMBER
,'U' AS BUFFER_ELIGIBLE
,0 AS ALTERNATIVE_YIELD_RATE
,'Unknown' AS FTP_PORTFOLIO
,'U' AS REPO_FLAG
,0 AS LIMIT_EXPIRY_DATE
,ISNULL(rl.AL_DEFINITION, 'U') AS AL_DEFINITION
,0 AS REPO_AMOUNT
,'Unknown'AS TRANSACTION_NUMBER
,0 AS UNENCUMBERED_AMOUNT
,'Unknown' AS MARGIN_ACCOUNT_NUMBER
,CASE
    WHEN rl.REPORT_LINE_NUMBER BETWEEN 6 AND 17 THEN 7
    WHEN rl.REPORT_LINE_NUMBER=23 THEN 6
    ELSE 1
 END AS CASH_FLOW_TYPE
,0 as value_of_house
,0 as security_value
,'Unknown' as security_type
,0 AS INT_RATE_FLOOR
,0 AS INT_RATE_CEILING
,'Unk' as LCR_LINE_NUMBER
,'Unk' as AMM_LINE_NUMBER
,'Unknown' as basel_approach
,'Unknown' as CCR_portfolio
,'Unknown' as CCR_sector
,'Unknown' as CCR_region
FROM AllocationJunLLv2 b

INNER JOIN LIQ_EXCH_RATE_SIF fx ON 'USD'=fx.SOURCE_CURRENCY_CD
INNER JOIN DM_REPORT_LINE rl 
ON CAST(SUBSTRING(b.STBITEM, 5, 2) AS int)=rl.REPORT_LINE_NUMBER AND rl.REPORT_NUMBER=48
 WHERE ISNULL(CAST(CAST(
  CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE   dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END
  AS float)
  AS numeric(22,6))
, 0) <> 0
AND NOT (
LEFT(b.STBITEM, 3) = 'F48' AND 
RIGHT(b.STBITEM, 2) = 'C1' AND 
CAST(SUBSTRING(b.STBITEM, 5, 2) AS int) BETWEEN 6 AND 17
)


UPDATE LIQ_ACCT_EOD_SIF
SET 
    CURRENT_VALUE=-CURRENT_VALUE,
    MATURITY_VALUE=-MATURITY_VALUE
WHERE
    REPORT_DATE=@repdate

GO 

The errors I'm getting are:

Msg 8116, Level 16, State 1, Line 9
Argument data type datetime is invalid for argument 1 of substring function.
Msg 8116, Level 16, State 1, Line 17
Argument data type datetime is invalid for argument 1 of substring function.

Now I've tried

CAST(S_ReportingDate as nvchar(8))

but that didn't fix the problem, does anyone have any ideas?

How to select the Most Recent or last record from MySQL table using SQL syntax

How can I select Most Recent or Last 100 records from MY SQl Query. I have 18 Millions of records in database. I want to select 100 most recent records by using "Timestamp" Query but it would take more than 2 minutes of time. Have any other way to find the latest records without Order By

     SELECT DISTINCT(dd.Timestamp), dd.Amplitude , pd.PatientId   FROM DeviceDetails dd   JOIN DeviceSession ds 
     ON ds.DeviceSessionID = dd.ByDevSessionId  
     JOIN PatientSession ps ON ps.PatientSessionId = ds.ByPatientSessionId   
     JOIN PatientDetails pd ON pd.PatientDetailsId = ps.ByPatientId   
     WHERE   pd.HospitalPatientId = 'Rorys Test Ward Test Bed 2 @ 2015-07-31 18:36:41'
     AND dd.Timestamp < '2015-08-04 11:53:08.962'       
     ORDER BY dd.Timestamp   
     Limit 100