Forward Looking Thought: Raw Files
Oct 6, 2006
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
Phil
View 5 Replies
ADVERTISEMENT
Feb 17, 2014
1. Created a database with a couple of tables with no data.
2. Taken a full database backup - db.bak.
3. Deleted the database.
4. Restored the database db.bak and filled the tables with some data.
5. Taken log backup - dblog.trn
BACKUP LOG <dbname> to DISK='D:Demodblog.trn' WITH NO_TRUNCATE, INIT
6. Dropped the database again.
7. Restored the database again from db.bak.
8. But when I am trying to restore log file dblog.trn on this database, i keep getting this error :
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2 RESTORE LOG is terminating abnormally.
View 2 Replies
View Related
Oct 30, 2015
I missed the ability to restore based on a time (10/23 6pm) due to our purge cycle in our production environment, but I was able to obtain the 10/18 full backup, the 10/23 differential backup, and the 4, 10/23 trans. log backups. I moved all the fore mentioned files to a staging environment, and now I am trying to restore all of the files to 10/23 6pm and I get :
"The log or differential backup cannot be restored because no files are ready to rollforward" error.
View 3 Replies
View Related
Nov 21, 2005
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
View 4 Replies
View Related
Apr 8, 2008
So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0
BEGIN
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors
WHERE StudentID = @studid AND MinorID = @minorid
END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorRequiredClasses](
[MajorClassID] [int] IDENTITY(0,1) NOT NULL,
[MajorDisciplineID] [int] NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MajorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = "Computer Science"
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
NEW QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
Let me know if you need more information...Hope someone can help!
View 4 Replies
View Related
Jul 14, 2006
I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!
The goal is to create the most efficient query that will display results of the daily calls and sales as
this:
Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL
The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
create table t1 (Date smalldatetime, EmpID int, Calls int)
create table t2 (Date smalldatetime, EmpID int, Sales int)
insert into t1
values ('7/1/2006', 1, 20)
insert into t1
values ('7/1/2006', 2, 25)
insert into t1
values ('7/1/2006', 4, 10)
insert into t2
values ('7/1/2006', 1, 5)
insert into t2
values ('7/1/2006', 2, 7)
insert into t2
values ('7/1/2006', 3, 1)
View 5 Replies
View Related
Feb 3, 2004
I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME
DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report
SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate)
FROM somewhere
WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
Thoughts?
View 3 Replies
View Related
Feb 7, 2004
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
View 3 Replies
View Related
Nov 15, 2007
So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>)
SqlParameter param = new SqlParameter();
param.ParameterName = "@return";
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value
int val = int.Parse(command.Parameters[0].ToString());
Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following
Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
View 2 Replies
View Related
Dec 29, 2003
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
AnyIdea
View 1 Replies
View Related
Sep 11, 2007
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
View 2 Replies
View Related
Jul 20, 2005
It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
View 3 Replies
View Related
Oct 22, 2007
Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
View 3 Replies
View Related
Jun 5, 2007
I am attempting to duplicate a nifty feature that one of my colleagues used:
We have parameters where a user can select a client or an individual account. There are available values for each parameter from two queries. I tried to have the available values in the account list dependent on the user's selection in the client list by passing the client parameter's value to the query for the account data set and using it in a where clause, but I get a "forward dependency" error.
The weird thing is that my colleague tried the same strategy and it worked. We together tried to set my report up the same as his, but cannot find why his works and mine doesn't. Any ideas?
His report works when deployed from my machine, so there must be something in the actual report that is different...
View 1 Replies
View Related
Apr 3, 2008
Hello all - I am trying to come up with a reasonable solution to an intermittent network. Specifically, I have a SQL Server 2005 installation that receives data from a client across a wireless network via OLE-DB. There are times when the network connection may not be available, and I would like to buffer the transaction (somehow!) locally, and then have it passed to the database upon re-establishing connectivity.
Any suggestions or recommendations would be greatly appreciated!
Thanks!
View 1 Replies
View Related
Jul 12, 2004
Does anyone know how to go back 1 record?
I know how to go forward 1 record:
Code:
SELECT TOP 1 *
FROM MyTable
WHERE [id] > 27
ORDER BY [id]
This will select the record with the id 28, if id 28 does not exist, then it will select the next available id.
however, then I try:
Code:
SELECT TOP 1 *
FROM MyTable
WHERE [id] < 27
ORDER BY [id]
It goes to the very first record in the table, becuase it selecting the TOP 1 record, How do I only go back 1 record, eg. id 26.
View 2 Replies
View Related
Jul 23, 2005
Hello All,DDL Statements:CREATE TABLE [dbo].[Table1] ([MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOI have a varchar column which represents dates in YYYYMMDD and MM/DD/YYformats. If I query using:SELECT MyDate FROM Table1WHERE DOB LIKE '________'Why aren't the dates in MM/DD/YY returned ? Is the / a specialcharacter in T-SQL ?Thanks in advance
View 1 Replies
View Related
Jun 15, 2007
Hello, we are trying to find a way to send all SQL traffic from multiple machines in a DMZ trough only one point. This way the firewall doesn't need to be opened to each and every machine.
This may be a simple question but I have never done it before. Any recommendations?
Thanks in advance.
View 4 Replies
View Related
Feb 19, 2008
im having trouble getting this to work:
alter proc [ProGeneral_College_Structure] @Year nvarchar(4)
as
begin
DECLARE @SQLStatement nvarchar(1000)
Set @SQLStatement = 'SELECT School AS Level1Code, DIVISIONS.Div AS Level2Code,
DIVISIONS.ProgArea AS Level3Code, DIVISIONS.progName AS LevelName
, ' + SUBSTRING(@Year,1,2) + '/' + SUBSTRING(@Year,3,2) + ' AS AcademicYearID FROM DIVISIONS
WHERE (((DIVISIONS.[' + @Year + '])=1))
ORDER BY DIVISIONS.School, DIVISIONS.Div, DIVISIONS.ProgArea'
EXEC(@SQLStatement)
end
It's something to do with the / concatenation I think, is it an escape character or something tried // obviously and CHAR(47).
before I get comments I know it's dynamic sql and it's not great but I can't edit the divisions table so have to use a dynamic column.
View 11 Replies
View Related
Jul 20, 2005
When you restore a backup from a point in time, how do you then knowwhich transaction ID to start with when you want to roll forward fromthat point in time to another point in time?
View 1 Replies
View Related
Jan 12, 2007
I want to set a Report Parameter on a field. The Report Parameter is called 'filter'. In the statement I put the Report Parameter in the WHERE-part:
WHERE ([DatabaseName$TableName].[FieldName] = @filter). After this I set the 'Available values' on the Report Parameter in the lay-out to Non-queried.
When the report is running, no problems.
But.....
Now I want to set 'Available values' on 'From Query' and refer to the data set, so the user can choose on which value he want to filter. But now, after running the preview the following error displays:
Error 1 [rsInvalidReportParameterDependency] The report parameter €˜filter€™ has a DefaultValue or a ValidValue that depends on the report parameter €œfilter€?. Forward dependencies are not valid.
Why can't I set the Report Parameter to 'From Query'? Anyone any suggestions???
(you can see the rest of my statement here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1098540&SiteID=1)
Thx a lot of helping me out with this topic.....
View 8 Replies
View Related
Jun 4, 2007
I am trying to get Event Forwarding to work between two SQL 2000 servers. Both are running SQL 2000 Enterprise SP4 and Windows 2003 Enterprise SP2. I am managing them from my local desktop running XP. I have both servers registered in Enterprise Mgr.
<Server XInstance Z> (named instance) is the server on which I have a MAPI client installed and configured along with SQL Operators and SQL Alerts. <Server Y> (default instance) is the server from which I want to forward events > Sev 17.
<Server XInstance Z> is set up to receive error msgs 9002 and 1105 and email operators that either a log is out of space or a data file is out of space. It has been verified on <Server XInstance Z> that it will send locally generated errors to the operators correctly.
<Server Y> is set to forward to <Server XInstance Z> any event that is sev 17 or greater. However, when I simulate an 1105 on <Server Y> I get this error msg in its Application Event log:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 316
Date: 6/4/2007
Time: 11:37:55 AM
User: N/A
Computer: <Server Y>
Description:
Unable to open the eventlog on forwarding server '<Server XInstance Z>' (reason: Sockets error 11004).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I figured this may be due to the fact that SQLAgent is trying to forward a Windows event to another Windows server running SQL but there is no default instance of SQL Server. So I thought I would add a bogus entry to my Enterprise Manager that was just <Server X> with no instance name. Obviously, EM failed to connect but asked if I wanted to register anyway. I chose Yes. So I was then able to change the server in Forward To on <Server Y> to <Server X>.
That actually worked and it began forwarding error messages to to <Server X>. But the error messages are going to the System error log as opposed to the Application error log where I need for them to go. This is the message from the System error log on <Server X>:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: Disk
Event ID: 17052
Date: 6/4/2007
Time: 11:32:40 AM
User: N/A
Computer: <Server X>
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'MyTestTable' in database 'Northwind' because the 'PRIMARY' filegroup is full..
Data:
0000: 51 04 00 00 11 00 00 00 Q.......
0008: 0c 00 00 00 43 00 48 00 ....C.H.
0010: 41 00 52 00 4c 00 45 00 A.R.L.E.
0018: 53 00 53 00 2d 00 58 00 S.S.-.X.
0020: 50 00 00 00 0a 00 00 00 P.......
0028: 48 00 4f 00 55 00 34 00 H.O.U.4.
0030: 51 00 31 00 31 00 30 00 Q.1.1.0.
0038: 35 00 00 00 5...
I'm stuck here now. Anyone else run into this problem?
View 1 Replies
View Related
Nov 14, 2014
I have a client that I provide financial modelling services to (using Excel). They have a requirement to start capturing subscriber movements in their SQL DB. how the table should be set-up and how to extract the necessary movements report. This is largely so that I may include these components in some of the financial models that I am working on.
Subscribers are reported as follows:
Opening subs (the prior periods closing balance; or the sum of new sales at point of 1st entry)
+ New Sales (new subs)
+ Upgrades (movement from a lower product package to the associated package)
- Downgrades (movement to a lower product package from the associated package)
- Churn (subscriber losses)
Closing Balance
All transactions are captured against a specific product package, on a specific date (ymd), and for an associated platform (e.g. digital TV, broadband TV, cable TV).
I believe we only need to capture new sales, upgrades, downgrades and churn. And then used a SP to compile the movements behaviour as described above.
So perhaps the table would appear as follows:
Platform
Package
Date
Movement
Value
DTV
PROD 1
2014-11-02
New Sales
8
DTV
PROD 1
2014-11-02
Upgrades
1
[code]....
So I am assuming that given a table such as the above, we could write a SP to produce an output such as (note, below looks at monthly total so will not agree back to sample above which contains only 2 days):
Platform
Package
Movement
September
October
November
DTV
PROD 1
OPEN
600
676
776
DTV
PROD 1
New Sales
92
106
88
[code]....
how one is best to accumulate the balances given that the open date for any given reporting period is in fact an accumulation of all balances since day 1.
How does one typically capture this type of thing in SQL?
View 4 Replies
View Related
Mar 23, 2014
I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,
[Code] .....
View 5 Replies
View Related
Oct 2, 2014
A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?
View 6 Replies
View Related
Jan 24, 2007
Hi,
I am using the AutoFetch Option with Fast Forward Cursors with SQLOLEDB to access SQL Server 2005. This really works out but for the first I execute the query only.
But as I use parameterized statements, when I re-use the cursor, just re-binding new variables and re-executing it again, the AutoFetch does not work any more.
I noticed that thru the SQL Server Profiler. I see a sp_cursorfetch been called at the second time the cursor is re-executed.
Does anyone know how to work it out?
Thanks in advance.
Marcelo.
View 3 Replies
View Related
Mar 28, 2007
Hi!
I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, it means that mirroring works correctly.
My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :
PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;
Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:
Microsoft SQL Native Client error '80004005'
Cannot open database "MYDBNAME" requested by the login. The login failed.
This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.
When I swap server, I execute on server ServerB command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,
my IIS 6.0 application automaticly turn back to ServerA without any action on my side.
I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client
View 10 Replies
View Related
Aug 27, 2007
Hi
I am creating a report on a database where some of the table names start
with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in
my query, even though I am encapsulating the table name in square brackets
eg. [@table1]
I have several data sets in the report that i am using to populate valid
parameters. These datasets are all variations of queries from tables that
have @ as the first character.
When i then try to run the report i get an error message as follows:
"The report parameter pool has a default value or a valid value that depends
on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of
the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report
parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool
FROM OCRD INNER JOIN
OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN
[@SD_POOLCONTRACT] INNER JOIN
[@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry
= [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN
[@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo =
[@SD_CONTRACTS].DocNum ON
case when len(ocrd.fathercard) = 0 then ocrd.cardcode
else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode
WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND
(CRD1.AdresType = 'b')
ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
Any help would be appreciated.
View 1 Replies
View Related
Apr 3, 2008
Hey all,
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
Thanks!
View 8 Replies
View Related
May 11, 2006
Hi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
View 8 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Mar 13, 2008
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
View 3 Replies
View Related
