SQL Server SP And SSMSE Intelli Sense
Apr 5, 2008
How can I know my sql server 2005 express service pack? The same goes for SSMSE. I can't find these information in the about box.
What about intelli sense for queries in SSMSE? Is it there yet or should I wait for sql server 2008 for dev. (http://channel9.msdn.com/Showpost.aspx?postid=387069)? I haven't seen the video but does anyone know any details? When it will be available, is it part of the sql server 2008 CTP, will it be available in the Express edition?
Thanks in advance,
Shehab.
View 1 Replies
ADVERTISEMENT
Feb 27, 2008
Hi all,
In my SQL Server Management Studio Express (SSMSE)=>Object Explorer=>Databases, the "NORTHWND" database was screwed up and is deleted completely. But under the same Object Explorer =>Databases, there is a database "C:SSMSE-BookChapter12WINCH12NORTHWND.MDF". I need to use this "NORTHWND" database in the SqlConnection. How can I specify the "Initial Catalog" (or "database") in the New SqlConnection code statement (in the ADO.NET 2.0-VB 2005 Express programming)?
Should it be like the following:
Dim sqlConnection As SqlConnection = New SqlConnction("Data Source=.SQLEXPRESS; Initial Catalog=C:SSMSE-BookChapter12WINCH12NORTHWND; Integrated Security=SSPI;") ? Or what is the right code statement for connecting to it?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
May 2, 2008
Hey guys, I am new to the SQL server scene and I am having some trouble connecting to a remote SQL Server with SQL Server Management Studio Express. I have the username, password, IP address, and port necessary to log in but am apparently not plugging them into the correct places. Can anyone at all point me in the right direction or somewhere that I might at least find the answer? I appreciate any help that can be provided.
Thanks a bunch,
Gary
View 4 Replies
View Related
Nov 24, 2007
In order to fix a web application bug the developer told me to to edit a stored procedure on my hosted SQL 2005 server. The hosting company's support say I should be able to do this using SQL Server Management Studio Express. I installed SSMSE, but it seems to want to only want to connect to a local server. Can I use this utility for remote DB management, or is there something else I need instead?
Thanks for your help.
-Michael
View 6 Replies
View Related
Feb 11, 2007
Hi,
Sorry if this is the wrong forum.
I'm trying to connect to a remote server (on shared hosting) with Management Studio Express. I tried using it on Vista and it connects to the server and database perfectly. However, on Vista it gives the following error everytime:
TITLE: Connect to Server------------------------------
Cannot connect to sgc.gbdns.net.
------------------------------ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------BUTTONS:
OK------------------------------
However, the host obviously has SQL server to allow remote connections as I'm able to connect from XP.
Both the XP and Vista PCs are running Avast free edition (the same builds) and both are running Windows firewall with management studio allowed through. Can anyone help?
View 6 Replies
View Related
Apr 28, 2006
I am having trouble to use SSMSE to remotely connect SQL Express server using IPs other than the base IP of the machine.
Machine settings:
(1) Has multiple IP configured. Those IP are accessible remotely
(2) Firewall is not on.
SQL Express server configuration:
(1) TCP/IP and named pipe are on
Browser service state:
(1) running
(2) listening at 0.0.0.0:1434 (UDP)
SSMSE connection results:
(1) with <baseIP><instance>, connects OK
(2) with <otherIP><instance>, connection failed.
Both connections use exactly the same authentication information.
Testing results using telnet:
(1) telnet <baseIP> 1434, OK
(2) telnet <otherIP> 1434, OK
Does SSMSE actually only listen on the base IP even it shows to listen on all the IPs?
View 10 Replies
View Related
Feb 28, 2008
Hi
Apologies for this question
I have just found out i/we need to upgrade to the full version of this management tool to make use of SSRS for a new CRM we are putting in place, The CRM company tells me we can just install the tool from our SQL 2005 disk.
Im wondering what the best way to go about this is? i would guess we just un-install the express version install the full version of the management tool and reconnect to the database. unless im missing anything?
thanks
View 1 Replies
View Related
Apr 25, 2006
Hi,
I am having problem with remote connection via TCP/IP connection on default port 1433. TCP/IP connection are enabled on the server.
any possible solution ?
See problem below:
TITLE: Connect to Server
------------------------------
Cannot connect to ns1.iandigroupltd.com,1433.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 7 Replies
View Related
Jan 13, 2007
Here is the situation:
I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.
I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String Dim builder As New SqlConnectionStringBuilder Dim cmd As New SqlCommand Dim reader As SqlDataReader Dim parameter As SqlParameter builder("Data Source") = ".SQLEXPRESS" builder("Initial Catalog") = "" builder("AttachDbFilename") = "C:My DocumentsVisual Studio 2005Projectsabcabcabc.mdf" builder("Integrated Security") = True builder("User Instance") = True lconnectionString = builder.ConnectionString Dim sqlConnection1 As New SqlConnection(lconnectionString) cmd.CommandText = "SP_add_collection" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open()
*******************************************************************************************************************
It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:My DocumentsVisual Studio 2005Projectsabcabcabc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )
If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()
I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?
Thanks very much !
apple
View 4 Replies
View Related
Feb 25, 2008
Hi all,
I ran the following CTE sql code:
Use ChemDatabase
GO
WITH PivotedTestResults AS
(
SELECT TR.AnalyteName, TR.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)
FROM TestResults TR
JOIN Samples S ON TR.SampleID = S.SampleID
GROUP BY TR.AnalyteName, TR.Unit
)
SELECT AnalyteName, UnitForConc,
avg1 = abs(Prim + Dupl) / 2,
avg2 = abs(Prim + QA) / 2,
avg3 = abs(Dupl + QA) / 2,
RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,
RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,
RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2
FROM PivotedTestResults
GO
//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Unit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Unit'.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?
Please help and advise.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
Mar 18, 2008
I have inherited a SQL 2005 server with a few small databases on it. There's a maintenance plan here that doesn't seem to make a lot of sense to me. Can anyone comment:
Every Sunday at 4:00 AM
1. Reorganize index on All user database Tables and Views - compact large objects.
2. Rebuild index on local server connection, All user databases, Tables and view, Original amount of free space.
3. Shrink database. All user databases. Limit 100MB.
I'm confused a little about item 3. Won't a shrink be kind of useless after all of the work that goes on in steps 1 and 2. When I ran this manually, the transaction logs jumped significantly.
Thanks in advance for your help.
Dean
View 5 Replies
View Related
Jun 7, 2007
Hi all,
I have installed SQL server 2005 express edition with SQL server Management studio express (SSMSE).
I have installed also SQLce and I would know how I can manage sql compact edition databases (.ldf) with sql SSMSE, if it is possible!
I would be grateful if you could explain it trought a detailed guide (I am a beginner user of sql server tools) or trought a link to useful sites.
I'm Looking forward for your reply and still trying...
Alessandro
View 1 Replies
View Related
Feb 20, 2002
l've written a cursor to koop through a table and then insert the last 100 records into a table.Reason why l want the last 100 records is to monitor and log the last 100 trans avery hr or so.
-- Declare the variables to store the values returned by FETCH.
SET ROWCOUNT 100
DECLARE
@customer_No char(15),
@loan_No char(12),
@date_Issued datetime ,
@maturity_Date datetime ,
@status int
DECLARE loan_cursor CURSOR FOR
SELECT customer_No,
loan_No,
date_Issued,
maturity_Date,
status
FROM loan
OPEN loan_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM loan_cursor
INTO @customer_No,
@loan_No,
@date_Issued ,
@maturity_Date,
@status,
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM loan_cursor
INTO @customer_No ,
@loan_No ,
@date_Issued ,
@maturity_Date ,
@status
END
CLOSE loan_cursor
DEALLOCATE loan_cursor;
insert into Loan1
(customer_No,
loan_No,
date_Issued ,
maturity_Date ,
status
)
select @customer_No,
@loan_No,
@date_Issued,
@maturity_Date,
@status
FROM loan
ORDER BY date_Issued desc;
View 3 Replies
View Related
Jun 23, 2006
I need any one's advice/imput on this...PLEASE!My computer will now begin the process of taking all the MS Access (NativeJet Engines - x30 total departmetns) and put the tbles/BE on SQL Server 2005and the Ms Access FE on MS Sharepoint.This is the kicker, say 20 out of the 30 (ball park) was created by oneperson and that is their whole job function was to create/maintain a QAtracking system and more.The person who created the 20 out 30 only knows intermediate ms access andsome vba, but NOT sql or net conversions (visual studio - all the differentlanguages), so the IT director asks me (I develop in MS Access andintermediate in VBA and can create web sites using publisher, front page andHTML) he asks me and this other person if we want to take on the challenge ofhelping him and the other IT guy in the conversion process of all of thesedb's.What does this do the developers who developed and still maintain thesecurrent 30 ms access db's, well you guessed it, it now takes all that hardwork that those developers did and still do (they still add more forms,updates) and it NOW takes the databases owners away from them and grant itnow to the person (s) who will maintain SQL Server 2005 ( I hope will be aDBA)???Is this true, once all the databases are converted, the owners will no longerbe able to go behind the scenes in tables, queries,etc.... It will now be inthe hands of a DBA?You know the funny thing is the IT Director wasn't even sure if he was goingto hire a DBA, who in the heck will maintain all of those db's on the server?There is only one other guy and he certainly does not have the training orskills or TIME.MY POINT QUESTION IS:when these conversion take place like this at a company, most of the time thems access dbs that have now be put into sql will now take the ownership awayfrom the owner (they cannot develop no more, unless they are sql friendly/dba)and put all of that into one persons hand (DBA) to maintain and development??????--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200606/1
View 4 Replies
View Related
Apr 14, 2008
Thanks to several guys here, I now understand how SQL Server configuration option works... Pretty nifty stuff.
Now, I'm trying to see if I can configure the Server property of the Connection Manager that holds the information for where my configuration table is. I thought about this and tried it, but it doesn't work. Then it occurred to me, this may not make sense to try to do because it is like the question, "what came first? the egg or the chicken?"
Am I making sense?
View 5 Replies
View Related
Jan 2, 2008
I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.
I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY.
This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML".
The generated query looks a little like :-
Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK
from DOCS DOC
inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID
inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID
inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID
This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....
My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest.
e.g. for the example
for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36
for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....??
This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong?
If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .
This question is about rankings returned from the ISABOUT function.
In the following example,
select * from documents as DOC
inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key"
inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key"
order by doc0.rank desc
The values I get from the doc1/2/3/4.RANK columns dont seem right.
In this example,
doc0.rank = 133
doc1.rank = 150
doc2.rank = 330
doc3.rank = 924
doc4.rank = 0
These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?).
Once again, is there something I missed or am I understanding it wrong?
Thanks in advance for any help into understanding the whys of this...
View 1 Replies
View Related
Jun 8, 2007
I am new to SSIS. i am trying to port database from SQL SERVER 2000 to 2005. i am using "Transfer SQL Server Objects" for this. i am just trying to move one object for testing wether it works or not. and it is not working. i am getting this error.
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.consta_AE'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Both DBs are on seprate machines. of you need any more info please let me know. that would be great help.
Thanks
View 8 Replies
View Related
Feb 27, 2007
We have an SSIS package that was created to migrate data in from a few production databases. The steps for the package are as follows...
backup databases on server 1 (prod database server) restore database to SSIS server (server 2) .
truncate worker tables in SSIS server's (server 2) Main DB database.
copy data from restored db tables to working db tables ( database to database)
Start Multiple threads (15 ) and run steps from here in parrallel
Combination of Data flow tasks and SQL scripts and Stored procedures used to flatten data out and combine data for reporting purposes.
The average run time is 8 hours.
the issue we are seeing is this, the package will fluctuate in run times from 4 hours to over 11 with no change in the data or the underlying SSIS package. We have looked for any changes or things that would effect this but have not found anything that changed...
Also, certain steps are running shorter while others double in time. there doesnt seem to be any rhyme or reason to this behaviour. The server is x64 12GB of RAM 2 dual core 3.2Ghz.
Please let me know if you need any more information or specifics...
the only thing I have seen so far that looks out of place is Tempdb has one of its files that is 20+GB.
Thanks,
Chris
View 4 Replies
View Related
Feb 5, 2008
Dear all,
We started to develop a datawarehouse solution for client back in December 2008 based on SQL 2008.
We are convinced that we can use some of the new feature included in the new version which is the reason we we chossed to go down this path.
Due to the delay of the next version we have some question that I would like to hear you opinion on.
The estimatet "go live" date is during spring (march/april)
Is it wrong to contiue the development on the 2008 version?
We would need to run Release candidate at the customer site until the product is released. Is there any major changes coming up that are already known?
So far in the development we have had no big problems with releasecandidate.
Thank you for you feedback.
View 4 Replies
View Related
Sep 21, 2007
I have a weird problem.
We are importing a very small subset of a big desktop database into a CE database on a mobile device for an occasionally connected application. The idea is that the mobile device can use this CE database as a fall back database in case we are not connected.
The database is a very simple list of barcodes.
Basically a single field as primary key
EAN13 bigint
When we import 200K rows (yes we have quite a lot of them). The database is 7MB!!!! A bit big I would say since 8 bytes times 200.000 is only 1.5 MB. Where does the extra space come from?
Yes I did compact the database after the import
View 1 Replies
View Related
Aug 22, 2007
Hi Guys,
I've been programming with SQL 7 for about a year and my company has finally decided to go SQL 2k5.
I've come accross a really irritating error when writing to the DB via ADO in ASP pages. I have a column in a table that is auto-incremental.
In SQL server 7 you just make an ADODB.Command object and enter the SQL query 'insert into table (columns) values ('val...') now for SQL 7 I can completely leave out the auto-incremental column (called 'ErrorNo') and simply specify the other columns and values in my insert query.
e.g. where my table is called master_error:
ErrorNo int identity (1, 1) not null
,ReportedBy char(10)
,ExpectedFixDate datetime
With ErrorNo being an auto-incremental identity, my query would be
INSERT INTO master_error (ReportedBy, ExpectedFixDate) VALUES ('Ben','01 Sep 2007')
this works perfectly with ADODB.Command when writing to SQL Server 7 from IIS 5.0
however when I execute the exact same command on the exact same table using ADODB.Command writing to SQL Server 2005 from IIS 6.0 I get an 'error 500 internal server error'
IIS log states:
|166|80040e14|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]Cannot_insert_the_value_NULL_into_column_'ErrorNo'__table_'ReACToR_V1.0.dbo.master_error';_column_does_not_allow_nulls._INSERT_fails. 80 - 192.168.78.159 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30) 500 0 0
I thought perhaps SQL 2005 might have different syntax so I typed the query directly into SQL Server 2005's version of query analyser and guess what... it worked fine.
I can't tell where the error lies. I find it hard to beleive that the error is in the code of my ASP page as it works perfectly against a sql 7 db.
Any ideas?
Many Thanks,
Ben Ward
View 8 Replies
View Related
May 3, 2006
I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.
I keep receiving the following error:
Server: Msg 8115, Level 16, State 8, Line 140
Arithmetic overflow error converting numeric to data type numeric.
The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.
Any help would be appreciated. Thanks.
Code below:
-------------------------------------------------------------
declare @dtAsOfdate DATETIME
set @dtAsOfDate = '2006-04-16';
DECLARE @RC INTEGER
-------------------------
-- 1) Eligible Investments:
-------------------------
-- Input: @SPVId - SPV we are running process for
-- @Yes - value of enum CCPEnum::eYesNoYes (get by lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).
DECLARE @Yes INTEGER
EXEC @RC = [dbo].CPLookupVal 'YesNo', 'Yes', @Yes OUTPUT
IF (@RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN
SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @Yes
END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok
--------------------------------------------------------------
-- 2) Calculate Weighted Average Price change ratio Market Value (by Group):
-- PCRMV - Price Change Ratio Market Value
--------------------------------------------------------------
-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN
SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,
cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9)) AS OCRate,
CAST ('' AS VARCHAR(6)) AS OCRateType,
CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2)) AS InterestAccrued
INTO #MVTriggerGroup
FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod ,
cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue,
GroupId
FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B
ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId
END
print 'end #2'
---------------------------------------------
-- Calculate OCRate to apply for each group.
---------------------------------------------
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
-------------------------------------
-- Calculate discounted Market Value
-------------------------------------
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
---------------------------------
-- Insert data from temp tables
---------------------------------
-- 1)
select * from #MVTriggerInvestments
print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio
FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRAN
View 4 Replies
View Related
Aug 28, 2007
I've looked at the other threads on this topic, and don't see an answer to the following question:
Why should an error destination time out waiting for error rows?
I'm using SQL Server Destinations both for my staging tables and for my "Error Staging" tables. Yet it seems that these are timing out if the package runs a long time without any error rows. This leads to two questiosn:
Why should an error destination time out waiting for error rows?
I can solve this by setting the timeouts to some very large number. But, is there a better way to do this?
Right now, if the package takes five minutes, I need to set the timeouts to longer than five minutes. That does not sound like a good idea.
View 7 Replies
View Related
Apr 24, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.
Thank you very much for your advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Mar 12, 2007
In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.
A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.
Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):
This issue occurs if the following conditions are true: €¢The workbook contains a PivotTable that uses key performance indicators (KPIs).€¢The KPIs are created in the Analysis Services Business Intelligence Development Studio.€¢One or more of the KPIs have an expression in the Current Time Member property.
Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?
Any thoughts?
View 6 Replies
View Related
Feb 17, 2006
Getting ready for a system deployment tomorrow and looked at the Teratrax Management tool instead of the Microsoft SQL Server Management Studio Express (SSMSE). Teratrax tool is nice (adds more features for scripting and file management) but thought we'd opt to stick with the free SSMSE for now.
Went to run SSMSE and it doesn't run. Pointer flickers once and then nothing happens. Okay, uninstalled Teratrax and tried SSMSE. No change. Uninstalled and re-installed SSMSE, followed by a reboot. No change.
Argh! Does anybody have any suggestions on what to look for to get SSMSE back in gear? It make life much easier for the green admin like myself and we need it for the interim.
Daggumit, frustrating!
Thanks in advance of any advice or input provided.
Mmmmmkay. Yeah, did you get the memo about the TPS reports?
View 1 Replies
View Related
Apr 25, 2006
Is SSMSE Redistributable?
View 1 Replies
View Related
May 31, 2007
can anyone tell me how to open/create a SQL Server 2005 Compact Edition database using SQL Server Management Studio Express?
cheers
View 5 Replies
View Related
Apr 18, 2008
Hello. I just started using SQL Server Management Studio Express. I am creating a new table by right-clicking the "Tables" folder and selecting "New Table". Then i hit F4 to bring up the "Properties" (because I want to associate a Schema to the table, but when the "Properties" section appears, it's blank. I can't figure out a way to get anything to appear there. Any ideas? Thanks!...
View 2 Replies
View Related
Feb 18, 2006
I have installed SQL Server 2k5 Express on my local machine. I also installed SSMSE on my local machine. When I try to launch SSMSE and connect to the SSExpress I get an error indicating that SSExpress is not configured to accept remote connections. Huh? Everything is local...I'm confused and frustrated. Any help is profusely appreciated.
Thank you
Zach
View 2 Replies
View Related
Jan 23, 2007
What's the difference between "SQL Server Management Studio" and "SQL Server Management Studio Express"? Is there any link to explain this?
Thanks,
Canada DBA
View 4 Replies
View Related
Jun 2, 2007
Using
SQL Server Express with advanced Services
Visual Web Developer 2005 Express
Microsoft Visual C# 2005 Express
SQL Server Management Studio Express
.NET 2
ASP.NET
Problem
After using SSMSE on an IIS ASP.NET database created with VWD this error starts.
"Cannot open user default database. Login failed.
Login failed for user 'machinenameASPNET'. "
If I never use SSMSE, I never have a problem.
I have tried every single suggestion I could find and none of them solve the problem.
Luckily, I am only in the development stages so at least I'm not loosing data.
Development Path
Create VWD Project
Create Database and Tables
Write WebService for DB access
Create C# Project
Connect to WebService
Write project code
Use SSMSE
Attach Database
Do non-admin fuctions on the database(I only opened it to get the auto-generated code for sql statements such as creating Columns so I could do it on the fly)
Detach Database - Check mark the Close Connections
Back to the C# Project
Run the code.
Cannot open user default database. Login failed.Login failed for user 'machinenameASPNET'.
What is SSMSE doing to the database that the web service can no longer log into it?
From VWD you can still connect from the Database Tab.
I finally got so fustrated I just got the SQL Code to create the tables and made a function to create them
after I delete and recreate the database.
View 6 Replies
View Related
Apr 10, 2007
When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.
Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!
Can anyone advise if I have missed something and there is a way to import easily using SSMSE?
Thanks.......in hope
View 6 Replies
View Related
