I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
I am trying to extract the non matching records and the matching ones in two tables from two servers, one a linked server in one go. For example if table A has records, Rec1, Rec2, Rec3 and Rec6 AND Table B has Rec1, Rec2, Rec3 and Rec7 I need to get in the result set Rec1, Rec2, Rec3, Rec6 and Rec7.
The real query I ran is as follows. I want to know the list of all tables in GlobalDB database in sg_qt1 and sg_q5 servers. NOTE : sg_q5 is a Linked server to sg_qt1.
Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19) from sysobjects a full outer JOIN sg_q5.globaldb.dbo.sysobjects b ON a.name = b.name where a.xtype = 'u' and b.xtype = 'u' and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')
If I run it from sg_qt1, the result I get contain all tables from sg_qt1 but not the non-matching ones from sg_q5.
I am obviously doing something wrong, but what is it?
Thanks. If possible please reply to, r.wimalaratne@iaea.org
The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.
I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?
Hi, I'm trying to set the value of the variable @prvYearMonth thru this sp. In the query analyzer I execute the following code to the see the results of my 'CabsSchedule_GetPrevYearMonth' SP, but the only see "The Command(s) completed successfully in the result. What am I missing??
Thanks in advance
CREATE PROCEDURE CabsSchedule_GetPrevYearMonth ( @prvYearMonth int OUTPUT )
AS BEGIN SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule) END GO
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
SELECT @tmpCount returns nothing. The RIGHT(....) function does not render any results. I am expecting '0006'.
I read that the data type must be compatible with varchar. The @cLastBarcode was declare as char(25). I have even tried casting the @cLastBarcode char string to type varchar.
I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.
To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following: Date 28-12-2007 Time 18:29:31 Server SQL2K5 Database(s) to tune [DBProd] Workload file C:Tempfiltered.trc Maximum tuning time 31 Minutes Time taken for tuning 31 Minutes Expected percentage improvement 20.52 Maximum space for recommendation (MB) 12874 Space used currently (MB) 7534 Space used by recommendation (MB) 8116 Number of events in workload 545 Number of events tuned 80 Number of statements tuned 145 Percent SELECT statements in the tuned set 77 Percent INSERT statements in the tuned set 13 Percent UPDATE statements in the tuned set 8 Number of indexes recommended to be created 15 Number of statistics recommended to be created 50 Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.
Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was: Date 31-12-2007 Time 10:03:09 Server SQL2K5 Database(s) to tune [DBProd] Workload file C:Tempfiltered.trc Maximum tuning time Unlimited Time taken for tuning 2 Days 13 Hours 44 Minutes Expected percentage improvement 0.00 Maximum space for recommendation (MB) 12874 Space used currently (MB) 7534 Space used by recommendation (MB) 7534 Number of events in workload 545 Number of events tuned 545 Number of statements tuned 1064 Percent SELECT statements in the tuned set 71 Percent INSERT statements in the tuned set 21 Percent DELETE statements in the tuned set 1 Percent UPDATE statements in the tuned set 5 This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation.
It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.
What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.
I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.
My first foray into the SQL CLR world is a simple function to return the size of a specified file. I created the function in VS2005, where it works as expected. Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.
Here's the class member code:
Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long
This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log. The function returns correct values in VS2005. The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.
So I€™m at a dead-end looking for the reason behind the following behavior. Just to make sure no one misses it, the 'behavior' is the difference in the number of reads between using sp_executesql and not.
The following statements are executed against a SQL 2000 database that contains >1,000,000 records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler
SQL 2: DECLARE @Priority int DECLARE @Activity_Code char(36)
SET @Priority = 0 SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69' update act_item set Priority = @Priority where activity_code = @activity_code
Reads: ~160 Duration: 0 ms
Random information:
Activity_code is an indexed field on the table, although it is not the primary key. There are a total of four indexes on the table, none of which include the priority as one of the fields. There are two triggers on the table, neither of which is executed for this SQL statement (there is an IF UPDATE(fieldname) surrounding the code in the trigger) There are no foreign relationships I checked (using perfmon) to see if a compilation/recompilation was happening. No it's not. Any suggestions as to avenues that could be examined would be appreciated.
HI, I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed for this i need a table and procedure to complete family tree Thanks @mbi
I am using LIKE operator for a query to get wild card searched data.
for example if i give AN, should get for %AN% only. But it is returning data for %NA% data too. I cross checked by giving simple data. Please help me on this.
I have a table in one database (source) and an equivalent table in another (destination) and I need to move from my source to my destination database.
When I move the data I need to do the equivalent of an updategram, so for instance compare primary keys and if it exists in both do an update, if it is in source but not destination do an insert, if it is in destination but not source do a delete.
Is there already a process for doing this, as it seems pretty fundamental but I've not really found anything that gives the result I need?
If not, is there a way of doing this with what is available within Data Flows or should I just go ahead and go through the pain of creating my own data flow destination object?
I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think
but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0
very strange..
doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case
i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...
more magically appear when hte same statement is ran in SSIS!!
I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...
Hi All, I am kindly seeking for help. I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following: Date ID R P M D... Y 1/1/90 A 1 2 3 4... 5 1/2/90 A 2 3 4 5... 1 ... 2/11/05 A 3 4 5 6... 2 1/1/90 B 1 2 3 4... 5 1/2/90 B 2 3 4 5... 1 ... 2/11/05 B 3 4 5 6... 2 ... The expected query results look like: ( this results from Date, ID and R fields) Date A B 1/1/90 1 1 1/2/90 2 2 ... 2/11/05 3 3
The SQL I wrote: select date, ID, A=sum(case when ID=A then R else 0 end), B=sum(case when id=B then R else 0 end) from MyTable Group by date
I would also like to get another set of results with the same format but from date,ID and P fields: Date A B 1/1/90 2 2 1/2/90 3 3 ... 2/11/05 4 4
select date, ID, A=sum(case when ID=A then P else 0 end), B=sum(case when id=B then P else 0 end) from MyTable Group by date
The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this? I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.
Any suggestion/comments are highly appreciated! shiparsons
I use the following sproc to populate a table that is used as the base recordset for a report.
For some reason, when the sproc is run from a scheduled job, it doesn't repopulate the table. It does, however, truncate the table. If I run it manually from query analyzer, it works fine.
I've checked all the permissions on all the object touched by the sproc, and everything looks right there. Is there another problem I should be looking for?
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
setuser N'mcorron' GO
CREATE PROCEDURE mcorron.CreateDiscOrders AS /* Creates table for Orders with disc items Actuate report */ SET NOCOUNT ON SET ANSI_WARNINGS OFF
TRUNCATE TABLE dbo.rptDiscOrders
INSERT INTO dbo.rptDiscOrders SELECT * FROM (SELECT ORD.product as prod_XREF, ORD.ORDER_NUMB, ORD.CustName, ord.units as ordunits, INV.Product, INV.Units FROM (SELECT TOP 100 PERCENT f.PARENT_SITE, f.SITE, dbo.vwCustBillTo.CustName, o.ORDER_NUMB, p.Prod_Xref, o.PRODUCT, o.ORDER_TONS * 2000 / m.part_wt AS UNITS FROM dbo.Lawn_Orders o INNER JOIN dbo.PRODUCT_XREF p ON o.PRODUCT = p.Product INNER JOIN dbo.FACILITY_MASTER f ON o.WHSE = f.SITE INNER JOIN dbo.Lawn_PartMstr m ON o.PRODUCT = m.part_code INNER JOIN dbo.vwCustBillTo ON o.BILLTO = dbo.vwCustBillTo.BillToNum WHERE (o.SHIP_DATE < DATEADD(d, 30, GETDATE())) and prod_xref not like 'dead%') ORD INNER JOIN (SELECT f.PARENT_SITE, x.Prod_Xref, i. Product, SUM(i.Qty) AS Units FROM dbo.Lawn_Inventory i INNER JOIN dbo.FACILITY_MASTER f ON i.Whse = f.SITE INNER JOIN dbo.PRODUCT_XREF x ON i. Product = x. Product WHERE (f.WHSE_TYPE = 'ship') GROUP BY f.PARENT_SITE, x.Prod_Xref, i. Product) INV ON ORD.PARENT_SITE = INV.PARENT_SITE AND ORD.Prod_Xref = INV.Prod_Xref) ordinv WHERE (Prod_Xref <> Product) GO setuser GO
Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.
select *
from [dbo].[dmTable]
WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'
select *
from [dbo].[dmTable]
where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')
I have a stored procedure that is Averaging a Difference in dates in seconds. All of the sudden it started throwing an Arithmetic overflow error. After running the query below on the same data, I can see that it is because the DateDiff in my procedure, which is calculating the difference in seconds, is returning a value greater than 68 years. Looking at the dates in the result table, I don't see how it is coming up with the values in the Years Difference column.
Code SnippetSELECT createdate, completeddate, DATEDIFF(y, createdate, completeddate) as 'years difference' FROM tasks WHERE (TaskStatusID = 3) and (createdate < completeddate) and (DATEDIFF(y, createdate, completeddate)>=68) ORDER BY completeddate
I have an Execute SQL Task that selects one column value from one row, so General > ResultSet = Single row. Result Set > Result Name = 0 (the first selected value) and Variable Name = User::objectTypeNbr. The task runs successfully, but after the it runs the value of User::objectTypeNbr is not changed.
User::objectTypeNbr > Data Type = Int32. When I declared the variable Value could not be empty so I set it to 0 arbitraily, assuming it would be overwritten when assigned a new value by the Execute SQL Task, but it remains 0 after the task runs. What am I missing here?
I am trying to convert an active x script in a script task. Below is a snippet of code. The underlined AsOfDate has a blue squiggly line under it and if I hover over it, it says "Declaration Expected."
Public Class ScriptMain
Dim AsOfDate As String
AsOfDate = Dts.Variables("MyDate").Value ...
Can someone please tell me what I'm missing? I thought maybe I'm missing an import statement, but I have:
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
I have used similar syntax in script components and it works fine.
In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM.
Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.
Is there something missing in this code? Is this a SQL Server bug?
// Set the required properties for the trans publication snapshot job. TransPublication tpublication = new TransPublication(); tpublication.ConnectionContext = conn; tpublication.Name = publicationName; tpublication.DatabaseName = publicationDbName; tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily; tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001); tpublication.SnapshotSchedule.ActiveStartDate = 20051101; string newString = "025800"; tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString); tpublication.Create();
What do I now have: A directory with Access Databases; around 20 databases, all dinamicly created; Each Database has on average 300 tables inside, all equally structured, all created by software; Each table has two DateTime fields, 4 double fields and 4 long int fields; Each table has around 10000 records, average.
The Directory is shared in a Windows 2003 Enterprise server. Around 20 users access the databases simultaneously, adding, retrieving and deleting data, over 100MBits LAN.
Here's the catch: As fast as possible, the program needs to retrieve 1 single record matching a single date from a given table in a given database. All databases work together. It needs to gets litterally thousands of individual records in order to work properly. Per user. That means thousands of requests, but not much data in each request. That's its core job. A small percentage of request write the record back , that is, update it. Maybe 2% of requests.
If I were to reproduce this situation in a SQL server 2005, what would be the expected time for lets say 50000 requests ? Or should I stick to Access ?
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here) the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring. I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID. FirstNextPreviousLast I am using a repeater to display the photo with the following sqlDataSource: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters> </asp:SqlDataSource> I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
I'm not seeing any built-in functionality for Report Builder to get Top N values. Am I missing something? Is there a way to do this? If not, is there a way to simulate this functionality?
