Hi, Suppose i want to display all teh tables that a database contains i usually use the below querry Select * From INFORMATION_SCHEMA.TABLES OR Select * From sys.TABLES
But here i want to display all the items that a schema contains. How can i do this. in sql200 or in sql2005 ????
I have table1 with orderID and demographic info. Table2 with orderID and items. I would like to have a results display like this: OrderIDDemographicInfo Item1Item2Item3....ect One line per order. When I do a join I displaying all items in different rows.
I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion,
if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth.
If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using.
Select
laa.PlanId,
laa.LoanId, ( 1-5 values are stored)
los.Name
From
LoansAttriApplied laa
Inner Join LoanOptions los on laa.LoanId = los.LoanId
Where
PlanId = @PlanId
So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get
can I display the data in A,B or 1,2,3 instead of
1
2
3
Can someone please give me some insight into this.
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
I have a Client-Server - App where every Client-User has his own DB. The server is monitoring changes to all Client-DB's via SqlDependency. My problem can be reproduced with a small application, it even might be a €œfeature€? and not a €œbug€?:
- Consider two Databases TestDb1 and TestDb2 running on one SQL Server 2005 instance.
- Both DB€™s have identical Schemas.
- Consider the two DB€™s have each one table named €œTable1€?.
- Both tables have the same schema as already mentioned (the fields Id and Text).
- Now I setup a SQLDependency object on each Database:
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
If I make any changes to the Table in TestDb1 I get two notifications with the different Id€™s but the same Info,Source,Type (saying e.g. Data,Change,Update). If I make changes to the Table in TestDb2 I again get two notifications with the same result. As soon as I rename the Table in one of the Db€™s (e.g. Table2) and also change my Sql-Query in the code €“ I get just one Notification as expected. This behaviour is the same even If I change the connectionstring so that it points to another machine. So it somehow seems to fire a notification for every change to a table with the same name €“ regardless of the connectionstring where the physical change was done.
Does anybody know if this is a wanted behaviour of SqlDependency ? Does anybody know how I can set this up so I can have two DB€™s with identical Schemas and only get a Notification from the DB I actually changed ?
Hello,I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
Here's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks. Function DoesUserExist(ByVal userName As String) As Boolean Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) Dim Cmd as New SQLCommand(queryString, sqlConnection) With Cmd.Parameters .Add(New SQLParameter("@username", username)) End With
sqlConnection.Open Dim blHasRows As Boolean Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.RecordsAffected > 0 then blHasRows="True" else blHasRows="False" End If
How can I create a table identical to another one? I need to copy the indexes a constraint too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I have a database with three different tables having the exact same fields. New records are written to table1, before moving to table2 and ultimately table3. I was wondering if it's possible to run the same query on all three tables at the same time. I need to get all unique instances in the JC field from each table after a specified date. I get an "Ambiguous column name" error on the JC and TimeID fields.
SELECT distinct [JC] FROM [table1], [table2], [table3] where timeid > '20090900';
I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:
John Smith 1 John Smith 1 John Smith 1 Jane Doh 1 Jane Doh 1 Al Johnson 1
but I need it to return totals like this
John Smith 3 Jane Doh 2 Al Johnson 1
This is the query I am using:
Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID) from Player INNER JOIN PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID join Action on Treatment.Action_ID = Action.Action_ID group by Player.First_Name, Player.Last_Name, Action.Employee_Id;
I have implemented a script to perform a MD5 hash on each row processed by the SSIS package so that it can be compared with a stored value to see if there has been a change in the record. This package processes over 1 million rows. In 12 of these rows I get a hash value that is different than the stored value despite the fact that the rows "look" identical. Curious about this, I used the both the CheckSum and Binary_Checksum feature from t-sql to check the rows and they both show the identical checksum value. I have exported the rows into text and did a compare and the records are identical. I assume there must be some hidden characters that is causing the hash to be different, has anyone else run into this issue? Any help is much appreciated.
i created a simple table to record all uploaded files to my website. now, it works, but the problem is, it posts to the table 2 times, as in it executes "Button1_Click" event twice. The result is i get two records which are the same, and only differs in primary key (because i set it as an autonumber). how do i fix this? thanks in advance here's the code: HTML: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Base_Files] ([User_ID], [Date_Posted], [File_Type], [File_Size], [File_Name], [File_Description]) VALUES (@User_ID, @Date_Posted, @File_Type, @File_Size, @File_Name, @File_Description)">
I have an "insert into" statement that creates two identical rows in a table, with this statement: delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values]) This is the code-behind that performs the insert: Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
We have MSSQL 2000 Server instance installed and working well on Windows 2003 Server machine [IBM X series-366] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space.
We further created an identical server instance on a new machine. More specifically, on Windows 2003 Server machine [Intel (R) Xeon (TM)] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space, we installed MSSQL 2000 Server and copied over all the dbs, applications ...
We were expecting same or similar performance (since processor speed, ram, hd, server and database configurations are all the same, with same indexes on same tables. However, for some reason, there is a noticeable difference in performance.
More specifically, I ran Profiler for 30 minutes on both servers simultaneously [same trace parameters]. The trace file of the new server is 3 times as large as that of the old one (i.e. It looks like more items are being processed). However, the average duration of the executed stored procedures is much longer on the new server than that of the old server.
Moreover, when I run same queries on 2 servers. The query on the new server always takes longer than that on the old server. And for tables where we don't have indexes, it takes much longer.
Following advice here(http://support.microsoft.com/kb/274750/), we configured our new server (just as was our old one configured) to use 15GB of RAM. I further compared the configurations of 2 servers by executing sp_configure (with advance options). The only difference I saw was that "remote proc trans" is set to off on the new server and on on the old server. I don't think it could affect this issue though.
Furthermore, the new server appears to have many more locks, as compared to the old server. Could it be because it is processing more items?
I cannot figure what is causing the queries to be slower on the new server.
Hi, I have two tables named Tab1 and Tab2. Both are identical in structure. The only diff is Tab2 has two more additional fields (FromDate and ToDate). The structure is like below : Col1 Col2 (Date field) Col3 Col4
Also Tab 2 have Col5 (From Date) Col6 (To Date)
Now I want to transfer some set of reocrds from Tab1 to Tab2. The additional Tab2 field (Col5 and Col6) values should be the minimum and maximum values of Tab1 date field for the current set.
How to accomplish this? Kindly help me in this regard.
Just setup SSRS and at the stage of generating the model. The error message "ParentKey and ChildKey are identical" is displayed when generating the model if the connection string contains the initial catalog specifier.
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b task_c task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus: queue_num task_num team queue_1 task_a red task_b red task_c red task_d red
No bonus for any team here queue_num task_num team queue_1 task_a red task_b blue task_c red task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
why my script is not allowing me to DECRYPTBYKEY once I restore my DB from PRODUCTION BACKUP....
-- SET Staging to Single User Mode to be able to RESTORE DB---
-- STEP 1 (Works No Problems Here)
USE master; GO ALTER DATABASE Staging SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO RESTORE DATABASE Staging FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMyDBRestore.bak' ; GO
-- STEP 2 - USE ONLY IF THE ABOVE IS UNSUCCESSFUL ||| FAILURE ****** RESTORE RUN THE FOLLOWING SCRIPT -----
--If the above is successful the DB sets itself back to MULTI_USER
--------------- @@@@@@@@@@@@@@ IMPORTANT UNCOMMIT AND RUN @@@@@@@@@@@@@@@@@@@@@@@@@ MANUAL STEPS ---------------------- -- RBD - Recreate Security ID'S AND PERMISSIONS FOR Stored Procedure EXECUTE RIGHTS, because PASSWORDS are different on lower -- environments --USE [Staging] --GO --/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/ --DROP USER [WebUser]
Hello,I have this peculiar problem concerning MS SQL Server.My company works with an mailing application (ASP) which uses SQLServer as it's repository. What I want to do is send data directlyfrom my own application to this SQL Server in order to feed themailing application.To test if this was possible I linked the tables from SQL Server in MSAccess and entered the data. This worked fine and the data was pickedup correctly by the mailing application.The problem occurs when I send the data from my application (Javaapplication with JDBC connection). The data is in this case no longerpicked up by the application. The strange thing is that the data whichis entered through Access and the data from the Application lookidentical in de database view. The problem also occurs when the datais send with the tool winSQL and when I view the data in here it stilllooks identical.Even more strange is when I select the record which is not working inAccess and copy it into a new record (only changing the key) itsuddenly works!Has anyone have an idea how this can be?Thanks in advance,Sander Janssen.
I am having trouble with a select statement where i try to use a variety of methods on two columns in different tables that, hypothetically, have exactly the same contents.
Below are the three Create Table statements, so you can see how everything is set up. The columns zone_monthly.specific and #temp_dec0607c.specifics come from almost the same original source, so I can't fathom how they could actually be different in their contents - I know some of the individual lines are the same.
Create Table category_specifics (ID integer, segment nvarchar(Max), specifics nvarchar(max))
BULK INSERT category_specifics
FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly_filescategory_list.txt'
Create Table zone_monthly (ID Integer, specific nvarchar(max), zone integer, dec06 money, dec07 money)
INSERT INTO zone_monthly (specific, zone)
SELECT specifics, '12' AS zone
From category_specifics
Create Table #temp_dec0607c (ID integer, specifics nvarchar(max), zone integer, dec07 money, dec06 money)
Bulk Insert #temp_dec0607c
FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly datazonedec0607.txt'
Now here is the SELECT statement that I am having trouble with - It returns no results, and, as I said before, I am rather certain it should return something. I have tried it with an inner join as well (code included), and again, no results.
