hi there,
I saw a similar thread before but i've tried it and no luck. I want run a query off a database (stawebdata.branchinfo) then a join into bluepages.phonelist
i've tired
JOIN bluepages.phonelist p
Incorrect syntax near the keyword 'JOIN'.
any ideas... is this possible?
complete code below:
select DISTINCT b.name, b.tel1, b.id, b.telprivate,b.sc, b.manager,b.branchEmail
from branchInfo b
inner
JOIN bluepages.phonelist p on b.name = p.subdepartment
where b.name like 'Sarah' or b.id LIKE '%@keyword%' OR b.county LIKE '%@keyword%' OR b.Area LIKE '%@keyword%' OR b.Manager LIKE '%@keyword%' OR b.AssistantManager LIKE '%@keyword%' OR b.Postcode LIKE '%@keyword%' OR b.PseudoCity LIKE '%@keyword%' OR b.TicketingTA LIKE '%@keyword%' OR b.SLC LIKE '%@keyword%' OR b.ItineraryTA LIKE '%@keyword%' OR b.TIDS LIKE '%@keyword%' OR b.ABTA LIKE '%@keyword%' and
p.department = 'UK Branch' and
b.isbranch <> 'False' or
(p.lastName = '@keyword' or p.firstname = '@keyword')
order by b.name asc
GO
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I am searching for a sp that can generate the entire objects on all my databass in the server. Something like the 'Generate sql scripts' for sql7 but i need it to be capable to run as a scheduled job and automaticly enters in all my databases in the server.
I need to take sql scripts for all my development databases every night. I have something like 80 databases in development on the server.
Is there a way to connect to two separate databases at the same time through my web application?
In my web.config under the configuration/appSetting I have created two keys for database connection: <add key="MyConn" value="server=PLMNBKPLMNBKSQLSERVER;uid=sa;pwd=xxxx;database=SourcingDB" /> <add key="MyConn2" value="server=PLMNBKPLMNBKSQLSERVER;uid=sa;pwd=xxxx;database=SeaTender" />
This enable me to pass the connection string to a session variable through a dropdownlist box and connect to the desired database.
However, I would like to create a dataset from two tables which are not in the same database, one in the "SourcingDB" and the other one in the "SeaTender".
Hey everyone. I am somewhat of a newbie to the database world. I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.
I want some of the tables of one of the databases to be linked to the tables of the other.
I need to know which are the best ways to connect to a remote SQL SERVER 2000 from inside a VB6 application. By remote I mean on a dedicated server outside my LAN that I can access by IP address.
i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me . i am going to place my web application on local server (C# asp.net 2.0) . thank you,
i tried this code c# file public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
//dr.Close(); //dbcon2.Close(); } web.config<configuration> <appSettings> <add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/> <add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/> </appSettings> <connectionStrings/> this 2 databases are from single my loacl server it wont work it gives exception please healp me.
I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
I have moved my databases to 2000 to instance of 2005 on the same server. Now i set databases offline in 2000 as i did upgrade use backup & recovery method.
& my connection string Password=test;Persist Security Info=True;User ID=test;Initial Catalog=databasename;Data Source=ipaddress
i was using above connection string to connect when using 2000 databases form applications in the production machine
I get the following error when i am running my applications now after moving to 2005
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)
IS it because i have two server on one server or What do i need to do in order to fix this....tried several thing by searching nothing worked out..
Is it possible to use IF inside a query, in the WHERE statement? I started with the query right below, but I onlye got error. After testing and rewriting a lot I ended up with the last query. But there hast to be a better, smarter, more elegant way to write this query? Any hint? ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all links from the requested category.-- ============================================= (@CategoryId int, @AdminFilter bit)AS SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId IF (@AdminFilter = 1) print 'AND Link.Hidden = @AdminFilter' ORDER BY Link.Title ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all NOT hidden links from the requested category.-- If in Administrators role the return ALL links (the hidden ones also).-- ============================================= (@CategoryId int, @AdminFilter bit)AS IF (@AdminFilter = 1) BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId ORDER BY Link.Title END ELSE BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId AND Link.Hidden = @AdminFilter ORDER BY Link.Title END Regards, Sigurd
I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
I'm wondering if it is possible to use IF statements in a query, for example if this was my query:
SELECT Asset, Source, Val1, Val2, Val3 FROM tableA
Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative. Could I do something like this:
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
Hi all, I have a website under development. when trying to load page for the first time, I am getting sometimes a timeout error messege. My guess is that one of the queries is problematic and takes too long. I need to track the problematic query. one way to do that is using the ASP trace option. So this is what I did and I got the below error. the error as I can see is inside a BEGIN_INIT function, but I dont have a fnction like this in my code. Do you have any suggestion how to track? here is the trace code: Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Unhandled Execution Error Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) at System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) at System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() at System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 8.31843797517178 3.282671
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
-- declared variables declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100) declare @list varchar(8000) -- set values to variables set @list = '' set @database_name = 'data200802_dan' set @table_name = 'other02' set @primary_key_field = 'callid'
use database
select @list = @list + column_name + ', ' from information_schema.columns where table_name = @table_name --table name and column_name != @primary_key_field --unique identifier select @list = substring(@list, 1, len(rtrim(@list)) - 1)
--above 5 lines btw came from a helper in the msdn forum. thanks
SELECT DISTINCT @list INTO '#' + @table_name FROM @table_name @table_name + ':' IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0 BEGIN INSERT INTO @database_name + '.dbo.' + @table_name + '(' + @list + ')' SELECT @list FROM '#' + @table_name END ELSE BEGIN DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')' GOTO @table_name END DROP TABLE '#' + @table_name
the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.
is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, THE_Big_One1.STC_SCAN,
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
I have to write a stored procedure what repair a table. It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID'). I do not found the way to write this procedure to can create statement from parameter of the procedure. I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'. If the parameter is in the 'WHERE', nothing problem.
My procedure is: CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
declare @OrphanRowCount int select @OrphanRowCount = 1
while(@OrphanRowCount > 0) begin DECLARE KWCursor CURSOR FOR SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);
OPEN KWCursor; FETCH NEXT FROM KWCursor INTO @OrphanRowCount CLOSE KWCursor DEALLOCATE KWCursor
IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0) BEGIN exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)') END end END GO
How can I run fully parameterized queries from an SP. I can make it only like exec('DELETE FROM' + @TableName + ...)
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ? GROUP BY Panel_Position)
i get this error returned: 0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails. Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in (SELECT Panel_Position FROM tblMeasurement_Results WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ? GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
I have created an assembly with permission set safe, and a function inside the assembly. The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like: "The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error. And my .dll is also signed.
Has this something to do with writing something a config file? I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
