I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
Hi,
Can any one tell me
how to find out the count of individual chars in a String
ie., my string is s='ganesh kumar'
my output must be as below
Character Occurance
--------------------
g 1
a 2
n 1
e 1
s 1
h 1
k 1
u 1
m 1
r 1
----------------------
Can any one do it,
This is interview question..
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
Hello,
I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.
For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,
For 1st group
'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc
Similarly for other groups as wll.
Thanks!
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
Please can someone advise me on how I can create a statement that will query each individual row within a table.
As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.
An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.
Please find below the script - its very much in its infancy!!
CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date
update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date
update tbl_mobile_reallocate
set status = 'Closed'
GO
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
Thanks.
DanYeung
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies View RelatedHi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .
test99: tableName countRows
t1 20
t2 30
t3 25
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
What is the best strategy for backing up individual tables in a database, not the whole database? Thank you for your help.
Best Regards,
Mark
I am recieving complete row in one single column and I have pipe delimeter in this row . I want to retrieve the data in individual columns...Currently row is in one column
Data
02|vinod sahu
Expected output
Col1 col2
02 VInod Sahu
Here's my two tables:
tblClients
--------------------------------------------------
ClientID (varchar(3)) | ClientName (varchar(100))
--------------------------------------------------
001 | First
002 | Second
tblProjects
---------------------------------------------------
ProjectID (varchar(6)) | ProjectName (varchar(100))
---------------------------------------------------
001001 | First Project1
001002 | First Project2
002001 | Second Project1
002002 | Second Project2
As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.
The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.
I need something like:
tblClients INNER JOIN tblProjects ON
tblClients.ClientID = tblProjects.First3DigitsOfTheProjectID
Hi,
In previous versions of SQL Server you were able to execute single steps within a DTS package. It doesn't seemt to be possible in visual studio. Does anybody know otherwise?
Thanks
Suppose my database has 3 table A though C and I need to get the number of records in each. I could do it as individual selects like
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
or
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
UNION
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
UNION
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
Is there any problem in unioning statements like these? In reality there were over a hundred tables and the TSQL was generated by looping through SYSOBJECTS. I eventually used the first construct as the XML was simpler to generate.
Hello all,
I would like to use MDX to identify "contracts" which are in a table A and not in a table B, and count them to have a total per "company".
It should be something like:
Contract / Flag contracts not in table B (1=yes, 0=not)
---------------------------------------------------------------
Contract 1 1
Contract 2 0
Contract 3 1
Contract 4 1
Contract 5 0
Contract 6 0
TOTAL 3
So far I know how to distinguish at contract level which records belong to table A and not table B, but I do not have the total for the company.
In SQL it should be something like that:
SELECT Company,count(*)
FROM tableA
left join tableB
on tableA.Contract = tableB.Contract
WHERE tableB.Contract is null
GROUP BY Company
Thanks,
Guillaume
I noticed a web query was running unreasonably slow, so I investigated
further and found out that query was searching on the primary key field
of a table, but instead of using the index seek, it acutually did a index
scan. I don't know why that query didn't use PK index, since the search
was on PK itself.
There is an other SQL server which has the exact same table(same data), I ran
that query against that table, and the query did what it suppose to do,
it used PK index, and was able to retrieve data very fast.
So I ran following commands and see if the systerm would use PK when
running the query:
(recreate all index(all non clusted))
sp_updatestat (all indexes)
sp_recompile (table)
But it didn't work, PK index was still not been used when that query was run.
Although I were able to slove the problem by force the web query to
use PK index, but I really don't like to force useage on any index.
So I thought there might be some corruptions in that table.
I didn't a dbcc showcontig, it showed that Page Density is 95%
and Frag is only 3.4% I also didn't some manual calculation
and only found very very small amount of unused space.
But when I ran sp_spaceused against that table,
and found out that the ROW COUNT for that table is 0!
but if I did select count (*), it returned the correct row count.
So I ran following commands
dbcc checkdb --- no error reported
dbcc checktable --- no error reported
dbcc updateusage (table) --- still got 0 row cound for sp_spaceused
(0 rows count in sysindexes for that table)
Question 1: Why PK index was not been used when the "where" of that query
was PK itself.
Question 2: Why that table has 0 row count?
Question 3: How can I fix the row count problem without introducing a
clustered index on that table or rebuilding that table?
Thanks guys!
Xiao
ps. that table has about 400,000 rows, and size of 20 MB
select singer.id,singer.singer,count(albums.id) from singer inner join albums on albums.singer=singer.id where singer.country='England' order by singer.singer
doesnt't work because of the count(albums.id)
how can I do ?
hi all,
how do we sum up all the quantity in table A, and save the summary quantity into table B? can we do it live? i mean evtime quantities in table A change, the total sum in table B also change accordingly.. pliz help... thanks in advance
~~~Focus on problem, not solution~~~
Is it possible to directly get the row count of a different table in a RS2005 report?
I'd like to use this row count number in a calculation in another table...
This would be a lot cleaner than creating a new dataset to provide me with this value.
Thanks!
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1
I think I made this example very simple and yet can't fathom why it won't batch the statements.
I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would.
Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters.
Does anyone see what I'm missing here?Nathan
I think jthis is a bug. I have a table created and populated on its own filegroup. I backup the db(all filegroups) and the trans log then I drop the one table. When I try to restore from my backups, it insists that I back up the trans log again. I do, then do the restore of both the filegroup and the trans log. The restore finishes, but my table is still not there and I can never get it back.
Any one with comments please help
Brad kreuzburg
First of all, I`m a very new entrant to the world of SQL Server. So I`ll make some mistakes.
I need to know how to write a stored procedure that will insert or update INDIVIDUAL rows. All the documentation I`ve seen is pretty coy on how to actually do this.
Any actual examples, or any tips at all would be greatly appreciated.
Thank you very much.
I am writing a utility that browses all steps, connections, and tasks in a given DTS package. One of the functions is to rename each item using its description, ie, removing spaces, formatting it, and then appending it to the standard DTSTask_ActiveScriptTask_MyCustomNameDerivedFromTh eDescription. I am being sure to change everything to match this in both the Step and task for a given set. The behaviour that is occuring is this. The package appears to be named correctly, and even seems to run correctly, but none of the precedence constraints, it, OnFail, OnSuccess, OnCOmpletion seem to show up in the DTS Designer Environment. The package does seem to be running correctly, any idea what would cause this behaviour?
View 1 Replies View RelatedSQL Server's Books On-line seems to hint that this can be done. Does anyone have any info or practical application of this? Can someone confirm that this is possible please.
Aquafortis
In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
I have SQL Server running on our web server located at a managed hosting site at Rackspace. I've got some developers who are going to be editing various sites and will need access to specific databases. Is there something built in to MSSQL, or a third party app, which will allow for this kind of remote access? They will be doing everything from restructuring these DB's to just inputting data.
Thanks!
Chris Sanders
Good day,
I have a Job that runs every half an hour, and has about 30 steps. If a step fails it moves on to the next step (the steps are not dependant on one another)... However I would like to receive notification if that occurs. I have SQL 2005, with database mail enabled and set up (works fine for other jobs)... The question is how do I get it to send me an email if a single step fails, however the job on a whole succeeds?
Do I need to set up a step between each job?
Step 1 An actual step, on failure: step 2 On success Step3
Step 2 A step that emails the failure of step 1, then goes to step 3
Step 3 An actual step
etc etc... Or is there a better way?