SQL

1K

Solutions

6

Articles & Videos

1K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am new to entity framework, Whilst i love it, there are still some things i cant figure out. I have good experience with SQL and in SQL this is a easy task.

I have attached a copy of the db tables and their relationships. Hopefully someone can help.

I need to see all items in the orderitems table which have a corresponding statusid of 100 in the orders table.
from that list i then need to see all items in the orderitems table grouped by the store in which they belong.

Any help is appreciated :)
Model.PNG
0
Are your AD admin tools letting you down?
LVL 6
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

So I've inherited a somewhat small environment where I manage Active Directory but the former DBA apparently had all these scripts doing tons of things against AD and everything else in the environment.  
Problem is one of the things that's happening is we have students and alumni in this small campus. Well the script, which I believe is the one below, is moving people to the Alumni OU even though they are a student.
I can ascertain that some of this is partially SQL query but I was hoping someone with a bit of experience could help translate what is going on here and what criteria/reason is moving someone from one OU to another OU or vice versa.

From the script, I see that one important variable appears to be:
$StudentsThatShouldMove = Fill-Dataset $IntegrationConnString $Sql_StudentsThatShouldMove
   
Does that mean I have to look to the SQL side to see what's going on?
#. \\scorch01.VelCollegenet.edu\C$\Users\Public\Documents\Scripts\_CreateAcctsAndDistGroupsV4.ps1
# .\_CreateAcctsAndDistGroupsV4.ps1
#########0#########0#########0#########0#########0#########0#########0#########0
#
# # Description: Move students and alumni accounts to the correct OUs
#
# History:
#	???????? EWM Initial Creation
#   20111213 EWM Sql Server Agent Job 
#				 [Active Directory NEW students daily].[Shell Game (move stuff around where it goes)]
#				 Occurs daily at 9:00:00 PM with no end date
#
#########0#########0#########0#########0#########0#########0#########0#########0

Open in new window

0
I'm running into the below error when using the OLE DB (ADO) SQL Server Native Client 10. Server is running SQL Server 2008 R2. We are able to make a connection using Microsoft OLE DB Provider for SQL Server fine however the reports are putting out unreadable data. Thanks in advance.
Crystal reports error
0
For Microsoft SQL, 2012, I was told that the following, ""Database encryption scan" indicates that  a database is being created.  A quick google search is pulling up information that suggests otherwise.  I am looking for something definitive.   I am working with others to be alerted when a database is created and another when the database is encrypted.
0
I have been tasked to build an online audit questionnaire/survey. I am fairly new to asp.net. I want to create the questions and server controls dynamically with data from sql server. For each questions I will need to dynamically create two  (Yes/No) radio buttons. Any help would be greatly appreciated. I am using asp.net forms.
0
Hi expert i have a query with below code please help....

-- 1st i have created a table .
SQL> CREATE TABLE TARGET(SNO NUMBER(12),NAME VARCHAR2(12),SID NUMBER(12) CONSTRAINT PK_RAJ_SID PRIMARY KEY,
  2  LOC VARCHAR2(12));

Table created.

SQL> INSERT INTO TARGET VALUES(&NO,'&NAME',&SID,'&LOC');
Enter value for no: 1
Enter value for name: RAJ
Enter value for sid: 100
Enter value for loc: ODISHA

1 row created.

SQL> /
Enter value for no: 2
Enter value for name: TAPA
Enter value for sid: 101
Enter value for loc: PURI
1 row created.
SQL> /
Enter value for no: 3
Enter value for name: MIHIR
Enter value for sid: 102
Enter value for loc: BBSR
1 row created.
SQL> /
Enter value for no: 4
Enter value for name: SOUMYA
Enter value for sid: 103
Enter value for loc: HYD

1 row created.

SQL> /
Enter value for no: 5
Enter value for name: RANJAN
Enter value for sid: 104
Enter value for loc: BNG

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TARGET;

      SNO NAME               SID LOC                                                                
--------- ------------ --------- ------------                                                      
        1 RAJ                100 ODISHA                                                            
        2 TAPA               101 PURI                                                              
        3 MIHIR              102 BBSR                                    …
0
I have two tables (TBL_A and TBL_B). There is a one to many relationship between them. Every record in TBL_A has zero to many related records in TBL_B.

When all TBL_B records with the same TBL_A idnum have a statuscode of 'INACTIVE', I want to change the related TBL_A statuscode to 'INACVTIVE'. Following are more detaiils:

TBL_A has 2 columns (it actually has more but for this example, it is enough). They are:

- IDNUM  (this is the key field for TBL_A)
- STATUSCODE

TBL_B has 3 columns (it actually has more but for this example, it is enough). They are:

- IDNUM (this is the key field for TBL_B)
- STATUSCODE
- TBLAIDNUM (this is the foreign key that points back to TBL_A)

I've attached an excel file with a couple of examples and the results that I am trying to achieve.

I hope this provides enough information for you. Also hope you are okay with the excel example.

Any ideas will be appreciated.
TBL-A-AND-B-EXAMPLES.xlsx
0
I have a SQL Server table with column FullName VARCHAR(300)

I have a .net import from file process that runs fine except...
Where there are accent characters
It substitutes a ? as it goes into SQL.
Exemple   'PIñERO RODRIGUEZ' goes in as 'PI?ERO RODRIGUEZ'

However..if I run a simple update...
Things update correctly in SQL and can be selected with no issues

UPDATE dbo.Individuals
SET LASTNAME = UPPER('PIñERO RODRIGUEZ')
WHERE IndividualID = 204140


Or I do something simple in SQL like...
INSERT INTO Individuals
VALUES ( UPPER('PIñERO RODRIGUEZ'))   - (Abbreviated code of course...)

Inserts fine

So...

Do I need to change my import Sub (VB.NET) ?

Possibly the "AddWithValue" should actually be this type of format???
oCom2.Parameters.Add(New SqlParameter("@" + header(k), SqlDbType.VarChar, 300)).Value = returnRecords.Rows(i).Item(k).ToString()

This is how its sendingthe insert right now
Dim returnRecords As DataTable = Session("sheetData")
Dim test As String = hdJsonData.Value
 Dim header As String() = test.Split(",")
Dim columnNames As String() = returnRecords.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray()

Using... blah...blah...blah...
oCom2.CommandText = "InsertImportContactStaging"
 oCom2.CommandType = CommandType.StoredProcedure
oCom2.Parameters.AddWithValue("@ImportID", GUID)
oCom2.Parameters.AddWithValue("@ImportRecordType", Session("ImportFileType").ToString() + "")

             …
0
Hello Experts,
In continuation to : https://www.experts-exchange.com/questions/29014655/Powershell-help-for-creating-accounts.html
I have a further enhancement request,
I have 2 Columns in SQL
ADGroup & O365Group

These columns contains values, which are separated by comma (,) these are existing groups in AD & O365
I want them to be integrated into the script so that when the new user is created he is added to the respective groups in both the places.

Thanks in advance.
A
0
Good morning,

I am trying, with the query below, to grab "scanning" data.  From the Transactions table I'm grabbing the user and sales order (SO).  I then grab quantities from the soitem table (looking for when SO = fsono).

The query works fine unless someone scans an SO more than once (thus resulting in the record being added again into the transaction table).

How can I grab distinct records from the transaction table and then left outer join the other tables to get me my results?

Thank you in advance.

Select 
  Description, sum(FnAltQty) as totalDoorCount, userID, fsono 
from 
  DoorTrak.dbo.Transactions 
  left outer join DoorTrak.dbo.Entities on DoorTrak.dbo.Transactions.userID = DoorTrak.dbo.Entities.entityID 
  left outer join M2MDATA01.dbo.soitem on DoorTrak.dbo.Transactions.SO = M2MDATA01.dbo.soitem.fsono 
where 
  userID >= 1000 
and 
  month(dateCreated) = 6 
and 
  day(dateCreated) = 21 
and 
  year(dateCreated) = 2017 
and 
  fprodcl in ('SD', 'SM', 'SR','LV','SL','SS','ST') 
group by 
  userid, description, fsono 
order by 
  userid, fsono

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure
LVL 4
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Hi,
Is it better to install Microsoft SQL Server on Azure box or to completely migrate to Azure SQL Server.
Migrating to Azure SQL Server might take long, so will getting used to it.
What are the benefits of moving to Azure SQL Server and are they worth the effort?
Are there any disadvantages of installing and using Microsoft SQL Server on the box instead?
Thanks!
0
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-06-21T11:42:47). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 10.155.16.253:25)
0
I have a SQL query where i attempted to use the solution presented in the thread, "Get the last 13 week from SQL query". It works WELL but I have two issues.
1.  When I use that solution in the where clause, any calculation that I have ROUNDS to 1 or 0, never the decimal
2.  How do you get the last 13 weeks using just the week number?

My data only shows week and year.
0
I'm following along with a video by Ben-Gan called Creative Uses of Apply Operator and he shows this code

Select orderiid, orderdate, nextyear
from sales.order
  cross apply(values(year(orderdate))) as a1(orderyear)
    cross apply(values(year(orderyear +))) as a2(nextyear)
where orderyear > 2007

---end code----

He comes up with a result set where the nextyear column is one year later than the orderdate, i.e., if orderdate is 2008-01-01, nextyear is 2009
you can see it video place...

 https://youtu.be/-m426WYclz8?t=1291

However when I try using it with this code on two different databases 2012 AdventureWorks or AdventureworksLT  I use the same code on different column names..


select SalesOrderID,  OrderDate, NextOrderYear
from SalesLT.SalesOrderHeader
      cross apply (values(year(OrderDate))) as a1(orderYear)
            cross apply (values(year(orderYear + 1))) as a2(NextOrderYear)
            where orderYear > 2001

I get the

 NextOrderYear as being 1905

Obviously something is not right. Just not sure what?
0
How can I validate address using API  in C# or any batch process?
0
I have been asked to migrate/update an Access/SQL Application so it can run in a VM Environment on a true SQL Server; I have a general understanding to get started, but this is my first VM project, so step one is to plan for the unexpected. I request that you treat me as a novice when making suggestions.

Rather than write a novel about the function of this Access/SQL Application and to minimize suppositions, I would rather respond to questions about it.

To start this off, here is the current configuration ...
Standalone Workstation
Windows 10 64 Bit
Access Pro Plus 2013 (Front End)
SQL 2016 Express (Back End)

What would be the best approach and what pitfalls should be anticipated?
0
I want to return the Top 5 vCount for/in each vGroup in this query, but I'm having trouble figuring out where to put it ?

		/****** Region ******/		
		SELECT	vGroup, vType, count(*) AS vCount
		FROM (
		SELECT        'Region visits' AS vGroup, Districts.name AS vType
		FROM             (v_Visits INNER JOIN Customers ON v_Visits.customerId = Customers.id) INNER JOIN Districts ON Customers.districtId = Districts.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

		/****** Brand ******/
		UNION ALL
		SELECT	vGroup, vType, count(*) AS vCount
		FROM(
		SELECT        'Brand visits' AS VGroup, Products.brandName as vType
		FROM            v_Visits INNER JOIN Visits_r_Products ON v_Visits.id = Visits_r_Products.visitId INNER JOIN Products ON Visits_r_Products.productId = Products.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

Open in new window

0
I have a database that contains quotes for pricing. I am trying to copy a quote from the quote table and it's related records into the same tables as a way to provide the user a starting point rather than creating a new one from scratch. I have 3 tables...a quote is made up of several assemblies from the assemblies table and each assembly can contain many assembly items from the assemblyItems table.

So here is how it should work. I provide the new quote number to the stored procedure. It will copy the quote from the quote table into the same quote table using the new quote number. Also copy any related Assemblies into the assemblies table and each assembly's item into the assemblyItems table.

I also need the new QuoteID as a return value into my .NET app.

Thank you,
Randy

example structure
Quote
	|
	|----Assembly
	|	|
	|	|---AssemblyItem
	|	|---AssemblyItem
	|	|---AssemblyItem
	|
	|----Assembly
	|	|
	|	|---AssemblyItem
	|	|---AssemblyItem
	|	|---AssemblyItem
	|
	|----Assembly
	|	|
	|	|---AssemblyItem
	|	|---AssemblyItem
	|	|---AssemblyItem

Open in new window

0
Hello All,

I wrote small query where select query will fetch the status of one ORder . If the order status is not 1 it will update the status to another status. If not found it will handle the exception.
I am made one script.In the comment can you help in recitfiying the script asap. Need this script in this format only.



DECLARE
       
        v_REFERENCE_CODE varchar2(30);
        v_status_lkp varchar2(30);
        cursor ObsoleteSalesOrder is
               select a.REFERENCE_CODE,a.status_lkp from BASE_SALES_ORDER a,BASE_SALES_ORDER_LINE b where
                    a.SALES_ORDER_ID =b.SALES_ORDER_ID
                    and a.REFERENCE_CODE ='10132834';
       
BEGIN
open ObsoleteSalesOrder;
loop
fetch ObsoleteSalesOrder into v_REFERENCE_CODE,v_status_lkp;
exit when ObsoleteSalesOrder%notfound;
loop
if v_status_lkp !=1

--Do Below update
update base_sales_order set obsolete_flag = 1 where reference_code IN ('10132834');
update base_sales_order_line set obsolete_flag = 1 where sales_order_id
IN (select sales_order_id from base_sales_order where reference_code IN ('10132834'));
commit;
DBMS_OUTPUT.PUT_LINE('SalesOrder MArked it obsolete');

else if v_status = 1
DBMS_OUTPUT.PUT_LINE('SalesOrder is in obsolete');

else
DBMS_OUTPUT.PUT_LINE ('No sales ORder Found');

--Exception handling
end loop;
--Exception handling
else
end loop;
--Exception Handling
close ObsoleteSalesOrder;            
end;
/
0
Major Incident Management Communications
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

hi i got the following report where i add two column to summary the total of two column
1 after i edit the report am now geting more page than original report
2 the report is showing wrong total
beforeedit.jpg
afteredit.jpg
0
Hi EE,

I am sure this question would have been asked and answered here. But here is my requirements.

Drop all foreign key constraints in my db
Truncate all tables

Recreate all foreign key constraints. I have been trying to use https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx but somehow i am unable to make it work.

Can you please help?

regards
0
hai...in phpmyadmin data base table my DOB is coming in adding foramt...did i need to change the type...???

my code in php format:

 $DOBMonth = false;
if(isset($_POST['DOBMonth'])){
    $DOBMonth = $_POST['DOBMonth'];
 }

$DOBDay = false;
if(isset($_POST['DOBDay'])){
    $DOBDay = $_POST['DOBDay'];
 }

$DOBYear = false;
if(isset($_POST['DOBYear'])){
    $DOBYear = $_POST['DOBYear'];
 }


$DateOfBirth=$DOBMonth+"/"+$DOBDay+"/"+$DOBYear;
0
Since we upgraded to SQL 2008 from SQL 2000 i have faced the following problem: I can run the VB Script that runs a DTS from the SQL Server, however running the same VB Script to to execute the DTS from a client PC has the above error message. I have tried, installing MS SQL studio on the client connecting to the DB instance and tried running the VB Script but it still wont work. im lost, please help

Regards,
Grand
0
I was thinking maybe Northwinds DB but there may be better. Any thoughts or links appreciated.
0
Dear Experts,

My client is using Symantec Backup Exec 2012

They have three servers to do the backup set at Highest (SQL) , High (Exchange) and Medium (Apps)

All servers are to backup at 11pm everyday. Mon to Thurs (incremental), Friday (Full)

The backup for Exchange and Apps server are successful.

But the SQL server is still running the backup but there is no action.
CKG-Backup1.JPG
CKG-Backup2.JPG
CKG-Backup3.JPG
CKG-Backup4.JPG
CKG-Backup5.JPG
0

SQL

1K

Solutions

6

Articles & Videos

1K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.