ColdFusion not maintaining connection to Azure Data Warehouse

Our ColdFusion 2016 Enterprise server (Windows Server 2012 R2) is not maintaining connections to an Azure Data Warehouse. The first Azure query on a page takes a second or more to run. Subsequent Azure queries on the same page take a fraction of a second, e.g.:

test1 (Datasource=azureDev, **Time=3485ms**, Records=1) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:12.012 select count(*) cnt from dimpatient where name like 'smith%' and birthdate >'2014-02-01'   test2 (Datasource=AzureDev, **Time=125ms**, Records=3) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:12.012 select * from dbo.dimPatientMergeStatus   test3 (Datasource=azureDev, **Time=281ms**, Records=1) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:13.013 select count(*) cnt from dimpatient where name like 'jones%' and birthdate >'2004-02-01'  

It seems apparent that CF is taking extra time to actually make the connection while running the first query on the page. We've tried with various queries and re-arranging their order and always end up with the same result.

We are connecting to Azure using the latest MS jdbc driver (mssql-jdbc-6.2.2.jre8.jar) and 'Maintain Connections' is checked. We first attempted to connect using the built in Microsoft SQL Server driver but kept getting this error:

Connection verification failed for data source: AzureDev2 java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error fetching requested database meta-data info. 

We do not see this issue when we run the queries in SSMS.

Any idea what might be wrong?

1 Answers

Answers 1

How does ColdFusion Server manage database connections when there are client variables?

With ColdFusion Server version 4.5.1 SP1 and higher, when you store your client variables in a database, your code connects to the database only when a variable is set. This prevents unnecessary database connections, for instance, in a case where you are using client management, but no client variables are present in a particular request.

https://helpx.adobe.com/coldfusion/kb/database-connections-handled-coldfusion.html

Read More

Generate backup and restore the SQL Database on LAN systems

I am creating one windows desktop application but I am stucked at a point where I need guidance from SO developers.

My requirements :

My main requirement is to create a windows form application that can copy database of one system to another.

see below snap.

What I tried So Far?

I successfully create backup file and restored it in local system using C# but right now it only works for local system (System A).

What issues I am facing?

I want to achieve this both using C#. I can handle all this using shared folder but that's not recommended.

1. how to access backup generated in another system of LAN because we will never have access to other system's directory. I think I can do it with sharing folder, but is there any other way that I can copy backup file and store it in the system where my application is running.

2. How can I restore backup file to another LAN system. as I know SQL is not giving rights to restore databases from outside local directory.

Is there any other way to achieve this kind of (Copy database) functionality?

Any help related to flow or any reference will be appreciated.

4 Answers

Answers 1

You can create a backup of a DB into a remote location. For example, System B would be creating a backup in a shared folder of System C. Then you can restore the backup on System C. You could also do it the other way round, create a shared folder on System B, create the backup into this folder and restore the backup on System C from the remote location.

Considering the nature of what you want to accomplish, I assume the following:

1. The user account running application on System A has enough priviliges to access both SQL Server instances in System B and System C.
2. If recommendations were followed, then SQL Server Logins were created using Windows Athentication Mode. If this is the case then the application in System A authenticates to both servers with the user account running the application.
3. A shared folder should be created on System C. For the sake of example lets say D:\TEST\ is shared as \\SYSTEM C\TEST\.
4. The user account running the application on System A should be granted enough previliges to access and write to the shared folder from System C

If all previous conditions can be met, then from the Application in System A you can execute the following command on System B:

BACKUP DATABASE [MyDataBase] TO  DISK = N'\\SYSTEM C\TEST\MyDataBase.bak' WITH NOFORMAT, INIT,  NAME = N'MyDataBase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 

Supposing \\SYSTEM C\TEST\ is the remote address for D:\TEST\ (in System C, obviously) then, from application in System A, you can execute the following command on System C:

RESTORE DATABASE [MyDataBaseCopy] FROM  DISK = N'D:\TEST\MyDataBase.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5 

Both commands can be exectuted from SqlCommand instances, one with a connection to System B and the other with a connection to System C.

Hope it helps.

EDIT 1

Thanks to @PMF's comments pointing out that there may be scenarios in which this solution, as is, won't work. In the case you get this kind of error:

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '\\SYSTEM C\TEST\MyDataBase.bak. Operating system error 5(Access is denied.). 

You can try one of the following options given your scenario:

1. If your network is under a Windows Domain and SQL Server's Service Account in System B is a managed service account or a Domain Account, then you can grant write priviliges on the shared folder in System C to SQL Server's Service Account. This is considered best practice.
2. If your network is under a Windows Domain and SQL Server's Service Account in System B is not a managed service account or a Domain Account, then you can set SQL Server's Service Account to be one of these and proceed to option 1. This is considered best practice.
3. In any other case and if you don't mind sharing the folder to everyone then do so granting write access. In this case you will not have problems with this solution no matter what kind of account SQL Server is running on. This is not best practice.

Answers 2

If the account you are running against doesn't have access to the machines, but you have another admin account...you can access using impersonation ( impersonate the higher priviledge acct,) and the admin share (e.g. \servername\c$ to access a machine's c drive. I've used this impersonation class before...call the higher priviledge methods inside it https://www.codeproject.com/Articles/10090/A-small-C-Class-for-impersonating-a-User

Answers 3

Rather than backup and restore, it sounds like you're attempting to replicate a database from one server to another. There is a thorough link on MSDN about how to do this, using multiple options.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f5ee85c7-c9d3-438e-a835-daa2c785026b/copy-sql-database-tables-to-another-sql-server?forum=sqlgetstarted

You might consider building out your transfer process in SSIS (and any other steps you might wish to include such as data cleansing and other preparation), then calling that SSIS package either natively from your app or have the app call a SQL agent task which executes the package.

Answers 4

My first instinct when some says "I want to copy a DB in C#" is: That is with 95% propabiltiy the wrong track. Do it in the DB or OS layer. Trying to pull that off in C# only adds a whole layer of overhead and stuff that can go wrong.

It think what you want in the end is for System C to have a copy of System B. The DB Termn for that is Replication. Particular if you want System C to be a backup DB if System B goes down, the proper replication/cluster approaches are always preferable. https://en.wikipedia.org/wiki/Replication_(computing)#Database_replication

Most commerical DBMS even have build in support for that kind of stuff and you are better off just using that. If you can not use those: Good luck.

Read More

SQL Server: Record size larger than expected

My table consists of 3 columns

| Column Name | Data Type | Size | Value       | real      | 4 | LogId       | int       | 4 | SigId       | smallint  | 2 

One primary key is set for columns LogId, SigId.

The sum of all size's is 4+4+2=10, however using sys.dm_db_index_physical_statsI get, that the average (and min/max) record size in bytes is 25. Can someone explain? Am I comparing apples and oranges?

1 Answers

Answers 1

The physical record length includes row overhead in addition to the space needed for the actual column values. On my SQL Server instance, I get an average record length of 17 reported with the following table:

CREATE TABLE dbo.Example1(       Value real NOT NULL     , LogId int NOT NULL     , SigId smallint NOT NULL     , CONSTRAINT PK_Example1 PRIMARY KEY CLUSTERED(LogId, SigId) ); GO INSERT INTO dbo.Example1 (Value, LogId, SigId) VALUES(1, 2, 3); GO SELECT avg_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example1'),1,0,'DETAILED') WHERE index_level = 0; GO 

The 17 byte record length reported by sys.dm_db_index_physical_stats includes 10 bytes for data, 4 bytes for the record header, 2 bytes for the column count, and 1 byte for the NULL bitmap. See Paul Randal's Anatomy of a record article for details of the record structure.

Below is a script to dump the first clustered index data page using DBCC_PAGE as determined by the undocumented (don't use it in production) sys.dm_db_database_page_allocations table-valued function:

DECLARE       @database_id int = DB_ID()     , @object_id int = OBJECT_ID(N'dbo.Example1')     , @allocated_page_file_id int     , @allocated_page_page_id int; --get first clustered index data page SELECT       @allocated_page_file_id = allocated_page_file_id     , @allocated_page_page_id = allocated_page_page_id FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 1, 1, 'DETAILED') WHERE     page_type_desc = N'DATA_PAGE'     AND previous_page_page_id IS NULL --first page of clustered index; --dump record DBCC TRACEON(3604); DBCC PAGE(@database_id,@allocated_page_file_id,@allocated_page_page_id,1); DBCC TRACEOFF(3604); GO 

Here is an excerpt from the results on my instance with the physical record structure fields called out:

DATA:   Slot 0, Offset 0x60, Length 17, DumpStyle BYTE  Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 17  Memory Dump @0x0000002262C7A060  0000000000000000:   10000e00 02000000 03000000 803f0300 00        .............?...                     |        |        |   |        |    |null bitmap (1 byte)                     |        |        |   |        |column count (2 bytes)                     |        |        |   |Value column data (4-byte real)                     |        |        |SigId column data (2-byte smallint)                     |        |LogId column data (4-byte int)                     |Record header (2-byte record type and 2 byte offset to null bitmap) 

As to why your actual record length is 25 instead of 17 as in this example, the likely cause is schema changes were made after the table was initially created as Martin suggested in his comment. If the database has a row-versioning isolation level enabled, there will be additional overhead as mentioned in Paul's blog post but I doubt that is the reason here since that overhead would be more than 8 bytes.

Read More

Generated KML file from SQL query save to local drive

My SQL Query generates a XML output:

         select 'TEST.kml' as name,                  (select 'TEST' as name, (                  select (                         select top 10 issue as name,                          null as description,                          null as 'Point/coordinates',                          (                               select                                          null as altitudeMode,                                         Coordinates as 'coordinates'                               for xml path('Polygon'), type)                  from Mapping for xml path('Placemark'), type))                      for xml path ('Line') , type)                  for xml path ('Doc'), root('kml')) 

I want to save the output of the query as .XML file on to local drive.Please advise.

3 Answers

Answers 1

Not the most elegant way but it is possible to use bulk copy program and xp_cmdshell to do this. Few things first, xp_cmdshell is blocked by default by SQL Server as part of the security configuration so you will need to enable that first and BCP requires you to have access to the directory that you want to create the file.

To enable xp_cmdshell you'll need run sp_configure and RECONFIGURE, use this:

EXEC sp_configure'xp_cmdshell', 1 RECONFIGURE GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO 

Then you can run the following:

EXEC xp_cmdshell 'bcp "SELECT * FROM [Database].dbo.[Table] FOR XML AUTO, ELEMENTS" queryout "C:\test.xml" -c -T' 

Just add your query into it and make sure you add [] around your table names.

The Microsoft Documents for xp_cmdshell are here and bcp can be found here

Answers 2

Using bcp is definite choice especially when working with large data sets. Alternatively, you can try using SQL Management Studio - Export Data.

1. Open the interface - Right Click on database name, then Tasks, then Export Data

2. The menu is opened. Click Next

   

3. Then choose SQL Server Native Client, sql server, database name and authentication method:

   

4. Then where to save the data:

   

5. Then how we are getting the data (in your case SQL query):

   

6. Past the query:

   

7. Then we have some settings, click finish.

   

Answers 3

To save the results of a remote query to a local file, you could use a Powershell script like this example:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI") $command = New-Object System.Data.SqlClient.SqlCommand(@("     select 'TEST.kml' as name,                  (select 'TEST' as name, (                  select (                         select top 10 issue as name,                          null as description,                          null as 'Point/coordinates',                          (                               select                                          null as altitudeMode,                                         Coordinates as 'coordinates'                               for xml path('Polygon'), type)                  from Mapping for xml path('Placemark'), type))                      for xml path ('Line') , type)                  for xml path ('Doc'), root('kml');"), $connection); $connection.Open(); $command.ExecuteScalar() | Out-File -FilePath "C:\KmlFiles\YourFile.kml"; $connection.Close(); 

Read More

Using ExecuteNonQueryAsync and Reporting Progress

I thought I was trying to do something very simple. I just want to report a running number on the screen so the user gets the idea that the SQL Stored Procedure that I'm executing is working and that they don't get impatient and start clicking buttons.

The problem is that I can't figure out how to actually call the progress reporter for the ExecutNonQueryAsync command. It gets stuck in my reporting loop and never executes the command but, if I put it after the async command, it will get executed and result will never not equal zero.

Any thoughts, comments, ideas would be appreciated. Thank you so much!

        int i = 0;         lblProcessing.Text = "Transactions " + i.ToString();         int result = 0;         while (result==0)         {             i++;             if (i % 500 == 0)             {                 lblProcessing.Text = "Transactions " + i.ToString();                 lblProcessing.Refresh();             }          }         //  Yes - I know - the code never gets here - that is the problem!          result = await cmd.ExecuteNonQueryAsync(); 

5 Answers

Answers 1

Do you just want to let the user know that something is happening, and you don't actually need to display current progress?

If so, you could just display a ProgressBar with its Style set to Marquee.

If you want this to be a "self-contained" method, you could display the progress bar on a modal form, and include the form code in the method itself.

E.g.

public void ExecuteNonQueryWithProgress(SqlCommand cmd) {     Form f = new Form() {         Text = "Please wait...",         Size = new Size(400, 100),         StartPosition = FormStartPosition.CenterScreen,         FormBorderStyle = FormBorderStyle.FixedDialog,         MaximizeBox = false,         ControlBox = false     };     f.Controls.Add(new ProgressBar() {          Style = ProgressBarStyle.Marquee,         Dock = DockStyle.Fill     });     f.Shown += async (sender, e) => {         await cmd.ExecuteNonQueryAsync();         f.Close();     };     f.ShowDialog(); } 

Answers 2

You're not going to be able to get ExecuteNonQueryAsync to do what you want here. To do what you're looking for, the result of the method would have to be either row by row or in chunks incremented during the SQL call, but that's not how submitting a query batch to SQL Server works or really how you would want it to work from an overhead perspective. You hand a SQL statement to the server and after it is finished processing the statement, it returns the total number of rows affected by the statement.

Answers 3

The simplest way to do this is to use a second connection to monitor the progress, and report on it. Here's a little sample to get you started:

using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Text; using System.Threading.Tasks;  namespace Microsoft.Samples.SqlServer {     public class SessionStats     {         public long Reads { get; set; }         public long Writes { get; set; }         public long CpuTime { get; set; }         public long RowCount { get; set; }         public long WaitTime { get; set; }         public string LastWaitType { get; set; }         public string Status { get; set; }          public override string ToString()         {             return $"Reads {Reads}, Writes {Writes}, CPU {CpuTime}, RowCount {RowCount}, WaitTime {WaitTime}, LastWaitType {LastWaitType}, Status {Status}";         }     }     public class SqlCommandWithProgress     {           public static async Task ExecuteNonQuery(string ConnectionString, string Query, Action<SessionStats> OnProgress)         {             using (var rdr = await ExecuteReader(ConnectionString, Query, OnProgress))             {                 rdr.Dispose();             }         }          public static async Task<DataTable> ExecuteDataTable(string ConnectionString, string Query, Action<SessionStats> OnProgress)         {             using (var rdr = await ExecuteReader(ConnectionString, Query, OnProgress))             {                 var dt = new DataTable();                  dt.Load(rdr);                 return dt;             }         }           public static async Task<SqlDataReader> ExecuteReader(string ConnectionString, string Query, Action<SessionStats> OnProgress)         {             var mainCon = new SqlConnection(ConnectionString);             using (var monitorCon = new SqlConnection(ConnectionString))             {                 mainCon.Open();                 monitorCon.Open();                    var cmd = new SqlCommand("select @@spid session_id", mainCon);                 var spid = Convert.ToInt32(cmd.ExecuteScalar());                  cmd = new SqlCommand(Query, mainCon);                  var monitorQuery = @" select s.reads, s.writes, r.cpu_time, s.row_count, r.wait_time, r.last_wait_type, r.status from sys.dm_exec_requests r join sys.dm_exec_sessions s    on r.session_id = s.session_id where r.session_id = @session_id";                  var monitorCmd = new SqlCommand(monitorQuery, monitorCon);                 monitorCmd.Parameters.Add(new SqlParameter("@session_id", spid));                  var queryTask = cmd.ExecuteReaderAsync( CommandBehavior.CloseConnection );                  var cols = new { reads = 0, writes = 1, cpu_time =2,row_count = 3, wait_time = 4, last_wait_type = 5, status = 6 };                 while (!queryTask.IsCompleted)                 {                     var firstTask = await Task.WhenAny(queryTask, Task.Delay(1000));                     if (firstTask == queryTask)                     {                         break;                     }                     using (var rdr = await monitorCmd.ExecuteReaderAsync())                     {                         await rdr.ReadAsync();                         var result = new SessionStats()                         {                             Reads = Convert.ToInt64(rdr[cols.reads]),                             Writes = Convert.ToInt64(rdr[cols.writes]),                             RowCount = Convert.ToInt64(rdr[cols.row_count]),                             CpuTime = Convert.ToInt64(rdr[cols.cpu_time]),                             WaitTime = Convert.ToInt64(rdr[cols.wait_time]),                             LastWaitType = Convert.ToString(rdr[cols.last_wait_type]),                             Status = Convert.ToString(rdr[cols.status]),                         };                         OnProgress(result);                      }                  }                 return queryTask.Result;               }         }     } } 

Which you would call something like this:

    class Program     {          static void Main(string[] args)         {             Run().Wait();          }         static async Task Run()         {             var constr = "server=localhost;database=tempdb;integrated security=true";             var sql = @" set nocount on; select newid() d into #foo from sys.objects, sys.objects o2, sys.columns  order by newid(); select count(*) from #foo; ";              using (var rdr = await SqlCommandWithProgress.ExecuteReader(constr, sql, s => Console.WriteLine(s)))             {                 if (!rdr.IsClosed)                 {                     while (rdr.Read())                     {                         Console.WriteLine("Row read");                     }                 }             }             Console.WriteLine("Hit any key to exit.");             Console.ReadKey();           }     } 

Which outputs:

Reads 0, Writes 0, CPU 1061, RowCount 0, WaitTime 0, LastWaitType SOS_SCHEDULER_YIELD, Status running Reads 0, Writes 0, CPU 2096, RowCount 0, WaitTime 0, LastWaitType SOS_SCHEDULER_YIELD, Status running Reads 0, Writes 0, CPU 4553, RowCount 11043136, WaitTime 198, LastWaitType CXPACKET, Status suspended Row read Hit any key to exit. 

Answers 4

That is an interesting question. I have had to implement similar things in the past. In our case the priority was to:

• Keep client side responsive in case the user doesn't want to stick around and wait.
• Update the user of action and progress.

What I would do is use threading to run the process in the background like:

HostingEnvironment.QueueBackgroundWorkItem(ct => FunctionThatCallsSQLandTakesTime(p, q, s)); 

Then using a way to estimate work time I would increment a progress bar from client side on a clock. For this, query your data for a variable that gives you a linear relationship to the work time needed by FunctionThatCallsSQLandTakesTime.

For example; the number of active users this month drives the time FunctionThatCallsSQLandTakesTime takes. For each 10000 user it takes 5 minutes. So you can update your progress bar accordingly.

Answers 5

I'm wondering if this might be a reasonable approach:

    IAsyncResult result = cmd2.BeginExecuteNonQuery();     int count = 0;     while (!result.IsCompleted)     {          count++;          if (count % 500 == 0)          {             lblProcessing.Text = "Transactions " + i.ToString();             lblProcessing.Refresh();          }          // Wait for 1/10 second, so the counter          // does not consume all available resources           // on the main thread.          System.Threading.Thread.Sleep(100);     } 

Read More

Business Hours Logic and exceptions to Opening Hours

I Have created a date dimension from using the following:

https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

This is just a standard one and I have added 2 columns for OpeningTime and Closing Time with the value of Opening being 08:00:00 and Closing being 18:00:00.

I also have a computed field that works out the difference in minutes between the start time and end time as a non persistent computed field.

I have the following Logic below, but before give you this, let me set the scene of showing the context of the situation of the business, so they are normally open from 8am to 6pm Mon - Fri and also Sat at 8am - 1pm

However there can be exception to the rules as they can open for longer or even on sundays. The Code i have below for some reason falls over by showing less on a sunday given that on rare occasions if they were to be open on a sunday for argument sake 8am to 1pm. Obviously for whatever extending opening hours they are open for even on a Sunday, I would manually have to add it this to the Dim_Date calender which for starttime and enddate I have 00:00:00 for both giving me a 0 for datediff in min by default unless the business tell me otherwise.

But the following Code does NOT take that into account and calculates less than what it should be, something wrong with the code if someone can please provide a solution, basically I just want the code to be flexible so I only make changes to the calender even for extended hours or unusual business hours and the code to reflect that. Thank you.

declare @Date1 datetime = '2017-08-01 08:00:00' declare @Date2 datetime = '2017-08-07 09:10:00'   declare @StartTime time = cast(@Date1 as time) declare @EndTime time = cast(@Date2 as time) declare @CCStartTime time = (select StartTime from dim_date where id = convert(nvarchar(8),@Date1,112)) declare @CCEndTime time = (select EndTime from dim_date where id = convert(nvarchar(8),@Date2,112))   declare @ActualStart time = (select case              when datename(DW,@Date1)='Sunday' then '00:00:00'             when @StartTime between '00:00:00' and @CCStartTime then @CCStartTime             when @StartTime between  @CCStartTime and @CCEndTime then @StartTime             when @StartTime between @CCEndTime and  '23:59:59' then @CCEndTime end)  declare @ActualEnd time = (select case              when datename(DW,@Date2)='Sunday' then '00:00:00'             when @EndTime between '00:00:00' and @CCStartTime then @CCStartTime             when @EndTime between @CCStartTime and @CCEndTime then @EndTime             when @EndTime between @CCEndTime and '23:59:59' then @CCEndTime end)   declare @DiffrenceStart int = isnull(DATEDIFF(minute,@CCStartTime,@ActualStart),0)  declare @DiffrenceEnd int = isnull(DATEDIFF(minute,@ActualEnd,@CCEndTime),0)  /* select @StartTime as StartDate select @EndTime as EndDate select @CCStartTime as CCStartDate select @CCEndTime as CCEndDate select @ActualStart as ActualStart select @ActualEnd as ActualEnd select abs(@DiffrenceStart) as DiffrenceStart  select abs(@DiffrenceEnd) as DifrenceEnd */    select sum(Min)- (@DiffrenceStart + @DiffrenceEnd) from dim_date where id between convert(nvarchar(8),@Date1,112) and convert(nvarchar(8),@Date2,112) 

1 Answers

Answers 1

Look at this:

declare @ActualEnd time = (select case              when datename(DW,@Date2)='Sunday' then '00:00:00' 

souldn't it be:

declare @ActualEnd time = (select case              when datename(DW,@Date2)='Sunday' then '23:59:59' 

Read More

Managing and Closing Dynamically created SQL connections in .net

I have a c# windows form application that connects to databases dynamically where each user may connect to different databases.

The current implementation is as follows:

Connection Repository that contains a dynamically populated list of connections (per user).

When a user initiates a request that requires a database connection the respective connection is looked up from the connection repository ,opened , and then used in the user request .

Code Sample from the connection repository

public class RepoItem {     public string databasename;     public SqlConnection sqlcnn; }  public class ConnectionRepository {     private List<RepoItem> connectionrepositroylist;      public SqlConnection getConnection(String dbname)     {         SqlConnection cnn = (from n in connectionrepositroylist                              where n.databasename == dbname                              select n.sqlcnn).Single;          cnn.Open();         return cnn;     } } 

sorry for any code errors i just improvised a small version of the implementation for demonstration purpose.

I'am not closing connections after a command execution because it may be used by another command simultaneously.

The questions are:

• Should i be worried about closing the connections ?

• Does connection close automatically if it is idle for a specific period ?

I have a method in mind to implement a timer in the created Connection Repository and check for idle connections through the Executing ConnectionState Enumeration and close them manually. Any suggestions are welcome .

When i want a specific connection i call the getConnection function in the ConnectionRepository class and pass the database name as a parameter.

PS: I didn't post the complete implemented code because it is quite big and includes the preferences that affect the populating of the connection list.

4 Answers

Answers 1

I would suggest not to return the SQLConnection to the calling method at all. Instead, create a method that will accept an Action<SqlConnection>, create the connection inside a using block, and execute the action inside that block

This way you know that the connection will always be correctly closed and disposed, while giving the using code the freedom to do whatever it needs:

public class RepoItem {     public string databasename;     public SqlConnection sqlcnn; }  public class DatabaseConnector {     private List<RepoItem> connectionrepositroylist;      private SqlConnection GetConnection(String dbname)     {         return (from n in connectionrepositroylist                 where n.databasename == dbname                 select n.sqlcnn).SingleOrDefault();     }      public void Execute(String dbname, Action<SqlConnection> action)     {         using (var cnn = GetConnection(dbname))         {             if (cnn != null) // in case dbname is not in the list...             {                 cnn.Open();                 action(cnn);             }         }      } } 

Then, to execute an sql statement you can do something like this:

public void ExecuteReaderExample(string dbName, string sql) {     Execute("dbName",     connection =>     {         using (var cmd = new SqlCommand(sql, connection))         {             using (var reader = cmd.ExecuteReader())             {                 while (reader.Read())                 {                     // do stuff with data form the database                 }             }         }     }); } 

Of course, you can also wrap the SqlCommand in a method like this. I've been working with this approach for quite some time now, and as far as I can tell it's working well. In fact, It's working so well I've published a project on git hub based on this approach.
It saves you a lot of the plumbing when dealing with ado.net, by wrapping the connection, command, reader and adapter much the same way. Feel free to download it and adapt to your needs.

P.S. To answer your questions directly:

Should i be worried about closing the connections ?

Yes, you should.

Does connection close automatically if it is idle for a specific period ?

No, it doesn't.

However, implementing a method like I suggested will handle closing and disposing the connection object for you, so you don't need to worry about it.

Update

As Yahfoufi wrote in his comment, this design has a flaw, since multiple commands are using the same instance of SqlConnection, you are risking closing the connection while other commands are running. However, fixing this design flaw is very easy - instead of holding SqlConnection in RepoItem you can simply hold the connection string:

public class RepoItem {     public string DatabaseName {get; set;}     public string ConnectionString {get; set;} } 

Then you change the GetConnection method like this:

    private SqlConnection GetConnection(String dbname)     {         return new SqlConnection(from n in connectionrepositroylist                 where n.databasename == dbname                 select n.sqlcnn).SingleOrDefault());     } 

Now each Execute method is working on it's own individual instance of SqlConnection so you don't need to worry about closing in the middle of some other command executing.

However, While we are on the subject of refactoring, I would suggest removing the RepoItem class all together and instead of using a List<RepoItem> to hold the connection strings simply use a Dictionary<string, string>, where the database name is the key and the connection string is the value. This way you can only have one connection string per database name, and your GetConnection method is simplified to this:

private Dictionary<string, string> connectionrepositroylist;      private SqlConnection GetConnection(String dbname)     {         SqlConnection con = null;         if(connectionrepositroylist.ContainsKey(dbname))         {             con = new SqlConnection(connectionrepositroylist[dbname]);         }         return con;     } 

So, the complete DatabaseConnector class will look like this:

public class DatabaseConnector {     private Dictionary<string, string> connectionrepositroylist;      private SqlConnection GetConnection(String dbname)     {         SqlConnection con = null;         if(connectionrepositroylist.ContainsKey(dbname))         {             con = new SqlConnection(connectionrepositroylist[dbname]);         }         return con;     }      public void Execute(String dbname, Action<SqlConnection> action)     {         using (var cnn = GetConnection(dbname))         {             if (cnn != null) // in case dbname is not in the list...             {                 cnn.Open();                 action(cnn);             }         }      }      // Of course, You will need a way to populate your dictionary -      // I suggest having a couple of methods like this to add, update and remove items.     public bool AddOrUpdateDataBaseName(string dbname, string connectionString)     {         if(connectionrepositroylist.ContainsKey(dbname))         {             connectionrepositroylist[dbname] = connectionString;         }         else         {             connectionrepositroylist.Add(dbname, connectionString);         }     } } 

Answers 2

The good news is that ADO.Net manages your connection pools dynamically, so there's minimal overhead in you dynamically opening and closing connections in code. There's a good document here if you want to look through the detail.

To answer the specific questions you've raised:

Should i be worried about closing the connections ?

Yes, but not for the reasons you may think. Microsoft encourage you to close your connections, so as to return them to the pool for (re)use elsewhere in your code. Closing the connection doesn't actually close it - it merely returns the underlying connection to the pool. Failure to close your connections properly can lead to delays in them being returned to the pool, thus adversely affecting your applications performance as more connections need to be added to the pool to cope with demand.

Does connection close automatically if it is idle for a specific period ?

A connection is only returned to the pool when it's Dispose or Finalise methods get called. If you create a connection and drop it into a static container then it will not be returned to the pool at all. As such, your ConnectionRepository may actually be harming performance.

I have a method in mind to implement a timer in the created Connection Repository and check for idle connections

This is unnecessary - close your connections to allow them to return to the pool. This way they will be available for other threads to use

Personally, I'd suggest that you modify your RepoItem class to store connection strings, rather than connection objects, and let ADO.Net's pooling do all the heavy lifting.

public static class ConnectionRepository {     private static readonly Dictionary<string, string> Connections = new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase);      public static bool Contains(string key)     {         return Connections.ContainsKey(key);     }      public static void Add(string key, string connectionString)     {         Connections.Add(key, connectionString);     }      public static SqlConnection Get(string key)     {         var con = new SqlConnection(Connections[key]);         con.Open();         return con;     } } 

With this in place, you can query the database as follows:

public static void foo() {     using (var con = ConnectionRepository.Get("MyConnection"))     using (var cmd = new SqlCommand("SELECT * FROM MyTable", con))     {         var dr = cmd.ExecuteReader();         //...     } } 

Once the query has executed and the connection is no longer required, the using() block calls its Dispose() method and releases the underlying connection back to the pool for re-use.

Answers 3

As @tinudu says, the SqlConnection class reuses existing connections automatically - you don't have to implement that yourself. See SQL Server Connection Pooling.

If you create the SqlConnection object in a using statement, C# will close the connection automatically as required.

Wrapping the whole thing (create connection, open, run query, close connection) in a function is the best idea. You can put the function in a repository base-class, so it is available to all your repositories.

You would need several functions for the different types of SQL query (select, update, stored proc) but you only need to write one of each - they will get reused.

Answers 4

if you worried about so many conditions let say parallel execution as well so consider reserving connections for app and close all at once when app is closing.

public class RepoItem {     public string databasename;     public SqlConnection sqlcnn; }  public class ConnectionRepository {     private List<RepoItem> connectionrepositroylist;      public SqlConnection getConnection(String dbname)     {         SqlConnection cnn = (from n in connectionrepositroylist                              where n.databasename == dbname                              select n.sqlcnn).Single;         if (cnn!= null && cnn.State == cnn.Closed) // Impelement other checks as well         {          cnn.Open();         }          return cnn;     } } 

Implement CloseConnections and call while application closing i.e

Application.ApplicationExit event

 public void CloseConnections()     {         foreach (var connection in connectionrepositroylist)         {             try             {                 if (connection.State == System.Data.ConnectionState.Open) // check other conditions                 {                     connection.Close();                 }             }             catch (Exception)             {                 //logging or special handling             }         }      } 

Points to be note If some query is still executing and user tries to shut down or close the app can consider following implementations

1. Wont allow the application shutdown . Callback delegate will help in this case to ensure that query is returned.

2. Force stop and close the connection

Read More

SSIS writing window username instead of service account while logging

I have implemented a SSIS 2008 package using BIDS. My package currently logs exceptions and information to the database. I am running the package via a SQL Job. I have configured the job to run using a service account. The problem that I am facing is that when the logs are written, I am seeing my windows credentials written to the username column instead of service account credentials. I am running the job via SSMS using my windows credentials. I have been told that Jobs would run via any DBA guy and they may not log in using Service account.

Following are the steps I followed for the job to run as service account.

1. Created a credential with the service account as identity.
2. Created a proxy associating with the credential.
3. Configured the job to use the proxy account.

Below is the connection string from my package. I had removed Integrated Security=SSPI; and provided the userid and password of the service account but got an error message saying incorrect login.

<DTS:Property DTS:Name="ConnectionString">Data Source=xxxxxxxxx;User ID=;Initial Catalog=xxxxxx;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=xxxxxx-{452DA0BD-2ACF-4780-9DB0-5A64ABB406A1}xxxxxx\xxxxxx.xxxxxxx;Auto Translate=False;</DTS:Property> 

Another thing that I have noticed is the creator name in the package is having my windows username. I tried updating that but no luck.

The errors are logged using event handlers

Event handler

The connection string in the dtscConfig file

Data Source=xxx\xxx;Initial Catalog=xxx;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=xxx\xxxxxxx;Auto Translate=False; 

2 Answers

Answers 1

I have fixed the issue. I am passing system:username in parameter to the stored procedure which has solved the issue

Answers 2

SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

• SQLAgentUserRole

• SQLAgentReaderRole

• SQLAgentOperatorRole

You can stored username in parameter.

More details

Read More

SSIS Script Component Cannot Load Assembly Until Manually Opened

I am creating a SSIS project via Biml (Using the current version of BimlExpress per the Varigence BimlExpress page) that uses a script component within a Data Flow Task. The project is created without issue however errors when it gets to the Script Component:

Could not load file or assembly 'Microsoft.SqlServer.DTSPipelineWrap, Version=14.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

at ScriptMain.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

I can see this dll within the GAC and when I try to add the reference manually, SSIS states that I can't add it as it is already included in the project.

However, if I open the Script Component and manually Build it - but change nothing else - the Data Flow Task will run through without issue.

To clarify, I know I have changed nothing else as I am recreating this project from the Biml each time I want to test a new approach, which provides a consistent baseline.

---

Does anyone know why a Script Component that includes a reference to a GAC dll, will only work when manually Built but not beforehand?

I have used this exact Biml before without issue, though on a different environment. Worked using Visual Studio 2012 against a SQL Server 2016 instance yet doesn't here within Visual Studio 2015 (targeting 2012) against a SQL Server 2016 instance.

For further context I have also tested this outputting the SSIS Project targeted at SQL Server 2012, 2014 and 2016 with the exact same error message.

---

Update

This appears to definitely be a dll version problem in the project as output from BimlExpress, in that if I manually create the v4.0_14.100.0.0__89845dcd8080cc91 folder with the version 13 dll inside at C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap the script component now errors out with the following:

System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{ID removed just in case}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

1 Answers

Answers 1

I guess your problem may be related to the Express version.

See at the related page the differences between Express and BimlStudio versions (Feature Comparison Chart under "Compare with BimlStudio" button):

https://www.varigence.com/BimlExpress

Read More

No qualifying bean of type 'org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder' available

In Java project, I am using Sprig Boot 1.5.3.RELEASE. It is connecting with two databases i.e. MongoDB and Microsoft SQLServer. When I run it with spring-boot:run goal, it works fine. However, when I try to run it with package goal then below error is reported by test cases despite the fact that those test cases are not connecting to SQL Server database:

Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {}     at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoMatchingBeanFound(DefaultListableBeanFactory.java:1486)     at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1104)     at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066)     at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:835)     at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741)     at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:467)     .....     ..... 

MediationTest.java (Java class containing test cases generating above error)

@RunWith(SpringRunner.class) @DataMongoTest(excludeAutoConfiguration = EmbeddedMongoAutoConfiguration.class) @SpringBootTest(classes = { Application.class }) public class MediationTest {      @Autowired     private SwiftFormat swiftFormat;     ......................     ...................... 

MsqlDbConfig.java

@Configuration @EnableTransactionManagement @EnableJpaRepositories(entityManagerFactoryRef = "msqlEntityManagerFactory", transactionManagerRef = "msqlTransactionManager", basePackages = { "com.msql.data" }) public class MsqlDbConfig {      @Bean(name = "msqlDataSource")     @ConfigurationProperties(prefix = "msql.datasource")     public DataSource dataSource() {         return DataSourceBuilder.create().build();     }      @Bean(name = "msqlEntityManagerFactory")     public LocalContainerEntityManagerFactoryBean msqlEntityManagerFactory(             EntityManagerFactoryBuilder builder,             @Qualifier("msqlDataSource") DataSource dataSource) {         return builder.dataSource(dataSource)                 .packages("com.utils.msql.info")                 .persistenceUnit("msql").build();     }      @Bean(name = "msqlTransactionManager")     public PlatformTransactionManager msqlTransactionManager(             @Qualifier("msqlEntityManagerFactory") EntityManagerFactory msqlEntityManagerFactory) {         return new JpaTransactionManager(msqlEntityManagerFactory);     } } 

application.properties

spring.data.mongodb.uri=mongodb://dev-abc-123:27017/db  msql.datasource.url=jdbc:sqlserver://ABC-SQL14-WXX;databaseName=dev msql.datasource.username=dev msql.datasource.password=***** msql.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver msql.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy spring.jpa.show-sql=true 

1 Answers

Answers 1

The spring-boot:run goal is defined by the Mojo included within the spring-boot-maven-plugin project. You can find it here. https://github.com/spring-projects/spring-boot/blob/8e3baf3130220a331d540cb07e1aca263b721b38/spring-boot-tools/spring-boot-maven-plugin/src/main/java/org/springframework/boot/maven/RunMojo.java.

The requiresDependencyResolution scope is set to Test. This will include the dependencies from each phase on the classpath. Take a look at the specification here. https://maven.apache.org/developers/mojo-api-specification.html

The package goal provided by Maven wouldn't include these additional dependencies on the classpath and I believe that is the cause of your issues.

Spring Boot provides a repackage goal which is what should be used for building out executable spring-boot applications.

However, to get more to the point. I think if you update your test to exclude an additional class it might fix your problem.

@DataMongoTest(excludeAutoConfiguration = {EmbeddedMongoAutoConfiguration.class, HibernateJpaAutoConfiguration.class})

Read More

SQL Server error after update: The token supplied to the function is invalid

I was using my machine (and SSMS) and everything was working fine. I did a reboot whereupon Windows 10 applied the following updates:

Windows Malicious Software Removal Tool for Windows 8, 8.1, 10 and Windows Server 2012, 2012 R2, 2016 x64 Edition - April 2017 (KB890830) Security Update for Adobe Flash Player for Windows 10 Version 1607 (for x64-based Systems) (KB4018483) Security Update for Microsoft Silverlight (KB4017094) Cumulative Update for Windows 10 Version 1607 for x64-based Systems (KB4015217) 

Now when I try to login in SSMS I'm getting the following error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The token supplied to the function is invalid (Microsoft SQL Server)

The token supplied to the function is invalid

This looks like it is security related, but I don't know what to do to fix it. My machine uses Windows Server Essentials for backups (and the connector is green).

2 Answers

Answers 1

I had faced the same issue with SQL Server. I followed some steps and my problem was resolved. Those steps are as -

1. Click Start, click Run, type regedit in the Open box, and then click OK.

2. Locate and then click the following subkey in the registry: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SecurityProviders\SCHANNEL

3. On the Edit menu, point to New, and then click DWORD Value.

4. Type SendExtraRecord for the name of the DWORD value, and then press Enter.
5. Right-click SendExtraRecord, and then click Modify.
6. In the Value data box, type 2 to disable the split record in schannel, and then click OK.
7. Exit Registry Editor.

Answers 2

Execute in command line with administrative privilege and reboot.

netsh winsock reset 

Read More

Hibernate with sqlserver deadlock issue

Following are the two tables with its entity classes.

tbl_rules

| rule_id | rule_name |

    @Entity     @Table(name = "db_user_name.tbl_rules")     public class Rule implements Serializable {         private static final long serialVersionUID = 1L;          @Id         @Basic(optional = false)         @GenericGenerator(name = "incre", strategy = "increment")         @GeneratedValue(generator = "incre")         @Column(name = "rule_id", unique = true, nullable = false)         private long ruleId;          @Column(name = "rule_name", length = 250)         private String ruleName;          @OneToMany(fetch = FetchType.LAZY, mappedBy = "rules")         private Set<Benchmark> benchmarks = new HashSet<Benchmark>(0);         ... getters and setters     } 

tbl_benchmark

| benchmark_id | rule_id |

@Entity @Table(name = "tbl_benchmark", catalog = "db_user_name") @DynamicUpdate(value = true) public class Benchmark implements Serializable {     private static final long serialVersionUID = 1L;      @Id     @Basic(optional = false)     @Column(name = "benchmark_id", unique = true, nullable = false)     private Long benchmarkId;      @ManyToOne(fetch = FetchType.LAZY)     @JoinColumn(name = "rule_id", nullable = false)     private Rule rules;     .. getter and setters } 

Facing deadlock issue with sql server db in the following case

• HibernateSessionManager.beginTransaction();
• call saveRule(rule) // in the backend rule and benchmark both tables are locked (using sql server locked tables query)
• call saveBenchmark(benchmark) // deadlock at this method
• HibernateSessionManager.commit();

Code where deadlock is happened:

HibernateSessionManager.beginTransaction();             UserIdManager.setCurrentGroupId(2);             if (savingObjects.get(AmlConstants.USERCREDENTAILS_STRING) != null){                     userCredentials = (UserCredentials) savingObjects.get(AmlConstants.USERCREDENTAILS_STRING);                     Util.setAuditLogField(AmlConstants.USERIDSTRING);                     this.getAmlDAOFactory().getUserCredentialsDAO().updateUserDetails(userCredentials);                 if (savingObjects.get(AmlConstants.USERBRANCHMAPPING_STRING) != null){                     userBranchMapping = (UserBranchMapping) savingObjects.get(AmlConstants.USERBRANCHMAPPING_STRING);                       Util.setAuditLogField(AmlConstants.BRANCH_STRING);                       this.getAmlDAOFactory().getUserBranchMappingDAO().saveUserBranchMapping(userBranchMapping);                 }             }             HibernateSessionManager.commit(); 

saveRule:

@Override     public Rule saveRule(Rule rule) throws Exception {         try {             getSession().saveOrUpdate(rule);             getSession().flush();         } catch (RuntimeException e) {             e.printStackTrace();             throw e;         }         return rule;     } 

saveBenchmark:

@Override     public Benchmark saveBenchMark(Benchmark benchmark) throws Exception {         try {             if (benchmark.getBenchmarkId() == null)                 benchmark.setBenchmarkId(getBenchmarkCount() + 1);             getSession().clear();             getSession().saveOrUpdate(benchmark);             getSession().flush();         } catch (RuntimeException e) {             // logger.error("Runtime error while saving benchmark", e);             e.printStackTrace();         } catch (Exception e) {             logger.error("Exception while saving benchmark " + e.getMessage(), e);         }         return benchmark;     } 

Spring-Hib confg file:

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"  destroy-method="close">             <property name="driverClassName" value="${jdbc.driverClassName}" />     ..      <property name="hibernateProperties">                 <props>                    <prop key="hibernate.dialect">com.aml.hibernate.SQLServerCustomeDialect</prop>                    <prop key="hibernate.character_encoding">UTF-8</prop>                    <prop key="hibernate.connection.useUnicode">true</prop>                     <prop key="hibernate.show_sql">true</prop>                     <prop key="hibernate.generate_statistics">false</prop>                 </props>             </property>     .. 

HibernateSessionManager.java

public class HibernateSessionManager {      public  static Logger logger = Logger.getLogger(HibernateSessionManager.class);     public static final ThreadLocal<Session> currentSession = new ThreadLocal<Session>();     public static final ThreadLocal<java.util.List<Session>> sessionList = new ThreadLocal<java.util.List<Session>>();      /** Store transaction object on thread local      * this helps to make a request processing transactional */     public static final ThreadLocal<Transaction> transaction = new ThreadLocal<Transaction>();     public static final ThreadLocal<Map<String, Transaction>> transactionMap = new ThreadLocal<Map<String, Transaction>>();      /** keep the beginner method path which helps to commit on the same method only       * we are not supposed to make nested commits under a single request */     public static final ThreadLocal<String> callerXPath = new ThreadLocal<String>();        /**      * Returns existing hibernate session binded with current request thread,       * if no session already bind with current thread then it will open a new session, bind to current thread       * and returns the session object      *       * @param sessionFactory      * @return      * @throws HibernateException      */     public static Session currentSession(SessionFactory sessionFactory) throws HibernateException {          Session s = (Session) currentSession.get();         // Open a new Session, if this Thread has none yet         if (s == null || !s.isOpen())         {             s = sessionFactory.openSession();             currentSession.set(s);             if(sessionList.get()==null)                 sessionList.set(new LinkedList<Session>());             sessionList.get().add(s);             logger.debug("Opened new session:"+currentSession.get().hashCode());         }else{             logger.debug("returning existing session:"+currentSession.get().hashCode());         }         return s;     }       /**      * Closes all the sessions binded with current request thread      * @throws HibernateException      */     public static void closeSession() throws HibernateException {         currentSession.set(null);         transaction.set(null);         callerXPath.set(null);          try{             if(sessionList.get()!=null)                 for (int i = 0; i < sessionList.get().size(); i++) {                     Session s = sessionList.get().get(i);                     try{                         if (s != null && s.isOpen())                             s.close();                         logger.debug("Closed session - session local:"+ (s!=null?s.hashCode(): ""));                     }catch (Exception e) { logger.debug("Error while closing session: ", e); }                 }             transactionMap.get().clear();         }catch (Exception e) { logger.debug("Error while closing session: ", e); }         sessionList.set(null);         transactionMap.set(null);     }         // ------------------- Transaction management ------------------     /**      * Starts a new hibernate transaction on the session binded to current request thread      * if there is already a transaction started on this thread, ignores creation of another transaction      * all the db calls on a single request thread has to come under a single transaction      * @return      */     public static boolean beginTransaction(){         try{             logger.debug("beginTransaction............... ");              Transaction t = transaction.get();             if(t == null && callerXPath.get()==null){                 Session s = currentSession.get();                 t = s.beginTransaction();                 t.registerSynchronization(new Synchronization() {                      @Override                     public void beforeCompletion() {                         logger.debug("Transaction-beforeCompletion............... ");                      }                      @Override                     public void afterCompletion(int status) {                         logger.debug("Transaction-afterCompletion............... "+status);                      }                 });                 transaction.set(t);                 callerXPath.set(getCallerMethodInvolvedinTransaction());                  if(transactionMap.get()==null)                     transactionMap.set(new HashMap<String, Transaction>());                  transactionMap.get().put(callerXPath.get(), t);                 logger.debug("Started new hibernate transaction:"+t);             }         }catch (Exception e) {             logger.error("Error while starting new transaction: ", e);             return false;         }         return true;     }       /**      * if we already have a hibernate transaction created on the current request thread and some thing is committed on it      * it will rollback the changes done after the transaction initialization      */     public static void rollback(){         try{             Transaction t = transactionMap.get().get(callerXPath.get());             if(t != null){                 t.rollback();                 logger.debug("Roll back success on transaction:"+t);             }         }catch (Exception e) {             logger.error("Exception while trying to rollback", e);         }     }       /**      * Commits all the changes done after the transaction started on the current request thread      * this accepts the commit command from the only method which started the transaction      * This will unlink the current session and then currentSession() method can give another session as existing one is unlinked on the thread local      */     public static void commit(){         try{             logger.debug("commit............... ");              Transaction t = transaction.get();             if(t != null /*&& !t.wasCommitted()*/                     && callerXPath.get()!=null && callerXPath.get().equals(getCallerMethodInvolvedinTransaction())){                 t.commit();                  currentSession.get().clear();                 currentSession.set(null);                 transaction.set(null);                 callerXPath.set(null);                  logger.debug("Commit success on transaction:"+t);             }         }catch (Exception e) {             logger.error("Exception while trying to commit", e);         }     }              /**      * get the caller method xpath: <package>.<classname>.<methodname>      * @return      */     public static String getCallerMethodInvolvedinTransaction() {         try{             StackTraceElement[] stElements = Thread.currentThread().getStackTrace();             return stElements[3].toString().split("\\(")[0];             /*for (int i = 3; i < stElements.length; i++) {                 String rawFQN = stElements[i].toString().split("\\(")[0];                 String className = rawFQN.substring(0, rawFQN.lastIndexOf('.'));                 String methodName = rawFQN.substring(rawFQN.lastIndexOf('.')+1);                 Object carObj = Class.forName(className).newInstance();                  ClassPool pool = ClassPool.getDefault();                 CtClass cc = pool.get(className);                 CtMethod methodX = cc.getDeclaredMethod(methodName);                 int xlineNumber = methodX.getMethodInfo().getLineNumber(0);                  Method method = carObj.getClass().getMethod(methodName);                 if (method.isAnnotationPresent(JCTransaction.class))                 {                     return rawFQN;                 }             }*/         }catch (Exception e) {             logger.error("" , e);         }         return null;     } } 

But same working fine with oracle db (with oracle hib properties).

2 Answers

Answers 1

There has to be something wrong with your code or you should never lock yourself out. Two different connections can block each other, but one connection should never block on its own locks. I haven't looked at the code in detail, I'll focus on why you get the problem with SQL Server and not with Oracle.

Oracle always uses versioning for rows, so rows are never locked just because they are read. SQL Server on the other hand normally takes read locks and a read lock will prevent writes from other sessions. You can probably change the SQL Server isolation level to READ_COMMITED_SNAPSHOT in order to hide the issue, but it will still be there.

I don't understand why you are clearing the session in several locations, that should almost never be done. I also don't understand all that code dealing with the transaction in HibernateSessionManager, that is probably the root cause of the issue. Somehow you're running with more than one transaction. Keep things simple instead and the issue is likely to go away!

Answers 2

As per my understanding, you already defined OneToMany relation between Rule & BenchMark. So while building Rule Entitiy / Object you build Benchmark too. Am I right?

Analysis: I am assuming Benchmark object is also populated and saved in Set Benchmarks. So right now you are saving Rule, as hibernate it will try to save Benchmark too. In same transaction you are trying to save Benchmark again and due to this transaction manager goes in deadlock situation.

Solution: Before setting benchmark in rule object try to populate benchmarkid and save only Rule which will save your behcmark object too.

@Override     public Rule saveRule(Rule rule) throws Exception {         try {             //Get BenchMark from rule...             // Your code to get Benchmark from rule.             // Populate benchmarkId             if (benchmark.getBenchmarkId() == null) {                 benchmark.setBenchmarkId(getBenchmarkCount() + 1);             }             getSession().saveOrUpdate(rule);             getSession().flush();         } catch (RuntimeException e) {             e.printStackTrace();             throw e;         }         return rule;     } 

PS: Please refer Hibernate docs too.

Read More

How to configure/Enable the form based authentication in SSRS 2008 RS

I have two reports , one is for internal users and another one is for external users,

For internal Users i need to enable form authentication to view the report instead of creating the user accounts in the server.

For external User i do not want to enable any authentication, so that they can access the report from the browser without any authentication.

I followed the below steps by using the SSRS samples from the below link, after did all the changes i am getting HTTP500 Error. please help to enable this form based authentication.

modify the RSReportServer.config file

Step 1:-

<Authentication>     <AuthenticationTypes>          <Custom/>     </AuthenticationTypes>     <EnableAuthPersistence>true</EnableAuthPersistence> </Authentication> 

Step 2:-

<Security>     <Extension Name="Forms"  Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,  Microsoft.Samples.ReportingServices.CustomSecurity" >         <Configuration>             <AdminConfiguration>                 <UserName>username</UserName>             </AdminConfiguration>         </Configuration>     </Extension> </Security> <Authentication>     <Extension Name="Forms" Type="Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension, Microsoft.Samples.ReportingServices.CustomSecurity" /> </Authentication> 

Step 3:-

<UI>     <CustomAuthenticationUI>         <loginUrl>/Pages/UILogon.aspx</loginUrl>         <UseSSL>True</UseSSL>     </CustomAuthenticationUI>     <ReportServerUrl>http://<server>/ReportServer</ReportServerUrl> </UI>  

modify the RSSrvPolicy.config file

Step 4:-

<CodeGroup class="UnionCodeGroup" version="1" Name="SecurityExtensionCodeGroup" Description="Code group for the sample security extension" PermissionSetName="FullTrust">     <IMembershipCondition      class="UrlMembershipCondition"     version="1"     Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll"     /> </CodeGroup>  

modify the RSMgrPolicy.config file

Step 5:-

<CodeGroup  class="FirstMatchCodeGroup"  version="1"  PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. ">     <IMembershipCondition      class="ZoneMembershipCondition"     version="1"     Zone="MyComputer" /> "}*To use Forms Authentication, you need to modify the Web.config files for Report Manager and Report Server 

modify the Web.config file for Report Server

Step 6:-

<authentication mode="Forms">     <forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60" path="/">             </forms> </authentication> Add the following <authorization> element directly after the <authentication> element. <authorization>      <deny users="?" /> </authorization>.  

Web.config file for Report Manager

Step 7 :-

Disable impersonation by locating the section <identity impersonate= "true" /> and changing it to the following: <identity impersonate="false" />. Locate the <authentication> element and change the Mode attribute to Forms. Add the following keys to the <appSettings> element. <add key="ReportServer" value="<Server Name>"/> <add key="ReportServerInstance" value="<Instance Name>"/> 

Reference URL https://msftrsprodsamples.codeplex.com/wikipage?title=SS2008R2%21Security%20Extension%20Sample&FocusElement=Comment

After performing all the above steps , when i am trying to register user i am getting the error called Keyword not supported: 'mssqlserver;integrated security'.

0 Answers

Read More

Enabling sql server 64 bit for network use — configuration manager blank

I have a sql server instance on a machine that I want to open up to being accessible over the network. However, I think there's multiple issues at play here and I'm not sure which one is the real contributing factor.

The version I'm using is:

select @@version 

Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Jun 10 2015 03:35:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 14393: )

I believe I need to enable network configuration in the SQL Server Configuration Manager, but when I look into my manager, I don't see anything of use:

Is there anything that clearly leaps out as to why this would be blank? Am I going about this the right way?

1 Answers

Answers 1

Re-install SQL Server and/or OS as necessary.

See similar questions here and here. What version is your OS? This is possibly an architecture mismatch - 64-bit SQL on 32-bit machine? Or version mismatch. SQL Enterprise installed on Windows Home edition?

Yes, you may be able to troubleshoot and fix, but SQL installs do some heavy lifting. The best and most thorough solution here is clean up and start fresh. It will provide you piece of mind.

Read More

Formatting a pivot result by merging rows in Sql

The background:

Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An interviewer goes to House 1 and interviews two members Member 1 and Member 2. He asks questions like. What is this house address?,What is your name and age?. The answers for that is common for the Members and the answers that are specific for them are stored in the same table

After doing some Joining on some tables and pivoting the result I end up getting the following table structure.

What was achieved so far

| ID | ADDRESS |   MEMBER |    AGE | SubformIteration  | |----|---------|----------|--------|-------------------| |  1 | HOUSE 1 |   (null) | (null) |    (null)         | |  1 |  (null) | MEMBER h |     18 |         s0        | |  1 |  (null) | MEMBER i |     19 |         s1        | |  2 | HOUSE 2 |   (null) | (null) |    (null)         | |  2 |  (null) | MEMBER x |     36 |         s0        | |  2 |  (null) | MEMBER y |     35 |         s1        | |  3 | HOUSE 3 |   (null) | (null) |    (null)         | |  3 |  (null) | MEMBER a |     18 |         s0        | |  3 |  (null) | MEMBER b |     19 |         s1        | 

I am trying to find a way to get the table to be formatted as below:

Desired output

| ID | ADDRESS |   MEMBER |    AGE | SubformIteration  | |----|---------|----------|--------|-------------------| |  1 | HOUSE 1 | MEMBER 1 |     18 |        s0         | |  1 | HOUSE 1 | MEMBER 2 |     19 |        s1         | |  2 | HOUSE 2 | MEMBER x |     36 |        s0         | |  2 | HOUSE 2 | MEMBER y |     35 |        s1         | |  3 | HOUSE 3 | MEMBER a |     18 |        s0         | |  3 | HOUSE 3 | MEMBER b |     19 |        s1         | 

I do not have enough sql vocabulary to describe and search the operation/procedure required to so As I am new to SQL and I would be really thankful if anybody could tell me an efficient way to achieve this.

Important

DO NOT RELY UPON THE QuestionText column as it will be changes When somebody decided to change the questions

Edit

Source tables

Sql fiddle link with all the below tables

As per the suggestions in the answers, I am posting the source table and the queries in hope that there will be a better understanding of the problem

Questions table

+------------+--------------+---------+----------+---------------+ | QuestionID | QuestionText |  type   | SurveyID | IsIncremental | +------------+--------------+---------+----------+---------------+ |       3483 | subform      | subform |      311 |             1 | |       3484 | MEMBER       | text    |      311 |             0 | |       3485 | AGE          | number  |      311 |             0 | |       3486 | ADDRESS      | address |      311 |             0 | +------------+--------------+---------+----------+---------------+ 

Results table

+----------+-------------------------+----------+ | ResultID |      DateSubmitted      | SurveyID | +----------+-------------------------+----------+ |     2272 | 2017-04-12 05:11:41.477 |      311 | |     2273 | 2017-04-12 05:12:22.227 |      311 | |     2274 | 2017-04-12 05:13:02.227 |      311 | +----------+-------------------------+----------+ 

Chunks table, where all the answers are stored:

+---------+------------+----------+------------+------------------+ | ChunkID |   Answer   | ResultID | QuestionID | SubFormIteration | +---------+------------+----------+------------+------------------+ |    9606 | HOUSE 1    |     2272 |       3486 | NULL             | |    9607 | MEMEBER 1  |     2272 |       3484 | NULL             | |    9608 | 12         |     2272 |       3485 | NULL             | |    9609 | MEMBER 2   |     2272 |       3484 | s1               | |    9610 | 10         |     2272 |       3485 | s1               | |    9611 | MEMEBER 1  |     2272 |       3484 | s0               | |    9612 | 12         |     2272 |       3485 | s0               | |    9613 | MEMBER 2   |     2272 |       3484 | s1               | |    9614 | 10         |     2272 |       3485 | s1               | |    9615 | HOUSE 2    |     2273 |       3486 | NULL             | |    9616 | MEMBER A   |     2273 |       3484 | NULL             | |    9617 | 23         |     2273 |       3485 | NULL             | |    9618 | MEMBER B   |     2273 |       3484 | s1               | |    9619 | 25         |     2273 |       3485 | s1               | |    9620 | MEMBER A   |     2273 |       3484 | s0               | |    9621 | 23         |     2273 |       3485 | s0               | |    9622 | MEMBER B   |     2273 |       3484 | s1               | |    9623 | 25         |     2273 |       3485 | s1               | |    9624 | HOUSE 3    |     2274 |       3486 | NULL             | |    9625 | MEMBER K   |     2274 |       3484 | NULL             | |    9626 | 41         |     2274 |       3485 | NULL             | |    9627 | MEMBER J   |     2274 |       3484 | s1               | |    9628 | 26         |     2274 |       3485 | s1               | |    9629 | MEMBER K   |     2274 |       3484 | s0               | |    9630 | 41         |     2274 |       3485 | s0               | |    9631 | MEMBER J   |     2274 |       3484 | s1               | |    9632 | 26         |     2274 |       3485 | s1               | +---------+------------+----------+------------+------------------+ 

I've written the following stored procedure which yields the first ever table given in this question:

ALTER PROCEDURE [dbo].[ResultForSurvey] @SurveyID int AS DECLARE @cols AS NVARCHAR(MAX),     @query  AS NVARCHAR(MAX),@colsAggregated as nvarchar(max); IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL   DROP TABLE #Temp; SELECT * INTO #Temp FROM (Select Answer=( case                      When Questions.type='checkboxes' or Questions.IsIncremental=1 THEN  STUFF((SELECT distinct ',' + c.Answer              FROM Chunks c Where c.ResultID=Results.ResultID and c.QuestionID=Questions.QuestionID and (Chunks.SubFormIteration IS NULL )             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'')          else  Chunks.Answer end),Chunks.SubFormIteration,Questions.QuestionText,Questions.type,Questions.QuestionID,Chunks.ResultID,Results.ResultID as Action,Results.DateSubmitted,Results.Username,Results.SurveyID from Chunks Join Questions on Questions.QuestionID= Chunks.QuestionID Join Results on Results.ResultID=Chunks.ResultID Where Results.SurveyID=@SurveyID) as X   SET @colsAggregated = STUFF((SELECT distinct ','+ 'max('+ QUOTENAME(c.QuestionText)+') as '+ QUOTENAME(c.QuestionText)             FROM #Temp c             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')         ,1,1,'')         print @colsAggregated SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.QuestionText)              FROM #Temp c             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'') set @query = 'SELECT ResultID,max(Username) as Username,max(DateSubmitted) as DateSubmitted,max(SubFormIteration) as SubFormIteration, ' + @colsAggregated + ' from              (                 select *                  from #Temp            ) as y             pivot              (                  max(Answer)                 for QuestionText in (' + @cols + ')             ) as p GROUP BY      ResultID,SubFormIteration' execute(@query) 

5 Answers

Answers 1

It may be beneficial to post the query that got you your original results; there is a possibility that the original query could be rewritten to avoid this complexity. With the given information, this is the most simplistic way of solving this problem:

SELECT    h1.Id,    h2.Address,    h1.Member,    h1.Age,    h1.MemberNo FROM House h1   INNER JOIN House h2   ON h1.Id = h2.Id WHERE h2.Address IS NOT NULL  -- Eliminates the results whre the Address is NULL after the join   AND h1.Member IS NOT NULL -- Eliminates the results that would show up from the original table (t1) where there is no Member field 

Update:

Here is a simple example of the table structure using temp tables:

DROP TABLE #Questions DROP TABLE #Results DROP TABLE #Chunks  CREATE TABLE #Questions (   QuestionId INT,   QuestionText VARCHAR(MAX),   type VARCHAR(MAX),   SurveyID INT,   IsIncremental INT )  CREATE TABLE #Results (     ResultId INT,     DateSubmitted DATETIME,     SurveyID INT )  CREATE TABLE #Chunks (     ChunkId INT,     Answer VARCHAR(MAX),     ResultId INT,     QuestionId INT,     SubFormIteration VARCHAR(20) )  INSERT INTO #Results VALUES (2272, '04-12-2017', 311), (2273, '04-12-2017', 311), (2274, '04-12-2017', 311)  INSERT INTO #Chunks VALUES (9606, 'WhiteHouse', 2272, 3486, NULL), (9607, 'MEMBER 1', 2272, 3484, NULL), (9608, '12', 2272, 3485, NULL), (9609, 'MEMBER 2', 2272, 3484, 's1'), (9610, '10', 2272, 3485, 's1'), (9611, 'MEMBER 1', 2272, 3484, 's0'), (9612, '12', 2272, 3485, 's0'), (9613, 'MEMBER 2', 2272, 3484, 's1'), (9614, '10', 2272, 3485, 's1'), (9615, 'RpBhavan', 2273, 3486, NULL), (9618, 'MEMBER B', 2273, 3484, 's1'), (9619, '25', 2273, 3485, 's1'), (9620, 'MEMBER A', 2273, 3484, 's0'), (9621, '23', 2273, 3485, 's0')  INSERT INTO #Questions VALUES (3483, 'subform', 'subform', 311, 1), ( 3484, 'MEMBER', 'text', 311, 0 ), (3485, 'AGE', 'number', 311, 0), (3486, 'ADDRESS', 'address', 311, 0) 

Here is a way to produce the results your looking for without the use of PIVOTs and XML:

; WITH Responses AS ( SELECT     c.ResultId,     QuestionText,     Answer,     c.SubFormIteration FROM #Chunks c     INNER JOIN #Results r     ON c.ResultId = r.ResultId     INNER JOIN #Questions q     ON q.QuestionId = c.QuestionId WHERE c.SubFormIteration IS NOT NULL -- Removes the "Address" responses and duplicate Answers ), FindAddress AS ( -- Pulls ONLY the address for each ResultId SELECT     ResultId,     MAX(CASE WHEN QuestionText = 'ADDRESS' THEN Answer END) AS [Address] FROM #Chunks c     INNER JOIN #Questions q     ON q.QuestionId = c.QuestionId GROUP BY ResultId ) -- Combines all responses and the address back together SELECT     r.ResultId,     fa.Address,     MAX(CASE WHEN QuestionText = 'MEMBER' THEN Answer END) AS [MEMBER],     MAX(CASE WHEN QuestionText = 'AGE' THEN Answer END) AS [Age],     SubFormIteration FROM Responses r     INNER JOIN FindAddress fa     ON fa.ResultId = r.ResultId GROUP BY r.ResultId, SubFormIteration, fa.Address 

Essentially, I broke a rather large query into a Common Table Expression (CTE). Each query had a purpose: a) Response pulls all responses except the address, b) Pulls only the address based on ResultId, and c) Combine both queries together.

The MAX(CASE...) followed by GROUP BY is an alternative method to using PIVOTS and they essentially perform the same.

To apply this query to your specific case, you should only need to change the name of the tables.

Answers 2

As far as I understand: you want to do this dynamically. For this you need to prepare the question text and run it.

The columns are being prepared. Then merged with the query.

DECLARE @Columns NVARCHAR(MAX) DECLARE @Query NVARCHAR(MAX)  SELECT  @Columns = 'C.ResultId' +            (                 SELECT                     ',' +                            CASE WHEN COL.QuestionText = 'ADDRESS' THEN 'MAX(AA.Answer)' +  COL.QuestionText                     ELSE 'MAX(CASE WHEN Q.QuestionText = ''' + COL.QuestionText  + ''' THEN C.Answer ELSE  '''' END) AS ' +  COL.QuestionText END                 FROM                      #Questions COL                 WHERE                     COL.QuestionText != 'subform'                        FOR XML PATH ('')            ) +             ',MAX(C.SubFormIteration) AS SubFormIteration'  SET @Query = '            SELECT ' +                 @Columns +          ' FROM                 #Chunks C INNER JOIN                 #Results R ON C.ResultId = R.ResultId INNER JOIN                 #Questions Q ON Q.QuestionId = C.QuestionId INNER JOIN                (                     SELECT                          IC.ResultId,                         MAX(IC.Answer) AS Answer                     FROM                         #Chunks IC INNER JOIN                         #Results IR ON IC.ResultId = IR.ResultId INNER JOIN                         #Questions IQ ON IQ.QuestionId = IC.QuestionId                      WHERE                         IQ.QuestionText = ''ADDRESS''                     GROUP BY                         IC.ResultId                 ) AA ON C.ResultId = AA.ResultId             WHERE                C.SubFormIteration IS NOT NULL                                 GROUP BY                   C.ResultId,                       C.SubFormIteration                         ' --SELECT @Query EXEC sp_executesql @Query 

Output:

ResultId    MEMBER      AGE  ADDRESS      SubFormIteration ----------- ----------- ---- ------------ -------------------- 2272        MEMBER 1    12   WhiteHouse   s0 2272        MEMBER 2    10   WhiteHouse   s1 2273        MEMBER A    23   RpBhavan     s0 2273        MEMBER B    25   RpBhavan     s1 

For Comment:

Columns "ResultId" and "SubFormIteration" are grouped and the result is. But the grouping operation is incorrect because the address information looks like this. The query and result are below.

ResultId    MEMBER       AGE      ADDRESS      SubFormIteration ----------- ------------------------------------------------------- 2272        MEMBER 1     12       WhiteHouse   NULL -- Which value you want to group. s1 or s0 2272        MEMBER 1     12                    s0 2272        MEMBER 2     10                    s1 2273                              RpBhavan     NULL -- Which value you want to group. s1 or s0 2273        MEMBER A     23                    s0 2273        MEMBER B     25                            

Query:

DECLARE @Columns NVARCHAR(MAX) DECLARE @Query NVARCHAR(MAX)  SELECT  @Columns = 'C.ResultId' +            (                 SELECT                     ',' +                                                'MAX(CASE WHEN Q.QuestionText = ''' + COL.QuestionText  + ''' THEN C.Answer ELSE  '''' END) AS ' +  COL.QuestionText                 FROM                      #Questions COL                 WHERE                     COL.QuestionText != 'subform'                        FOR XML PATH ('')            ) +             ',MAX(C.SubFormIteration            ) AS SubFormIteration'  SET @Query = '            SELECT ' +                 @Columns +          ' FROM                 #Chunks C INNER JOIN                 #Results R ON C.ResultId = R.ResultId INNER JOIN                 #Questions Q ON Q.QuestionId = C.QuestionId                                            GROUP BY                   C.ResultId,                       C.SubFormIteration                         ' --SELECT @Query EXEC sp_executesql @Query 

Answers 3

If the above table is a result of multiple joins/etc, it would be better if we could give a recommendation based on the actual schema available to you. However, if the sample table in the SQL Fiddle link is all you have to work with, try the following:

SELECT h.address, p.member, p.age, p.memberno FROM House h      INNER JOIN        House p         ON h.id = p.id          AND h.member IS NULL          AND p.member IS NOT NULL 

Answers 4

We can't see your input query, but my guess is that you're getting those null columns because of a LEFT or RIGHT join in your source query. If you could split your result vertically into two views like this:

| ID | ADDRESS | |----|---------| |  1 | HOUSE 1 |     

and

| ID |  MEMBER  |    AGE | MEMBERNO | |----|----------|--------|----------| |  1 | MEMBER 1 |     18 |        1 | |  1 | MEMBER 2 |     19 |        2 | 

and then join them on ID field, you'll get precisely the result you need.

Edit

After looking at your edit, here is how you apply the above method in your scenario:

First query:

SELECT ID, ADDRESS FROM YourTable WHERE ADDRESS IS NOT NULL 

Second query:

SELECT MEMBER, AGE, MEMBERNO WHERE MEMBER IS NOT NULL AND AGE IS NOT NULL AND MEMBERNO IS NOT NULL 

Now join them together on ID:

SELECT * FROM   (SELECT ID, ADDRESS FROM YourTable WHERE ADDRESS IS NOT NULL) AS A   INNER JOIN   (SELECT MEMBER, AGE, MEMBERNO WHERE MEMBER IS NOT NULL AND AGE IS NOT NULL AND MEMBERNO IS NOT NULL) AS B   ON A.ID = B.ID 

Answers 5

By Looking Of Your Answer i think you need result of CROSS JOIN of Two Table

You can use this query:

SELECT * from table1,table2 

This will help you...

Read More