SQL

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 running some queries in Powershell.

I have created a function that returns an object I can use foreach (hence the return ,$table)
I first obtain a sql reader and load a data table with the returned recordset.

$rdr = $command.ExecuteReader()
          
 $table = new-object “System.Data.DataTable”
 $table.Load($rdr )
    
  return ,$table

Open in new window


The issue is that when iterating through the results I cannot perform an additional calls $command.ExecuteReader() even if I create a new command object and set the query text for that new command object.

I get the following error when I try: "There is already an open DataReader associated with this Command which must be closed first."

So I tried to close the reader after loading the data table

$rdr = $command.ExecuteReader()
          
 $table = new-object “System.Data.DataTable”
 $table.Load($rdr)
 $rdr.Close()

  return ,$table

Open in new window


With the data table filled and returned by the function, why can't I close the reader and then make a subsquent call to $command.ExecuteReader() ????
0
Will your db performance match your db growth?
LVL 2
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

I have set up my connection to an Oracle Database in Excel and the Command Text in the definition tab of the Properties window says 'SELECT * FROM "PS_OWNER"."PS_JOB"' which returns too much data for an excel spreadsheet. So my question is, can I use VBA to return the data the same way I return it in SQL Developer?
0
Hi Experts,

I am trying to write a value to a field when it is either null or blank.

I have tried:
Select
IsNull(NULLIF(SiteCode, ''),'NON-VHA') as SITECODE,
From Site_CTE

but it does not work

Please help and thanks
0
I have a stored procedure that I know is probably set up wrong but what is causing problems consistently is the attempt to return the row number.  I have the following code but I'm not sure since the table wasn't specifically designed with an identity row that this will work. Sorry, I'm new to trying to pull in row and not an expert at testing stored procedures either.
I was hoping to test executing the sp and return all declared output columns. I attached a file with a screenshot of the table columns.

 
ALTER PROCEDURE [dbo].[cpsp_preg_num]
(
--@Design Bit,
@pi_enterprise_id  CHAR(5),
@pi_practice_id  CHAR(4),
@pi_cur_person_id  UNIQUEIDENTIFIER,
@txt_date			VARCHAR(10) OUTPUT,
@txt_acttext		VARCHAR(75) OUTPUT,
@txt_method			VARCHAR(30) OUTPUT,
@txt_comments		VARCHAR(100) OUTPUT,
@txt_completedby    VARCHAR(100) OUTPUT,
@txt_time			INT OUTPUT,
@new_identity		INT OUTPUT
)
AS

-- SELECT @new_identity = SCOPE_IDENTITY()
 select top 1
 @txt_date = txt_date,		
@txt_acttext = txt_acttext, 	
@txt_method	= txt_method,	
@txt_comments	= txt_comments,
@txt_completedby = txt_completed_by,    
@txt_time		=	txt_time,
@new_identity	 = SCOPE_IDENTITY()
From cpsp_ext_ 
where txt_pregNum = 1
order by create_timestamp asc

Open in new window

cpsp-table.jpg
0
Hi All,

I have a query where I am trying to find duplicates, and once I find the duplicates I need to make another pass to the table to get a column that was not part of the dup query to display the values in separate columns.
 Below is an example.

Path name is not part of the dup query

Select Date, Drill_group, NameGroup, Drillname, Count(1) As DupCount
From Sourcetable
Group by Date, Drill_group, NameGroup, Drillname,
Having Count(1) > 1

Source table
Date      DRILL_GROUP      Name Group      Drill Name      PATHNAME
20170630      ATTRIB_SECTOR      Gold      OPTION      MATERIAL
20170630      ATTRIB_SECTOR      Gold      OPTION      OTHER
20170630      ATTRIB_SECTOR      Intl      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Intl      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Income      AMERICAN      CONSUMER
20170630      ATTRIB_SECTOR      Income      AMERICAN      OTHER
20170630      ATTRIB_SECTOR      MLP      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      MLP      ENERGY      PETRO
20170630      ATTRIB_SECTOR      Advisors      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      Advisors      ENERGY      PETRO
20170630      ATTRIB_SECTOR      GTC      CAPITAL      HEALTH

Result for Dup Query:
Date      DRILL_GROUP      Name Group      Drill Name      Dup Count
20170630      ATTRIB_SECTOR      Gold      OPTION      2
20170630      ATTRIB_SECTOR      Intl      CAPITAL      2
0
Struggling a bit with a query. Here's the details:

Table1

ID, StartDate, EndDate, Approval (Bool/bit), Hours (Decimal 5/2), FiscalYear, EMPLID


Table2

ID, EmployeeCode, FirstName, LastName, Mgr



I need to display T2.FisrtName, T2.LastName, T2.Mgr, T1.StartDate, T1.EndDate, T1.FiscalYear, And the sum of T1.Hours
WHERE T1.EMPLID = T2.EmployeeCode

So basically I'm looking to display 1 record for each record in T2 while displaying the sum of T1.Hours for each correlating T1.EMPLID/T2.EmployeeCode record(s)


Result might look something like:


Peter     Griffin     Glenn Quagmire     1/1/2000     1/6/2000     1999     84.5



How would I do this?
0
Hey

How do I connect to a SQL server - and return a recordset? (Trusted_Connection=True)

For example (Select * from mytable where field1=1)

Using VB.NET

Thanks in advance

Mike
0
I can't seem to find this in my search. I simply have a table or range in my excel spreadsheet and I'm looking for a VBA code to insert the data into SQL Server.  I don't want to use any integrated services or an OPENROWSET in SSMS since this will be a spreadsheet in a shared folder.
0
In SQL Server, I have a View with a function that Calcs a certain number for people based off other settings, such as this (details of the function aren't important), with this output...

select FName, LName, Gender, dbo.CalcNum(field1,field2) as MyNum from tPeople

Al, Smith, M, 12
Bob, Carson, M, 13
Hank, Small, M, 3
Jake, Big, M, 45
Sandra, Lee, F, 9
Ellie, Day, F, 9
Debra, May, F, 10
etc.

What I now need is another View that queries this first View, with a total count of MyNum of all those rows with a specific number (for example, a total of all those with a value of 8) in different columns, and separated into 2 different rows by Gender. Not only that, but include a couple of columns that are number ranges.

For example, columns are Gender, TotNum7, TotNum8, TotNum9, TotNum10, TotNum8to10, TotNum11to15.

So given the above data, the output would be...

F, 0, 0, 2, 1, 3, 0
M, 0, 0, 0, 0, 0, 2

This basically says...

Of Females, 2 have the number 9, 1 has number 10, 3 are in range 8 to 10, and none in other columns.
Of Males, 2 have the number in the range 11 to 15, and none in other columns.

How can I do this? I suspect a PIVOT query? I tried to understand how they work, but in doing so, I'm not sure it's even the right option. And even if it is, I don't grasp how to make it work. Thoughts? To give a starting point with the data from above...

create view View1
as
select 'Al' as FName, 'Smith' as LName, 'M' as Gender, 12 As MyNum
union …
0
Hello

I am looking for a stored procedure to look only in a specific table

I see this procedure all over the internet

https://www.sqlservercentral.com/Forums/Topic1232230-391-1.aspx 

that works like so:

EXEC SearchAllTables 'Computer'

but I want to look only in one table

EXEC SearchTable 'TableName'  'Computer'
0
Watch the Recording: Learning MySQL 5.7
LVL 2
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

I am trying to write an insert statement.  One of my values contains an & (ampersand) and I keep receiving an error.  The value also contains a ;.  I don' t know if that will be an issue.

How can I insert something similar to below example
insert into mytable
(field1, field2, field3)
values
("MyName", "MyValue", "123&;456")

Open in new window

The error is:  "SQL Error (1064):  You have an error in your SQL syntax; check the manual that corresponds to your MySQL version ....."

Shows the value attempting to be inserted as where the error is.
0
We moved data from a set of fields in a table to another set of fields in the same table.  

These are the old field names:
QWife1Child1 , QWife1Child2,  QWife1Child3
 QWife2Child1 , QWife2Child2,  QWife2Child3
 QHusb1Child1 , QHusb1Child2,  QHusb1Child3
 QHusb2Child1 , QHusb2Child2,  QHusb2Child3

These are the new field names:
Child1, Child2, Child3 ... Child10

If there is a name in the old field then we copy it to a new field.  Not every couple has a child together.  They could each have children from previous marriages.  Keeping that in mind Child1 doesn't always  = QWife1Child1.  Child1 could = QHusb2Child1.

I wrote the query to complete this task and I believe that it ran like I wanted.  I just need to make sure.  

I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields.  The query I wrote to do this is giving me syntax errors.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I change this query to get the data I need?

SELECT q.MattersQTRUSTINFO, 
	QWife1Child1 , QWife1Child2,  QWife1Child3
	QWife2Child1 , QWife2Child2,  QWife2Child3
	QHusb1Child1 , QHusb1Child2,  QHusb1Child3
	QHusb2Child1 , QHusb2Child2,  QHusb2Child3
FROM [MattersQTRUSTINFO] q
JOIN Matters m ON m.matters = q.Matters
WHERE QWife1Child1  NOT IN (SELECT qchild1, QCHILD2, QCHILD3, QCHILD4, QCHILD5, QCHILD6, QCHILD7, QCHILD8, QCHILD9, QCHILD10

Open in new window

0
I have the following info in a column called TOOL.

TOOL

25-111111
1250-111111


How can I write a query that gives me a result set like below:
COLUMN
25
1250
0
I am trying to get an sql procdure to work. it is the second select statement that is causing the problem. i am getting the following error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. for each record there should be only one result. is it the outer join that is causing the problem.

what i want returned
status 0 should show Order Stated
status 1 should show Order approved

(sample select statement for test)

SELECT
OrderId,
DateCreated,
DateShipped,
BillName,
CustomerId,
Status,
(select OrderStatusText
from OrdersTable od LEFT OUTER JOIN OrderStatusTable OST on OD.Status = OST.OrderStatusId) aS StatusName
FROM OrdersTable ot
where CustomerId = 'acddc51c-99c9-41db-aaed-13e96db35a3d'
0
Hello,
Can you please help,
I'm using below code, but it  returns multiple rows.
I need to combine the NoteText, seperated by __

Select Distinct FO.Orderno AS [Order #],FO.Orderdate AS [Order Date], NoteText AS [Reason]
From FinalizedOrders FO
INNER JOIN FinalizedOrderNotes ON FO.OrderNo=FinalizedOrderNotes.OrderNo
Where NoteText Like '%QA1%'
AND FO.orderdate >= ................. and ............... and ..................

Example Results:
Order #                       Order Date                                  Reason
7517502                       2017-07-10 08:31:55.000      Q1-Test1
7517502                       2017-07-10 08:31:55.000      Q1-Test2
7517502                       2017-07-10 08:31:55.000      Q1-Test3

Would like to have it
7517502                       2017-07-10 08:31:55.000      Q1-Test1__Q1-Test2__Q1-Test3

Your help is appreciated.
0
Hi guys,

In SQL SERVER 2008R2, I want to know if today is the 4th BUSINESS DAY DATE before end of the month.

Thanks.
0
I have a form with two list boxes. I want the one on the left to contain all documents assigned to a parent record. I want the other box to show all documents NOT assigned to that record. I used to have a Select query for the Unassigned box that had another Select query within it. The inner select query used a left join to identify records WITHOUT a match. But I lost the query syntax! Any help appreciated.
0
I'm trying to set up a trigger that will execute a stored procedure when the row being inserted contains certain verbiage.

The procedure is only sending an email notification.
I'd like to pass a value from the row to the procedure so that it can be included in the email.   It's a date value.

I keep getting the following error when I try to create the trigger:

SP2-0552: Bind variable "NEW" not declared.

Here is the trigger code:

CREATE OR REPLACE SCHEMA_NAME.TRG_INTERFACE_LOG_ERROR
AFTER INSERT ON SCHEMA_NAME.INTERFACE_LOG
REFERENCING NEW AS NEW
FOR EACH ROW WHEN (new.USER_MESSAGE='java.sql.SQLRecoverableException: Closed Connection')
 
BEGIN
 
 SCHEMA_NAME.EMAIL_EXCHANGE_INCIDENT( :NEW.LOG_DATE );

END;
/

Here is the table:
SQL> desc schema_name.interface_log
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 LOG_ID                                    NOT NULL NUMBER(10)
 LOG_DATE                                           DATE
 USER_MESSAGE                                       VARCHAR2(4000)

I could sure use an expert eye.
0
Hi Experts,

I try to check a value in a SQL where clause, but get the error: CPD4374

On the  Where xxxx in() of the next statement

Exec SQL Select * from my file
Where Field1 = :Parm1
  and Field2 = in(:Parm2)

Field2 is numeric(15,0)
Parm2 is Character(128) and contains  '12,13,45,16,32  '

What is missing?
0
Get proactive database performance tuning online
LVL 2
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

I installed SQL Server 2014 Express in Windows Server 2012 Standart. I've restored a backup of SQL Server 2008 and everything works fine on the server.
I've enabled TCP via Configuration Manager as well as Management Studio.

But I have a problem: I can not configure ODBC access from a terminal, where I downloaded and installed the ODBC driver 11.

Are there any settings to be made in Server 2012 or any other detail in Server 2014 Express?

Tks.

Translate from portuguese:

"Instalei o SQL Server 2014 Express no Windows Server 2012 Standart. Restaurei um backup do SQL Server 2008 e tudo funciona bem no servidor.
Eu habilitei o TCP via Configuration Manager, bem como Management Studio.

Mas tenho um problema: não estou conseguindo configurar o acesso via ODBC a partir de um terminal, onde baixei e instalei o ODBC driver 11.

Tem alguma configuração a ser feita no Server 2012 ou algum outro detalhe no Server 2014 Express? "
0
On my php page I have a select statement.  I copied the SQL to SQL Server to test and modify it.

The results are returning in SQL Server and also on the php without the modification.

I added the below to the sql select in PHP (after testing it works in SQL Server).

Original Code
CONVERT(VARCHAR(10), [I].[shipDate], 101),

Open in new window

Modified Code
CASE RIGHT((
		SELECT TOP 1 INV.Name AS InvoiceName 
		FROM SO_Shipment SHIP  
		INNER JOIN SO_Invoice INV ON INV.id_invoice = SHIP.id_invoice
		INNER JOIN SO_Items ITEMS ON ITEMS.id_order = SO.id_order
								  AND ITEMS.id_shipment = SHIP.id_shipment
		WHERE 
		SO.id_order = SO.id_order
		AND RIGHT (INV.Name, 1) = 'B'
		AND ITEMS.Name1 = I.Name1 
		), 1)
	WHEN 'B' THEN
		CONVERT(VARCHAR(10), [I].[shipDate], 101)
	ELSE
		CASE RIGHT([SO].[name], 1)
			WHEN 'B' THEN
				'1/1/3000'
			ELSE
				CONVERT(VARCHAR(10), [I].[shipDate], 101)
			END
	END AS [shipDate]
,

Open in new window

The above SQL returns the desired results.  However, when added to the php page and running I receive my headers but no values.
I modified the Select Case to
'3/1/2017' AS shipDate,

Open in new window

and it returns data in PHP.

I also attempted to create a Stored Procedure in SQL SERVER with the SQL code including the CASE Statement.  It works in SQL Server.

I added the below code to PHP
	$itemsTable = ms_select_table("EXEC [MyDbase].[dbo].[Get_Items];");

Open in new window

Below code shows "0" results with the SQL and SPROC.
	ECHO(COUNT($itemsTable)." unique results.<br />");

Open in new window

Why is the Select Case NOT working in PHP?  How do I get this to work?
0
I am using a SQL Server DB as the back end of an Access 2013 application.

A view, which is to be used for the row source of my combo box,  has been defined in SQL and linked to the front end,.  The view contains and 'Order By' in SQL Server and I understand that the Order By is not used to order the view in Access.

It all works but when I used the View as the row source the items are not in any particular order.  I want them ordered.

What I did to accomplish that was click on the '...' on the rowsource line, which took me to query builder.  I pulled the fileds into the query and set the Order I wanted.  Now the entries in the dropdown come up just as I want them.

The rowsource is now:
"SELECT dbo_vADrop_MuniMaster.MuniMastID, dbo_vADrop_MuniMaster.MuniCode, dbo_vADrop_MuniMaster.MuniName FROM dbo_vADrop_MuniMaster ORDER BY dbo_vADrop_MuniMaster.MuniCode;"

My question:  Is the way I accomplished the sorted dropwdown, the best way?  

Since this view/drop down may be used on multiple forms I thought it might be best to define a permanent Access query with the view as the basis.  The Access query would sort the data in the order I want.  The Access query would the be used as the row source on all drop downs using information from this view.

Just thought I'd check with the experts on the recommended way of handling this situation.
0
I have a simple query.
SELECT twt.Word
FROM Dictionary.[dbo].[TblWordTags] TWT
WHERE twt.word like'%WB' AND ClientName<>'Smmt'
GROUP BY Word

Open in new window


It returns

Word
XLWB
(180)SWB
LWB
TDI(180)SWB
(102)LWB
MWB
TDCI(90)LWB
SWB
TDCI(90)SWB

I would like to extract the Lwb, Swb, Mwb, xlwb so any other parts are excluded. the required substring could appear anywhere in the string.

I am not sure the best way, I guess I could say find the string and if true force the value like an iif in access. not sure what is best.
0
Hi Experts
I use Veeam 8.0 Free only Tape function and MS SQL express installed on the Windows 2012 R2 Server.
today i got error, related  SQL Server max 10Gb place.

is there a way i can solve this Problem.I reduced sometape Job which are not need any more.But not help.

error:SQL server is not available.
Could not allocate a new page for database 'VeeamBackup' because of insufficient disk space in filegroup 'PRIMARY'.
 Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated.
0
I have a schema (database) in MSSQL that is called PRODUCTION.  I also have security group setup in Active Directory called DB_USERS.  

I am having a problem with the syntax to grant the security group, (and all of the members of it), the rights of SELECT, INSERT, UPDATE, ALTER and DELETE to "all" of the tables inside of this DB.

Can someone provide me with a syntax SQL query command to do that, or alternatively, how I can do that through SSMS?   For some reason I am just not getting the syntax correct.

I tried the following script, and it creates everything, but it errors out on the last line when it comes to the GRANT command.

USE master;
GO
CREATE LOGIN [MYCOMPANY\DB_USERS] FROM WINDOWS;
GO
USE PRODUCTION;
GO
CREATE USER [MYCOMPANY\DB_USERS] FROM LOGIN [MYCOMPANY\DB_USERS];
GO
CREATE ROLE DBACCESS;
GO
EXEC sp_addrolemember 'DBACCESS', 'MYCOMPANY\DB_USERS';
GO
GRANT SELECT, INSERT,UPDATE,ALTER,DELETE ON SCHEMA::PRODUCTION TO DBACCESS;
GO

The error I am getting is "unable to access schema PRODUCTION. It does not exist or you do not have permissions."

I am logged in as SA.

Thank you in advance,
Jeff
0

SQL

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.