I'm pretty new to ssrs so apologies if this is a simple question.
When I generate a report in my browser, above the report it shows several menu options spread out vertically like below:
This takes up a lot of room and is pretty ugly, so how to I change it so that they are arrange horizontly to look more like this?
(SQL Server 2012 being used)
I found some topics on query optimization, and comparing EXISTS to COUNT, but I couldn't find this exact problem.
I have a query that looks something like this:
select * from
tblAccount as acc
join tblUser as user on acc.AccountId = user.AccountId
join tblAddress as addr on acc.AccountId = addr.AccountId
... **a few more joins**
where acc.AccountId in (
select * accountid from
(select accountid, count(*) from tblUser
where flag = 1
group by accountId) as tbl where c != 1
This query runs in an instant (although the db is quite big, around 70Gb).
When I wrap the query in an EXISTS as in:
if exists
(
**Exact same query as above**
)
begin
RAISERROR('Account found without exactly one flagged user.', 16, 1);
end
else
begin
print 'test passed.'
end
Suddenly the query takes about 5-6 seconds to complete. I've tried specifying IF EXISTS (SELECT TOP 1 FROM... and also tried NOT EXISTS (which was even slower). But neither work to speed this up.
If the normal select query completes basically instantly, then does anyone know why wrapping it in the EXISTS causes so much extra computation? And/or anyone have any ideas to work around this (I'm just trying to throw an error if any records are found at all by the original query).
Thanks!
CREATE PROCEDURE spCountTableRowWHere
@TblName VARCHAR(50),
@TblID VARCHAR(10) = 'Id',
@WhereClause NVARCHAR(500) = '1=1'
AS
BEGIN
DECLARE @Query NVARCHAR(500)
DECLARE @ParamDefinition NVARCHAR(40)
DECLARE @Count INT
SET @Query = 'SELECT @C = COUNT('+@TblID+') FROM '+@TblName+' WHERE '+@WhereClause
SET @ParamDefinition = '@C INT OUTPUT'
EXECUTE SP_EXECUTESQL @Query, @ParamDefinition, @C = @Count OUTPUT
SELECT @Count
END
I am new in SQL and I am wondering if this kind of procedure is better than a separate procedures for different tables.
I am using SQL Server 12/Azure and have 3 tables (T1, T2, T3) where T1 has 1-many with T2 and T3, I want to select from T2 and return the information of T1 records and their associated T3 records. To give a simplified example, T1 is "Customer", T1 is "Orders", T3 is "CustomerAddresses", so a customer can have many orders and multiple addresses. Now I want to query the orders and include the customers information and addresses, to make things a little bit complicated, the query for orders could include matching on the customer addresses, e.g. get the orders for these addresses.
Customer Table
----------------------
Id, Name,...
----------------------
Orders Table
------------------------------
OrderId, CustomerKey, Date,...
------------------------------
CustomerAddresses
-----------------------------------------------
AutoNumber, CustomerKey, Street, ZipCode,...
-----------------------------------------------
I am having trouble writing the best way (optimized) to return all the results in one transaction and dynamically generate the sql statements, this is how I think the results should come back:
Orders (T2) and customer information (T1) are returned in one result-set/table and CustomerAddresses (T2) are returned in another result-set/table. I am using ADO.NET to generate and execute the queries and use System.Data.SqlClient.SqlDataReader to loop on the returned results.
Example of how the results could come back:
Order-Customer Table
-------------------------------
Order.OrderId, Customer.Id, Customer.Name, Order.Date,....
-------------------------------
CustomerAddresses
-------------------------------
AutoNumber, CustomerKey, Street
-------------------------------
This is an example of a query that I currently generate:
SELECT [Order].[OrderId], [Order].[Date], [Customer].[Id], [Customer].[Name]
FROM Order
INNER JOIN [Customer] on [Order].[CustomerKey] = [Customer].[Id]
WHERE ([Order].[Date] > '2015-06-28')
Questions: 1. How do I extend the above query to also allow returning the CustomerAddresses in a separate result-set/table? To enable matching on the CustomerAddresses I should be able to do a join with the Customer table and include whatever columns I need to match in the WHERE statement.
I am trying to register using mvc web api in a simple way:
This is my users model:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Namespace WebApi.Models
Public Class users
Public Property userid() As Guid
Public Property logintype() As String
Public Property username() As String
Public Property password() As String
Public Property email() As String
Public Property createddate() As DateTime
End Class
End Namespace
This is my userdetails model:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Namespace WebApi.Models
Public Class userdetails
Private users As New List(Of users)()
Private con As SqlConnection
Private da As SqlDataAdapter
Private ds As New DataSet()
Dim userid As String = System.Guid.NewGuid.ToString()
Public Function RegisterUser(ByVal username As String, ByVal password As String, ByVal email As String, ByVal logintype As String) As String
'Create ConnectionString and Inser Statement
Try
Using cn As New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=usersList;Persist Security Info=True;User ID=sa;Password=*****")
cn.Open()
Dim cmd As New SqlCommand()
cmd.CommandText = "INSERT INTO users (userid,logintype,username, password, email, createddate) VALUES(@userid,@logintype,@username,@password,@email,@createddate)"
Dim strDate As String = Date.Now.ToString("MM/dd/yyyy hh:mm:ss tt")
Dim param1 As New SqlParameter()
param1.ParameterName = "@userid"
param1.Value = userid
cmd.Parameters.Add(param1)
Dim param2 As New SqlParameter()
param2.ParameterName = "@logintype"
param2.Value = logintype
cmd.Parameters.Add(param2)
Dim param3 As New SqlParameter()
param3.ParameterName = "@username"
param3.Value = username
cmd.Parameters.Add(param3)
Dim param4 As New SqlParameter()
param4.ParameterName = "@password"
param4.Value = password
cmd.Parameters.Add(param4)
Dim param5 As New SqlParameter()
param5.ParameterName = "@email"
param5.Value = email
cmd.Parameters.Add(param5)
Dim param6 As New SqlParameter()
param6.ParameterName = "@createddate"
param6.Value = strDate
cmd.Parameters.Add(param6)
cmd.Connection = cn
cmd.ExecuteNonQuery()
cn.Close()
End Using
Return True
Catch
Return False
End Try
End Function
Public Function GetAll() As IEnumerable(Of users)
con = New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=usersList;Persist Security Info=True;User ID=sa;Password=*****")
da = New SqlDataAdapter("select * from users", con)
da.Fill(ds)
For Each dr As DataRow In ds.Tables(0).Rows
users.Add(New users() With {.userid = Guid.Parse(dr(0).ToString()), .logintype = dr(1).ToString(), .username = dr(2).ToString(), .password = dr(3).ToString(), .email = dr(4).ToString(), .createddate = DateTime.Parse(dr(5).ToString())})
Next dr
Return users
End Function
Public Function GetUserById(ByVal userid As String) As String
Dim con As New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=usersList;Persist Security Info=True;User ID=sa;Password=*****")
con.Open()
Dim email As String = String.Empty
Dim cmd As New SqlCommand("select * from users where userid=@userid", con)
cmd.Parameters.AddWithValue("@userid", userid)
Using sdr As SqlDataReader = cmd.ExecuteReader()
If sdr.Read() Then
email = sdr("email").ToString()
End If
End Using
con.Close()
Return email
End Function
End Class
End Namespace
This is my userdetails controller:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Net
Imports System.Net.Http
Imports System.Web.Http
Imports MvcApplication1.WebApi.Models
Namespace WebApi.Controllers
Public Class usercontroller
Inherits ApiController
Private Shared ReadOnly repository As New userdetails()
Public Function RegisterUser(<FromBody()> ByVal username As String, <FromBody()> ByVal password As String, <FromBody()> ByVal email As String, <FromBody()> ByVal logintype As String) As String
Return repository.RegisterUser(username, password, email, logintype)
End Function
Public Function GetAllUsers() As IEnumerable(Of users)
Return repository.GetAll()
End Function
Public Function GetEmail(ByVal id As String) As String
Return repository.GetUserById(id)
End Function
End Class
End Namespace
Now I'm able to get the userdetails with Id but when trying to post the details its not hitting the breakpoint and this is how I'm trying to post using fiddler and getting 500 response:
Can anyone say me how do I post the data in a correct way?
I updated the tables in MS SQL Server by changing a lot of the data types stored. To update this change in my ASP.NET MVC project, I refreshed the database connection, updated the model from database in my .edmx diagram and tranformed all T4 templates.
However, now all my class definitions for each table have disappeared. Is there a way to regenerate these .cs documents?
Any help would be appreciated.
I am developing re-engineering a system so that all my existing desktop system can be hosted in a single server & single database (a sort of SAAS model).
While combining all the database the new database will be 500+GB. More, the expected concurrent users will be 5000+ users.
I am confused on which databased to choose between SQLSERVER & PROGRESQL.
Thanks Shakti
