I have a DTS package that has two steps. The first steps executes a sql statement that selects data into a temporary table. The next step emails the results of a "select * from #mytable" to me. I ran the job today and did not get an email from it. So, I then copied both statements to Query Analyzer and ran them in the same window. The SELECT query ran fine but the xpsendmail part gave me an error that #mytable does not exist.
This is my query
drop table #update_status
select col1,col2,col3
into #update_status
from mydb.mytable
go
EXEC master..xp_sendmail
@recipients = 'fkearney',
@query = 'select * from #update_status',
@subject = 'status updated',
@message = 'These records had a status update:',
@attach_results = 'TRUE', @width = 250
How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @TotalJobPrice = fnTotalJobPrice(@JobID) Hope that is clear. Thanks in advance,
Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.
Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error: Server: Msg 8630, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor encountered an unexpected error during execution.
Here are some of the dates that were present in the table: DateColumn1 ----------- 1900-01-01 857:44:45.813 1900-01-01 872:51:16.427 1900-01-01 872:54:57.440 1900-01-01 873:09:32.107 1900-01-01 873:13:10.560 1900-01-01 873:16:49.867 1900-01-01 888:27:00.640
I have some troubles getting done a lookup... data i get from a legacy system has to be cleaned up and split up into normalized form. i get invoice data and want to split up invoices into a separate table as customers. therefore i lookup if the customer of the current line is not already in my newly created customer-table. if it isn't there (error output of lookup transformation) i insert it into the customer-table.
The problem is that after the customer is inserted into the customer-table, it still is not found by the lookup transform because the lookup uses caching to hold the reference table (and so is the exact same customer inserted again and again and again). Is there any way to disable caching and let the lookup transformation do a select for every new row it gets? or at least refilling the cache if some event happens?
I have a procedure where I am inserting some elements into #Table and then finally get the datset I need. Now when I am using this procedure as dataset to my report, it throws up the following error:
Invalid object Name "#TEMP2".
The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?
I am not sure should I posting it here or in T-SQL Forum.
I renamed a database table, and then try to create a view with that table. However, the renamed table does not appear in view designer. I have to close and re-open SQL Server Management studio 2014.
I have converted a access database to sql server 2000 for some reason i keep getting this error base table not found. I know the sql database is there and i also know the table is there. I'm not sure why i keep getting this error..
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type FROM Table1 t1 WHERE t1.Key2 IS NULL AND t1.Type LIKE 'TypeA' AND t1.Key1 NOT IN (SELECT Key1 FROM Table1 t2 WHERE t1.Key1 = t2.Key2 AND t1.Key1 <> t2.Key1 AND t2.Type LIKE 'TypeB')
1) When I run a query Select * from MY_Table the query never completes. If I press cancel in Query Analyzer after 1 second or 2 minutes, it always shows the same 174 records. The total table size is 800 KB with 3148 rows .. according to TaskPad
2) No queries, even queries that would return only 1 record complete execution against that table
3) I am unable to rename the table, if I try to rename the table Enterprise Manager becomes unresponsive and has to be closed.
4) If I try to browse the rows through Enterprise manager by scrolling through the records, it allows me to go down through a number of rows and then Enterprise manager gives me a timeout expired error.
5) I ran a DBCC CheckDB command ran for 1 hr and 7 minutes. It says there are 0 allocation errors and 0 consistency errors in the database.
6) The other database tables seem to be OK and the Database is operational except for calls involving the 1 Table
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
I've got a package which reads a text file into a table and updates another. I set up configurations so that I could import it into the SSIS store on both my dev and live servers. Now, I'm getting this error. I tried removing the configs and am still getting it.
I've been through each step and everything looks okay. Does anyone have any idea (a) what's wrong, (b) how to localise the error or (c) get any additional information? Or do I just have to recreate the package from scratch?
Error at PartnerLinkFlatFileImporter: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at PartnerLinkFlatFileImporter [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
We have a brand new sql 2k server. Its all up to date on service packs(both win2k and SQL).I went in to enterprise manager and added a new db. I can run sprocsand raw sql against the new db just fine from Query Analyzer.When I try to make a dts package that refers to my new db I get thefollowing error:Error desription: deferred prepare could not be completed.Could not locate entry in sysdatabases for database 'mydbname'. Noentry found with that name. Make sure that the name is enteredcorrectly.OK, so I look in sysdatabases and see 'mydbname'. Just in case im anidiot... I actually copied the db name from that table and paste it inmy dts window.My dts package is using an "execute SQL task" to call a stored proc.To be ecxact:USE mydbnameGOEXEC MyProcWhen I click on the parse button, is when I get that error message.I have deleted the db, restarted the server, and recreated the db witha slightly different name. I can replace mydbname with pubs ornorthwind and all is ok. It just doesnt like new db's.Any help would be greatly appreciated.Thanks,Dave
I've found a bug in SQL CE 3.0/3.1. This has been confirmed to be present by other users of this and the microsoft.public newsgroups, both in the Windows CE versions and the desktop x86 versions. I got this response:
Laxmi Narsimha Rao ORUGANTI MSFT wrote:
Can you please provide the simplified repro with full VS Solution you have thru our blog: http://blogs.msdn.com/sqlservercompact/contact.aspx. First contact us and then we will exchange directly thru mail.
And that's where it has ended (after I contacted the team via the blog - no response). Other forum users have suggested that I post here repeatedly until I get a response, so that's what I'm doing. Apologies.
Been trying to get this one but need a little input. SELECT a.Id,g.LabName...... FROM someTbl a,table g WHERE a.id = @idAND (b.calLab = g.ID OR g.LabName = ???) With the above, if b.calLab = 0 then there is no entry in table g.(I didn't develop the table or would have included NONE) So, how to tell g.LabName to come back as 'None' if b.calLab = 0? Thanks, Zath
Hello, I'm working on a function that will return the results in a SELECT statement. But it's not being called properly, and I can't understand why it isn't working. If anyone can help me out I'd greatly appreciate it. Here's the function code:1 FUNCTION [dbo].[UDFproduct_Search]2 ( 3 @search_string varchar(255)4 )5 RETURNS TABLE 6 AS7 RETURN 8 (9 SELECT product_id10 FROM product11 WHERE product_item_number LIKE '%' + @search_string + '%' OR12 product_part_number LIKE '%' + @search_string + '%' OR13 product_name LIKE '%' + @search_string + '%' OR14 product_commodity LIKE '%' + @search_string + '%'15 ) This SELECT statement returns the expected value:SELECT * FROM UDFproduct_Search('CABINET') This SELECT statement returns the expected value:1 SELECT * FROM where_used WHERE product_id 2 IN(SELECT product_id FROM product3 WHERE product_item_number LIKE '%' + 'CABINET' + '%' OR4 product_part_number LIKE '%' + 'CABINET' + '%' OR5 product_name LIKE '%' + 'CABINET' + '%' OR6 product_commodity LIKE '%' + 'CABINET' + '%') This SELECT statement returns errors:SELECT * FROM where_used WHERE product_id IN(UDFproduct_Search('CABINET') Error:'UDFproduct_Search' is not a recognized built-in function name. I've tried adding dbo to the function call, and in various locations, but I still get the error. Hope someone can point something out I missed. Thanks.
hi... i am very new to SQL server.. previously was using MySQL... now i am trying to connect my project to SQL Server.. but i wasnt able to... i keep getting errors
System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.Open() at icms.DB.q(String mySTR) in C:icmsDB.vb:line 30
below is my webconfig i am not very sure about the value for the user.. because if it is MySQL i can get it from my control centre.. but what about SQL server ? where should i get my value ?
Dim myCMD As New SqlCommand Public Sub New() Dim db_server = AppSettings("db_server") Dim db = AppSettings("db") Dim db_user = AppSettings("db_user") Dim db_pwd = AppSettings("db_pwd") Dim DBConnection As String = "DRIVER={SQL Server};" & _ '<==== is my driver correct? "SERVER=" & db_server & ";" & _ "DATABASE=" & db & ";" & _ "UID=" & db_user & ";" & _ "PASSWORD=" & db_pwd & ";" & _ "OPTION=3;" myDB.ConnectionString = DBConnection myCMD.Connection = myDB End Sub 'Public Function q(ByVal mySTR As String) As OdbcDataReader 'SQL Query Public Function q(ByVal myStr As String) As SqlDataReader myCMD.CommandText = myStr Try myDB.Open() q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConnection) Catch ex As Exception Err(ex.ToString) End Try
End Function Public Sub c(ByVal mySTR As String) ' COMMAND ONLY Try myCMD.Connection.Open() myCMD.CommandText = mySTR myCMD.ExecuteNonQuery() myCMD.Connection.Close() Catch ex As Exception Err(ex.ToString) End Try End Sub
I have inhereted this application and I am a newbee at asp.net. Fisrt my apologies for posting such a large amount, I just wanted to give you all the information that I possibly can.
I have the follwoing error "Specified SQL server not found".
Server Error in '/ppasa' Application. --------------------------------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]Specified SQL server not found. 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.Runtime.InteropServices.COMException: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]Specified SQL server not found.
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:
[COMException (0x80004005): [Microsoft][ODBC SQL Server Driver][DBMSLPCN]Specified SQL server not found.] ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options) +0 PPASA.PPASA.OpenCloseDB(Boolean Connect) in c:inetpubwwwrootPPASAPPASA.vb:9 PPASA.Login.btnSubmit_Click(Object sender, EventArgs e) in c:inetpubwwwrootPPASALogin.aspx.vb:38 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
The process of my application works as follows. 1)Login.aspx submits the username and password. It is linked to Login.aspx.vb with the following statement <%@ Page Language="vb" AutoEventWireup="false" Codebehind="Login.aspx.vb" Inherits="PPASA.Login"%> 2)In Login.aspx.vb (below) has a call to PPASA.vb with the following statement Call OpenCloseDB(True).
Public Class Login Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub Protected WithEvents Label1 As System.Web.UI.WebControls.Label Protected WithEvents Image1 As System.Web.UI.WebControls.Image Protected WithEvents Label2 As System.Web.UI.WebControls.Label Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox Protected WithEvents Label3 As System.Web.UI.WebControls.Label Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button Protected WithEvents btnClear As System.Web.UI.WebControls.Button
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim rs As ADODB.Recordset
Call OpenCloseDB(True) rs = cnn.Execute("SELECT ID FROM PPASA_User WHERE Name='" & UCase(txtUsername.Text) & "' AND Password='" & txtPassword.Text & "'") If Not rs.BOF And Not rs.EOF Then Call PopulateUserInfo(rs.Fields(0).Value) Server.Transfer("PPASAMain.aspx") Else Call ClearLoginBoxes() End If Call OpenCloseDB(False)
End Sub
Public Sub PopulateUserInfo(ByVal MyID As Integer)
Dim rs As ADODB.Recordset
Call OpenCloseDB(True) rs = cnn.Execute("SELECT ID,Name,Security FROM PPASA_User WHERE ID=" & MyID) If Not rs.BOF And Not rs.EOF Then rs.MoveFirst() 'CurrentUser.ID = rs.Fields(0).Value 'CurrentUser.Name = rs.Fields(1).Value 'CurrentUser.Security = rs.Fields(2).Value Session.Add("ID", rs.Fields(0).Value) Session.Add("Name", rs.Fields(1).Value) Session.Add("Security", rs.Fields(2).Value) Session.Add("DonorID", 0) End If Call OpenCloseDB(False) End Sub
Private Sub ClearLoginBoxes() txtUsername.Text = "" txtPassword.Text = "" End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click Call ClearLoginBoxes() End Sub End Class
3)PPASA.vb then creates the connection to the Database. PPASA.vb looks like this.
Module PPASA
Public cnn As New ADODB.Connection
Public Sub OpenCloseDB(ByVal Connect As Boolean)
If Connect = True Then If cnn.State = 0 Then cnn.Open("Description=PPASA;DRIVER=SQL Server;SERVER=THE-ONESERVERA;UID=sa;PWD=123;APP=Microsoft Data Access Components;DATABASE=PPASA") End If Else If Connect = False Then If cnn.State = 1 Then cnn.Close() End If End If End Sub
Public Function CheckSecurity(ByVal Actual As Integer, ByVal Required As Integer) As Boolean If Actual >= Required Then CheckSecurity = True Else CheckSecurity = False End If End Function End Module
4)After I submit Login.aspx the application boms and gives me the "Specified SQL server not found".
Extra Info:
The Sql server is up and running. I have tested it by connecting with 2 test connection files index.asp and index2 .aspx. Here are the files. The files also return results from the pubs db.
index.asp
<% Set conn = Server.CreateObject("ADODB.Connection") strConn="DRIVER={SQL Server};SERVER=THE-ONESERVERA;UID=sa;PWD=123;DATABASE=pubs"
conn.open strConn
sql = "SELECT * from authors" set rs = conn.Execute(sql) if not rs.EOF then while not rs.EOF lastname = rs("au_lname") firstname = rs("au_fname") response.write "LASTNAME :: " & lastname & " \// FIRSTNAME :: " & firstname & "<br>" rs.movenext wend end if conn.Close() Set conn = Nothing %>
and index2.aspx
<%@ Page aspcompat=true %> <% Dim objConn Dim strConn Dim objRs Dim objCmd Dim objField
If Not objRs.EOF then Do While Not objRs.EOF response.write ("VALUES :: ") For Each objField In objRs.Fields Response.Write (" " & objField.value & " \// ") Next response.write ("<br>") objRs.movenext Loop End if
objConn.Close() %>
There are also the following files in the bin directory. Interop.ADOR.dll Interop.MSChart20Lib.dll Interop.Scripting.dll PPASA.dll utCharting.dll
So my question is this, why do I get the error "Specified SQL server not found", when I dont get the error with the test connection files.? Do I have to do anything with the .dll file in the bin directory, like register them or something? How does the Login.aspx.vb file call the PPASA.vb file when I see no reference to PPASA.vb in the Login.aspx.vb file? I would appreciate any help or suggestions. Thanks
I have 2 server 2003 boxes one has sql 2000 and the other has IIS. I can pull up my pages on the iis server but as soon as my app tries to access the sql server I get cannot find server. All my settings are correct in the web.config file. I do not have this problem with the same setup on server 2000 boxes. Also active dir is not set up on the 2000 or 2003 boxes. Can someone please help. thanx weisenbr
I am trying to connect to my SQL Server through my ASP.NET webpages. My problem is that, no matter what I do, it always says that the server is not found. I know the IP address of my server, and this IP address works from other computers within my network, just not mine.
I have no idea what it could be. If anyone knows, any help would be greatly appriciated.
Hi, i have just started working with asp.net and purchased a book which has MSDE with it for database access. Ihave followed all the instructions on how to install MSDE, however when i try to set up a database using Miscrosoft ASP.NET Web Matrix, i get the error message:Unable to connect to the database server.SQL server does not exist or access denied.ConnectionOpen(Connect()).This message occurs when i am creating a new database.does anyone have any ideas why this may be happening?I have MySQL installed on my computer, could this be interfering with the MSDE ?Thanks....
Sometimes when I try to connect to an existing server (sql 7.0) , I get the error message "specified sql server not found-check registration properties" and it refuses to connect to it. Has anyone encountered this before? Do help.
and i get "[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found."
From my older host I have no problems connecting to my server... so I assumed it must be something at my new host, but I'm able to connect to other remote sql servers from my new isp (for example I can connect from my new ISP to the old ISP's SQL server w/o a problem), but for some reason I can't from the new host connect to mine.. is there a setting on mine that needs to change?
newhostwebsvr to oldhostsql works... oldhostwebsvr to mylocalsql works.. newhostwebsvr to mylocalsql doesn't work??
Hi there, I access to server SQL by an application on web,I configured the ODBC box for using TCP/IP and the server use also the TCP/IP. I have this message :
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found. /medecin/IdentificationAcces/membre_enregistrer.asp, line 108
Can somebody help me with the following error while deploying my cube:
The attribute key cannot be found : dbo_fact_table, Column: datetime Value 25/10/1901 4:18:00 pm...
The year 1901 is included in the time period of the time dimension. The calendar includes the following dates : 1/1/1753 - 31/12/2007 (Time binding) Why this referential integrity error occurs ??
Please help me because it is urgent and I cannot find a solution..
Can someone please tell me how to fix this following error in SQL 2005 When trying to create a maintenance plan, Method Not Found:'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIYtils..ctro()' (Mircosoft.SqlServer.MaintenancePlanTasksUI)
I have got an XP machine where I have installed a SQL Server for mytesting.I recently started working on a vb.net project requiring MSDE. Afterexecuting the Setup, made using Install Shield, including theinstallation of an instance of MSDE I can't start the Query Analyzer onmy "normal" SQL server or any instance, I am having a message saying"the specified file can not be found". I have to do a system restore torecover this functionality.Here is a pic http://www.corobori.com/sos/picSQLMSDE.jpgJean-Lucwww.corobori.com
