We're working on a new release of a software.
In the existing version, each client connects and executes a long batch of
analysis on some tables.
In the new release, we need to allow each client to execute analysis on a
subset of data, according to certain login information.
Our idea is:
1) Connection to db
2) Creation of one View according to restrictions
3) Execution of analysis using just created view and some tables.
The problem is that executing
create view myvista as
select * from tb_data where testo='A1'
from two different clients, creates just one view.
Question: Does it exist a way of creating a VIEW with a connection scope,
something like 'select into ##temptable'?
We tried Create View #MyVista or ##Myvista, but this name is not accepted.
Alternative way, is creating a ##TempTable, at the beginning of the batch,
and using it as data source instead of full table, but, due to really huge
amount of data, this could really disturb server performances.
I'm trying to write an expression for the "Hidden" property of a column in a table. The column is only populated with data if the group on my table is open. If the group is collapsed, then the column is empty. I'd like to make the column hidden if the group is collapsed. So I'm thinking it would be something like this:
But I can't find anyone who has written this anywhere.
I'm basically trying to make this report do the same thing a matrix does, but the matrix doesn't let me label the columns. I put textboxes above the matrix in line with the columns, but when I deploy the report to ReportManager, the textboxes get thrown all over the place and don't line up with the columns anymore.
So I guess if you can't answer the first question, an alternate question I have is how are you supposed to label the columns (row groups) in your matrix? They don't have headers, if I try to line textboxes up with them they get all messed up during deployment. They have that textbox up there in the top left corner that runs across the top of all of the row group columns, but if I put labels separated spaces in that textbox, the spaces get removed in rendering so the text is pushed all to the left and doesn't line up anymore.
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
I have a matrix in which there are 7 columns.I need to control visibility of a column based on the prompt values provided. For example: If prompt values are Act,Ban,Mag then only these column data should come up if i do not select any of these then they should not show up.
I have been using tis page as a reference http://forums.asp.net/thread/1511323.aspxbut i cant seem to get this to work. The above page suggests using Dim newId As Object = e.Command.Parameters("@RETURN_VALUE").Value to get the value but when i do that i get an error that Command is not a member of system.web.ui.webcontrols.formViewInsertedEventArgs Can anyone help?ThanksMatt
HelloI am trying to run a program to check for transaction scopeI have written the following code.But it seems I need to add a namespace or referenceWhat namespace am I supposed to addthanksusing (TransactionScope scope = new TransactionScope(TransactionScope.Required, options)){SqlConnection MyCon = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=demo");MyCon.Open();SqlCommand Mycmd=new SqlCommand("insert into t1 values 574,'scope','10/10/2007',3,3,3");Mycmd.ExecuteNonQuery();SqlConnection MyCon1 = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=persons");MyCon1.Open();SqlCommand Mycmd1 = new SqlCommand("insert into persons values 'scope',123,123,12,'scope'");Mycmd1.ExecuteNonQuery();}
Hi folks I'm using a function to create a record on a database, and then I want to return the ID of that record to passinto another function. I believe its scope identity that does this, but I'm not sure how to do it. public static void putrecordin(string record) { SqlCommand cmd = new SqlCommand("insert Table (record) values (@record; scope identity)" conn.Open(); cmd.Parameters.Add(new SqlParameter("@record", record)); cmd.ExecuteNonQuery(); conn.Close(); another(new-record-ID, anothervalue); } public static void another(string new-record-ID) {do stuff } so you'll see I have a function called putrecordin, and at the end of the sql statment I want to return the id of the new record and pass it into another function called another. Anyone know how to do this? Thanks!
In the following Query, is Table1.Column3 updated in the subquery for each row in Table1, or does it take the first value, or is it NULL, or is it ____?
Select Column1, Column2 from Table1 where Column1 IN (Select Col1 from Table2 where Col2 > Table1.Column3 Group by Col1 Having count(Col1) > 1) and Table1.Column3 < Column4
To Parse it out. This then has to be inserted into another table with an identity column (damn Identity column), and I need to grab the generated id for each row, because then there are other children tables that need to be populated.
Question: Is there any set way to grab multiple generated id's? Or do I need to loop or use a cursor?
Is an index in a database the equivalent for a <TH scope="col"> in a columnof atable in the html code?--Luigi ( un italiano che vive in Svezia)https://www.scaiecat-spa-gigi.com/s...kor-italien.php
I've been trying to figure out the usage of "Nothing" as the scope parameter, and the more I try, the more I can confused.
It says on the RDL spec:
"For expressions inside data regions:
.......
Specifying the keyword Nothing as the scope is equivalent to specifying the outermost data region containing the report item in which the aggregate is used."
It seems that what's it saying is that if I have an expression in a table as = last(somefield, "the name of the table") is the same as = last(somefield,Nothing) But, apparently it;s not. (I tried last, first,sum, count,CountRows, min, max...........) it doesn't matter where I put "=last(somefield,Nothing)" in the table (i tried table header,footer, detail, and table group header, footer), none of them worked. and I tried it everywhere in matrix, in charts. It's just not working. It always complains about the invalid scope not being the containing data region, containing grouping or dataset name.
However, the only way I get the "Nothing" as the scope to work is in a RunningValue function, not in any other aggregate funcitons.
Anyone help me with this, please... I need a complete definition on the usage of Nothing as scope.
Variable scope of package variables should be in a dropdown. I want to copy (20+) variables from one sequence container to another. Do I have to retype all the names, types and initial values because I made the mistake not to place them one level higher?
I am really starting to like CTEs. My only qualm is that you can only use them within the first statement after you declare them. I wish they would remain active for the duration of the sql batch just like everything else (variables,local temp tables, ect). Is there any trick so that you can use them multiple times? Maybe define them as a string and do dynamic sql or something like that? Hopefully (i have not researched the new version of sql server) in SQL Server 2008 the scope has increase. Anyways any help would be appreciated.
I have a field on a report where on occasion, will display 'PR'. We need to have this field hidden if that is the value of the field. I wrote an expression for visibility like: iif(Fields!ThisName.Value="'PR'", false, true), however it still diplays. Is there something else that I need to do here?
I'm running the following query in SQL Server 2005: select name from master..syslogins; It is being executed from within a stored procedure.
For user 'sa' - I get the complete list of users. For a user (say 'user1') with NO sysadmin privilege - I get only two names: 'sa' and 'user1'
Is there a way for me to retrieve the complete list of users even for 'user1' without making any changes to his profile (or making very MINIMAL changes to profile)?
I don't want to give sysadmin profile to this user. I know 'GRANT VIEW ANY DEFINITION TO public' works, but don't want to do that either.
I want to control the visibility of the report bases on if certain columns have the data or not. Since our DB can accept EMPTY strings. I need to check for both NULLs and EMPTY STRINGS.
I have writen the following VB function
Public Function IsEmpty(ByVal value As Object) As Boolean If IsNothing(value) Then Return true Else If(value.ToString().Length = 0) Return true End If Return false End Function
In the "visibility" expression window I have the following
=(Code.IsEmpty(Fields!COLUMN1.Value)) and (Code.IsEmpty(Fields!COLUMN2)) and (Code.IsEmpty(Fields!COLUMN3))
Hi, I've been trying to insert data in a Sql server (.mdf) db and use SCOPE IDENTITY to be able to insert an id in both the parent and the child tables. However, I don't know how to write one of the lines correctly: Dim MyConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString) Dim MySQL As String = "INSERT INTO Document (fid, serie, vnr, datum, vtext, sign) " & _ "Values (@fid, @serie, @vnr, @datum, @vtext, @sign) Select @verid=SCOPE_IDENTITY()" Dim Cmd As New SqlCommand(MySQL, MyConn) With Cmd.Parameters .Add(New SqlParameter("@fid", fid)) .Add(New SqlParameter("@serie", SerieDropDownList.SelectedValue)) .Add(New SqlParameter("@vnr", vnr)) .Add(New SqlParameter("@datum", datumet)) .Add(New SqlParameter("@vtext", VtextTextBox.Text)) .Add(New SqlParameter("@sign", sign)) .Add(New SqlParameter("@verid", SqlDbType.Int)) 'HERE IS THE LINE THAT SHOULD BE CHANGED (OR SO I THINK) End With MyConn.Open() Cmd.ExecuteNonQuery() Dim p As SqlParameter = Cmd.Parameters.Add("@verid", SqlDbType.Int) p.Direction = ParameterDirection.Output Dim verid = Cmd.Parameters("@verid").Value.ToString For Each row As GridViewRow In StampelGridView.Rows Dim kpnr As String = row.Cells(0).Text Dim kst As String = row.Cells(1).Text If kst = " " Then kst = "" Dim projekt As String = row.Cells(2).Text If projekt = " " Then projekt = "" Dim debettext As String = "0.00" Dim kredittext As String = "0.00" If row.Cells(3).Text.Length > 3 Then debettext = Replace(row.Cells(3).Text, ",", ".") If row.Cells(4).Text.Length > 3 Then kredittext = Replace(row.Cells(4).Text, ",", ".") Dim ptext As String = row.Cells(5).Text If ptext = " " Then ptext = "" Dim MySQLpost As String = "INSERT INTO Vpost (verid, kpnr, kst, projekt, debet, kredit, ptext) " & _ "Values (@verid, @kpnr, @kst, @projekt, @debet, @kredit, @ptext)" Dim Cmdpost As New SqlCommand(MySQLpost, MyConn) With Cmdpost.Parameters .Add(New SqlParameter("@verid", p)) 'THIS SHOULD CHECK OUT AS WELL... .Add(New SqlParameter("@kpnr", kpnr)) .Add(New SqlParameter("@kst", kst)) .Add(New SqlParameter("@projekt", projekt)) .Add(New SqlParameter("@debet", debettext)) .Add(New SqlParameter("@kredit", kredittext)) .Add(New SqlParameter("@ptext", ptext)) End With Cmdpost.ExecuteNonQuery() Next MyConn.Close() In other words, the verid is the id of the new post in the first table. This id should be inserted in the new table as well. Thank you very much in advance fro helping me out! I've worked a LONG time with this. Pettrer
Is it possible to write the InsertCommand for a SqlDataSource to return the value of the AUTONumber of a field generated when a new record is added to a database table and display that value on label1.Text after the postback of ItemInsersted event? For example, <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString >" InsertCommand="INSERT INTO [NearMiss] ([Branch], [Division] VALUES (@Branch, @Division); SELECT SCOPE_IDENTITY()" SelectCommand="SELECT [NearMissID], [Branch], [Division], FROM [NearMiss]" <InsertParameters> <asp parameter Name="Branch" Type="Int32" /> <asp parameter Name="Division" Type="String" /> </InsertParameters> </asp:SqlDataSource>
NearMissID would be the value of the autonumber generated by the SQL Server
I am trying to retrieve the value of the autonumber generated when a new record is inserted into a SQL 2000 Database using a SqlDataSource attached to a FormView. Is this possible? Can some give me a short example of how to retrieve that value and display it in a textbox? Thanks for any help with this. It seems like it would be a common issue for .net developers.
Hi, I have been trying to get the scope Identity after inserting a record using an ObjectDataSource. I can't find what event, or how to get the value that the scope identity returns. Here is my Sproc.
ALTER PROCEDURE dbo.[YourCompany_LanCustomer_Insert] ( @DNNUserID int, @FirstName nvarchar(50), @LastName nvarchar(50), @Address nvarchar(50), @Address2 nvarchar(50), @City nvarchar(50), @State nvarchar(50), @Zip nvarchar(50), @EmailAddress nvarchar(50), @PhoneNumber nvarchar(50), @CustomerID int OUTPUT ) AS INSERT INTO YourCompany_LanCustomer (DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered) VALUES (@DNNUserID, @FirstName, @LastName, @Address, @Address2, @City, @State, @Zip, @EmailAddress, @PhoneNumber, getdate()) SET @CustomerID = Scope_Identity() RETURN
This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!
I have a detailed report ..with summary lines and detailed lines (drill down).
I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows
when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).
I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.
How do I modify RowNumber(Nothing) to exclude counting the detailed rows....???
