Query to list the logins and the databases they have access
Hi Friends, I am in the need of query to list all the users with their respective databases they have access to. Can somebody please help me. Thanks, ...
sql
Flag column or foreign key?
I have ENTERPRISES and DOMAINS table. The property of each enterprise is that it should have a single primary domain, but it can have more than one domain. I have come up with this table structure +---------------------------------------+ | ENTERPRISES | +----+--------------+-------------------+ | ID | Name ...
SQL with table name as parameter and query longer than 4000 characters
I am trying to write a stored procedure that takes a table name as a parameter. Yes I already know this is a security vulnerability, but this is an internal stored proc that doesn't face typical risks of SQL Injection. What I have so far is something like the following: CREATE PROCEDURE [dbo].[myprocedure] @tableName sysname AS DEC...
OJB / Oracle XE sql debug-display problem
Hello, I have a Java application, and use OJB as my ORM technology. I have an Oracle XE installation locally to develop against. The problem is when I need to debug a problem, I like looking at the SQL output. Here is an example of SQL I can view through the "Top SQL" interface in Oracle XE: select a_bunch_of_fields from KREW_DOC...
SQL CLR Stored Procedure - Relative path for DllImport?
I have a C# project consisting of stored procedures that reference a win32 dll from another project in the solution. Currently, dllimport is using absolute paths to reference it. If I use a relative path instead, where is that path relative to once the clr stored procs assembly is loaded in sql server? ...
Check if Stored procedures have syntax errors
I have tons of stored procedures in my database. We are constantly changing the data structure (we are in development) Is there a tool that will tell me which stored procedures won't compile? When you create a stored procedure it prevents you if there is an invalid table or column, but if you change the column name after the stored pro...
Problem with select statement via a linked server
Hi I am using MS SQL Server 2000 and have a link from a test db to a live db which is in replication. The link seems to work fine and I can select from any of the tables using any of the fields apart from the field with the constraints on creating ids. So if I run select * from person where firstname like 'john' this works fine, but th...
Referencing MySQL Alias of Aggregate Column
Following up on my question summarizing-two-conditions-on-the-same-sql-table, I added a RATIO column that is simply one SUM(...) column divided by a second SUM(...) column: SELECT COMPANY_ID, SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE, SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE S...
Where can I find an excellent database search engine script?
I've been looking for PHP, SQL open scripts on retrieving data from DB according to search terms. Now, every script I stumbled upon would not go well at this: say I'm looking for "barack obama's gay rights policy" ok? but in the DB there's "the obama policy on gays and their rights - an honest article". Albeit this is a pertine...
AlphaNumeric ordering in SQL vs. LINQ and .NET
I encountered an interesting thing today that I have never noticed before. It appears that SQL and LINQ order AlphaNumeric strings differently. Data table contains rows: A G 6 P 1 D J 2 T Z 9 F 0 If I perform an Order By in the SQL, I receive the following results: A D F G J P T Z 0 1 2 6 9 Now consider this LINQ sample: class Progr...
Dropping and recreating tables... to avoid indexing issues?
I'm running into an interesting argument with a DBA who insists that the DELETE command should not be used in T-SQL, that instead the proper way to remove data is to move the data you'd like to keep to a temp table, and drop and re-create the original table. The justification given for this is that it should prevent index fragmentation i...
Excel VBA INSERT INTO statement using variables
I am using an SQL query to insert data from an excel worksheet into an Access database. I put all the data that I want from the worksheet into variables: rwyNumber = Range("b13").Value & Range("c13").Value testDate = Range("b5").Value testTime = Range("b6").Value rwySide1 = Range("b14").Value firstThird1 = Range("b28").Value secondThir...
MySQL: Join two tables with different number of rows and one same column name
Hi, i have two tables "*TABLE_A*" and "*TABLE_B*", and i want to connect them the same way like in "*RESULT_TABLE*". TABLE_A: +-----------+-----------+---------+ | row_id | category | val_1 | +-----------+-----------+---------+ | 1067 | cat1 | 6.5 | | 2666 | test | 6.5 | | 2710 | cat1 | 2.1...
Storing Connection Strings in Registry?
We decided to use the registry for handling our deployment with connection strings in our VB.net application. The requirements are: If the program cannot connect to the server, first check the registry for a connection string. IF not, create the folder and fill in the name, type, and data. Make sure its encrypted. I have never e...
SQL Query - Get Most Recent Revision
Say I have parent table Projects: ProjectID ProjectNam 1 Test Project 1 2 Test Project 2 and child table ProjectRevisions: ProjectRevID ProjectID DateCreated 11 1 10/15/2009 12 1 10/19/2009 13 1 10/25/2009 21 2 10/05/2009 How do I end up wi...
How to Parse and Append text to a stored procedure in SQL Server 2005 via a parameter
Does anyone know of a way to append text to a stored procedure from within another stored procedure? I would like to do something like the following in SQL Server 2005: Declare str as Nvarchar(Max) = '' set @spStr = dbo.spTest + 'Where testCol1 = ''Test''' exec(@spStr) I understand this may open some discussion about SQL ...
Syntax for SQL Server when either of parameter is passed
If one of the paramater is passed / or both passed how to match that to single column in WHERE clause. If both are passed should get result set just with the first one else with second one. Ex: --either will be passed declare @processStepID int = null declare @PROCESS_StepID int = null if(@processStepID is null) select * from CTL.CTR...
SQL best practice to deal with default sort order
A lot of SQL code I've read, it seems like the developer assumes that the default sort order always hold. For example when building an HTML select list they would just SELECT id, name FROM table without issuing an ORDER BY clause. From my own experience it seems like dbms alway order data using FIFO if no ORDER BY clause is given and n...
Postgresql HAVING clause limitation
Why can't one use an output column in the having clause in postgresql? It doesn't change expressivity of the language anyhow, just forces people to rewrite output column definition in having clause. Is a way to avoid that, apart from putting the whole query as a subquery in SELECT * FROM (...) AS t WHERE condition ? ...
SQL Server 2008 Performance Question
I have a table with 30 columns and about 3.4 million records. Is it reasonable for SELECT * FROM [Table]; to take 8 to 12 minutes to return all 3.4 million results? If not, where's a good place/resource to begin diagnosing my problem? ...