Database

SQL 2016 Optimization ...

5-May-18 1:14

Member 13806085 wrote:
Perform Write and Read as fast as possible using all the hardware resources.

"As fast as possible" will take some experimenting and reading, will depend on your data-structure, indexes, triggers, and a bunch more - a bit much for a single forum-post.

Member 13806085 wrote:
I have configured Always On Read Routing, how can I test it?

Check if the routing-URL is invoked and run a SQL-trace.

Configure Read-Only Routing for an Availability Group (SQL Server) | Microsoft Docs[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 138060854-May-18 1:10

Member 13806085

4-May-18 1:10

How to stop execution of other tasks in SSIS if a script task gets Dts.TaskResult = (int)ScriptResults.Failure;

indian1432-May-18 14:12

Self answered just if somebody has similar problem it would be helpful. How to stop execution of other tasks in SSIS if a script task gets Dts.TaskResult = (int)ScriptResults.Failure;

2-May-18 14:12

Hi,

I am writing an SSIS Package, want to stop all the remaining tasks if I get Dts.TaskResult as failure (I am checking if file exists in Script task), I am trying different options like assigning false value to another variable etc, so far no success, can somebody suggest me the best way to stop execution of remaining tasks if a Script task gets failure result, any idea would be greatly helpful. thanks in advance friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

indian1433-May-18 8:41

Insert dynamic SQL Values into Temp Table

3-May-18 8:41

Hi friends,

Its easy, create a Boolean or some other type variable, assign that value accordingly in the script task, then after the script task, you can have more than one Precedence Constraint Editor, in that, take the Evaluation Operation as either expression or expression and Constraint etc according to your needs then set the expression with your variable that you have assigned in the script task, now after the script task it will either continue with next steps or it will go to the task you have assigned after the failure constraint.

This is all it is, thank you SSIS and thanks for all the friends who tried to help or support me.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

-- modified 3-May-18 14:48pm.

indian14326-Apr-18 7:03

Re: Insert dynamic SQL Values into Temp Table

26-Apr-18 7:03

Hi,

I need to insert Dynamic SQL Values into Temp table, but I am able to insert values into Global Temp table but I don't know how to insert Global Temp table values into Temp table back again. Any help would be greatly helpful. Any sort of implementation to insert Dynamic SQL values into Temp table is fine, directly or indirectly is fine.

Here is how I am able to insert the Dynamic SQL Values into Global temp table, but not able to do the same for the Temp table, even if I can insert from Global Temp table to Temp table is also fine. Any help would be greatly helpful - thanks in advance buddies.

DECLARE @sqlCommand varchar(max) = 'SELECT TABLE_NAME INTO ##TableNames FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='''+ @schema + ''''

    EXEC (@sqlCommand)
    EXEC ('SELECT * FROM [##TableNames] ')

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Richard Deeming26-Apr-18 8:00

Richard Deeming

26-Apr-18 8:00

You have to create the temp table before executing the dynamic SQL:

SQL

CREATE TABLE #TableNames (TABLE_NAME sysname NOT NULL);

DECLARE @sqlCommand nvarchar(2000) = N'INSERT INTO #TableNames (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema';

EXEC sp_executesql @sqlCommand, N'@schema nvarchar(128)', @schema = @schema;

SELECT * FROM #TableNames;

DROP TABLE #TableNames;

You also need to avoid string concatenation, otherwise you'll introduce a SQL Injection[^] vulnerability.
sp_executesql (Transact-SQL) | Microsoft Docs[^]

But in this example, you don't need dynamic SQL at all.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Insert dynamic SQL Values into Temp Table

indian14326-Apr-18 10:09

Shipping tracking Link output to google search from MYSQL Database

26-Apr-18 10:09

Thank you very much that did it great, its a great help.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

mysql query

Member 1360728325-Apr-18 16:47

Member 13607283

25-Apr-18 16:47

Hi guys, need help again.. newbie here just learning mysql. I have a timesheet table that have a few columns. ie Date, InvNum, Fieldhrs, UserID, ShopHrs.
Everyday employees enter their data into database via web page which works great.
My problem is this, trying to write a query that checks if every user has entered data for a certain date.. i've tried the following query, but just returns blank not userid ?

SELECT UserID FROM timesheet
WHERE "2018-04-23" NOT IN
(
SELECT Date
FROM timesheet
)
Any help would be appreciated if someone could point me in right direction.. ie a real newbie.

Re: mysql query

Jörgen Andersson25-Apr-18 21:48

Jörgen Andersson

25-Apr-18 21:48

You need a reference for all active users.
Assuming you have a Users table you should try something similar to this:

SQL

SELECT  u.UserID
FROM    Users u
LEFT JOIN Timesheet t
    ON  t.UserID = u.UserID
    AND t.Date = "2018-04-23"
WHERE   t.UserID IS NULL
    AND u.IsActive = 1

The existence of IsActive is just an assumption on my part. Add or change conditions as needed.

There are other solutions, but this is the probably the best way to do it.

Wrong is evil and must be defeated. - Jeff Ello

Re: mysql query

Member 1360728326-Apr-18 10:56

Member 13607283

26-Apr-18 10:56

Thanks. Very helpful. Got it working following your suggestion. Much appreciated.

Re: mysql query

Jörgen Andersson26-Apr-18 21:22

Jörgen Andersson

26-Apr-18 21:22

You're welcome

Wrong is evil and must be defeated. - Jeff Ello

bfg9000d36025-Apr-18 4:51

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 4:51

This line
$output = "Tracking Number: $tracking_number<br />Order Number: $sales_order_number<br />Parts: $parts";

I need whatever $tracking_number outputs to become a hyperlink for google search with the outputted number search.

Example

The database outputs

Tracking Number: 923489234 <=This number needs to be a blue hyperlink link to google search with that number search
Order Number: 234324
Parts: 30405324

Once that tracking number is search in google, google determines what carrier it is saves me allot of trouble. I wish it was this simple $output = "Tracking Number: $tracking_number link  google.com/search?q=$tracking_number any ideas guys. Thanks

The rest of my code is below if you want to view it.

<?php
$output = NULL;

if(isset($_POST['submit'])){
     //Connect to the database
      $mysqli = NEW  MySQLi("","","","");

 $search = $mysqli->real_escape_string($_POST['search']);

 //Query the database
  $resultSet = $mysqli->query("SELECT * FROM invlist WHERE sales_order_number = '$search'");

  if($resultSet->num_rows > 0){
      while($rows = $resultSet->fetch_assoc())
      {
           $tracking_number = $rows['tracking_number'];
           $sales_order_number = $rows['sales_order_number'];
           $parts = $rows['parts'];

           $output = "Tracking Number: $tracking_number<br />Order Number: $sales_order_number<br />Parts: $parts";
      }
  }else{
      $output = "No results";
  }
}
?>


<form method="POST">
<input type="TEXT"  name="search" />
<input type="SUBMIT" name="submit" value="Search" />
</form>

<?php echo $output; ?>

Eddy Vluggen25-Apr-18 5:37

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 5:37

Google shows URL's from the sites it has crawled. To become part of the search-results, they URL's must be submitted to Google.

Ask Google to recrawl your URLs - Search Console Help[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

bfg9000d36025-Apr-18 7:19

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 7:19

somebody said to add this but it does not seem to be working

$output += "<br><a href="https://google.com/search?q=$tracking_number">Search</a>";

Eddy Vluggen25-Apr-18 8:19

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 8:19

Of course it didn't. Google doesn't read your code. If your link appears on the web, and that location is indexed, then that URL will be indexed.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

bfg9000d36025-Apr-18 8:31

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 8:31

I think we are getting crossed here on what I need.

I have a searchable database that I created, when you enter the order number it outputs this information

Tracking Number:
Order Number:
Parts:
-----------------------

Ok lets say

my database yielded these results

Tracking Number: 123219843
Order Number: 9432
Parts: 234923

Since the database yielded Tracking Number: 123219843 I need to have that number thrown over to google to be searched by google. so it needs to become a link. I dont want to have to phyiscally copy and paste the tracking number into google.

Eddy Vluggen25-Apr-18 8:38

Re: Shipping tracking Link output to google search from MYSQL Database

25-Apr-18 8:38

If you want your link to show up, you have to submit it, or a page containing those links.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Richard Deeming25-Apr-18 9:46

Richard Deeming

25-Apr-18 9:46

What do you mean by "not working"? If it generates a link that looks like:

HTML

<a href="https://www.google.com/search?q=123219843">Search</a>

then it should work: Search

Depending on the tracking code, it might not find the results you want. But it will open a Google search for the tracking code.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Shipping tracking Link output to google search from MYSQL Database

bfg9000d36025-Apr-18 9:50