No buffer space available Fix (affected program: PostgreSQL for windows)

No buffer space available Fix: "If you are running Windows NT/2000/XP follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once there, you must create the entry TcpNumConnections. To do this, right click in the right pane and choose new from the menu and select DWORD Value. Give it the name TcpNumConnections. Then right click it and select modify and enter a value of 200.

Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this)."

Crystal Reports - Database Login dialog when using datasets - .NET VB:

Re: Crystal Reports - Database Login dialog when using datasets
I found out something... You need to check the properties in the 'Datasource Location' screen on your form/report (in the designer). In more detail... right click on 'Database Fields' and choose 'Set Datasource Location'... then verify or 'Replace' the datatable (or dataset) you have with the new one.

This is a HUGE ISSUE if you were using a custom namespace and, from the previous post/replys had to remove the namespace... it DOES NOT get removed from the datasource. Now, using this 'Set Datasource Location' helps with not having to redesign or drop the fields (or new fields) back on the form... it simply updates it for you.

This got rid of the 'Database Login Dialog' from popping up!

i hope this helps someone
ward0093

boolean queries technique for monthly summary report

original article: http://blogs.solidhouse.com/david.woods/CategoryView,category,SQL%202000.aspx

SQL Boolean Queries

The other night at edmug Richard Campbell presented some good SQL Querying Tips & Techniques. A lot of them were about sql 2005 which was nice to see (espeically using Common Table Expressions which are quite handy in the case of recursion).

The coolest thing I found was in regards to a cross tab query. The example used was to get sales amount by month for each employee. As you can see in the following example this is done with a lot of subqueries that builds a hideous execution plan.

SELECT Salespeople.Salesperson, SUM(S1.Quantity*S1.Price) AS Total,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=1 AND S2.Sales_ID = S1.Sales_ID) AS Jan,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=2 AND S2.Sales_ID = S1.Sales_ID) AS Feb,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=3 AND S2.Sales_ID = S1.Sales_ID) AS Mar,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=4 AND S2.Sales_ID = S1.Sales_ID) AS Apr,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=5 AND S2.Sales_ID = S1.Sales_ID) AS May,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=6 AND S2.Sales_ID = S1.Sales_ID) AS Jun,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=7 AND S2.Sales_ID = S1.Sales_ID) AS Jul,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=8 AND S2.Sales_ID = S1.Sales_ID) AS Aug,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=9 AND S2.Sales_ID = S1.Sales_ID) AS Sep,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=10 AND S2.Sales_ID = S1.Sales_ID) AS Oct,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=11 AND S2.Sales_ID = S1.Sales_ID) AS Nov,
(SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS S2 WHERE
DatePart(mm,S2.Invoice_Date)=12 AND S2.Sales_ID = S1.Sales_ID) AS Dec
FROM Sales AS S1 INNER JOIN Salespeople ON S1.Sales_ID = Salespeople.Sales_ID
GROUP BY Salespeople.Salesperson, S1.Sales_ID;

A Russiam mathemetician by the name of Rozenshtein has a great query (I beleive it was called a boolean query)

SELECT Salespeople.Salesperson, SUM(Sales.Quantity*Sales.Price) AS Total,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-1)))) AS Jan,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-2)))) AS Feb,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-3)))) AS Mar,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-4)))) AS Apr,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-5)))) AS May,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-6)))) AS Jun,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-7)))) AS Jul,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-8)))) AS Aug,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-9)))) AS Sep,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-10)))) AS Oct,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-11)))) AS Nov,
SUM(Sales.Quantity*Sales.Price*(1-ABS(SIGN(DatePart(mm,Sales.Invoice_Date)-12)))) AS Dec
FROM Sales INNER JOIN Salespeople ON Sales.Sales_ID = Salespeople.Sales_ID
GROUP BY Salespeople.Salesperson;


Breaking this down we take the numeric month out of the Invoice date with the DatePart(mm, Sales.Invoice_date) section. Next the value is run through the SIGN method. SIGN converts a number to a +1 if greater than 0, 0 if the value is 0, or -1 if the number is less than 0. Then convert the number to a positive using ABS (absolute value). Then subtract the value from 1 so now we have a boolean 0 or -1 for the current row. Then multiply the data (in this case the queantiy * price) by the value we have been calculated. So if the row is in the current month it will be multiplied by -1, otherwise by 0 (and hence equal 0 so it will not be included in the sum).

Here is a table showing the value at each step

	Subtraction	SIGN	ABS	Subtraction
Jan	2	1	1	0
Feb	1	1	1	0
Mar	0	0	0	1
Apr	-1	-1	1	0
May	-2	-1	1	0
Jun	-3	-1	1	0

If you look at the execution plan for this method you will see it is drastically more efficient which really helps with large amounts of data. This technique does not have to be used for dates. A good example Richard Campbell showed was using CharIndex to find a name (as it will return -1 if not found, 0 if an exact match, or greater than 0 if contained within the word). Basically anything you can turn into a numeric to say you have matched something vs. not matched then this technique should be applicable.

connection string for postgresql blob and insufficient base table information error

"Driver={PostgreSQL ANSI};uid=postgres-su;pwd=whiteboard-su;Database=thephoto;Server=localhost;ByteaAsLongVarBinary=1;ReadOnly=0;Parse=1"

mono exe

After using Mono for a while, you'll probably get sick of running programs using the mono command. Fear not though, since there's a little-known Linux feature ready to rescue you: miscellaneous binary format support. This lets you specify applications to run different types of files, much like file associations in Nautilus or Konqueror. Try these commands:

modprobe binfmt_misc
mount -t binfmt_misc none /proc/sys/fs/binfmt_misc
echo ':CLR:M::MZ::/usr/bin/mono:' > /proc/sys/fs/binfmt_misc/register

If everything goes well, running Mono EXE files should work transparently. Put these commands into an appropriate startup script and you should be set. If you're running Debian though, you shouldn't have to bother - the Mono packages are built to set up miscellaneous binary support automatically

solution to no buffer space available postgresql (postgres is port 5432)

When you try to connect from TCP ports greater than 5000 you receive the error 'WSAENOBUFS (10055)': "RESOLUTION
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
The default maximum number of ephemeral TCP ports is 5000 in the products that are included in the 'Applies to' section. A new parameter has been added in these products. To increase the maximum number of ephemeral ports, follow these steps:
1. Start Registry Editor.
2. Locate the following subkey in the registry, and then click Parameters:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
3. On the Edit menu, click New, and then add the following registry entry:
Value Name: MaxUserPort
Value Type: DWORD
Value data: 65534
Valid Range: 5000-65534 (decimal)
Default: 0x1388 (5000 decimal)
Description: This parameter controls the maximum port number that is used when a program requests any available user port from the system. Typically , ephemeral (short-lived) ports are allocated between the values of 1024 and 5000 inclusive.
4. Quit Registry Editor."

how to save images in postgresql database

Set ImgXCtrl1.Picture = LoadPicture("c:\windows\wallpaperbrucelee14.jpg")

Set c = New ADODB.Connection
c.CursorLocation = adUseClient
c.Open "Driver={PostgreSQL ANSI};uid=postgres-su;pwd=whiteboard-su;Server=localhost;Database=test;ByteaAsLongVarBinary=1"





Dim jpg As New MycJpg ' core code comes from intel jpeg library

Set r = New ADODB.Recordset
r.CursorLocation = adUseClient
r.Open "SELECT i, imx FROM images where 1 = 0", c, adOpenStatic, adLockOptimistic


Dim b() As Byte
b = jpg.PicToJpba(ImgXCtrl1.Picture)


r.AddNew
r.Fields("i").Value = 3
r.Fields("imx").Value = b
r.Update