Hi,
Is there a way to write a select statement in order to find out which database a certain view is in? One way is to write "select * from view" and switch between available databases until "Invalid object name 'vw_X'" does not show.
Any other ways?
I am getting email from the end client and i need to validate in sql query.
View 3 Replies View RelatedHi,
I am working on a .dll which need to access .sdf ( sql server mobile db). In my project, I added a reference "System.Data.SqlServerCe.dll". The dll is located in C:Program FilesMicrosoft Visual Studio 8Common7IDE.
I am able to compile the project. Then I created a Unit project for this dll file. In Unit project, I added the same reference.
Now the magic things happen. The compiling was failed. I got "Error 1 The type 'System.Data.Common.DbConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. C:VS2005MobileDBUnitMobileDBProjectUnitMobileDB.cs 50 13 UnitMobileDBProject".
I don't understand how come the same .dll could pass in a dll project but failed in unit test project. And I never saw the System.Data.SqlServerCe display in ".Net" Section of Add reference. But in my office, I installed same version of VS2005 for software tester. I could see the System.Data.SqlServerCe display in the ".Net" section of Add Reference.
Please help me to fix this problem. Thanks.
have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email @ sign exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the @ sign does not exist then I would need the EMAILflag to be a N.
This what I have but the emailaddress column is not always null it just has spaces in the field.
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress, CASE WHEN emailaddress IS NULL THEN 'N' ELSE 'Y' END AS emailflag
FROM orders;
Any HELP would be GREAT.
Thank You!!
Dear friends,
is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
or we have to use sp_helptext?
is there any better way?
thank you very much.
Vinod
How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
I have this query:
SELECT a.*
FROM Unscheduled_Arrival a INNER JOIN (SELECT Person_ID
,[arrival date]
,[leaving date]
,[unique stay id]
,[area code]
FROM Unscheduled_Arrival
[Code] ....
And what I am trying to do is find instances where a person has arrived back at a different area within 7 days of leaving from a planned visit to any other area different from the last. so lets say we had person ID = 1 and they had had 3 planned visits to one area (not equal to area_gh) in the last 4 months but after the 2nd planned trip to the other area, and before the commencement of the 3rd planned trip to any area not equal to area_gh, they had arrived unscheduled to area_gh, and this unplanned visit was within 7 days of leaving the 2nd last planned visit to any area not equal to area_gh.
The query I have shows data from unscheduled arrivals to the area_gh for a person who has also had visits to any area other than area_gh, but each record for unscheduled visits to area_gh duplicates for the amount of times the person has had scheduled visits to any area not equal to area_gh. This is the first problem but is as a result of not having the correct SQL for the refinement of the data.
This refinement requires that I return only the data that relates to the unscheduled arrival that is within 7 days of the last scheduled arrival, not duplicate rows of area_gh visits for all the scheduled arrivals.
Because my data is showing the data I want but the count is related to the scheduled arrivals I think I need to rewrite the query or alter it.
The data is already restricted to a year's worth of data.
I just need to find any instances of unscheduled arrivals (for arrival at area_gh) within 7 days of the end of any scheduled arrival (determined using leaving date) to area equal to area_gh.
There can be any number of scheduled arrivals and any number of unscheduled arrivals for each person. The [unique stay id] is distinct for each visit so person 1 can have 3 stays, all with a different [unique stay id].
Why don't you folks (SQL SERVER Management Studio Team or SQL SERVER Database Engine Team) provide an option by using which I would be in a position to know what all the other objects are depended on a selected object and vice versa. That means:
When I select a Table within the SQL SERVER Management Studio if there is an option for understanding "Which all the objects (tables or views) it is depended on (I mean Foreign key and etc... kind of relations) and also Which all the objects (Tables, Views, Stored Procedures and etc...) are using this table", It would be of great help for all developers.
I can be contacted over kotis@microsoft.com
I BLOG @ http://blogs.msdn.com/kreddy
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI've created C#.net program (behind code style).
when I run it in Internet explorer, the following error occurs in IE window.
pls instruct me how to handle and correct this error.
And how to initialize the connectionstring... Great thank!
Server Error in '/' Application.
--------------------------------------------------------------------------------
The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values
into another table. IN VB this was simple but the while in TSQL seems
a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays
--insert values
insert into table values (value1, value2)
--then increment counters and repeat
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped
How do you think..and why ?
thanks
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped
How do you think..and why ?
thanks
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data.
I ONLY WANT
a. records that are only in A but not in B
b. records that are only in B but not in A
______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS
AS
BEGIN
DECLARE @Rowcount AS INT
DECLARE @First_Name AS VARCHAR(50)
DECLARE @Last_Name AS VARCHAR(50)
DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess
SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK
SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------'
PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ +
CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Prowess WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Sdk WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END
DROP TABLE #Prowess
DROP TABLE #Sdk
END
declare @var varchar(50)
set @var= 'COLUMNNAME'
select ID, a.@var , b.@var
from rooper a
join jim_rooper b on b.id = a.id
join b_rooper bb on bb.id = a.id
where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
Hi
I am writing T-SQL pls tell me wheather it is correct syntax or not
DECLARE @Chg1 VARCHAR(500)
SET @Chg1 = 'declare @AntID numeric exec casp_Switch_BackupData @AntID = #ANTID#'
SET @Chg1 = REPLACE(@Chg1,'#ANTID#','@AntID')
EXEC (@Chg1)
As I am getting following output
Command(s) completed successfully.
T.I.A
hi!
i want to use IN query like
select ... from ...
where field1 in (...)
and field2 in (....)
when i write query like this, the result is display.
but its wrong.
is it correct?
Dim strsql As String
strsql = "insert into MYENTRY(entryid) "
strsql &= "VALUES ("
strsql &= "'" & strtheEntryid & ")' "
I am not sure if this is correct snytax?
hi
if row.col1 = nothing then ...
instead of (sql2k) if dtssource("col1") = null then...
TIA
Hello to all,
Is it correct way to register my CLR library instead of having T-SQL codes (eg, Strored Procedure, Functions and Triggers) in the database in the following case:
Code security: If my Application (in .NET 2.0) and SQL Server Express in same PC and I have to give Windows-Administrator password to my application-user (to install/unistall some other softwares)
Thanks
PSDCHD
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
View 2 Replies View RelatedI'm trying to get a year count and a year amount of payments made by a client. Below is my statement, it is not giving a correct count. If a client made more than one payment on the same day it counts it as 1.
What can I do to this statement to get correct totals?
SELECT DISTINCT Client_ID, DATEPART(year,PaymentDate) AS 'Year', SUM(AmountPaid) AS 'TotalYearlyPayments', COUNT(DISTINCT Payment_ID) AS 'YearlyPaymentCount'FROM tblPaymentsWHERE Client_ID = @ClientIDGROUP BY Client_ID, DATEPART(year, PaymentDate)ORDER BY Client_ID, Year
Hi, I'm building a web application in which I want to prevent SQL injection. I'm using stored procedures, and using queries on my app like this:in my database...create proc createStudy@title varchar(200),@text textasinsert into studies values(@title,@text)goand in my web app...query="createStudy '"+titleBox.Text+"','"+textBox.Text+"'"; //title and text boxes are textboxes, createStudy is a stored procedure in my databaseodmccommand cmd = new odbccommand(query,con);con.Open();cmd.ExecuteNonQuery();But before this I do this code:if (titleBox.Text.Contains("Drop") || titleBox.Text.Contains("Delete")) messageLabel.Text="No permissions to do that";else(...my code)Is this ok to prevent SQL injection?!?
View 5 Replies View RelatedHi,
This is strange....
I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).
For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:
Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?
Many TIA.
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
and (r.id = 'anything')
I have two tables:
1) Table that holds all available ports.
2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table.
On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table:
1/1
1/2
1/3
1/4
Users table:
User A , 1/1
User A , 1/2
ASP page display:
------------
User A Holds:
1/1
1/2
------------
Available:
1/3
1/4
------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it !
I've tried all kinds of Joins and nesting SELECT queries with no luck.
I hate SQL. I want to die.
I have this sql statement:
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are:
PersonSkills:
id, ProductID, Skills
Product:
id, ProductName
Ive been reading and playing around with this without success.
create proc AuthorTable @AuthotID int,
@AuthorName varchar(20),
@AuthorBook varchar(60),
as
insert into dbo.Author
( AuthorID,
AuthorName,
AuthorBook
)
values
( @AuthotID ,
@AuthorName,
@AuthorBook
)
spatle