Stored Proc References

Q) How do I find all the stored procs that reference tables containing the column named CompanyID

A)

select
cols.table_name,
so.name
from
information_schema.columns cols ,
syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where
cols.column_name = 'CompanyID'
and sc.TEXT LIKE '%' + cols.table_name + '%'


The first thing is to find all the tables with company id

select * from information_schema.columns where column_name = 'CompanyID'

The second thing is to find all the stored procs that reference a table

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

thanks to pinal dave
http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/


The last thing is to combine the two queries which was the select at the top

select
cols.table_name,
so.name
from
information_schema.columns cols ,
syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where
cols.column_name = 'CompanyID'
and sc.TEXT LIKE '%' + cols.table_name + '%'

WCF - Error in deserializing body of reply message

I used WCF services to connect to a SOAP service at Cyber Source.

Visual Studio takes care of most details which is great but the code didnt work. The reply threw a CommunicationException with the message:

"Error in deserializing body of reply message for operation 'runTransaction'."

The problem (for me) is that the buffer sizes are too small. I needed to increase the size of the maxTableCharCount parameter in the config file.

<readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
maxBytesPerRead="4096" maxNameTableCharCount="16384" />


I found the answer here:

http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/9bce995f-21d6-4898-9c17-31d6b10587cb/

Change Database Connection for EF Model in VS

Q) How do I connect my EF Entity Model to a different database?


When you add an entity Model to your project a db connection wizard is presented where you specify the connection details. If you want to change the database name or server at some point you can edit the connection string in the app.config file for the project containing your entity model.

Windows Server 2008 Sql Server 2008 Firewall Configuration

After installing Sql Server 2008 an exception needs to be added to the firewall.

Sql Server uses the following Ports as noted here
I only opened up 1433.

Beyond using SQL Browser, you can perform a network scan looking for services listening on default ports for any of the SQL components. These default ports are

Database Engine	1433
SQL Browser	1434 for DB engine
SQL Broker	4022, by convention
Analysis Services	2383
Reporting Services	80/443

I found some instructions here http://msdn.microsoft.com/en-us/library/cc646023.aspx

I ran this command.

PS C:\Users\Jimmy>netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope= SUBNET profile = CURRENT

IMPORTANT: Command executed successfully.
However, "netsh firewall" is deprecated;
use "netsh advfirewall firewall" instead.
For more information on using "netsh advfirewall firewall" commands
instead of "netsh firewall", see KB article 947709
at http://go.microsoft.com/fwlink/?linkid=121488 .

Ok.

PS C:\Users\Jimmy>

Who's Logged into that Server

Q) How can I determine who's logged into a remote computer

qwinsta /server:utility2

Q) How can I close one of the sessions

PS C:\> rwinsta /server:utility2 3

I found this information at the following link