ASP.NET SQL datasource combine two queries

I am currently working on a project for my University Degree using ASP.NET webforms with C#. We are tasked with creating a student information system. I am trying to select from the database information about the student and also about who their advisor of studies is. I can get the ID of the advisor of studies, but I then need to select from the user table twice to get the name of the student and the advisor of studies.
I am making use of an SQL datasource to populate boundfields in a gridview.
Below I have 2 SQL statements which I need to somehow combine into 1.

First select to get the name of the advisor of studies (UserId is known from the cookie of the logged in user):

SELECT aos.Forename as StaffForename, aos.Surname as StaffSurname, sdt.AdvisorStudiesID 
FROM [User] aos, [StudentDetails] sdt 
WHERE (sdt.StudentId=@UserId) AND (aos.UserId=sdt.AdvisorStudiesID); 

Second select to get the name and details of the student:

SELECT DISTINCT Forename as StudentForename, Surname as StudentSurname, TitleLong, PersonalTutor, ProfileImage 
FROM [User] u, [StudentDegree] sd, [Degree] d  
WHERE (u.UserId=@UserId) AND (d.DegreeId=sd.DegreeId) AND (sd.StudentId=@UserId);

I had tried just separating these queries with a colon in the SQL datasource, but that did not work(see below)

<asp:SqlDataSource ID="Student" runat="server" ConnectionString="<%$ 
ConnectionStrings:QSIS_Connection %>" SelectCommand="SELECT aos.Forename 
as StaffForename, aos.Surname as StaffSurname, sdt.AdvisorStudiesID FROM 
[User] aos, [StudentDetails] sdt WHERE (sdt.StudentId=@UserId) AND 
(aos.UserId=sdt.AdvisorStudiesID); SELECT DISTINCT Forename as 
StudentForename, Surname as StudentSurname, TitleLong, PersonalTutor, 
ProfileImage FROM [User] u, [StudentDegree] sd, [Degree] d  WHERE 
(u.UserId=@UserId) AND (d.DegreeId=sd.DegreeId) AND 
(sd.StudentId=@UserId);"></asp:SqlDataSource>

Any help would be much appreciated.

Generic Windows Account To Query Data

I have been able to run the following VBA code (within Excel) using my own Windows account to pull data from a SQL server:

Sub GetData()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String

    ConnectionString = "Provider=SQLOLEDB; Data Source = ServerName;Trusted_connection=yes;"

However, now I’m at the point where I have additional users I would like to provide the file so that they can run the VBA and refresh the data (among other things). I have a generic Windows account that I would like to use, however the solution is not as simple as changing the connection string as shown below because the password is not a SQL server password but rather a Windows password.

ConnectionString = "Provider=SQLOLEDB; Data Source = ServerName;UID=user;PWD=password"

I’ve read about things such as “impersonation”, but haven’t been able to figure it out. Is there any reasonably simple solution to make this work? Much thanks!

how to remove space from SQL when joining multi columns

,Rtrim (Ltrim(IsNUll((Cast(FLOOR([COL1]) as CHAR (5))),'')+' '
+ isnull([COL2],'') + ' ' + (IsNull([COL3],'')))) as COLALL

The two outputs I get :

Output #1:

12345 main Street 

Output #2:

12    main Street

Output #1 is good because COL1 has 5 characters.
Output #2 is not good cause COL1 has 2 characters.

How to make it so even when COL1 has 2 characters I get the following result:

12 main Street 

Creating a conditional column on a table T-SQL

I have a table called dbo.asientti. Inside, there is a column called "D/H". It contains identifiers: "D" for debit, and "H" for credit. On another column I have the debit and credit amount. The "D/H" column tells me if the amount is a credit or a debit.

What I want to do is separate them: that is, to create a column containing all the debit, and another column containing all the credit.

My guess is that I have to create some kind of conditional column. In other words, I want to tell the computer: "Create a column on dbo.asientti such that, if the value on "D/H" for amount x is "D", then x is inserted on the column". Then, I would do the same with "H" in order to have only the credit amounts.

That is, the debit amount on a new column D, and the credit amount H on another one.

Is there any way to do this on Transact SQL? I looked for information on conditional columns but I could not find anything.

Thanks in advance……

What is the most efficient way to store a variable number of columns in SQL Server?

What is the most efficient way to store a variable amount of columns in MS-SQL?

I have a requirement to store a large number (several million) records into a Microsoft SQL server (via c#). Most columns are standard, but certain groups of users will need to add their own custom columns, and record data in them.
The data in each custom column field will not be large, but the number of records with a certain set of custom columns will be in the millions.

I do not know ahead of time what these columns might be (in terms of name or datatype), but I’ll need to pull reports based on these columns as effeciently as possible..

What is the most efficient way of storing the new varying columns and data?

  • Entity-Attribute-Value model?
    Con’s: Efficiency if there’s a large number of custom columns (= large number of rows)?

  • A extra table “CustomColumns”?
    Storing columnName, Data, Datatype each time an entry has a custom column, for each column.
    Con’s: A table with a large number of records, perhaps not the most efficient storage.

  • Serialise the extra columns for each record into a single field
    Con’s: Lookup efficiency and stored procedure complicated when running reports based on a custom field.

  • Any other?

Edit: Think I may be confusing option (1) and (2): I actually meant, is the following the best approach :

Entity (User Groups) 
    id   | name           | description
    --   | ----           | ------------ 
    1    | user group 1   | user group 1
    2    | user group 2   | user group 2


Attribute 
    id   | name      | type     | entityids  (best way to do this for 2 user 
    --   | ----      | ----     |            groups using same attribute?
    1    | att1      | string   | 1,2           
    2    | att2      | int      | 2
    3    | att3      | string   | 1
    4    | att4      | numeric  | 2
    5    | att5      | string   | 1

Value
    id  | entityId| attributeId  | value     
    --  | --------| -----------  | -----
    1   | 1       | 1            | a    
    2   | 1       | 2            | 1
    3   | 1       | 3            | b  
    4   | 1       | 3            | c    
    5   | 1       | 3            | d
    6   | 1       | 3            | 75   
    7   | 1       | 5            | Inches

How to clear SQL Server Extended Events Event File

How to clear SQL Server extended events that are stored in a file?

Background

Where are the files for SQL Server Extended Events Event File target stored?

I want to delete months worth of log files; but SQL Server doesn’t tell me where the files are:

enter image description here

I would follow the advice on Microsoft’s SQL Server forums:

Clear events in file target?

Simply stop the session and delete .xel file if it’s no longer required.

Attempt#3

I tried doing the single most obvious thing that any user interface designer worth their salt would have created from the beginning: Right-click the event file target, and select:

  • Clear
  • Delete
  • Purge
  • Empty

Except there is no option to do any of those obvious things:

enter image description here

Attempt#4

I also tried going into the Extended Events menu, and clicking Clear Data. But the option is inexplicably disabled:

enter image description here

Attempt#5

I also tried to script the Extended Events Session, in order to see where it is storing the files. But of course SQL Server team is not helpful:

ADD TARGET package0.event_file(SET filename=N'Expensive Queries',max_file_size=(25),max_rollover_files=(4)),

Attempt#6

In SQL Server Profiler. If you wanted to clear the events you pushed the button to clear the events:

enter image description here

SQL Profiler is deprecated, and it’s replacement provides no way to clear the events.


What is the way to clear the events?

Bonus Reading

Install RTVS R Host Broker Service

We’ve tried to setup remote work spaces on a Windows 2012 R2 server to allow R for Visual Studio (RTVS) to connect remotely to that server.

We’ve following the MS install guide https://docs.microsoft.com/en-us/visualstudio/rtvs/setting-up-remote-r-workspaces however we haven’t been able to get the service working.

The install guide mentions v1.0, but this isn’t available in github and v1.3 gives different results to those described in the install guide. After running the installer none of the expected services R Host Broker Service and R User Profile Service were showing under services or task manager, even after a reboot.

Also the install guide references a config file (Microsoft.R.Host.Broker.Config.json) where you specify the certificate to be used, this file didn’t exist under the v1.3 file or anywhere on the C drive.

Has anyone managed to get this working?

Why is there a big difference in latch waits for an update against a table variable vs temp table?

Help me to understand latches difference between table variable and temp table.
Example:

SET NOCOUNT ON

DECLARE @t TABLE (id int NOT NULL IDENTITY(1,1) PRIMARY KEY, ss varchar(50))
INSERT INTO @t (ss)
    VALUES ('TestTest'), ('TestTest')

declare @n int = 0

WHILE  @n < 100000
BEGIN
    SET @n += 1

    UPDATE @t
    SET ss = REVERSE(ss)
END

Run it!
And check wait statistic:

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = <spid>

Result:

session_id wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms

951        PAGELATCH_SH                                                 1                    0                    0                    0
951        PAGELATCH_EX                                                 200002               12655                379                  12414
951        SOS_SCHEDULER_YIELD                                          22                   636                  205                  636
951        MEMORY_ALLOCATION_EXT                                        45                   0                    0                    0

It seems that latches were taken 2 times for insert and 200000 times for update.
But when we switch table variable (@t) to temp table (#t) we have no latches at all:

session_id wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms

1108       SOS_SCHEDULER_YIELD                                          910                  83                   4                    82
1108       MEMORY_ALLOCATION_EXT                                        110                  0                    0                    0

I have 2 questions:

  1. Why we have latches in table variable and no one for temp table?
  2. Why latches taken for EVERY row? Why not only once per update (transaction)?

SSIS System.Reflection.TargetInvocationException

I’ve recently built an SSIS package (2008) which consumes an Excel 2007 spreadsheet (.xlsx extension). The contents of the spreadsheet are dumped into a recordset object which is then passed to a Script Task Editor which iterates through each row, formats the data in the appropriate manner and then writes it out to a csv file.

The CSV output goes to the same network location as the Excel file was found in. On my local development environment everything works exactly as it should, but when the package is deployed to the live SQL server and executed from there, we get the following error:

Error: 2018-02-16 12:42:41.60 Code: 0x00000001 Source: Export Recordset To CSV Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: “component “Recordset Destination” (338)” wrote 564 rows. —> System.Runtime.InteropServices.COMException (0xC0010014): “component “Recordset Destination” (338)” wrote 564 rows. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariable100.get_Value() at Microsoft.SqlServer.Dts.Runtime.Variable.get_Value() — End of inner exception stack trace — at Microsoft.SqlServer.Dts.Runtime.Variable.get_Value() at ST_5a1e9be825584201adc4e2b6248e429e.vbproj.ScriptMain.Main() — End of inner exception stack trace — at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:42:39 Finished: 12:42:41 Elapsed: 1.64 seconds. The package execution failed. The step failed.

Initially we thought that the issue was because the package was trying to run the 64-bit version of Excel, but we’ve ruled that out as a cause.

I’m at a complete loss to understand why the package is working without issue on my local machine, but is failing on the remote server, especially since the code that I’m using for this is almost identical to the code in a separate package which works without issue.