I would like to get single values from a huge sql server and put it into a table. let's say 4 by 4. How do I do that?
I have a connection string with a select statement that will only return a value. But, I do not know how to put that value into a table.
Thank you.
Hi,
I'm using Reporting Services 2005. In my report I'm binding data in a matrix by grouping 3 row fields (nested within each other) and 1 column field. Now I get the subtotals alright but I also want to get percentage at row1 only rather than grouping it with either in row or in column. I've tried so many ways but am not able to do it. Can anybody help me on this or is there a way in SSRS 2005 to get over this.
Also please suggest something instantly, as I'm terribly in the need
Thanks
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.
I have data in both table like
CompanyTable
ID Name
1 name1
2 name2
OrderTable
OrderId CompanyTabID
1 1
2 1
3 1
4 1
In my query I want to show all orders in single row.
ID Name Orders
1 name1 1,2,3,4
2 name2 null
Is anybody can help on it.
Thanks
Arvind
Hello all,
I created one Database in my server,n i wanted the same database in my local computer.So i copied it and restored in my database. Now i created another one new table in my server. I want to copy that table into my computer. How can i copy a single table in database and how can i restore(paste) that into my existing databse of my local compuer.
Can u please help me to resove this problem.
Thanks.
rita
Hello,
Let me frist start saying that I am no SQL DB guru or have any great knowledge. I am sure this questions is the most basic question posted here. I would like to know how to export a single table from an SQL 2005 DB. I need to export this table from one SQL server to another. Just one table and its contents. Also, I woud like this exported table to be saved as a *.dat file?
Thanks in advanced.
I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
I'm kinda stuck on how to do this.
Thanks!
I have a database dump using mysqldump. Now I want to import a table from the dump file. How would one do this?
I've used mysqlimport < table.sql, but what if I have databasedump.sql which includes all the tables, but I only want to import ONE table?
-D
Sharepoint is a pretty darn dynamic service and that got me thinking of how databases are created.
Just wondering out loud, surely someone has thought of creating databases in such a manner, but I don't know if it's a thought that has been struck down.
It would look something like this:
Single Table
ID uniqueidentifier PK
ParentID uniqueidentifier FK to ID
Name nvarchar(MAX)
Value nvarchar(MAX)
In this manner, you would create your database "columns" as needed in the data-layer.
If that is too strict, (every datatype would be encoded to base64), you could create a value option for basically every data type. EG. nvarchar(max), nvarbinary(max).... and add another field that describes the data-type to be used for that "column"
ID uniqueidentifier PK
ParentID uniqueidentifier FK to ID
Name nvarchar(MAX)
DataType nvarchar(50) //constrained to allowable datatypes
MaxLen nvarchar(31) //ahh, what the hey, let's add this for sniggles.
ValueNvarchar nvarchar(MAX) nullable
ValueNvarbin nvarbinary(MAX) nullable
....
Now, to allow the "values" for those "columns", you may be able to still use the single-table approach, but it may be better to have an extra table for that (probably even a different partition and drive).
Things to consider, indexing.....
In development, the data-layer would handle the creation/reading of table columns.
The business-layer, which could be many for different parts of a company, would make it's requests to the DL. It may need a username, and the DL would either just create it, or suggest an already existing username column. The path to that username may not be where a particular biz element wants it, so they will ask the DL create another under a diff path.
The business-layer is probably the most important reason for wanting a single dynamic table.
In the end, the relation structure could be like:
Human //dir, no value, no parent (root)
FirstName //value - text
LastName //value - text
Parent1 //value - Me Id
Sibling1 //value - Me Id
SiblingN //value - Me Id
School //dir, no val
ParentId //value - Id (perhaps category would be Building)
Name //value - text
The sibling N would be an example of how a table may need to be dynamic. A positive of the single-table approach is no limitation on number of "columns". Another is the ease to move a hierarchical structure if needed. School may want to be University instead of just "school" and be placed as a child of "school".
Looking for any thoughts on the subject,
Nathan
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
Hello, As the heading states, I'd like to copy a database table from VWDE over to SQL SME, where it'll replace its namesake. I've tried the 'attach' method but was denied due to server permissions. Is there another way of doing this, or will I have to delete the database and then run a script to reinstate (annoyingly convoluted)? This would be so much easier if the host supported SQL 2005 Express. Thanks in advance
View 2 Replies View RelatedHow can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
Hallo
i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers.
I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times:
MainTable record contains the latest and current version
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
MainTableAudit contains edited ID002 versions
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02
ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16
I would like to build a report like this:
first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages
modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212"
modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232"
modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240"
current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.
I am using the default ASPNETDB.MDF database for a project in SSE 2005 and VWD 2005. I need to use the UserName field from the aspnet_Users table as a foreign key in another table I have in the database. I am doing this so that I can grab data generated from additional fields that I'm adding to the membership registration wizard. However, I am obviously missing some steps since the user name doesn't show up in my second table. What should I do besides creating a relationship between the two fields and tables? Should I be writing some sort of SQL statement to accomplish this?
View 6 Replies View RelatedHI
I have created a stored procedure in SQL Server which outputs all records in a table.
How can I execute that procedure in access please? ( show that table in access?)
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
Hi,
We have a report published to two report servers ( same configuration). this report displays data in two side by side tables. on server 1, the left side table does not get displayed where as both the tables show fine on server 2.
What could be the reason for this
Any help greatly appreciated.
Thanks,
Phani
Hi all,
I have to change the date format for one database in a group of databases in my sql server 2000,Can you please tell me how to change the date format.
thanks in advance
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
HiI have this code, but i can't get it to work, and if i delete <% %> then the if statsment is not working, how do i get this code to work.
1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
2 SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @SiteMainID)">
3 <% Dim pageString As String = "Def"
4 If Not (Request.QueryString("Page") Is Nothing) Then
5 pageString = Request.QueryString("Page").ToString
6 End If
7 If pageString = "Def" Then
8 %>
9 <SelectParameters>
10 <asp:Parameter DefaultValue="1" Name="SiteMainID" Type="Int32" />
11 </SelectParameters>
12 <%
13 Else
14 If pageString = "Page1" Then
15 %>
16 <SelectParameters>
17 <asp:Parameter DefaultValue="2" Name="SiteMainID" Type="Int32" />
18 </SelectParameters>
19 <%
20 Else
21 If pageString = "Page2" Then
22 %>
23 <SelectParameters>
24 <asp:Parameter DefaultValue="3" Name="SiteMainID" Type="Int32" />
25 </SelectParameters>
26 <%
27
28 End If
29 End If
30 %>
31 </asp:SqlDataSource>
Hi,
My question is how can i get a page show all of my pictures i got in folder c:..Images? by using Database. Becouse I want some of the pictures to be shown in one page for theirselves.
Database looks like:
ID | Pname | Pact |
1 | picture1 | Yes |
2 | picture2 | No | etc.
Now i want picture 1 and 2 to be shown in my Allpictures.aspx
I tried by using <asp:repeater...>... But i got a problem when i wanted the <img src=<%#EVAL(Pname)/>... becouse i cant do it that way...
Thanks,
Even Knutli
Salam to All
Plz give me Sql query which return list of Database By providing these information(Server name, Uerid ,Pasword)
Thanks
Hello,Does anyone know what procedure to run to show information such as theowner of a specific database for MSSQL2000? I want to make a script toloop through all the databases on a server and display owner and otherhelpful information.Best regards,Andreas
View 1 Replies View RelatedHi all,
What is the best way to show when a table was last MODIFIED?
Thanks,
Stan
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
I have a query
select salesId,count(*) from salesline group by salesid
Result will be
salesid Nos
----- ---
SO001 2
SO002 4
I want to display single record like below if there is no record available in the table
salesid Nos
So00? 0
I have several databases that are used to collect data for differentclient-projects. My intention was to collect the data, and administermySQL so each client could see only their data. Each of thesedatabases used to collect data have the same tables. There are otherdatabases which are available to all the users which contain commoninformation.The command "show databases" lists all of the databases (visible tothe particular user) and not the one's I would like to display (thedatabases used to collect data). Is there a way to nest a singlemySQL command to show only the databases that have the same particulartable?
View 1 Replies View RelatedI have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls
Employee A 5
Employee B 0
Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls
Employee A 5
Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
I'm trying to use the following code to examine table schema for SQL 2000. But when I do, the IsKey value is null or blank. I tried this on two different tables - a State table with a char(2) primary key containing the state abbreviation and a Trend table which has an identity column as the primary key. Public Shared Function GetSchema(ByVal sTable As String) As String
Dim sb As New StringBuilder
Dim oConn As SqlConnection = OpenConn()
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM " & sTable, oConn)
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim dt As DataTable = dr.GetSchemaTable()
dr.Close()
oConn.Close()
For ic As Integer = 0 To dt.Columns.Count - 1
sb.Append(ic & ":" & CNull(dt.Columns(ic).ColumnName) & " ")
Next
sb.Append(vbCrLf)
For ir As Integer = 0 To dt.Rows.Count - 1
For ic As Integer = 0 To dt.Columns.Count - 1
sb.Append(ic & ":" & CNull(dt.Rows(ir).Item(ic).ToString) & " ")
Next
sb.Append(vbCrLf)
Next
Return sb.ToString
End Function
Here's the output from the function:
? datahelper.GetSchema("State")
"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType
0:StateCode 1:0 2:2 3:255 4:255 5:False 6: 7: 8: 9:StateCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3
0:State 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:State 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22
0:CountryCode 1:2 2:2 3:255 4:255 5:False 6: 7: 8: 9:CountryCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3
"
? datahelper.GetSchema("Trend")
"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType
0:TrendID 1:0 2:4 3:10 4:255 5:False 6: 7: 8: 9:TrendID 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:True 18:True 19:False 20: 21:False 22:True 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8
0:Description 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:Description 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22
0:Length 1:2 2:4 3:10 4:255 5:False 6: 7: 8: 9:Length 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8
"
Column 6 is IsKey but just displays 6: whereas IsIdentity displays correctly for Column 17. Can someone help me? Thanks in advance.
I want to ask something because i need.
Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?
can you teach me how to do and any example(s)?
Just like "describe select(*) from table_name" in DB2?
Thanks!
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to