|
|
vkstarry wrote: YYYY/MM/DD
always works.
vkstarry wrote: MM/DD/YYYY
might work depending on your date settings.
|
|
|
|
|
Hi . I have written an assembly in which it has two user defined functions . In sql when i create the assemblies , it does not make any errors even when i create my first function again i do not have any errors but when i create the second function , the server computer has this error , the most intresting thing for me is that i have installed all of them in another computer and it worked but in my server it has this error
can you help me ?
the error is :
" Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008. "
|
|
|
|
|
googling for Failed to open malformed assembly with HRESULT 0x80070008 turned up this[^]. Check your disk!
|
|
|
|
|
Hi
I need help in writing a SQL query that gives the difference in values for two consecutive dates.
My table structure is as follows :
Collapse | Copy Code
Symbol Name Dates Outstanding values
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0.7995
VAN Equity 12/5/2011 0.7000
VAN Equity 12/8/2011 0.7000
I want the output in the following form :
Collapse | Copy Code
Symbol Name Dates Difference
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0
VAN Equity 12/5/2011 -0.0995
VAN Equity 12/8/2011 0
I came up with the below query.
Collapse | Copy Code
WITH LHP AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
FROM test_table as LI
)
SELECT mc.[Outstanding Values] - mp.[Outstanding Values],
mc.[Outstanding Values] , mp.[Outstanding Values]
FROM LHP mc
inner JOIN LHP mp
ON mc.rn = mp.rn - 1
The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :
Collapse | Copy Code
Symbol Name Dates Outstanding Values
VAN Equity 2011-12-03 00:00:00.000 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995
VAN Equity 2011-12-05 00:00:00.000 0.7
VAN Equity 2011-12-08 00:00:00.000 0.7
VAN Equity 2011-12-09 00:00:00.000 0.6
VIN Equity 2011-12-03 00:00:00.000 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2
VIN Equity 2011-12-05 00:00:00.000 0.7
VIN Equity 2011-12-08 00:00:00.000 0.7
VIN Equity 2011-12-09 00:00:00.000 0.6
VAT Equity 2011-12-03 00:00:00.000 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2
VAT Equity 2011-12-05 00:00:00.000 0.7
VAT Equity 2011-12-08 00:00:00.000 0.7
VAT Equity 2011-12-09 00:00:00.000 0.6
i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.
Collapse | Copy Code
No Outstanding Outstanding
Name Values Values
0.6995 0.7995 0.1
-0.1 0.1 0.2
-0.5995 0.2 0.7995
0.0995 0.7995 0.7
0 0.7 0.7
0.1 0.7 0.6
Any help would be greatly appreciated.
|
|
|
|
|
Export
Symbol Name Dates Outstanding values
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0.7995
VAN Equity 12/5/2011 0.7000
VAN Equity 12/8/2011 0.7000
To Excel, Sort by date, insert a column for difference, insert a formula in one cell then drag down the formular for all the other cells.
|
|
|
|
|
do you mean as below?(see last column):
VAN Equity 2011-12-03 00:00:00.000 0.7995 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995 0
VAN Equity 2011-12-05 00:00:00.000 0.7 -0.0995
VAN Equity 2011-12-08 00:00:00.000 0.7 0
VAN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VIN Equity 2011-12-03 00:00:00.000 0.1 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2 0.1
VIN Equity 2011-12-05 00:00:00.000 0.7 0.5
VIN Equity 2011-12-08 00:00:00.000 0.7 0
VIN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VAT Equity 2011-12-03 00:00:00.000 0.1 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2 0.1
VAT Equity 2011-12-05 00:00:00.000 0.7 0.5
VAT Equity 2011-12-08 00:00:00.000 0.7 0
VAT Equity 2011-12-09 00:00:00.000 0.6 -0.1
Maybe I have a nice way
|
|
|
|
|
esProc can solve this problem easily. It is just like Excel+SQL, a free tool, see: A Query Language Over-perform SQL
[^].
code as below:
A1: =sqlsvr.query("select Name,Dates,[Outstanding Values] from test_table order by Name,Dates")
A2: =A1.derive(:Difference)
A3: =A2.group(Name)
A4: =A3.(~.run(Difference='Outstanding Values'-'Outstanding Values'[-1]))
I can't post a image file,so here are some explains:
A1 cell:query some data from database.
A2 cell:add a column to A1, named "Difference", just no data.
A3 cell:group the data by field "Name" in A2 cell. Here are 3 groups,
A4 cell:within every group(i.e. "~"), modify the field "Difference". The algorithm is: "this row" subtract "last row"(i.e. 'Outstanding Values'[-1])
I think esProc is more simple for mass data computation
BTW. how to post a image file?
|
|
|
|
|
Hi
How to write a where condition in the OPENQUERY to get data from ms access using linked server.
select * from openquery(AccessDB,'select * from TblEmp where EmpPK=1'). This query show the following error message
" OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessDB" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "select * from TblEmp where EmpPK=1" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessDB". "
With out where condition its working fine
Thankyou
YPKI
|
|
|
|
|
Had you added server to sql server
|
|
|
|
|
Access gives you the error message "No value given for one or more required parameters." when you specify a non-existent column name. It assumes that it is a query parameter and complains that its value has not been supplied.
Check if the TblEmp table in the Access database contains an EmpPK column. Most probably, it does not. You might have misspelled the column name.
modified 26-Dec-11 5:11am.
|
|
|
|
|
How to check whether a database has a table out of that database
|
|
|
|
|
This might help you:
select count(table_name) from information_schema.tables
However, some databases might not support it.
|
|
|
|
|
yuvarajujogi wrote: How to check whether a database has a table out of that database
Why, will it also hold tables out of "other" databases?
You can check if a table exists by quering sys.tables in Sql Server. Other database-servers will have similar options.
Bastard Programmer from Hell 
|
|
|
|
|
It depends which database you are using.
If you are using MSAccess , then you can write the code in any language and using TABLE collection you can count and note the tables name
If you are using SQL SERVER , then here is querey "SELECT * FROM Sys.Tables"
If you are using MySQL , then here is query
SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
|
|
|
|
|
Yet another way to get a list of tables in an ADO.net-supported database:
System.Data.Common.DbConnection con =
this.command.Connection as System.Data.Common.DbConnection ;
if ( con != null )
{
System.Data.DataTable temp = con.GetSchema
(
"TABLES"
) ;
temp.DefaultView.RowFilter = "TABLE_TYPE='TABLE' OR TABLE_TYPE='BASE TABLE'" ;
temp.DefaultView.Sort = "TABLE_NAME" ;
|
|
|
|
|
yuvarajujogi wrote: How to check whether a database has a table out of that database
What are you going to do if it doesn't?
|
|
|
|
|
I want to create a table in that database similar to the table in another database.
|
|
|
|
|
try with this..
If Object_ID('dbo.MyTable') IS NOT NULL
PRINT 'MyTable Exists'
Else
PRINT 'MyTable Not Exists'
with regards
Karthik Harve
|
|
|
|
|
Hey,
What database are you talking about?
MySQL,
Microsoft SQL Server
ORACLE
....
|
|
|
|
|
|
Once Again, What version of SQL Server?
SQL Server 2000
SQL Server 2003
or What?
|
|
|
|
|
You can use this code to check if a table exists.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
PRINT 'MyTable exists.'
ELSE
PRINT 'MyTable does not exist.'
|
|
|
|
|
try with this..
If Object_ID('dbo.MyTable') IS NOT NULL
PRINT 'MyTable Exists'
Else
PRINT 'MyTable Not Exists'
with regards
Karthik Harve
|
|
|
|
|
If there is a view or stored procedure with the same name, this script will fail.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
