Help Problem On SELECT Cycle Backward

May 29, 2008

need help on logical SELECT cycle



Code Snippet
SELECT empID, unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, unit_date, GETDATE())) % 4, 0), 4) AS new_unit
FROM dbo.empList




i have this code
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
IT OK

but

if i put
unit_date = 01/04/2008
and unit=1
than i get new_unit=2

but it should be 3
it dont go backward ok



if i put
unit_date = 01/06/2008
and unit=1
than i get new_unit=4

but it should be 2
it dont go forward ok !

TNX

View 1 Replies


ADVERTISEMENT

Error Log Cycle

Mar 10, 2005

Quick question (I hope) regarding the cycling of the sql server error logs.

I am looking at implementing a daily / weekly job to run the stored procedure "sp_cycle_errorlog", but was wondering if there was any way of stopping the sql server process cycling the logs automatically when it is started? Therefore the logs will only be cycled when the job runs.

Many thanks.

View 1 Replies View Related

Counting Records Using A WHILE Cycle

May 27, 2015

I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:

Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END

how could I build this query or what better way of doing the job there are...

View 1 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 2 Replies View Related

52 Week Backup Cycle...

Sep 13, 2007

Hi

uh

im on the blag....

Can someone help me with a script for the following?

7am tuesday, week 1 of the year:
full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.

7am Tuesday, week 2 of the year:
exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..

ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.

help me obi-wan SQLee. cos I cant bloody do it....

Thanks

Alastair
Methodology



"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

View 6 Replies View Related

How Can You Stop And Cycle Through A Column(s) Of Data?

Jun 16, 2007

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

View 3 Replies View Related

SQLAgent Doesn't Cycle Log Gracefully

Jan 26, 2007

Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog" once a week to cycle the SQL Server Agent log. Job is owned by "sa".

Most servers run this with no problem, but one active node of an active/active SQL Servers cluster fails with the message:

Executed as user: DOMAINSQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.

The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:

(Date/Time)+ [412] Errorlog has been reinitialized. See previous log for older entries.

If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. Failing over has no effect.

I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)

View 14 Replies View Related

Query To Show Life Cycle Revenue?

Jan 2, 2007

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table  and a sales table joined by custno.
SELECT     Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM         Customer INNER JOIN                      Sales ON Customer.CustNo = Sales.CustNo
 The output I'd like is
CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount
Getting the first inv date seems straight forward
SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo
However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.
 Can anyone point me in the right direction?
 

View 2 Replies View Related

Display Flag For OrderType Cycle Values

Aug 9, 2015

I have a table with EquipmentNumber, OrderType, BreakdownIndicator and RowID columns and below are the values for one equipmentnumber,

EuipmentNumber OrderType BreakdownIndicator RowId Flag
145812 PM04 1 Yes
145812 PM07 2 Yes
145812 PM07 3 Yes
145812 PM04 4 Yes
145812 PM07 5
145812 PM07 X 6
  145812 PM07 7
  145812 PM04 8 Yes
145812 PM04 9 Yes
145812 PM04 10 Yes

I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,

1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.

View 12 Replies View Related

Transact SQL :: Get Start And End Date Of Billing Cycle

Oct 9, 2015

I have weekly billing cycle. And the billing cycle can start on any day of the week.

Assumption: Start day of week is MONDAY

Hence, the following

DayNumber Day
1 Monday
if start day is 1, then billing cycle is Monday - Sunday
2 Tuesday
if start day is 2, then billing cycle is Tuesday - Monday
3 Wednesday
if start day is 3, then billing cycle is Wednesday - Tuesday
4 Thursday
if start day is 4, then billing cycle is Thursday - Wednesday
5 Friday
if start day is 5, then billing cycle is Friday - Thursday
6 Saturday
if start day is 6, then billing cycle is Saturday - Friday
7 Sunday
if start day is 7, then billing cycle is Sunday - Saturday

For a given date, i need find start date and end date of current billing cycle.

For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015

View 6 Replies View Related

SQL Backward Compatibility?

Apr 26, 2007

I get the impression that osql (or somewhere in the sql processing) precompiles the entire script before it executes anything. In particular, this is a problem because it means you can't use IF statements to bracket new features in a script designed to be run on both old and new versions of SQL Server. I'm trying to handle an issue whereby I need to use "CREATE LOGIN" on SQL Server 2005 because I need to set CHECK_POLICY = OFF, and you can't do that with sp_addlogin. However, on SQL Server 2000, while I can't use CREATE LOGIN, I don't need to because the default password policy is such that the password being used does not fail without it (as it does in SQL Server 2005, and is why we need to set CHECK_POLICY), so I can simply use sp_addlogin to create the user w/o a CHEC_POLICY setting.

It appears however, due to the way that SQL is processed, it is impossible to create an SQL script of this nature that will work under both SQL Server 2000 and SQL Server 2005. I added code to check the Product Version, and can successfully bracket the code necessary with IF statements, but even though the IF statement would cause the CREATE LOGIN code to not be executed on SQL Server 2000, it errors anyway apparently because it is preparsing the script and of course, SQL Server 2000 doesn't have CREATE LOGIN. Consequently, checking Product Version is useless in this case. It looks like we'll have to do the version check outside of SQL and invoke script A for SQL Server 2000 and script B for SQL Server 2005.

Unless that is, I misunderstand the error I get from SQL Server 2000, or if there's some other way to compatibly do such a conditional. Here's an example script that runs fine under SQL Server 2005:

---------------
declare @ProductVersion as integer
set @ProductVersion = cast(left(cast(serverproperty('productversion')
as varchar(30)),1) as integer)

print 'Product Version = ' + cast(@ProductVersion as char)

IF @ProductVersion < 9 exec sp_addlogin 'testuser', 'fubar', 'master'
IF @ProductVersion > 8
BEGIN
CREATE LOGIN testuser WITH PASSWORD = 'fubar',
CHECK_POLICY = OFF, DEFAULT_DATABASE = [master]
END
----------------

On SQL Server 2000, @ProductVersion gets set to 8, but I get the following error:

Msg 170, Level 15, State 1, Server TESTSVR, Line 10
Line 10: Incorrect syntax near 'LOGIN'.


Any thoughts?


--

Sync



--

Sync

View 1 Replies View Related

Backward Coompatibility

Jul 16, 2007

We have two different machines in different locations running SQL2000. One was upgraded to SQL2005. The problem is we can't upgrade the other at the moment cause it is running SBS2003 (requires alot more planning), But we would like to get information from the computer running SQL2005. Is there a patch that can be obtained so that we can open the database created using SQL2005 in SQL2000? We desperately need the database and upgrading to SQL2005 with SBS2003 is not straight forward.

View 2 Replies View Related

Backward Compatibility Details

Jul 14, 1999

Does anyone know if the enhanced data types (character length up to 8,000 bytes for some types) and the increase in the number of tables used in joins are available when using the 65 backward compatibility mode?

Many thanks in advance...

View 1 Replies View Related

Working With Backward Compatibility

Apr 12, 2007

Microsoft Authority,

As our application is going to be closed in few months and for testing purpose

we decided to continue running dts packages in SQL server 2005. As part of upgrade we are migrating all others( Databases, OLAP and Reporting Services). We have created SSAS 2005 cubes which we want to process using Analysis Services 2000 Processing Task in DTS. Is this possible? If not possible, Our organization need a confirmation from a Microsoft manual that it cannot.

The link about backward compatibility describes about using SQL 2005 datasources in DTS and editing Analysis Services Processing Task with SQL Server 2000 Analysis Services installed. However it does not mention anything about support for processing SSAS 2005 cubes with the help of Analysis Services 2000 Processing task. Would you please provide a link which gives a description about this.

Many Thanks in advance for assistance..

Subhash Subramanyam

View 3 Replies View Related

Rowset Does Not Support Fetching Backward.

Dec 29, 2007

Hi,

I get the following error.
Err.Description - "Rowset does not support fetching backward."
Err. Number - -2147217884

Can somebody tell me the reason behind this error.

My Reasearch: I have got to know that I get this error in the line of code recordset.MoveLast in My code.

My Code environment: I have built an SQL Query string which calls the Strored procedure with one parameter (parameter tyoe is string type). I have attached the SP code below.

and SP contains the call for querying other database in the same server using OPENQUERY command.

and the VB code contains code as Recordset.MoveLast. (Error is generated here and Recordset.RecordCount is also -1)

SP code is:




Code Block
CREATE PROCEDURE [dbo].[sp_RMS]
@sRMS_Status CHAR(12)

AS
BEGIN
DECLARE @sSQL VARCHAR (1000)
SET @sSQL = '''SELECT *
FROM VW_JOKE
WHERE JOKE_ID = ''' + '''' + @sRMS_Status + '''' +
''' ORDER BY JOKE_NO''
EXEC ( 'SELECT * FROM OPENQUERY(lnk_joke__cat,' + @sSQL + ')' )
END



My Questions:
1). Is there any problem in SP as I am using OpenQuery? I queries the SP and concluded that it generates result properly.

2). Is there any problem with the Recordset.MoveLast line? I am using adOpenKeyset, adLockReadOnly as parameters to my Recordset.Open command along with other parameters.


Let me know the corrective steps to be taken to get rid of this error.

Thanks in Advance for your valuable time
Ranjan Jain

View 1 Replies View Related

How To Generate Date Backward From End To Start

Jan 16, 2008

how to generate date backward from end to start
like this
begin
-- loop to insert date backward
while
@end_date>=@start_Date
begin
INSERT INTO @tb_temp

from middle of the month to end of the month

serial date
------------------------------
1 19/03/2008
2 18/03/2008
3 17/03/2007
..............
19 01/03/2007


and put it in this part of code
tnx




Code BlockDECLARE

List_of_emp CURSOR FOR

SELECT

emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp

OPEN

List_of_emp

FETCH

List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate

SET @current = @StartDate

-----------------

-- loop on all in the list

while

@@Fetch_Status = 0

begin

-- loop to insert info of emp shifts

while

@current<=@EndDate

begin

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])


View 2 Replies View Related

Backward Compatiblity Of Management Studio

Dec 9, 2005

I'm relatively new to SQL Server.    I have spent some time in Enterprise Manager for our SQL Server 2000 instances.    We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.

View 1 Replies View Related

Find Within Report Will Not Search Backward

Jan 5, 2007

I have used SSRS since the original SQL 2000 beta, so am pretty experienced with it.

I think there may be a bug in V2.0 (2005) that did not exist in V1.0:

Have just started cutting over my V1.0 reports to V2.0 (2005). One of my users today showed me that the "Find" feature within a V2.0 report searches forward fine. In one case, a three page report, it found the first item on the third page. However, when the "Find" feature was used from that point to get something on the first page, a message was received that it could not find the item. After manually navigating back to page one, and re-entering the same data in "Find" from there, it found the data with no problem on page one.

Has anyone else seen this behavior?

Thanks, Tim

View 1 Replies View Related

How To Detect Backward Compatibility Pack If It's Already Installed Using C#?

Aug 29, 2006

We have our custom install code using C# for a huge Client desktop application. We package SQL Express 2005 along with Backward Compatibility pack as this application was using MSDE before and at 11th hour management decided to pack SQL Express and backward compatibility pack.

During installation we want to detect if the Backward compatibility pack is already installed to avoid overwrite. Can anyone suggest how to detect if Backward compatibility pack is already installed using C#? I am sure there should be some registery keys to look for but don't know which.

Thanks.

View 1 Replies View Related

SQL Server 2005: Backward Compaibility Errors

Nov 22, 2006

I am running a SQL Server 2000 enterprise default instance and SQL Server 2005 standard named instances on a machine. I uninstalled the default instance and tried to instance 2005 but was unable to with the error:

'an installation packages for the product Microsoft SQL Server Backward Compatibility cannot be found. Try the installation again using a valid copy of the installation packages sqlserver2005_bc.msi'



I have tried to repair the backward compatibility currently installed and have also tried runing the msi file again but it will not install.



I have also put back 2000 as the default instance. then tried to apply sp1 to the 2005 named instances and i cannot do that either. anyone have any ideas?



Thank you.



'



View 3 Replies View Related

How To Change The List Order In My Stord Prosege Backward

Jan 23, 2008

need help how to
change the shift order in my stord prosege backward
on the field "shifttype"
not like this
shifttype
---------------------------------------------------------
111111 2008-02-24 Sunday 1
111111 2008-02-23 Saturday 2
111111 2008-02-22 Friday 3
111111 2008-02-21 Thursday 4
111111 2008-02-20 Wednesday 5
111111 2008-02-19 Tuesday 6
111111 2008-02-18 Monday 7
111111 2008-02-17 Sunday 8
111111 2008-02-16 Saturday 1
111111 2008-02-15 Friday 2
111111 2008-02-14 Thursday 3
111111 2008-02-13 Wednesday 4
111111 2008-02-12 Tuesday 5
111111 2008-02-11 Monday 6
111111 2008-02-10 Sunday 7
---------------------------------------------------------------------------------------
i need it like this
shifttype
------------------------------------------------------
111111 2008-02-24 Sunday 8
111111 2008-02-23 Saturday 7
111111 2008-02-22 Friday 6
111111 2008-02-21 Thursday 5
111111 2008-02-20 Wednesday 4
111111 2008-02-19 Tuesday 3
111111 2008-02-18 Monday 2
111111 2008-02-17 Sunday 1
111111 2008-02-16 Saturday 8
111111 2008-02-15 Friday 7
111111 2008-02-14 Thursday 6
111111 2008-02-13 Wednesday 5
111111 2008-02-12 Tuesday 4
111111 2008-02-11 Monday 3
111111 2008-02-10 Sunday 2




Code Snippet
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int NOT NULL,
[ShiftType] int NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL
)
INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103) UNION ALL
SELECT 222222,2,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 333333,3,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,-1*spt.number,Enddate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)

--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id= 8 and DATENAME (dw,ShiftDate )='Saturday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts
--select datediff(ms,@g,getdate())






View 5 Replies View Related

SQL CE 3.5 Server Tools On Vista 64 - Where's IIS Backward Compatitlity Components

Mar 13, 2008

I'm trying to upgrade from the SCE 3.0 server tools to the 3.5 server tools. I've uninstalled the 3.0 tools and while trying to install the 3.5 tools I get an error on the IIS requirement indicating that with IIS 7.0 I need to install the backward compatibily components and can not proceed with the install.

Searching Microsoft I get hits that mention the IIS backward compatibility components, but no links on where to download them. This might be a useful link to add to the sticky with the downloads for SCE 3.5. Also, one of the requirements referneces on the SCE3.5 server tools download, indicates that the backward IIS compatibility components were supported on 32 bit OSs, no mention of 64; this also has me concerened.

I'm running Visual Studio 2008 and SQL Server 2005 on my workstation. My device has SCE 2.0, 3.0 & 3.5 on it; these all are working fine. I'm trying to upgrade everything to 3.5 so I can get rid of prior versions. From within VS2008 I can connect to SCE3.5 SDFs and have updated a couple of databases to 3.5. What I want to do is connect to the 3.5 SDFs from SQL Server 2005; which is why I am trying to install the server tools.

I've got SQL 2008 installed on a VM and am able to connect to 3.5 SDFs but I'm trying to avoid the hassle of firing the VM and schlepping files across the VM boundary to work with the 3.5 SDFs. It's becoming a pain. Which is why I am trying to install the server tools.

Is there a resolution to this? Can I get the server tools installed on a 64 bit Vista box? Is there a link for the backward compatibility tools?


Steve

View 8 Replies View Related

MSSQL 2005: Backward Compatibility Problem With Database Owner

Nov 25, 2005

Hello all,i'm moving all my development to the new 2005/Fw2 environment.Now, i have an MSSQL 2005 server on the development server. To restore an existing database, i've created a new database with MSSQL 2000 compatibility level, and i've restored the backup over it. Everything seems ok, but when i try to create a diagram, i get the following error message:"Database diagram support objects cannot be installed because the database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."I've checked the properties and there seems to be the right owner (sa). If i create a new database with 2005 compatibility level, everithing works fine.Where is the problem?Also, a side question: by setting compatibility level to 2000, is it correct that the database can be deployed to a 2000 Server environment with no problems?Thanks in advance for any advice. -LV

View 4 Replies View Related

Backward Compatibility Upgrade Failed On Service Pack 1 For SQL Server 2005

May 19, 2006

Not sure if this gets fixed on a restart, but the Backward Compatibility upgrade/piece failed on service pack 1 install for SQL Server 2005. Here is the log output. Anyone experience this?



05/19/2006 08:58:27.648 ================================================================================
05/19/2006 08:58:27.648 Hotfix package launched
05/19/2006 09:00:04.368 Attempting to install instance: SQL Server Native Client
05/19/2006 09:00:04.383 Attempting to install target: CLARITY
05/19/2006 09:00:04.383 Attempting to install file: sqlncli.msi
05/19/2006 09:00:04.399 Attempting to install file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi
05/19/2006 09:00:04.399 Creating MSI install log file at: C:WINDOWSHotfixRedist9LogsRedist9_Hotfix_KB913090_sqlncli.msi.log
05/19/2006 09:00:04.415 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
05/19/2006 09:00:04.415 Failed to read registry key: Debug
05/19/2006 09:00:51.040 MSP returned 3010: A restart is required to complete the install. This message is indicative of a success. This does not include installs where the ForceReboot action is run. This error code is not available on Windows Installer version 1.0.
05/19/2006 09:00:51.118 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
05/19/2006 09:00:51.118 Failed to read registry key: Debug
05/19/2006 09:00:51.118 Pending reboot, successfully installed file: \<server>s$917f50731dade382ab1bHotFixSqlncliFilessqlncli.msi
05/19/2006 09:00:51.134 Successfully installed target: <SERVER>
05/19/2006 09:00:51.134 Successfully installed instance: SQL Server Native Client
05/19/2006 09:00:51.134
05/19/2006 09:00:51.134 Product Status Summary:
05/19/2006 09:00:51.134 Product: SQL Server Native Client
05/19/2006 09:00:51.134 SQL Server Native Client (RTM ) - Reboot Required
05/19/2006 09:00:51.134
05/19/2006 09:00:51.134 Product: Setup Support Files
05/19/2006 09:00:51.134 Setup Support Files (RTM ) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Database Services
05/19/2006 09:00:51.150 Database Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150 Reporting Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Notification Services
05/19/2006 09:00:51.150 Notification Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Integration Services
05/19/2006 09:00:51.150 Integration Services (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.150
05/19/2006 09:00:51.150 Product: Client Components
05/19/2006 09:00:51.165 Client Components (RTM 1399 ENU) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: MSXML 6.0 Parser
05/19/2006 09:00:51.165 MSXML 6.0 Parser (RTM ) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: SQLXML4
05/19/2006 09:00:51.165 SQLXML4 (RTM ) - Not Applied
05/19/2006 09:00:51.165
05/19/2006 09:00:51.165 Product: Backward Compatibility
05/19/2006 09:00:51.165 Backward Compatibility (RTM ) - Not Applied
05/19/2006 09:00:51.181
05/19/2006 09:00:51.181 Product: Microsoft SQL Server VSS Writer
05/19/2006 09:00:51.181 Microsoft SQL Server VSS Writer (RTM ) - Not Applied
05/19/2006 09:00:51.181

View 5 Replies View Related

SQL2005 SP2 IA64 Fails Installing Database And Backward Compatibility Components.

Aug 30, 2007



Hi, pls can anyone help me out with this? I'm installing SQL2005 SP2 over an Itanium machine with W2003 SP2 but it keeps on failing on those features. Client components got installed with no problems. I've installed SQL2005-SP2 over x86 and x64 (not Itanium) machines many times before successfuly. I've been investigating this issue for several days with no luck so I'm a bit clueless at the moment. As I'm using Administrator account I think it is not file permission related.
Summary says "Unable to install Windows Installer MSP file".

let me know if further info is nedeed.

Many thanks.



View 3 Replies View Related

Keyboard Not Working (backspace And Arrow Keys) When Viewing 2000 DTS Packages From 2005 (backward Compatibility)

May 4, 2006

I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.

We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:

Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components

We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.

Does anyone know of a fix or a workaround? Any help would be greatly appreciated.

Thanks, Mike

View 7 Replies View Related

Help Me -CURSOR Backward Insert From End Date &&> To Start Date

Jan 14, 2008

need help
help me -CURSOR backward insert from End Date > to Start Date
how to insert dates from end to start
like this
SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008'
i explain i have stord prosege that create mod cycle shift pattern
and it working ok
now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1
so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008

tnx




Code Block
DECLARE
@shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL)
INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103)
-- create shifts table
declare
@empShifts
TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
SET @current = @StartDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN

View 4 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work

Apr 29, 2008

Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.

select distinct ProductType
from Product
order by ProductType

Any suggestion? thx


View 12 Replies View Related

DB Engine :: Unable To Select Data From A Table Even After Providing Select Access

Aug 28, 2015

I am unable to the access on table even after providing the SELECT permission on table.

Used Query by me :

Here Test is schema ; Card is table ; User is Satish

To grant select on Table

GRANT SELECT ON TEST.Card  TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST  TO Satish.

View 8 Replies View Related

SELECT-Using Correlated Subqueries: Just Name In Results &&amp; 0 Row Affected In One Of MSDN2 SELECT Examples

Jan 11, 2008

Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name = 'Long-sleeve logo jersey') ;

GO

-- OR

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name = 'Long-sleeve logo jersey') ;

GO

=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved