[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

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

Sign up to Post

Hi

I am facing issue for logging row count in txt file. I have to log row count with date time in txt file, but below query is logging only date time not row count.

Below select query will return 0 row count.

select count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

Open in new window


Below query is not logging row count 0 in txt file. Please help me to find the root cause of this problem. I have to schedule job to run every 30 minutes using below query, the query output need to append (concat) single file as showed in the attached file
DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Open in new window


I am herewith attaching Count_Final.txt
Count_Final.txt
0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Hi

Can you please tell me how to use getdate:00:00:00 and getdate:11:59:00 in SQL between condition

select count(*) from tablename where createdon between 'getdate:00:00:00' and 'getdate:11:59:00'

thanks in advance
0
I have an sql server  2016  table TB_Docs with fields
    DocUID,  DocID , DocName, …..

I have TB_DocAttr  with following  fields
   UID,   OBJID , PropertyVal ,  StrValue  
 
( OBJID in TB-DocAttr  is a foreign key to primary key DocUID  in TB_Docs)

sample   TB_Docs
   DocUID    DocID             DocName
    123          UID_Doc1         Doc1
    345          UID_Doc2         Doc2
    678          UID_Doc3         Doc3

 TB_DocAttr
    UID       OBJID   PropertyVal      StrVal
     111        123       Prop1                Val11
     222        123      Prop2                Val12
    333        123       Prop3                Val13
    444        345       Prop2                 Val22  
    555        345       Prop3                 Val23  
   
 I need a query that can give the properties and  values  in the same row  like this
    DocName     DocUID        Prop1         Prop2       Prop3
   Doc1                  123            Val11           Val12        Val13
   Doc2                   345                                Val22        Val23

    In Oracle I could get  the "flattening" of the properties  with  
       MAX(DECODE(p.propertyval,'Prop1',p.strvalue))             AS  Prop1,
       MAX(DECODE(p.propertyval,'Prop2',p.strvalue))             AS  Prop2
   
  How can I get it in Sql Server?
0
Hello,

I have created SQL job using below script to execute every 30 minutes interval from 12:00:00 AM and 11:59:59 PM. The job will create new CSV file on shared folder for every 30 minutes. Instead of creating new CSV file (multiple CSV) every time is it possible in SQL to concat (append) new data on existing file like below? If its possible in MS SQL, can you please share the sample script.

Like: data should be appended like below on single file:

Execution DateTime: 12102018 20:00
Count: 10

Execution DateTime: 12102018 20:30
Count: 10

Execution DateTime: 12102018 21:00
Count: 15

Execution DateTime: 12102018 21:30
Count: 10
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\Count_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from DBName.dbo.IB where Notes = ''BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

0
Hello,

My requirement:
1.      I have to fetch number of records created in half an hour interval from 12am to 11:59pm in every 30 minutes.
2.      We have make it dynamic to run every 30 minute.
3.      This job will give a report for everyday and every 30 mints interval in csv format

To achieve above, I have created below SQL but it's giving error. Can you please help me to fix below error.
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\YourData.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from IB where Notes = ''M_BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

Error:
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'IB'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL
1
Hi

Through the advice from this plat form I have moved all the ms access tables to Ms Sql server express with some amendments of course on the sub forms and data type which were a problem, I avoided the pass through queries due to some challenges on updating. So I'm using the less DSN File and ODBC 17 which is working fine and the speed is no different with Ms Access Back-end:

Deployment:

(1) I want to deploy this application to a client outside our network, this mean there will be a new server to host the tables, now correct me if I,m wrong here:

(a) I will download an MS Sql Server Express on a nominated computer to act as a server where all other 50 work stations will be linked
(b) On the 50 work station I will install the same ODBC 17 and MS Access runtime 2016 if no full license available
(c)  The ODBC connection string  will now change or I will replace my computer name currently showing to the new computer or server name.  After that before logging out I will compile the application to accde, I know after make an accde it may want to log in, but at this point I expect to fail because of the change to host computer, but when I install it to one of the work station mentioned above I expect it to located the correct server at that point.
(d) Anything else?

MS Access 64 Vs 32

Here I have serious problems most of my clients are now shifting 64 BIT office , now I understand there is a code out there that can allow a 32 BIT written application to run on …
0
I would like to read JSON URL link from SQL SERVER. I would like to build a stored procedure by passing JSON URL Link and would like to store output into a SQL SERVER table.
0
How can you tell if a SPOC is signed with a certificate?

Working on a dbase and apparently a certificate was used.  Having issues accessing data from the database and believe this is the issue.
0
I've got a potential new client who wants me to perform some ETL operations on a set tables (SCADA data) which is in the format:

EntityID   FieldName    FieldValue    ReportTime

I've worked with this particular data provider before and getting the data from that table structure into several relational tables involves some serious translation operations.  The client wants to extract data from this data table every 20 minutes for some data fields, and once a day for others.

Eventually, we will also be creating a FE to link to the Azure database for limited data entry, modification, and reporting.

Having only minimal experience with Azure and the billing model, I need to know:

1.  Can you create linked servers in Azure? so that I can easily extract data from this offsite data source?

2.  Would it make more sense, giving the Azure billing scheme (which I don't understand at all) to use an on-prem SQL Server to perform the ETL operations and then push it to Azure?
0
Hi EE,

How does one patch a SQL Server analysis instance with the latest service pack?

In this case SSAS 2008 SP2. I get the following error, see attached.

Any assistance is appreciated.

Thank you.
error.PNG
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

We have 5 machines, each machine inserts a record to a SQL database every second.
At the record generation time, it writes the order number, machineID, a machine status (either U up or D down) and the time period).
Machine data is attached.

Ref:
(note that ordernumber is 7 digits and the next digit is machineID, so first line order is 2633448 and machine is 2)
OrderNumber      Machine ID      Status      TimePeriod
2633448      2      U      2018-12-07 08:08:13.000
2601441      4      U      2018-12-07 08:08:12.000
2637183      3      D      2018-12-07 08:08:08.000
2624657      5      U      2018-12-07 08:08:08.000
2633448      2      U      2018-12-07 08:08:03.000
2601441      4      U      2018-12-07 08:08:02.000
2637183      3      D      2018-12-07 08:07:59.000
2624657      5      U      2018-12-07 08:07:59.000
2633448      2      U      2018-12-07 08:07:54.000
2601441      4      U      2018-12-07 08:07:52.000

What I'm trying to accomplish is a report of time-based data showing when the machine switches status and the total duration of the status.
Something like this:
OrderNumber      Machine ID      Status      Status End      Status Start      Duration
2633448      2      U      2018-12-07 08:08:13.000      2018-12-07 07:57:10.000      0:11:03
2633448      2      D      2018-12-07 07:57:49.000      2018-12-07 07:57:42.000      0:00:07
2633448      2      U      2018-12-07 07:57:41.000      2018-12-07 07:57:11.000      0:00:30

As part of the first step, my thinking is to find the points where the status changed using a lag.
Like this...

Select 
[OrderNumber],
[MachineNumber],
Status,
Lag(Timeperiod, 1) Over (partition by Ordernumber, [Status] order by OrderNumber, TimePEriod) As 

Open in new window

0
I have a one SSRS report in that there is filter with start date and end date. Now I want to change the format of the calendar which opens when I select date.

Can any one suggest me how I can do that ,now the Date time format of the calendar is "dd/MM/yyyy" I want this should be "MM/dd/yyyy"

Thanks in advance.
0
Hi

Having worked through the SQL Server as a backend to Ms Access FE I want to make a code that will check the connection strings that is valid from the access FE module, for example if the FIRST string below is wrong then it should check the SECOND string or return connection FAILED result:
(1)      sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"
(2)      strConnect = "ODBC;Driver={SQL Server};UID="& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
From the above situation how do I use CASE SELECT  , END SELECT  in a VBA code? Do not worry about a full code I have a working code intact, its working fine.
I have put the same working VBA code in a module and is call by a macro called autoexe, now what to achieve with suggested selection is that before deployment to some clients I have to collect some as follows:
(1)      Computer Number ( for security VBA code purpose)
(2)      Computer name ( to used in the connection string above )
The current VBA will continue working silently without the user knowing what is happening, so in short string (1) will have my computer/server details while string (2) while have the new details for the new client computer name (Where the application will be deployed). The reasons for the above suggestion is as follows:
(1)      If I compile the application based on my computer connection it will fail to connect the new client computer because the server name will be different , hence the required  2 string …
0
hi, I need a script or tool for getting change history audit for last month in "file". Something  like -  name - "file.xls", change date - "01.12.18", changed by - "user01", change type - "modified" .
File is in our file server. thanks
0
where to download microsoft DTS 2017

only dts

regards
0
I trying to set up a job with SQL Server to send an email out whenever the Builder's General Contractor Ins Exp Date is coming up for expiration or expired.  If it is expiring/expired to send an email out to the specific contractor in the email column letting them know of it expiring.  I have set up database mail and tested that it is working.

How do I do this?
Capture.PNG
0
Has anyone seen this SSRS report function?   "intSuppress".  It is used in an expression.

Example:  =Code.rptFunctions.intSuppress(Fields!HEALTHNBR.Value, Join(Parameters!prmCardIncludes.Value))

I am modifying an older 2012 SSRS report and trying to find some information, documentation or examples on the internet and
seem to cannot find any.

Trying to find a definition of this before trying to modify and not to break it!

Thanks
0
What do i need to do to download a free version of SSIS?
0
I'm wondering if it's prudent to adjust the Database "Initial Size" value for my environment?
SQL version:  2014
Recovery model:  Full
Database Size:"   158GB

I recent ran a utility which indicated to me that: "89 growths took more than 15 seconds each. Consider setting file autogrowth to a smaller increment."
Autogrowth / Maxsize setting appears to be default of:  10% unlimited, and we DO see unexplained performance issues on this database.
Does it make sense to calculate an anticipated DB size,(for say, 1-3 years down the road?) reset Initial Size to that size, and keep the autogrowth setting as-is?
I've also seen on Microsoft's site that the growth % should be about 1/8" the size of the database...Should I change autogrowth from 10% to around 13GB?

Thoughts?
0
The 7 Worst Nightmares of a Sysadmin
LVL 1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

I am looking to go to the cloud and looking for reliable providers who are afforable



I am looking for a web version of Window Server

Windows Server 2012 or newer -- Standard, not Web edition

SQL Server (Workroup or Standard edition) not SQL Express *


Windows Client Access Licenses

Terminal Services to allow login for upto 3 users at a time (total of 10 or so named users)

16 GB RAM
perhaps 1 TB of disk memory, may be less if this is expensive

Any recommendatiuons are welcome

Gunjan
0
I am running SQL Server 2016 on Windows Server 2016 and have two large SQL Jobs. One runs in the early morning and one in the evening.

I've already setup Database mail and have tested successfully, so I get email alerts for completed jobs. I'd like to go a layer deeper.

I'd like to create an email alert if a job runs over a certain amount of time. There are 15 steps in this particular SQL Job, but a handful of them get hung-up often (mostly because some folks are allowed to upload Excel docs that the Job updates).

Is there a way to create a New Alert such that a given job step would send me a notification if it took over a certain amount of time? (e.g. ~30 minutes).

Thanks for your help.


...
0
I have a master detail realationship in the database.  A tables of Persons, and a table of Eyes.  There can only be two rows in the detail table (Eyes) pera  person in the Persons table. (we only have two eyes!)

I want to write a query that returns one row that inlcudes the data from the person, plus left eye and right eye.

Query 1 below returns two rows because of the left outer join i am using.  Both rows are identical.  I only need 1 row so i wrote query 2 that has a Top 1 statement.  This seams a little crude, if simple way to solve this problem.  I could also use Distinct.  Is there a better and recomened way to do this?

QUERY 1 - Returns two rows

select 
  mpv.id as mpv_id, 
  mpv.PersonId,
  mpv.RegisteringUnitId,

  medh.id as H_medh_id,    -- Right eye
  medh.Öga as H_Öga,
  medh.ÄrUrsprung as H_ÄrUrsprung,
 
  medv.id as V_medv_id,  -- Left eye
  medv.Öga as V_Öga,
  medv.ÄrUrsprung as V_ÄrUrsprung
 
FROM MAKULA_PersonVisits mpv 
  left outer join MAKULA_EyeData medH on mpv.id = medH.mpv_id and medH.Öga = 1 
  left outer join MAKULA_EyeData medV on mpv.id = medV.mpv_id and medV.Öga = 2 
 where mpv.id = 583536

QUERY 2 - Only 1 row

select top 1
  mpv.id as mpv_id, 
  mpv.PersonId,
  mpv.RegisteringUnitId,

  medh.id as H_medh_id,  -- Right eye
  medh.Öga as H_Öga,
  medh.ÄrUrsprung as H_ÄrUrsprung,
 
  medv.id as V_medv_id,  -- Left eye
  medv.Öga as V_Öga,
  medv.ÄrUrsprung as V_ÄrUrsprung
 
FROM MAKULA_PersonVisits mpv 
  left outer join 

Open in new window

0
I got the screenshot from my SSRS builder for MS SQL Server.
Inside of the textbox. Can I run sql statement like the following?
all I need is to get the value from =Fields!newleadfirstname.Value and try to run another select statement.

= select lead.firstname from leads where lead.firstname=Fields!newleadfirstname.Valuexxx
0
Hi Experts

I'm in a mixed group of SQL Server DBA's and developers and Oracle DBA's and developers from different business units that have only rarely interacted with each other in the past.   Our upper management has given us a fuzzy directive to cross-train ourselves between Oracle and SQL Server, and for the most part everyone in the group is doing a twelve-way blank stare on what the expectations are.

Question:  Does anyone have any references to existing Oracle and SQL Server cross-training?  Videos, published articles, blog posts..

This question is not..
A request for info on which is the better database.
An invitation for anyone to do a Google search and post the first thing that's returned, and then can't speak to it.

Things I've stumbled across so far..
PluralSight - Advanced SQL Queries in Oracle and SQL Server
MVA - SQL Server 2016 Essentials for the Database Administrator
Udemy - Data Migration: Database Migration from MS SQL to Oracle DB

Thanks in advance.
Jim
0
Our program is running successfully on over 300 clients. However, there is exactly one client using our application where the program is hanging on a specific operation. Looking at the source code we've determined that where it is hanging is when a database connection is trying to read a table at the same time that another connection has an open transaction on that table. Normally this isn't an issue because the transaction doesn't affect the row(s) being queried by the other connection. This customer, however, is seeing different behavior. This got me thinking. Is there a database or instance level setting that can be changed which might cause unusually pessimistic locking during the transaction process? That is to say, is there an option which locks an entire table during a transaction rather than whatever the default behavior is in SQL Server?
0

Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.