|
Nasty requirements - here is a first attempt using a cursor. I think I would change the EndDate calculation to a function so that you could remove the cursor.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @cnt INTEGER
DECLARE cur CURSOR FOR
SELECT
AbsentDate
FROM
#tmpTable
OPEN cur
FETCH NEXT FROM cur INTO @StartDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = 0
SET @EndDate = @StartDate
WHILE @cnt < 6
BEGIN
SET @cnt = @cnt + 1
SET @EndDate = DATEADD(d,1,@EndDate)
WHILE (DATEPART(dw,@EndDate) IN (1,7)) OR
(EXISTS(SELECT * FROM Holidays
WHERE HolidayDate = @EndDate))
BEGIN
SET @EndDate = DATEADD(d,1,@EndDate)
END
END
IF ((SELECT COUNT(*)
FROM #tmpTable
WHERE AbsentDate BETWEEN @StartDate AND @EndDate) > 5)
BEGIN
-- Save Date
-- or do other desired processing
END
FETCH NEXT FROM cur INTO @StartDate
END
CLOSE cur
DEALLOCATE cur
|
|
|
|
|
Hi,
When I have the following:
db.AddOutParameter(dbCommand, "@ReturnCode", DbType.Int32, 4);
What does the 4 mean? Does it mean the highest number will 9999? Or what?
When adding the follwoing datatypes, what does the size indicate:
int - size = 4
ntext - size = 16
Please can someone help me with my confusion.
Regards,
ma se
|
|
|
|
|
The int size of 4 refers to the number of bytes required to store an integer (which has a precision (i.e. max number of digits) of 10). The 16 for an ntext size refers to the bytes to hold a pointer to the actual ntext data, which is stored outside of the sql server leaf.
|
|
|
|
|
Number of bytes.
In this case, as 32 bit integer takes up 4 bytes (32/8 = 4). Normally, I only add the size to chars, varchars and varbinarys. The defaults for the standard fields (like Int32) is well known by the system.
|
|
|
|
|
Hi
I have an SQL database under a MS2005 SQL Server.
This is a ~200MB databse that grows from time to time, only one person (Administrator) adds\removes data from this database.
On the other side I have a client application that uses this database. At the moment the the SQL database is locked and has stored procedures as an "API".
Since allot of users accessing the data is CPU consuming and takes allot of bandwidth I would like to have the database localy on the clinet side, *locked*, enabeling only the execution of the stored procedures with an option to sync' with the main database on the SQL server. The end user can't add\remove items from the main\local database nor does he have the login\password for his local database.
I started looking into this and found SQL express and SQL mobile (everywhere).
My quersion is, which of the above is more suited for my application ?
Thanks,
Gilad.
|
|
|
|
|
|
Thank you for your quick answer.
At the moment the database is ~200MB growing at a rate of 80k per day, thats about 30MB a year, not too much. 99.999% of the data never changes so I would like the history data to stay on the client side.
The client gets all the data it needs and just the data it needs, nothing extra. This is very optimized.
I have many users accessing the database at once and would like only to send them the 80k daily "update data" rather then all the data.
Gilad.
|
|
|
|
|
Right now, as far as I'm aware, SQL Everywhere is only a CTP, I don't think it's been released yet. SQL Everywhere doesn't have per-user or per-object security. It also doesn't have stored procedures. You'll need to use SQL Express.
Given the size of the database I'd look at SQL Express. You'd probably want to use transactional replication so that the size of updates is small. You would still have to connect to the master database to execute any stored procedures that change data.
You have to have some kind of credentials to log into any SQL Express database. I presume that you mean that you wouldn't give out the system administrator password. That's sensible. I would grant the <machine>\Users Windows login access to the database server and map it to a user in the 'public' role in the database, then use Windows Authentication to connect to the database - no separate password needed. I've not done this but I know that SQL Express can subscribe to replication publications.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi Mike,
Thanks allot, you really hit the nail on its head.
I guess SQL everywhere\mobile isn't the solution for my situation.
Is SQL Express "Deployable" ? The client application is a "Click-One" App. Do I need a special liciense in order to use it on the client end ?
Thanks
Gilad.
|
|
|
|
|
You can distribute SQL Express to the client. Think of it as the replacement for MSDE, which it really is. You would probably need to have the client install SQL Express before your application, or have some code that runs at startup of your app to verify and install SQL Express prior to start up.
|
|
|
|
|
Hi,
I've got a small website which runs off an Access database (i know, i know - don't flame me) but Microsoft Access (as in the product) is not required on the server as it draws data through ADO; do you require any licenses to operate this?
Pol
|
|
|
|
|
The only link I can find is here[^], but this is in MSDN's Archive section so may well be out of date.
These days, Jet is under Windows File Protection on Windows 2000, XP and Server 2003, so it could probably be considered part of the operating system.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
i wish to connect to database at another PC by using C language,can anyone can tell me how to do that.ty
|
|
|
|
|
Attach via UNC share then use ADO.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
can give me connection string coding. or any reference web sites ty
|
|
|
|
|
Just use \\Computername\Sharename\Dbname.mdb for the database part of the connection string. You will need the appropriate permissions to access the share.
|
|
|
|
|
bahamutnice wrote: i wish to connect to database at another PC by using C language
Trying to connect to an Access database using just C is going to be a huge pain. Any reason for using C? I don’t believe ADO (which is the common method for data connectivity Windows) will work with C. I half wonder if DAO isn’t an option, it’s a technology that has been around quite a while.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
I´m working on some reports and I need to call a stored procedure from a repot... is it even possible?
cellardoor
|
|
|
|
|
|
What do you mean by "a report"?
A report is a collection of data presented in a user friendly format that may contain charts, graphs and even graphics.
OK do you mena reporting services, crystal reports or some other system.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
with this code:
execute storeprocedurename 'parameter1','parameter2',...
Human knowlege belongs to the world
|
|
|
|
|
Hi,
I'm trying to select the COUNT for individual rows within T-SQL while performing a join as follows:
select count(a.officer as officer, e.open_date as opened, e.close_date as closed, j.can_date as cancelled) from
escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_date
Getting the following error while atempting this query:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
Is this possible in T-SQL
Thanks,
Nino
|
|
|
|
|
select count(*) should give you the results you want. Count only counts the number of rows in a set. Using the field really doesn't do much unless you use distinct.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
SELECT
a.officer as officer,
e.open_date as opened,
e.close_date as closed,
j.can_date as cancelled,
count(*) as ct
FROM
escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_date
GROUP BY
a.officer,
a.open_date,
a.close_date,
a.can_date
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|