LOGO Unity, STLINE üzerindeki sıfır olan kur bilgisinin update edilmesi

Logo Unity'de, yerel para birimi ile dövizlerin birlikte gösterildiği bazı raporlarda "Divide by zero" hatası alıyorsanız LG_209_01_STLINE tablosunda döviz bilgisinin girilmemesinden kaynaklanan bir sorun olabilir. Aşağıdaki Query, STLINE tablosundaki döviz bilgisi sıfır olan satırların, döviz tablosundan ilgili tarihteki döviz bilgisi alınarak update yapılmasını sağlar. Sorguyu kendi tablolarınıza göre değiştirmelisiniz.


DECLARE @LOGREF INT
DECLARE @DATE DATETIME
DECLARE @RR FLOAT
DECLARE BIRIMKODU CURSOR FOR
SELECT LOGICALREF, DATE_
FROM LG_209_01_STLINE (NOLOCK) WHERE REPORTRATE=0 AND TRCODE IN (2,3,7,8,1,6)
OPEN BIRIMKODU
FETCH NEXT FROM BIRIMKODU
INTO @LOGREF, @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RR=RATES1 FROM L_DAILYEXCHANGES (nolock) WHERE CRTYPE=1 AND EDATE =@DATE
UPDATE LG_209_01_STLINE SET REPORTRATE =@RR
WHERE LOGICALREF=@LOGREF
FETCH NEXT FROM BIRIMKODU
INTO @LOGREF, @DATE
END
CLOSE BIRIMKODU
DEALLOCATE BIRIMKODU
GO

How to change postgresql Sequence number?

In general you can use the code below to set the value. (similar to
"ALTER TABLE XTable AUTO_INCREMENT = 311" in MsSQL database)

SELECT setval('schemaname.sequencename', 1467);

Expiring a cookie in CSharp

The first thing you need to understand about cookies is this: Cookies carry an expiry date. The second thing you need to understand is this: Expiry dates are the cause of most cookie-related bugs.

Every time you set the Value of a cookie, remember also to set the Expires date. If you fail to do this you will quickly find yourself losing Cookies owing to them having expired immediately when updating them on the client machine or when the browser closes.

When a cookie expires, the client no longer sends it to the server, so you need to make sure that the Expires property of the cookie is always in the future. If you just set a cookie's value then it will create a cookie with Expires set to DateTime.MinValue (01-Jan-0001 00:00).

You can set a cookie's Expires property using any DateTime value (a positive relief after ASP). For example, if you want a Cookie to expire after the user has not been to that part of your site for a week, you would set Expires = DateTime.Now.AddDays(7).

If you want the cookie to be permanent then the temptation is to use DateTime.MaxValue, as I did in the lat version of this article. However, there is a simple gotcha here.

DateTime.MaxValue is precisely 31-Dec-9999 25:59:59.9999999. But Netscape, even at version 7, doesn't cope with that value and expires the cookie immediately. Amazingly, and somewhat annoyingly, investigation showed that Netscape 7 will cope with 10-Nov-9999 21:47:44 but will not handle a second higher (I'll be honest, I didn't test it to any fraction of a second, I really wasn't interested).

Thus if, like me, you subscribe to the "it doesn't have to look pretty on Netscape, as long as it's functional on the latest version" school of thought, always use a date prior to that. A commonly accepted "permanent" cookie expiry date is DateTime.Now.AddYears(30), ie. 30 years into the future. If someone hasn't visited your site for that long, do you really care what the state was last time they were there?

SQL Query that search all column names based on criteria

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.


That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Serkan SONMEZ'
GO




CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END