|
You may want to approach this like a checkbook. Meaning, store a series of transactions where you add the numbers up to determine the current balance. The important thing to remember is that you need 2 dates on the transaction; Posted Date and Effective Date. The Posted date is the date that the transaction was entered into the system and the Effective Date is the date that the transaction should be considered. So you may have received money in your bank account on Monday but your data entry clerk didn't enter it into the system until Thursday. You can then generate queries that show the balance "as of" a given date. This is a classic situation. This will also allow you to have a history of the transactions.
Give it some thought.
David
|
|
|
|
|
Thanks for the reply - I do have all that billing transaction/integrity logic on the system - the bill items are assigned to a Bill, the bill has a bill date when it's posted etc. The triggers for that level don't affect the CallRecords table.
In this instance the original Bill and the BillItems would remain, but the CallRecords that originally underlied the BillItem need to be moved out.
I was trying to figure out a way to write the CallRecords trigger thus:
if (update CallRecords)
where (change is a valid move)
permit
else
rollback
The problem is how does a trigger determine this is a valid update (e.g. to move the call records to a new billitem), as opposed to an invalid one.
I think that the case here is that we can modify the call record if we are ALSO modifying the BillItemID to a new value - this is permitted.
'Howard
|
|
|
|
|
Hi,
If I understood your question correctly, you want to allow or prevent modifications on a row with BillItemID based on the values with earlier situation and new situation. If this is correct you should fetch old situation from table name deleted and new situation from table name inserted in update trigger. After that make relevant comparison for values and decide if the change allowed. These virtual tables are automatically managed by SQL Server.
Mika
|
|
|
|
|
Hi,
I'm trying to find some software that will simulate a SQL server on my computer so I can test an application.
Does Microsoft SQL allow you to do this?
Any recommendations on a good (and easy to use) package?
Cheers,
|
|
|
|
|
|
or you can get the developer version of SQL Server, it is a full version that only allows 5 connections.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all,
i installed SQL Server in silent mode as following
str = "C:\SQLEXPRFiles\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=Instance1 SECURITYMODE=SQL SAPWD=password DISABLENETWORKPROTOCOLS=0 "<br />
<br />
Shell(str, AppWinStyle.Hide, True)
After that i want to attach database to this server as following
Dim ComputerName As String = My.Computer.Name<br />
<br />
Dim MainServer As Server = New Server(ComputerName & "\Instance1")<br />
With MainServer.ConnectionContext<br />
.LoginSecure = False<br />
.Login = "sa"<br />
.Password = password <br />
.Connect()<br />
If MainServer.Databases.Contains(mydatabase) Then<br />
Else<br />
Dim logstr As String<br />
Dim datastr As String<br />
datastr = Application.StartupPath & "\mydatabase.mdf"<br />
logstr = Application.StartupPath & "\mydatabase_log.ldf"<br />
'Attach the database<br />
Dim sc As StringCollection<br />
sc = New StringCollection<br />
sc.Add(datastr)<br />
sc.Add(logstr)<br />
Try<br />
MainServer.AttachDatabase(mydatabase, sc, ComputerName & "\Owner")<br />
MsgBox("Database Attached")<br />
Catch ex As Exception<br />
MsgBox(ex.Message.ToString)<br />
End Try<br />
End If<br />
.Disconnect()<br />
.AutoDisconnectMode = AutoDisconnectMode.DisconnectIfPooled<br />
<br />
End With
but MainServer.AttachDatabase statement give error on VISTA but successfully run on XP . Inner exception is {"An exception occurred while executing a Transact-SQL statement or batch."}
any body have idea where i am wrong ?
modified on Friday, August 8, 2008 5:07 AM
|
|
|
|
|
Hi all,
Am running a open row set query in my "sql server management studio express".
Am getting an error "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".".
I think the open row set stored procedure. will work only in 32 bit.
So,
I want to know whether
1)How cam i know my "SQL server ' is 32bit or 64bit?
2)My OS is Windows xp in my computer?Could that be the reason?
3)How can i know whther Windows Xp is 32 bit or 64bit?
Any infprmation will be helpful
Thanking You
|
|
|
|
|
new2pgrmg wrote: Am getting an error "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".".
Your error is something else. Your linked server configuration has some problem. Does it have data transfer access?
new2pgrmg wrote: 1)How cam i know my "SQL server ' is 32bit or 64bit?
In management studio, execute @@Version, this will tell you if SQL Server is 32 bit or 64 bit.
|
|
|
|
|
hi all,
the following statement Installed the SQL Server with SQL AUthentication mode with USER 'SA' when we run this command in command promopt
C:\SQLEXPRFiles\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=myInstance SECURITYMODE=SQL SAPWD=mypassword DISABLENETWORKPROTOCOLS=0
but i want to create A different login(instead of 'sa') so what Parameter is required with above statement.
|
|
|
|
|
My friends & I have a problem with our one of the table of our SQL Data...
One of our Table Column shows like this when we view it in sql :
ChaInvent
<binary>
We dont know how to read & edit inside that CharInvent..??
That ChaInvent has all the information we need because that is place where are all the Inventory on the Game of the Character stored like weapons, armors, potion, ..etc..haysss..Any Help here?
Are there any script or program that can read & interpret those data readable by Human?? I tried to search all the Info here but NO LUCK...
|
|
|
|
|
you always get binary data when running a select.
It depends on the client how to process it, mostly it becomes a array of bytes.
You can show it by storing it to file or convert it to strings to show it in a memo or listbox.
You probably used the sql studio manager for viewing data,
this is not showing you binary data.
greetz
kurt
|
|
|
|
|
...."You can show it by storing it to file or convert it to strings to show it in a memo or listbox"...
How?? we don't know, Please Help us...
|
|
|
|
|
Or you can write a program in you're language. for example C#
or build a query like this..
EXEC master.dbo.sp_configure 'show advanced options',1 RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE
declare @query varchar(500)
set @query = 'BCP "SELECT * FROM YourTable" queryout "C:\YourTable.txt" -c -T -C850 -S(local)\SQLEXPRESS -Usa -Psa'
EXEC master..xp_cmdshell @query
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE
Be sure you're sql server has rights to write on the HD. (set local system account of you're SQL service)
greetz
Kurt
|
|
|
|
|
I heard that is not possible to disable database attach. Is that accurate?
Better way is to encrypt data. But ... encrypt is about string isn´t it? how can i encript other types of data? if it´s an int the database structure won´t accept other type, do you copy me?
id|f_name|l_name|age|
11 john silva 29
24 glen berger 45
39 dan smith 34
40 luiz owen 41
id|f_name|l_name|age|
d22wfd kfvjff fgvfrer feef
5rfrfb 37bfjkr r43hifi85 kfnrk3ifn
rrui8 r44r 4rh4i ni43i
What do you think?
nelsonpaixao@yahoo.com.br
|
|
|
|
|
nelsonpaixao wrote: What do you think?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
i want to retrieve creationDate and time from a file on the server HD
by using a query.
anyone any idea how i can do this?
thx
Kurt
|
|
|
|
|
Hi,
First thing that comes in mind is to create a small assembly using c#. Implement a method to extract file info you need and then install and register it to SQL Server. After that you can use this custom function in t-sql.
Mika
|
|
|
|
|
Take a look at xp_cmdshell.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
OK thx.
if i can get the result of XP_CmdShell, i can manage it.
greetz
Kurt
|
|
|
|
|
hi all,
i want to Create login on SQL Server EXPRESS .How can i create this login using SMO. ?
Plesae provide me some useful link or code snippet. Also how can i create login for database which is on SQL Server.
|
|
|
|
|
Thought about Google
Loads of articles and examples
^]
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi folks,.
Can any1 let me know how to sum the time component of two time values in oracle?
for e.g s = 09:00
t = 19:09
i want to add s and t
T.Balaji
|
|
|
|
|
Hi,
There are two questions concerning this:
- what about the date portion. Should the start date be current date (in your example the result goes to next date)
- what are the datatypes for time components, dates, strings?
This SELECT should give you some starting point to adding elements to a date. This returns first day of this month reflected to desired time (variable s) and then added with given time (variable t) and the result is formatted to string to see all the elements:
SELECT TO_CHAR( SYSDATE,
'dd.mm.yyyy hh24:mi:ss'
) AS CurrentTime,
TO_CHAR( TO_DATE('09:00','hh24:mi') -- results in first day of this month at 09:00
+ INTERVAL '19' HOUR -- adding hours
+ INTERVAL '09' MINUTE, -- adding minutes
'dd.mm.yyyy hh24:mi:ss' -- formatting
) AS AddedTime
FROM dual;
And the result:
CURRENTTIME ADDEDTIME
6.8.2008 21:18 2.8.2008 4:09
Notice that when using only time portion in to_date function, the date is set to the first day of current month.
Mika
|
|
|
|
|
Hello Friends,
How to create stored procedure in SQL Server 2005.
Please help me.
Thanks in advance.
Adarsh
|
|
|
|