|
In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.
Normally this concept is used for very fast database operations.
we can use the below connection string to create an in-memory db in sqlite.
public const string sqliteConnectionString = "Data Source=:memory:";
visit the link http://www.sqlite.org/inmemorydb.html[^] for getting more idea about it.
|
|
|
|
|
Prathapachandran.v wrote: In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.
I know what the term means.
Prathapachandran.v wrote: we can use the below connection string to create an in-memory db in sqlite.
Do you know the difference between 'how' and 'why'? Because I asked why it was needed.
|
|
|
|
|
Hi,
given the following tables (a student assessment system):
Student:
StudentID
StuAssess:
StudentID, SubjectCode, AssessID, CritID, CritVal
Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset:
SubjectCode, ResultCritVal, TargetCritVal
so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target.
I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset.
Hope I've managed to explain the requirement.
|
|
|
|
|
|
Thanks David.
Maybe it's getting too late here, but I can't figure out how CASE would help me, given that for any given subjectcode, each student would have EITHER a result (AssessID = 10, CritID = 35) OR a target (AssessID = 11, CritID = 42) OR both OR neither. I've got it in my head that I can only accomplish this through one or more self-joins, but the only common columns I can work with are StudentID and SubjectCode - there's no concept of a left or right table here.
Or am I really suffering brain-fade (it's been a long day)
Derek
|
|
|
|
|
SELECT DISTINCT
A.StudentID,
A.SubjectCode,
B.CritVal As ResultCritVal,
C.CritVal As TargetCritVal
FROM StuAssess A LEFT JOIN StuAssess B
ON A.StudentID=B.StudentID, A.SubjectCode=B.SubjectCode
LEFT JOIN StuAssess C
ON A.StudentID=C.StudentID, A.SubjectCode=C.SubjectCode
WHERE (B.AssessID=10 AND B.CritID=35) AND (C.AssessID=11 AND C.CritID=42)
I haven't test the code but I did something similar to that sometime back.
BTW... StuAssess ... I'd change that name.
|
|
|
|
|
How about a view that implements one criteria, called Result_view
create view result_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 10 and CritID = 35
;
Then another view that implements the other criteria, called Target_view
create view target_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 11 and CritID = 42
;
Then you query the views
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from result_view
union
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from target_view
;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I think he is looking for something in the form:
StudentID, SubjectCode, ResultCritVal, TargetCritVal
|
|
|
|
|
Very true, but he's failed to indicate how a ResultCritVal or TargetCritVal are calculated or populated. He's only shown what values for the original columns are to be. By using the views I suggested, the conditions for each criteria are provided. How those column values are 'translated' to a ResultCritVal or a TargetCritVal would still be necessary.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi, I have created one SSIS Package and saved a copy of file as "Enrypt Sensitive Data with Password" Option and saved in file system.
After that I have import package on SSIS server under MSDB folder.
Now I have create one SQL Job and in steps select SSIS Package and I am having one Domain User Name and used PROXY account to run the job.
Now the issue is that when I keep Server Console with login of the same Domain User Name, SQL Job is running fine daily...
But when I logged off on machine then Job fails.
Failed to understand the rights issue with Domain User Name.
User is Power User group member of Machine
and given rights as below as well.
Log on as a batch job
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment
But still facing issue when there is no session availble on server with that User.
Please help !!
Thankssssss
Thanks,
Sun Rays
To get something you must have to try once.
Rate answers if you like else reply me so can make it liked....
My Articles
|
|
|
|
|
A simple query with a left join sues to run quickly on SQL Server, but only with one parameter changed, it takes almost a minute.
The query is something like:
SELECT Table1.*, Table2.*
FROM Table1 Left JOIN Table2 ON Table1.ID=Table2.Table1ID
WHERE (Table1.Col1=38 OR Table1.Col1=-1) AND Table1.Col2=0
That produces around 280 lines of output in a second.
When we change Col1=38 to Col1=114 , it produces some 320 lines of output, in almost a minute!
We looked at the execution plan, and in the second case we found an extra "Lazy Spool" not present in the first query, which was estimate to cost a percent.
When I looked at that "Lazy Spool", I found a striking difference between estimated and actual values:
Actual Number of Rows:75124744
Estimated Number of Rows: 708724
That is more than a hundred times as much as estimated!
Actual Rebinds was 1, and Actual Rewinds 105!
Can you guess what went wrong here?
|
|
|
|
|
Bernhard Hiller wrote: Can you guess what went wrong here?
Is that your question or is this a quiz?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
mark merrens wrote:
Is ... this a quiz?
Does it look like that? Perhaps it is. But you did not win the prize of my 5.
|
|
|
|
|
Bernhard Hiller wrote: Can you guess what went wrong here?
Old statistics?
|
|
|
|
|
Thanks for this idea. The statistics did not bring improvement. Eventually an index on column Table1ID of Table2 brought the solution.
|
|
|
|
|
I have configured SQL Server 2008 Enterprise --Database Mail with the SQL below. But when I try to send mails with my hotmail e-mail account, I just get the error message(
select * from msdb.dbo.sysmail_event_log; ):Quote: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 24 (2011-11-17T17:20:53). Exception Message: Could not connect to mail server. (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 74.125.53.109:25)
I have tried several email account, like hotmail(smtp.live.com), QQ(smtp.qq.com), gmail(smtp.gmail.com) and 163(smtp.163.com), but what I got was the same message as above.
Any help would be greatly appreciated!
-- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
-- DECLARE DATABASE MAIL ACCOUNT PARAMETERS
Declare @qmail_account_name as varchar(64);
Declare @qmail_account_description as varchar(64);
Declare @qmail_account_email_address as varchar(64);
Declare @qmail_account_display_name as varchar(64);
Declare @qmail_account_username as varchar(64);
Declare @qmail_account_password as varchar(64);
Declare @qmail_account_mailserver_name as varchar(64);
-- DECLARE PROFILE PARAMETERS
declare @qmail_profile_profile_name as varchar(64);
declare @qmail_profile_description as varchar(64);
-- DECLARE PRINCIPLES
declare @qprincipal_name as varchar(16);
-- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE
set @qmail_account_mailserver_name = 'smtp.live.com'-- SMTP Server Name
set @qmail_account_username='hellomoney' -- SMTP User Name
set @qmail_account_email_address = 'hellomoney@hotmail.com' -- Email Address
set @qmail_account_password='123' -- PASSWORD FOR SMTP User
set @qmail_account_name = 'TestAdministrator'
set @qmail_account_description = 'Mail account for administrative e-mail.'
set @qmail_account_display_name = 'Test Automated Mailer'
-- PROFILE PARAMETERS ASSIGNMENT
set @qmail_profile_profile_name = 'MyEmailServiceProfile'
set @qmail_profile_description = 'Profile used for database mail jobs'
--PROFILE TYPE
set @qprincipal_name= 'public'
if ( @qmail_account_mailserver_name = '' or @qmail_account_username='' or @qmail_account_email_address = '' or @qmail_account_password='' )
begin
Select 'Please enter SMTP details' as [Comments]
end
else
Begin
if exists(select * from msdb.dbo.sysmail_account where [name]=@qmail_account_name)
begin
exec msdb.dbo.sysmail_delete_account_sp @account_name = @qmail_account_name
end
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @qmail_account_name,
@description = @qmail_account_description,
@email_address = @qmail_account_email_address,
@display_name = @qmail_account_display_name,
@username=@qmail_account_username,
@password=@qmail_account_password,
@mailserver_name = @qmail_account_mailserver_name
if exists(select * from msdb.dbo.sysmail_profile where [name]=@qmail_profile_profile_name)
begin
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = @qmail_profile_profile_name
end
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @qmail_profile_profile_name,
@description = @qmail_profile_description
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @qmail_profile_profile_name,
@account_name = @qmail_account_name,
@sequence_number = 1
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @qmail_profile_profile_name,
@principal_name = @qprincipal_name,
@is_default = 1 ;
end
|
|
|
|
|
Caveat I almost never use sql mail.
Are you sure that you can use a public SMTP server from sql mail. It would never occur to me to try, we always use an internal server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
im very new to asp.net.
i develop a website for e-commerce..now i developing a page to display the statistic of the product that bought by costumer..
here is my database structure..
Table Product-ProductID,ProductName,Description
Table Transaction-TransactionID,User,ProductID,Quantity
-in table Transaction, i store the product that bought by user with user name and quantity.
-table product, store the information about product
-this is my data, assume Bag ProductID=1 and Pencil ProductID=1 in table transaction..
-for table transaction:
TransactionID | User | ProductID | Quantity
1 | ali | 1 | 2
1 | abu | 2 | 3
1 | qwe | 1 | 5
i want make a statistic about the product that bought by users..
im using this Select query= "SELECT Product.ProductName, COUNT(Transaction.ProductID) AS ProductCount FROM Product INNER JOIN Transaction ON Product.ProductID = Transaction.ProductID GROUP BY Product.ProductName"
when using this query the result is,
ProductName | ProductCount
Bag | 2
Pencil | 1
i just want my result shown like i stated below..but i dont know how to do it..
ProductName | ProductCount
Bag | 7
Pencil | 3
please help me....
|
|
|
|
|
Instead of COUNT() try using SUM(Quantity)
BTW don't put ALL your question in code brackets, just the actual code snippets
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i will try..im very new to this site.so i dont know how to use forum yet.
|
|
|
|
|
You're doing fine, sensible question and layout is not critical, glad to be of service!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks ..it works ..
|
|
|
|
|
my database on sql server 2005, and i was convert my project VC++ to unicode,
the problem is :
when i try to read a field value string from recordset the data is "??????" the code is:
_variant_t vFieldName (strFieldName);
_variant_t ret = m_rs->GetFields ()->GetItem (vFieldName)->GetValue();
CString str = (wchar_t *)(_bstr_t)vVal;
when the language string is english ok but when it in other language the string is "???????"
mazen keeikati
|
|
|
|
|
my database on sql server 2005, and i was convert my project VC++ to unicode,
the problem is :
when i try to read a field value string from recordset the data is "??????" the code is:
_variant_t vFieldName (strFieldName);
_variant_t ret = m_rs->GetFields ()->GetItem (vFieldName)->GetValue();
CString str = (wchar_t *)(_bstr_t)vVal;
when the language string is english ok but when it in other language the string is "???????"
|
|
|
|
|
This implies that your fields in the database are varchar and not nvarchar.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|