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