Purging data off of an MSDE database.

Getting started with SQL Server

Index ‹ SQL Server

- Previous
  - 1
    - using case statement to determine Order by field and direction (asc or desc) when using Row_number I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors. Say I have a table called myTable with columns col1,col2,col3, Here's what I'm trying to do with myCTE AS ( Select col1 ,col2 ,col3 ,row_number() over (order by case when(@DIR = 'ASC') then case when @COLTOSORTBY='col1' then col1 asc when @COLTOSORTBY='col2' then col2 asc else col3 asc end else case when @COLTOSORTBY='col1' then col1 desc when @COLTOSORTBY='col2' then col2 desc else col3 desc end end from myTable ) Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance. Thanks. Tag: Purging data off of an MSDE database.
  - 2
    - Reporting Services 2005 with SQL 2000 Database Hi friends, i have some questions about Reporting Services 2005: Can i use RS 2005 with a SQL 2000 Database Can i create a report with RS 2005 (with a SQL 2K Database) a show it in a webform What do i need to install (in the webserver and in the Database server) in order to start designing reports in RS 2005 against a SQL 2K DB Thanks in advance. Regards Eduardo Tag: Purging data off of an MSDE database.
  - 3
    - Deleting data bloats log file When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite. Is this typical Can I do something to slim it down As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies). Tag: Purging data off of an MSDE database.
  - 4
    - Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers" a different scenario leads to the same problem, the error message: Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers" I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server. this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away. I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution Tag: Purging data off of an MSDE database.
  - 5
    - running ssis package with ssis run time compoenents and sql server 2000... Hi Jamie, thanks. I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs. Thanks, $wapnil Tag: Purging data off of an MSDE database.
  - 6
    - export table to excel how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3. I'm fairly new to using SQL server, and not experienced at all with Transact-SQL. Tag: Purging data off of an MSDE database.
  - 7
    - CTE behaviour in SQL 2005 I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there Have any one seen this or maybe can explain this USE AdventureWorks ; GO DECLARE @SortExpression Varchar ( 50 ) Set @SortExpression = 'SalesPersonID ASC' ; WITH Sales_CTE ( RowNumber , SalesPersonID , NumberOfOrders , MaxDate ) AS ( SELECT ROW_NUMBER () OVER ( Order by @SortExpression ) RowNumber , SalesPersonID , COUNT (*), MAX ( OrderDate ) FROM Sales . SalesOrderHeader GROUP BY SalesPersonID ) Select * From Sales_CTE ; WITH Sales_CTE1 ( RowNumber , SalesPersonID , NumberOfOrders , MaxDate ) AS ( SELECT ROW_NUMBER () OVER ( Order by SalesPersonID ASC ) RowNumber , SalesPersonID , COUNT (*), MAX ( OrderDate ) FROM Sales . SalesOrderHeader GROUP BY SalesPersonID ) Select * From Sales_CTE1 Tag: Purging data off of an MSDE database.
  - 8
    - Dynamically determine Linked Servers In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done CREATE FUNCTION dbo . stfGetSupervisorOrgUnitAssignments ( @SupervisorID INT ) RETURNS @ReturnTable TABLE ( Org_Unit INT PRIMARY KEY ) AS BEGIN DECLARE @Server VARCHAR ( 100 ) SELECT @Server = dbo . stfGetLinkedServerName () INSERT INTO @ReturnTable ( Org_Unit ) SELECT ReplicationID FROM OPENQUERY ( @Server , '....' ) END Any help would be greatly appreciated. Tag: Purging data off of an MSDE database.
  - 9
    - SQL Problems 1. now i'm using bulk insert command to read sales data form text file into posmaster table in databases. the problem is this bulk command only read from single text file. what command should i use if i want to read sales data from multiple text file. this is the sample of bulk insert command for single text file. BULK INSERT posmaster FROM 'c:\SALE\010407_S13101.txt' WITH ( FIRSTROW =1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) 2. what command should i use if i want the directory for each text file generate automatically when the sales text file transfer into SALE folder so that the user doesn't need to specify the directory manually. this is the sample of directory for text file. c:\SALE\010407_S13101.txt c:\SALE\010407_S13102.txt c:\SALE\010407_S13103.txt i want the program can call the directory first and then insert the sales data from text files into posmaster table in databases. this is the sample of sales text file : 010407_S13101.txt 1511950000008395A,10-04-200711:30:08.000,CLOSED,-0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000060 1511950000008395A,10-04-200711:30:09.000,CLOSED,+0000000000050,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000050 1511950000008395A,10/04/200711:30:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000020 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000060 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025 1511950000008397A,10-04-200711:31:10.000,CLOSED,-0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000020 010407_S13102.txt 1311010000012598A002,2007-04-01:13:36.000,CLOSED,-0000000005.00,00.00,+0000000000.00,00.00,+0000000000.00,-0000000005.00 after each text file sucessfully insert into posmaster table in databases, i need the file that hold the directory deleted. Tag: Purging data off of an MSDE database.
  - 10
    - Multiple web viewers and AsyncRendering true I have multiple viewers on a web page and initialy would like AsyncRendering = true on all viewers, so I can have the loading message. Once the user drill-through, I hide all other viewers and change to AsyncRendering = false, so the autosizing will work. But, with AsyncRendering = true (in code behind): private void SetDominateViewerProperties(ReportViewer rptViewer) { rptViewer.AsyncRendering = false; rptViewer.ShowToolBar = true; rptViewer.ZoomPercent = 100; rptViewer.Visible = true; } private void SetDefaultViewerProperties(ReportViewer rptViewer) { rptViewer.AsyncRendering = true; rptViewer.ShowToolBar = true; rptViewer.ZoomPercent = 100; rptViewer.Visible = true; } I have only one viewer showing, even though, I can see in debug that all viewers are set to visible. In SetDefaultViewerProperties , I change rendering to false, I get multiple viewers. Are there combinations of property values I need to set Tag: Purging data off of an MSDE database.
  - 11
    - UnknownSqlServerError Hi guys, I have a problem to have my webservice get to work. I exec this very simple: DROP ENDPOINT sql_endpoint ; GO CREATE ENDPOINT sql_endpoint STATE = STARTED AS HTTP ( PATH = '/sql' , AUTHENTICATION = ( INTEGRATED ), PORTS = ( CLEAR ), SITE = 'SQL' ) FOR SOAP ( WEBMETHOD 'GetSqlInfo' ( name = 'master.dbo.xp_msver' , SCHEMA = STANDARD ), WEBMETHOD 'DayAsNumber' ( name = 'master.sys.fn_MSdayasnumber' ), WSDL = DEFAULT , SCHEMA = STANDARD , DATABASE = 'master' , NAMESPACE = 'http://localhost/' ); GO ANd receive by executing this on the http http://localhost/sql sql_endpoint the following return - < SOAP-ENV:Fault xmlns qlsoapfaultcode =" http://schemas.microsoft.com/sqlserver/2004/SOAP/SqlSoapFaultCode " > - < SOAP-ENV:Code > < SOAP-ENV:Value > SOAP-ENV:Receiver </ SOAP-ENV:Value > - < SOAP-ENV ubcode > < SOAP-ENV:Value > sqlsoapfaultcode:UnknownSqlServerError </ SOAP-ENV:Value > </ SOAP-ENV ubcode > </ SOAP-ENV:Code > - < SOAP-ENV:Reason > < SOAP-ENV:Text xml:lang =" en-US " > A server error occurred while processing SOAP request: Receiver, UnknownSqlServerError </ SOAP-ENV:Text > </ SOAP-ENV:Reason > < SOAP-ENV:Node > http://localhost:80/sql sql_endpoint </ SOAP-ENV:Node > < SOAP-ENV:Role > http://schemas.microsoft.com/sqlserver/2004/SOAP </ SOAP-ENV:Role > - < SOAP-ENV etail > - < sqlresultstream qlMessage xsi:type =" sqlmessage qlMessage " > < sqlmessage:Class > 16 </ sqlmessage:Class > < sqlmessage:LineNumber > 25 </ sqlmessage:LineNumber > < sqlmessage:Message > An unexpected query string was passed to a Web Service Description Language (WSDL) generation procedure. </ sqlmessage:Message > < sqlmessage:Number > 17885 </ sqlmessage:Number > < sqlmessage rocedure > sp_http_generate_wsdl_defaultcomplexorsimple </ sqlmessage rocedure > < sqlmessage erver > MAN001 </ sqlmessage erver > < sqlmessage ource > Microsoft-SQL/9.0 </ sqlmessage ource > < sqlmessage tate > 1 </ sqlmessage tate > </ sqlresultstream qlMessage > </ SOAP-ENV etail > Also the sproc exec [master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple] 65544,0,N'localhost',N'sql_endpoint',N'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.2; SIMBAR={16042FF6-D542-475f-B8DD-ACCA1BA5EDD3}; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; InfoPath.2)' gives some strange reaction. Anyway the http://localhost/sql wsdl works Tag: Purging data off of an MSDE database.
  - 12
    - SQL Server 2005 Express Edition with Advanced Services Service Pack 2 - Error 65B Hi - Who else had received this error during Windows update I'm running Vista ultimate. There are a couple of posts on google, but no solutions yet. Tag: Purging data off of an MSDE database.
  - 13
    - ssis cannot pull columns from stored procedure? hi, i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this ALTER PROCEDURE [dbo] . [uspCreateBrightPointFile] AS SET nocount ON IF EXISTS ( SELECT TOP 1 * FROM brightpointfile ) TRUNCATE TABLE brightpointfile -- Get the order information from the database where vendorconfirmationID = 0 INSERT INTO brightpointfile SELECT o . orderid , o . requestid , '295193' AS araccountnumber , o . orderdate , 'PRIORITY' AS shipmethod , 'Asurion Dobson' AS billname , 'PO Box 110808' AS billaddress1 , 'Attn Account Receivable' AS billaddress2 , ' ' AS billaddress3 , 'Nashville' AS billcity , 'TN' AS billstate , '37222' AS billzip , c . fullname AS shipname , Replicate ( ' ' , 100 ) AS shipaddress1 , Replicate ( ' ' , 100 ) AS shipaddress2 , ' ' AS shipaddress3 , Replicate ( ' ' , 40 ) AS shipcity , ' ' AS shipstate , Replicate ( ' ' , 10 ) AS shipzip , '1' AS linenumber , ve . sku AS itemcode , o . quantity AS qty , r . typeid , r . customerid , 0 AS addressfound FROM [order] o WITH ( NoLock ) JOIN request r WITH ( NoLock ) ON o . requestid = r . requestid JOIN customer c WITH ( NoLock ) ON r . customerid = c . customerid JOIN ( SELECT [subequipid] , [subid] , [clientequipid] , [serialno] , [statusid] , [startdate] , [enddate] , [createdate] , [createuserid] FROM subequip s1 WITH ( NoLock ) WHERE s1 . statusid = 1 AND s1 . startdate = ( SELECT MAX ( s2 . startdate ) FROM subequip s2 WITH ( NoLock ) WHERE s2 . statusid = 1 AND s2 . subid = s1 . subid )) se ON r . subid = se . subid JOIN vendorequip ve WITH ( NoLock ) ON se . clientequipid = ve . clientequipid JOIN clientequip ce WITH ( NoLock ) ON ce . clientequipid = se . clientequipid WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar AND ve . typeid IN ( 1 ) -- only pull direct fulfillment (not store fulfillment) AND ve . vendorid IN ( 2 ) -- only pull vendor = CellStar Insurance AND o . orderdate > '2007-08-01' -- only pull orders after 08/01/2007 --IF @@ERROR <> 0 --RETURN 1 --First use the address types of 2 UPDATE brightpointfile WITH ( ROWLOCK ) SET shipaddress1 = b . address , shipaddress2 = b . address2 , shipcity = b . city , shipstate = b . stateid , shipzip = b . zipcode , addressfound = 1 FROM customeraddress a WITH ( NOLOCK ), address b WITH ( NOLOCK ) WHERE a . customerid = brightpointfile . customerid AND b . addressid = a . addressid AND a . typeid = 2 --IF @@ERROR <> 0 --RETURN 2 --Update the rest where the address type is 1 and there is no type 2 UPDATE brightpointfile WITH ( ROWLOCK ) SET shipaddress1 = b . address , shipaddress2 = b . address2 , shipcity = b . city , shipstate = b . stateid , shipzip = b . zipcode FROM customeraddress a WITH ( NOLOCK ), address b WITH ( NOLOCK ) WHERE a . customerid = brightpointfile . customerid AND b . addressid = a . addressid AND a . typeid = 1 AND brightpointfile . addressfound = 0 --IF @@ERROR <> 0 --RETURN 3 --Select all the records from the temp table, plus union in 2 extra records required by the client SELECT * FROM ( SELECT orderid , requestid , araccountnumber , orderdate , shipmethod , billname , billaddress1 , billaddress2 , billaddress3 , billcity , billstate , billzip , shipname , shipaddress1 , shipaddress2 , shipaddress3 , shipcity , shipstate , shipzip , 1 AS linenumber , itemcode , qty , 0 AS additional FROM Brightpointfile WITH ( nolock ) UNION ALL SELECT orderid , requestid , araccountnumber , orderdate , shipmethod , billname , billaddress1 , billaddress2 , billaddress3 , billcity , billstate , billzip , shipname , shipaddress1 , shipaddress2 , shipaddress3 , shipcity , shipstate , shipzip , brightpointdefaultsku . linenumber , brightpointdefaultsku . sku , 1 , 1 AS additional FROM Brightpointdefaultsku WITH ( nolock ), Brightpointfile WITH ( nolock )) a ORDER BY orderid , additional IF @@ERROR <> 0 RETURN 1 RETURN 0 Tag: Purging data off of an MSDE database.
  - 14
    - Problem with function Hello, I am unable to resolve an error message for following user defined function. Scope of this function: This functions generates a unique identifier no from a table and returns it to caller. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION SystemNoShiva1 ( -- Add the parameters for the function here ) RETURNS int AS begin DECLARE @st2 numeric(18,0) DECLARE @sys01 numeric(18,0) declare sys cursor for Select Max(SystemNo) From TISSystemMst1 open sys Fetch sys into @sys01 begin --DECLARE @st2 numeric(18,0) if (@@fetch_status = 0) -- IF @@ROWCOUNT <> 0 close sys Deallocate sys select @sys01 = @sys01 + 1 --update TISSystemMst1 set systemno = (@sys01) insert into TISSystemMst1 values(@sys01) select @st2 = @sys01 end RETURN @st2 end Messeg I receive is Msg 128, Level 15, State 1, Line 17 The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. How can I clear above error message Nilkanth Desai Tag: Purging data off of an MSDE database.
  - 15
    - Aggregation Wizard Question Does the aggregation wizard have issues with a large number of dimensions Adding one more dimension to my cube (the dimension added only has 64 members, Key and All levels only, both with aggregationusage set to Unrestricted) causes the aggregation wizard to stop designing at 14 aggregations instead of the original 155 before adding the dimension. Tag: Purging data off of an MSDE database.
  - 16
    - How to automatic update view when table's structure has changed For example: Create view View_AutoUpdate as ( select a.*,b.[name] from User as a,Company as b where a.CompanyID=b.CompanyID ) Done,but when i do it follow: Alter table User add UserAddress nvarchar(50) null Done. finally,i type "select * from View_AutoUpdate ",the list column is not right. thanks Tag: Purging data off of an MSDE database.
- Next
  - 1
    - SQL Problems 1. now i'm using bulk insert command to read sales data form text file into posmaster table in databases. the problem is this bulk command only read from single text file. what command should i use if i want to read sales data from multiple text file. this is the sample of bulk insert command for single text file. BULK INSERT posmaster FROM 'c:\SALE\010407_S13101.txt' WITH ( FIRSTROW =1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) 2. what command should i use if i want the directory for each text file generate automatically when the sales text file transfer into SALE folder so that the user doesn't need to specify the directory manually. this is the sample of directory for text file. c:\SALE\010407_S13101.txt c:\SALE\010407_S13102.txt c:\SALE\010407_S13103.txt i want the program can call the directory first and then insert the sales data from text files into posmaster table in databases. this is the sample of sales text file : 010407_S13101.txt 1511950000008395A,10-04-200711:30:08.000,CLOSED,-0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000060 1511950000008395A,10-04-200711:30:09.000,CLOSED,+0000000000050,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000050 1511950000008395A,10/04/200711:30:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000020 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090 1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000060 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090 1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025 1511950000008397A,10-04-200711:31:10.000,CLOSED,-0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000020 010407_S13102.txt 1311010000012598A002,2007-04-01:13:36.000,CLOSED,-0000000005.00,00.00,+0000000000.00,00.00,+0000000000.00,-0000000005.00 after each text file sucessfully insert into posmaster table in databases, i need the file that hold the directory deleted. Tag: Purging data off of an MSDE database.
  - 2
    - to archive content of a table frequently Hi , In a project with SQL Servcer 2005 the customer is interested to save ( archive ) content of a table frequently. They want to have possibility to restore the table content as before. I have suggested to export the table every day to a text file and save the file in Visual source safe. If they need there will be possibility to import the text file later. Now I wonder, is there any other way to do archiving of the table content in SQL Server 2005 Best regards Tag: Purging data off of an MSDE database.
  - 3
    - Adventure Works Cube Hi all, I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen Thanks, Tag: Purging data off of an MSDE database.
  - 4
    - Linking to 2 Databases I need help with a query that has to link to two different tables on two seperate databases. Sample Data: Code Snippet --Database 1 DECLARE @Client TABLE ( ClientID INT, ClientName VARCHAR(50) ) INSERT INTO @Client VALUES ( 211, 'Greaves Inc.' ) INSERT INTO @Client VALUES ( 375, 'Lotus' ) DECLARE @Members TABLE ( MemberID INT, ClientID INT, Name VARCHAR(50) ) INSERT INTO @Members VALUES ( 123, 375, 'Eagle' ) INSERT INTO @Members VALUES ( 124, 375, 'Jill' ) INSERT INTO @Members VALUES ( 125, 211, 'Jack' ) DECLARE @ContentViewer TABLE ( MemberID INT, ContentID INT, ContentType INT, RenderDate DATETIME ) INSERT INTO @ContentViewer VALUES ( 123, 162, 1, GETDATE() ) INSERT INTO @ContentViewer VALUES ( 124, 344, 2, GETDATE() ) INSERT INTO @ContentViewer VALUES ( 125, 687, 1, GETDATE() ) DECLARE @Product TABLE ( ProductID INT, Description VARCHAR(50) ) INSERT INTO @Product VALUES ( 162, 'Viagra' ) INSERT INTO @Product VALUES ( 1687, 'Placebos' ) --Seperate Database i.e. Database 2 DECLARE @Product2 TABLE ( ProductID INT, Description VARCHAR(50) ) INSERT INTO @Product2 VALUES ( 344, 'Silverwear' ) SELECT * FROM @Client SELECT * FROM @Members SELECT * FROM @ContentViewer SELECT * FROM @Product SELECT * FROM @Product2 My Problem here is that If ContentType is = to 2 then it must link the ContentID to ProductID prom the @Product2 Table in the second database, if the ContentType is = 1, then it must link to the ProductID in @Product tabl on the current database. I want a query so that i can use it to create a report in Reporting Services. This is just sample data to give you an idea. Here is my actual query Code Snippet SELECT C.ClientName, v.ContentID, v.ContentType, Count(v.MemberID) AS Hits, v.RenderDate FROM ViewerPaneRenderHistory v INNER JOIN Members M ON v.MemberID = m.MemberID INNER JOIN Client C ON m.ClientID = m.ClientID AND ContentType = 1 GROUP BY C.ClientName, v.ContentID, v.ContentType, v.memberID, v.RenderDate HAVING RenderDate > '01-Jan-2007' ORDER BY Hits desc The query is suppose to count the Number of people that accessed the content within the time period of 1 day i.e. HAVING Renderdate > (GetDate() - DAY(1)) -- Which Didn't work It also doesn't want to COUNT the members, that accessed the data right. Any Help would be AWESOME. Kind Regards Carel Greaves Tag: Purging data off of an MSDE database.
  - 5
    - Microsoft SQL Server Report Designer Hi, I have installed SQL Server 2000 and Reporting Services 2005 and Visual Studio 2003 all works OK but I need to create a report with the Microsoft SQL Server Report Designer 2005 and it doesn't appear in my VS, someone could help me with this Marcos. Tag: Purging data off of an MSDE database.
  - 6
    - Data retrieval is much slower in RS than in management studio A call to a stored procedure completes in 13 seconds when ran from within SQL Server Management Studio. A report whose data source uses the same stored procedure can take as long as 10 minutes to run. The same parameter values are used in both the direct call and the report. The execution log says almost all of that time is spent on data retrieval. How could that be What might be the cause Tag: Purging data off of an MSDE database.
  - 7
    - Using MDXScope Statement in RS2005 Has anyone used the MDX Scope Statement in a RS2005 Reporting Services Report I am not sure if the scope / end scope statement will work in a RS report script. Thanks, Tag: Purging data off of an MSDE database.
  - 8
    - Update many rows without locking I am trying to run a simple update statement that updates around 1 million records on SQL Server 2005. For example: update customers set CustCode='AAB' where CustType=72 I would like to update the table WITHOUT locking. In this case, there is no need to have "all or nothing" transactions. If it does a partial update and then fails, it's ok to only have half the records updated. The server is using up a lot of resources creating and releasing the locks. Plus users are getting locked out of the records during the update. I know this is by design, but in this case it's OK. I know I can use the " set transaction isolation level READ UNCOMMITTED " statement to fix the select statements from getting blocked, but there are way too many places that would have to be changed. Plus there are other updates to this table that need to be locked. So here is my question: Is there a way to do a transaction-less update Thanks, Stuart Fox Tag: Purging data off of an MSDE database.
  - 9
    - Looking for a subreport does anyone has a sample I am new to reports and worked on few but a single report using 1 stored procedure. Any sample or webcast. now i have a requirement creating report within a report(subreport) first stored procedure will bring few records and now using a id of each row i need to bring there child records(from child table) is it possible to show master record and then its related child records. and then number 2 master record and its related child records Please help thank you very much for the information. Tag: Purging data off of an MSDE database.
  - 10
    - How can i retrieve some rows ... Hi I have a table with a column 'value' as nvarchar I want to select some rows from this table that the value of 'value' can be converted to decimal ... it's possible that value of 'Value' contains some other characters other than digits . Tag: Purging data off of an MSDE database.
  - 11
    - Accessing the variable in Execute SQL Task I have a variable SYear with the data type Integer and the value 2005. Connection type is OLE DB, and my sql statement is: DECLARE @MyYear Int SET @MyYear = I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @MyYear, it is fine. What is wrong in here Somebody help me! Tag: Purging data off of an MSDE database.
  - 12
    - Property Promotion with multi-level XML data type How would you extract data from an XML datatype column when it has multiple levels I've done this with single levels using CROSS APPLY and the nodes method, but can't seem to grab data from the intermediate levels. Example: <Level1> <Level2>xyz</Level2> <Level2>xyz</Level2> <Level3>abc</Level3> <Level3>abc</Level3> <Level3>abc</Level3> <Level2>xyz</Level2> <Level2>xyz</Level2> </Level1> For the root level, I use the xml.value() function, and for the details I use the cross apply xml.nodes() function typically, but if I use the xpath in the xml.nodes, I have to use the path to the lowest level (level3) to grab those values, but can't seem to grab the changing values at the intermediate levels (level2). Would this involve multiple cross-apply instances Tag: Purging data off of an MSDE database.
  - 13
    - Function to convert binary(6) field containing Packed Decimal to an integer (or float) I have been given some data from a Mainframe (AS400 ) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value. The field contains values such as the following:- 0x20202020200C 0x202020022025 0x20202020DFFA I don't know how to interpret these. Has anyone got a function that can do this for me I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help Thanks! Tag: Purging data off of an MSDE database.
  - 14
    - How to get the meatdata of a result set How do I get the column names of result set returned by "Select * FROM t1" I am using ASP.NET library (SqlCommand for example) to issues this SQL statement. Thanks, Ricky. Tag: Purging data off of an MSDE database.
  - 15
    - SQL Server 2005 can't be added Active Directory, but the documentation says it can. Hi, I've installed SQL Server 2005 on Windows Server 2003 SP1 Standard Edition. Before I install SQL Server 2005, I've configured Windows to be an domain controller with Active Directory. As the documentation said that using Object Explorer and then selecting Properties, I have the property page of Server Properties. But I still can't find the Active Directory page. When I try to run the system stored procedure sp_ActiveDirectory_SCP, I can't either register or update. Is this an error or bug Using SQL Server 2000, I can easily add it to Active Directory. Now I can't do it in 2005 version. Please help! Eriawan Tag: Purging data off of an MSDE database.
  - 16
    - What type of security do I need here? I'm developing a SQL Server 2005 application that is using a MS Access Data Project as the front end. This application deals with sensitive data such as salary information and related calculations. I would like to prevent anyone but the user who comes through the Access app, from seeing any columns in the database, and from viewing or executing any procedures or functions. This includes DBAs. I'm not sure of the best way to approach this, or what type or level of security would fit in this situation. Thanks. W Tag: Purging data off of an MSDE database.

Travis H

How do I purge data off of an MSDE database. I only want to keep 6 months of data in the database. Right now I have data going back to 2004. I get errors about every 10 seconds. "Primary File Group is Full" is the error I am getting.

Re: Purging data off of an MSDE database.

Hemantgiri S. Goswami

Hi,

you may archive it to another database/server, refer http://www.sql-server-performance.com/forum/topic.asp TOPIC_ID=8846 http://sqljunkies.com/Forums/ShowPost.aspx PostID=1855 and http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic814.aspx for "Primary File Group is Full" error.

Hemantgiri S. Goswami

Re: Purging data off of an MSDE database.

Travis H

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database

Re: Purging data off of an MSDE database.

Jens K. Suessmeyer

hi;

if you dont want to extend your primary group and you want to delete the old data you will have to implement some logic, assuming that the table contain all the same field for saving the data where data has to be filtered, I would create a script to run within a job. If you want to automate the script creation you can do something like:

SELECT 'DELETE FROM ' + '[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + '] WHERE SomeDateColumn >= DATEADD(m,-6,GETDATE())'

FROM INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'), 'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Re: Purging data off of an MSDE database.

Hemantgiri S. Goswami

Hi,

Travis H wrote:

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database

Top

Re: Purging data off of an MSDE database.

Jens K. Suessmeyer

Depending on your version and edition you could also use horizantal partitioning which would move the older records to the other partition on probably another file group.

HTH, jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Top

Index

The team •