How Entertaining! [modified] - Database Discussion Boards

There is an answer to this on SQLServerCentral.com here[^]. You might have to sign up to view it, can't remember, but it is a reputable and useful site.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Hi Try the following:
Link 1 : sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/[^]

Or

DECLARE @StringToSearch NVARCHAR(MAX)
SET @StringToSearch = 'ENTER_SP_TO_SEARCH'

SELECT    
    [name],   
    (   
        SELECT    
            OBJECT_DEFINITION(obj2.object_id) AS [text()]   
        from sys.all_objects obj2   
        where obj2.object_id = obj1.object_id   
        FOR XML PATH(''), TYPE   
    ) AS Obj_text,   
    [type] as ObjType    
FROM sys.all_objects obj1  
WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @StringToSearch + '%'

Thanks
Md. Marufuzzaman

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Hello. I am getting the following error when trying to connect:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"

1) I tried seeing what services are running and nothing with SQL Server is listed.

2) I ran the Surface Area Configuration and the following message immediately pops up:

"No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)"

What in blazes is going on?

Thanks.

This tends to be a misleading, catch all error message. Assuming you actually have SQL installed and can see the database in SSMS then I would delete the credentials you are using and re create them. This may be cause by a restore from another server corrupting the user details.

Never underestimate the power of human stupidity
RAH

I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”.

Using the below statement, the listings returned are random however I’m not getting exactly 3 listings (rows) returned per shop.

SELECT  AdShops.ID, Shops.url, Shops.image_url, 
	Shops.user_name AS shop_name, Shops.title, L.listing_id AS listing_id, 
	L.title AS listing_title, L.price as price,
        L.image_url AS listing_image_url, L.url AS listing_url

FROM	AdShops INNER JOIN
	Shops ON AdShops.user_id = Shops.user_id INNER JOIN
	Listings AS L ON Shops.user_id = L.user_id

WHERE	(Shops.is_vacation = 0 AND Shops.listing_count > 2 AND 
	 L.listing_id IN
    		(SELECT	TOP 3 L2.listing_id
	 	 FROM   Etsy_Listings AS L2
	 	 WHERE  (L2.user_id = L.user_id)
	 	 ORDER BY NEWID()
		)
	)
ORDER BY Shops.shop_name

When 'ORDER BY NEWID()' is removed, I get exactly 3 rows returned per shop but it's the first 3 listings and not random - I’m stumped. Mad | :mad:

Anyone have any ideas on how to fix it? D'Oh! | :doh:

The ideal solution would be one record per store with the 3 listings (and associated data) were in columns and not rows – is this possible? Cool | :cool:

Thanks to Lieven Thumbs Up | :thumbsup:

stackoverflow.com[stackoverflow.com] for the key to solving the problem. Full solution is as follows:

SELECT AdShops.ID, Shops.url, Shops.image_url, 
       Shops.user_name AS shop_name, Shops.title, L.listing_id AS listing_id, 
       L.title AS listing_title, L.price as price,
       L.image_url AS listing_image_url, L.url AS listing_url
FROM   AdShops INNER JOIN
       Shops ON AdShops.user_id = Shops.user_id INNER JOIN
       Listings AS L ON Shops.user_id = L.user_id
WHERE  (Shops.is_vacation = 0 AND Shops.listing_count > 2 AND 
        L.listing_id IN
            (SELECT listing_id
             FROM    
                 (SELECT l2.user_id , l2.listing_id, RowNumber = ROW_NUMBER() 
                  OVER   (PARTITION BY l2.user_id ORDER BY NEWID())
                  FROM   Listings l2 INNER JOIN
                      (SELECT   user_id
                       FROM     Listings
                       GROUP BY user_id
                       HAVING   COUNT(*) >= 3
                      ) cnt ON cnt.user_id = l2.user_id  
                 ) l2 
             WHERE l2.RowNumber <= 3 and L2.user_id = L.user_id
            )
       )
ORDER BY Shops.shop_name

Anyone have any ideas on how to merge the 3 rows into a single row with the 3 listings moved into columns (several colums per listing) ???? D'Oh! | :doh:

Here we go again, this article may be of use Pivot two or more columns in SQL Server 2005[^]

This query basically represents the "inner" query in the article, now you need to extract the column labels to be pivotted on and write the outer query. I would stuff this query into a table var or view and work from there.

Never underestimate the power of human stupidity
RAH

I've spent the last three hours trying to create a new connection to an Access file in Server Explorer, following the guidance in VS2008 Help. Nothing works. But out of sheer frustration I clicked on Test Connection with the Connection String box blank. It passed. Apparently Test Connection does absolutely nothing, so I can stop wasting my time clicking on it. Clicking on OK tells me that the "Format of the initialization string does not conform to specification starting at index 0" which makes perfect sense to me. Apparently it's just as clear to Microsoft, as when I search on the error message it returns 0 responses. I guess they never tried connecting to an Access DB with VS2008...

Crap! I'm so tired of Microsoft making simple tasks impossible! I've got a simple mdb file from work with meter readings from a now defunct reading system. All I wanted to do was load and display two or three columns from one table in the file, and print them so the accountant can enter the readings into the billing system. I thought this would take about two hours - based on past experience doing very similar things. This was easier using DOS and Paradox!

For the record, I searched on Creating a New Connection in Server Explorer, located the connection string for Access in the resulting example, copied and pasted it into the wizard, and changed the file path to match my database file. There were several results, and I tried them all. Nothing worked, except the error message Microsoft doesn't recognize.

How do you folks do this for a living? Hard drugs? Alcohol in vast quantities?

I did some more searching, with no positive results. But I did manage to find a link to provide Microsoft with some feedback, to wit:

<EDIT>
I found the following step to be confusing:

1. In the Database file name box, enter the path to the .mdb file, or click the Browse button to locate the database file.

There is no such box in VS2008, just a Connection String request that offers no clues. Using the connection string examples provided by MSDN, with appropriate modifications to point to the path of my Access database, did not help. By the way, if I Test Connection using a blank connection string, it passes, but clicking OK results in the entirely worthless message,

“Format of the initialization string does not conform to specification starting at index 0.”

Utterly useless, as always. Thanks for being consistent, if not useful. Over the past 20+ years I’ve been setting lower expectations for Microsoft assistance, and you’ve consistently managed to somehow fall short of the goal every year. That’s a heck of an achievement! Few companies are capable of falling so short of customer expectations and staying in business. Keep up the good work – I’m planning to sell MSFT short in a few years to fund my retirement.

Just as a side note, I used Bing to find this solution; Google was much faster, and provided many more examples, but none of them worked, either.
</EDIT>

"A Journey of a Thousand Rest Stops Begins with a Single Movement"
<div class="modified">modified on Wednesday, April 28, 2010 1:20 AM</div>

Roger Wright wrote:
How do you folks do this for a living? Hard drugs? Alcohol in vast quantities?

Yes... and a good deal of self loathing!!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

Roger Wright wrote:
All I wanted to do was load and display two or three columns from one table in the file, and print them so the accountant can enter the readings into the billing system.

Why use VS? If you have the mdb chances are you have Access, so just open open Access, use the mdb, create a report and run it. Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

The laptop I need to use for meter reading, and exporting data, doesn't have Access installed, and no one wants to spend the money on buying a copy. But you're right, that would be the sensible solution.

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Here's a thought - could you use OpenOffice?
It has a database component which I've not used so I'm taking a guess.
But it is free. Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Roger Wright wrote:
Server Explorer

What's that? Never heard of it.

Roger Wright wrote:
connecting to an Access DB with VS2008...

Whatever for? That's for writing code, not for doing database work.

Generally, when I am trying to determine a connection string for some new database, I use a UDL file.

PIEBALDconsult wrote:
I use a UDL file.

Never heard of it, but I'll look it up. In actuality, I'm used to coding db stuff manually, and that's all I really understand, but I thought I'd try out the new toys in VS. Note to self:

Never put your faith in Wizards.

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Roger Wright wrote:
new toys

Stick with what works.

I did tonight, and hand coding the connection string - identical to that used in the wizard - worked perfectly, first try. Big Grin | :-D

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

PIEBALDconsult wrote:
when I am trying to determine a connection string for some new database, I use a UDL file.

Then you sound just the person to answer this[^] question.

It's time for a new signature.

Or maybe not... I only use the UDL file to get the connection string -- then I do other things with it.

I have a .Net app that uses access as a database and I use the following connection string with no problems. "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mydatabase.mdb;Jet OLEDB:Database Password=password;"

You could also check out http://www.connectionstrings.com/access[^].

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

The problem is in the Add Connection wizard, not the connection string. The same string that yields the completely incomprehensible error message when used with the wizard works perfectly when hand-coded in the app. I'll stick with the old fashioned way of doing connections from now on. Big Grin | :-D

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Just came across this post on the VB Developer Center.

Add Connection Wizard Fails[^]

It sounds like the installation of the Access data provider is a bit dodgy.

They suggest the following, and it apparently works a treet.

1) Run regedit.exe;
2) Browse to the location "HKEY_CLASSES_ROOT\CLSID\{F9AE8980-7E52-11d0-8964-00C04FD611D7}" in the tree;
3) If there is a child node in the tree called "ProgID", then you are experiencing a different problem.

Otherwise, follow these steps to modify the registry:

1) Right click the node and select "New > Key";
2) Enter "ProgID" as the name;
3) Go to the main window and double click "(Default)";
4) Enter the value "MSIDXS.1";

Now if you run Visual Studio, it should be working.

Hope this helps.

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Now this is interesting...

I have Visual Studio 2008 Express installed at work, and the registry settings are already exactly what you've described. It works perfectly, using the same database I'm trying to connect to at home.

What's odd is that, my home installation is VS 2008 Standard, and the connection wizard dialog box is entirely different! No Browse button, to start with, and of course I never get past that point to see the rest.

I'll check these registry settings as soon as I get home; this might just do the trick!

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

That worked a treat!

What an obscure fix for a non-obvious problem! Even worse, this source came from a VS2005 post; why is it still a problem in VS2008? Mad | :mad:

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Glad I could help.

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Hello,

does anyone know what is Enumeration in Sqlserver 2005,2008?
what is the advantages and how I can use it? when do I need to implement enumeration?

thanks