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

How can I estimate the local area network utilisation involved in remotely executing a simple SQL Server Stored Procedure ?

I have a windows application that uses ADO to execute a stored procedure on a Microsoft SQL Server 2005 database server.
The stored procedure is simple and returns a single result in an  integer output parameter.

I am executing this procedure every 10 seconds and have been asked to estimate the "network utilisation".  There are no input parameters to the stored procedure. I will be answering the question as "virtually no network utilisation" but I anticipate being asked how to justify this statement.
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Notice in my query below where my 2nd Join statement includes the field B.ID in functionC...that throws a "B.ID could not be bound" error. I just need to be able to pass the B.ID coming from my 1st Join statement (or even the A.ID from tableA would be alright) to the nested query that makes up my 2nd JOIN table. I considered making my nested select-query in the 2nd join its own function, but I'm not sure if that would even work inside a join statement with the ID being passed into that function as a variable (ie. "B.ID" instead of a literal ID like "1234").
SELECT A.Name, B.ID 
FROM tableA AS A 
LEFT OUTER JOIN tableB AS B ON A.ID = B.ID 
LEFT OUTER JOIN (select top 1 Car from functionC(B.ID) where color = 'green' order by orderDate desc) C 
     ON C.color = A.color 

Open in new window

0
Hello:

Below is my code.  As you'll notice, I have hard-coded the YEAR as '2017'.

The KBUK..GL10110 table contains the YEAR as the field YEAR1, and I have aliased the table as gl.  But, when I try to have gl.YEAR1 represent the YEAR (instead of hard-coding the year), most of the results return NULL.

Why is that, and how can I overcome it?

Thanks!

John


select gl2.ACTIVE, Accts.ACTINDX, gl3.ACTNUMST as [Account], gl2.ACTDESCR as [Account Description], 
'2017' AS [YEAR], Period.PeriodNumber, 
case when Period.[PeriodNumber] = 0 then 0 ELSE ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0) END as [Opening Balance], ISNULL(gl.DEBITAMT,0) as Debit, ISNULL(gl.CRDTAMNT,0) as Credit, 
ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change], ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance] 
FROM (select distinct ACTINDX from KBUK..GL00105) as Accts
CROSS JOIN (select 0 as PeriodNumber union all select 1 as PeriodNumber union all select 2 as PeriodNumber union all
select 3 as PeriodNumber union all select 4 as PeriodNumber union all select 5 as PeriodNumber union all
select 6 as PeriodNumber union all select 7 as PeriodNumber union all select 8 as PeriodNumber union all
select 9 as PeriodNumber union all select 10 as PeriodNumber union all select 11 as PeriodNumber union all
select 12 as PeriodNumber) as Period
LEFT OUTER JOIN

Open in new window

0
I am importing a file into SQL Server table

Some of the Nast names are Hispanic with the ~ over the n?  

In file it is fine
Once it is in SQL it gets replaced with a ?

AQnt ideas?
0
Hi

Is it possible to drop any key that may exist on a table in SQL using a SQL statement (not manually), or do you have to specifically know what that primary key is called?

Thanks
0
I have Microsoft SQL Server 2012 installed in a production environment.
The TempDB has grown to tak up the size of the C Drive (20GB in growth).
How can I shrink the TempDB in the production environment without impacting live users?
0
Hi

I am trying to drop a primary key in my SQL table [Machines]. The following isn't working

ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number

This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
I can see "Machine Number" so I am not sure what I am doing wrong
0
I have table

 USER_ID  ROLE_ID  
    1            11,32,44
    2             34, 11, 32, 44
    3             11
    4             44, 22, 32
   5             32
  6              32, 44


If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id  i pass all should be present, please help me with query oracle , it will really helpful for me
0
I would like to take this code and have it be reused for different stored procedures. This section of code would be called for each the the button on the form below. I have all the datatable, dbcommand, executenonquery, executescalar all in one c# class and call them when I need them I need to do this for the dataset code below

String connString = “”;

string sql = “select” – I would like to use a procedure so I can use the code in more than one place.

// create connection
Sqlconnection conn = new sqlconnection(connstring);

Try
{
Conn.open();

Sqldataadapter da = new sqldataapapter(sql, conn);

Dataset ds = new dataset();

Da.fill(ds, “”);
 
(would like to return ds.table
}
Catch
{
}
Finally
{
Conn.close();
0
Hi,

I've just been told I'm now in charge of and managing two SQL databases and have never used SQL before.

One of them we can remove as it is a backup and is no longer required. How do I safely back this up and then delete it so we can, if required, restore it?

Also: The db has a total size of 6,927Mb. 6021Mb of which is transaction log space. How do I go about reducing this log space? We are nowhere near using up the space allocated to logs but we are short on disk space on the server.

I've tried backing it up by doing a full backup and also a transaction log backup but no reduction in transaction log space.

So I'm looking for 3 things:

1) Reducing transaction log space
2) How to create a full backup that we can restore from
3) How to take the db offline and then delete it from Studio (assume Tasks > Offline, then delete)

Thanks and apologies for the noob questions but this has literally landed on my desk.
0
What is SQL Server and how does it work?
LVL 1
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Dear Experts,

Based on your experience could you please advise in which scenarios worth to use this "SELECT in JOIN" technique:
SELECT
	t1.LIFNR AS VendorNumber,
	t1.NAME1 AS VendorName,
	SubQuery.BUKRS AS CompanyCode
FROM
	AP.LFA1 t1
INNER JOIN
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1 WHERE AP.LFB1.BUKRS = 'SX1') SubQuery ON t1.LIFNR = SubQuery.LIFNR

Open in new window


instead of doing the same filtering in WHERE section?
SELECT
	AP.LFA1.LIFNR AS VendorNumber,
	AP.LFA1.NAME1 AS VendorName,
	AP.LFB1.BUKRS AS CompanyCode
FROM
	AP.LFA1
INNER JOIN
	AP.LFB1 ON AP.LFA1.LIFNR = AP.LFB1.LIFNR
WHERE
	AP.LFB1.BUKRS = 'SX1'

Open in new window


Thanks in advance,
0
Hello,
How can I modify this query to have filter like' starts with '
  IF  @PatientName <> '' 	       
			 SET @SQL = @SQL + '  AND PatientName LIKE  ''%' + CAST(@PatientName AS  Nvarchar(MAX)) +  '%'''
   

Open in new window



Regards
0
Hello,
I have a query (below) where I am trying to only pull the first 5 characters (alpha and numeric) of the ARINVT.DESCRIP field.  The field is a varchar in the Oracle database.  Any help ?


Select V_ORD_DTL_AVALIABILITY.CUSTNO,
  V_ORD_DTL_AVALIABILITY.COMPANY,
  ARINVT.ITEMNO,
  AKA.CUST_ITEMNO,
  V_ORD_DTL_AVALIABILITY.ORDERNO,
  ORDERS.PONO,
  V_ORD_DTL_AVALIABILITY.SHIP_QUAN As "QTY TO SHIP",
  (V_ORD_DTL_AVALIABILITY.UNIT_PRICE * V_ORD_DTL_AVALIABILITY.SHIP_QUAN) As
  "Sales $",
  V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE,
  ARINVT.DESCRIP,
  ARINVT.CUSER8,
  V_ORD_DTL_AVALIABILITY.ONHAND
From ((((V_ORD_DTL_AVALIABILITY V_ORD_DTL_AVALIABILITY
  Left Outer Join ORDERS ORDERS On V_ORD_DTL_AVALIABILITY.ORDERS_ID = ORDERS.ID)
  Left Outer Join RELEASES RELEASES On V_ORD_DTL_AVALIABILITY.RELEASE_ID =
    RELEASES.ID)
  Left Outer Join EPLANT EPLANT On V_ORD_DTL_AVALIABILITY.EPLANT_ID = EPLANT.ID)
  Left Outer Join ARINVT ARINVT On V_ORD_DTL_AVALIABILITY.ARINVT_ID = ARINVT.ID)
  Left Outer Join AKA AKA On ARINVT.ID = AKA.ARINVT_ID
Where V_ORD_DTL_AVALIABILITY.CUSTNO Is Not Null And
  V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE <= To_Date(SysDate + 43) And
  V_ORD_DTL_AVALIABILITY.EPLANT_ID = 2 And RELEASES.ID <> 0

Order By V_ORD_DTL_AVALIABILITY.MUST_SHIP_DATE
0
I have a person_name field with the value of eg, Smith, John but search method does not work using like. Is there any method?
I used this query but nothing is returned

I'm using this store proc with parameter @pname.

      SELECT personID,PERSONNAME FROM [dbo].[VW_UNION]
      WHERE ,PERSONNAME  LIKE UPPER(RTRIM(LTrim(@pname + '%')))
Because, the person name is split by "," , it there to execute a query and look for the first string before the  first ','  ?
0
I know it sounds stupid, but i try to get an average from the query and instead of 11.35 it gives me 11.3507588532884
I was using val function, i was using "Standard". "Fixed", "#,###.00" and so on...
My query should return 11.35 or even better 11.4


need your advice, please
test.accdb
0
I want to include a variable in a SQL query that is based on the results of a second SQL query that returns a number.

This SQL works, where I define a number to be a simple number (say ten):
  DEFINE XYZ = 10
  select   full_project_name, sum( SUM_MMBTU )/&&XYZ sumTotal
   from QCW_ALL_YEARLY_SUMS
   group by full_project_name
   order by sumTotal DESC

But I want to define variable XYZ to be the result of a second query that returns a single non-integer number:
   DEFINE XYZ = (select   sum( SUM_MMBTU )
              from QCW_ALL_YEARLY_SUMS
              where (category = 'Electricity Consumption' or category = 'Mobile Combustion')
              and not (Goal ='Goal Excluded' and Target = 'Target Exempt')));

But this gives error 'unexpected end of SQL command'

How can I DEFINE a variable to be the output from a SQL query that returns a single non-integer number?
0
If we have delimiters  as "." then     why "Will D. Smith" and "Will D Smith" returns similarity  as 0.98 instead of 1?
0
set serveroutput on;
       --DECLARE err_status NUMERIC;
       DECLARE v_REFERENCE_CODE varchar2(30);
       DECLARE v_status_lkp varchar2(30);
       DECLARE v_OBSOLETE_FLAG varchar2(30);
       DECLARE cursor cur_invalid_objects is
               select a.REFERENCE_CODE,a.status_lkp,a.OBSOLETE_FLAG from BASE_SALES_ORDER a,BASE_SALES_ORDER_LINE b where
                    a.SALES_ORDER_ID =b.SALES_ORDER_ID
                    and a.REFERENCE_CODE ='10132834';
        --rec_columns cur_invalid_objects%ROWTYPE;

BEGIN
        dbms_output.enable(10000);
        open cur_invalid_objects;
        loop
                fetch cur_invalid_objects into v_REFERENCE_CODE,v_status_lkp,v_OBSOLETE_FLAG;
                EXIT WHEN cur_invalid_objects%NOTFOUND;
                dbms_output.put_line('ROWTYPE ' || v_REFERENCE_CODE || '  ' || 'SalesOrder Status' || v_status_lkp || '  ' || ' SalesOrder Flag status' || v_OBSOLETE_FLAG);
               -- v_statement := 'alter index '|| rec_columns.index_name ||' rebuild partition '|| rec_columns.partition_name ||' online;' ;
                                                                execute immediate 'v_statement';
        end loop;
        close cur_invalid_objects;
               
end;
/



I am getting error like below.Can some one rectify it asap.

Error report -
ORA-06550: line 2, column 8:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

   begin function package pragma…
0
It would appear that SSIS is now called SSDT. Is that correct?

I need to learn as much simple functionality as I can of SSIS by Friday for an interview opportunity. The largest one I have ever had.
Then I need to brush up on my SQL Server Query abilities. Which are old and rusty. Any thoughts on how I can get caught up quickly?
I understand the concepts of both products and have used SQL Server in the past just need any input so that I can snag a job. Please advise. Thank you.
0
Why Off-Site Backups Are The Only Way To Go
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Hi,

I need your help in Oracle sql. I am converting a Sql Server Stored Procedure to Oracle and here is my problem.

I am using this sql and I want the date format to be in "mm-dd-yyyy" but the output is different. Can you please help what should I do to fix it.

select to_date('08-29-2016','mm-dd-yyyy') - 7 from dual;

and the output is "22-AUG-16"

what should I do so the output is "08-22-2016"

Thanks in advance to you all... :-)
0
Hello:

Below is my code and, as you can see, I have hard-coded the year "2017" as [Year].

The fiscal year is October 1 - September 30.  Instead of hard-coding "2017", I'd like for SQL to display this automatically.

How would I get it to do so?

Thank you!

John

select gl2.ACTIVE, Accts.ACTINDX, gl3.ACTNUMST as [Account], gl2.ACTDESCR as [Account Description], '2017' as [Year], Period.PeriodNumber, 
case when Period.[PeriodNumber] = 0 then 0 ELSE ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0) END as [Opening Balance], ISNULL(gl.DEBITAMT,0) as Debit, ISNULL(gl.CRDTAMNT,0) as Credit, 
ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change], ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance] 
FROM (select distinct ACTINDX from KBUK..GL00105) as Accts
CROSS JOIN (select 0 as PeriodNumber union all select 1 as PeriodNumber union all select 2 as PeriodNumber union all
select 3 as PeriodNumber union all select 4 as PeriodNumber union all select 5 as PeriodNumber union all
select 6 as PeriodNumber union all select 7 as PeriodNumber union all select 8 as PeriodNumber union all
select 9 as PeriodNumber union all select 10 as PeriodNumber union all select 11 as PeriodNumber union all
select 12 as PeriodNumber) as Period
LEFT OUTER JOIN KBUK..GL10110 as gl on Accts.ACTINDX = gl.ACTINDX and Period.PeriodNumber = gl.PERIODID
LEFT 

Open in new window

0
I have a table (User_Values) where I need to create 613 rows of data into columns "User_ValueKey" (primary key) and User_Values (int).  The values are sequential, from 333 to 948.  The data would look like this in the table itself

User_ValueKey      User_Value
333                          333
334                          334
335                          335

And so forth.

Is there a quick way to do this?  I have SQL Server 2008 R2 Express, if that helps.

Thank you!
0
I need to build JavaScript charts ( line and bar charts) where the data is called from a SQL stored procedure. I really do not have any JavaScript developed.
Below is what I have so far....which is not completed.

   dataDailySalesChart = {
            labels: ['M', 'T', 'W', 'T', 'F', 'S', 'S'],
            series: [
                [12, 17, 7, 17, 23, 18, 38]
            ]
        };

        optionsDailySalesChart = {
            lineSmooth: Chartist.Interpolation.cardinal({
                tension: 0
            }),
            low: 0,
            high: 50, 
            chartPadding: { top: 0, right: 0, bottom: 0, left: 0},
        }

        var dailySalesChart = new Chartist.Line('#dailySalesChart', dataDailySalesChart, optionsDailySalesChart);

        md.startAnimationForLineChart(dailySalesChart);

Open in new window


The JavaScript above is hardcoded and not dynamic. I want to call the stored procedure every ten minuets to up date the JavaScript chart.
thanks
0
Please see the attached Screen Shot...why am I getting a ton of rows when I all I want is the total number of rows?

Thanks,
MarkScreen Shot
0
We've got a few old customers that, believe it or not, still have old classic ASP sites.   We recently began to migrate them from SQL 2012 to Azure SQL Database.  After the database migration, we get the following errors on the sites:

Server Native Client 11.0][SQL Server]The request for procedure '[tablename]' failed because '[tablename]' is a table object.

I figure at this point there has to be a fix for this however I cannot seem to locate it.

Anyone have the magic bullet?
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.