SQL Select Instruction On Two Database At The Same Time
Dec 13, 2006
It may sound like a newb question and if thats the case, I'm really sorry, but I would like to know if something like this is possible....
Lets say I have a table called TestA on a database DBA on a ServerA
and I have an another table called TestB on a database DBB on a ServerB
Now each of these table has a a field Id, to make an INNER JOIN between these two tables, taht are located on two different server on two differents database...Is it possible? and if so what would be the SELECT instruction to do it? Thanks for taking the time to read this :)
Hi, I have such a problem:I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); } The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges. For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help..
I ran into this problem trying to deploy an SSIS package to a development server. I tested by creating a simple SSIS package on the server itself. Two blocks an OLE DB Source block and an OLE DB Destination block. Two tables in the same database, one the source, the other the destination. Connection manager test connection works fine.
Package will execute from the IDE (locally on the development server), source block will read the table subcessfully, but when the destination block executes it fails and will stay yellow in the status screen. during the execution SQLDumper.exe is triggered. Analyzing the dump tells me:
(2b40.2938): Illegal instruction - code c000001d (first/second chance not available) eax=04008010 ebx=00000004 ecx=00000010 edx=00000000 esi=00000940 edi=00000000 eip=7c82ed54 esp=0422f598 ebp=0422f608 iopl=0 nv up ei ng nz ac po cy cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000297
An illegal instruction was hit.
This error exists when the destination block is either an OLE DB Destinaltion or a SQL Server Destination block and the SSIS application is executing locally.
Strangely enough if I run this same SSIS application from my development laptop it works fine against either my local datbase instance (on the laptop) or against the development server (App running on laptop, DB on development server).
I have scanned the forums and search engines for this type of error, so any assistance would be appreciated.
we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.
how can i retrieve two columns from sqltable with - seperating the results and a common column name someth like this select id ,name from user id name 1 a 2 b 3 c i need the result set to be my_reports 1-a 2-b 3-c is that possible, if so, could anyone tell me how to do?
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category". Sample:
Human Resources All documents related to new hire process. New Hire Request forms, New Hire forms, Termination Forms Total Files: 25
Programs & Utilities Windows utilities and other tools to keep you safe on the net. Antivirus, Spyware Removal Tools, Other programs Total Files: 37
My SQL2005 DB has 2 tables.
1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription) 2) Files. (CategoryID, ParentCAtegoryID, FileTitle) My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents. My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only. Sample:
Human Resources. CategoryID=4, ParentCategoryID=NULL New Hire Request forms. CategoryID=13, Parent CategoryID=4 New Hire Forms. CategoryID=17, Parent CategoryID=4 Termination Forms. CategoryID=22, Parent CategoryID=4
When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories. Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else. I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
I am doing Select into to make a table (I want to skip the log), but I want to add an IDent column to the new table. Can I do this somehow in the Select into? I have tried a couple of approaches, but have come up short....
I am trying to design a query that will return all the records based on a single date but because of the date/time data type in MS SQL 2005 it defaults to the date and the time 00:00:00 which will return no records for me.
Is it possible to ignore the time part in the select statement?
I've got a table where the date and time fields are all out of sequence and I need them in sequence when I am extracting data from the table, but I'm having trouble writing the SQL.
I've got this line which sorts the date and time for the table:
SELECT * from tblcodegreydiv order by date desc, time desc
Now I'm trying to retrieve the closest record prior to a date,time selected by a user...
SELECT * from tblCodeGreyDiv WHERE recid=(SELECT MAX(recid) from tblCodeGreyDiv WHERE date + '' + time <= '2007-09-19 05:24')
I need to somehow sort the table before I run the above sql statement. How would I go about doing this?
Hi NG,i've got a mystic problem i can't solve, perhaps one of you has a good idea...i will explain with some code, so fo better understanding my probroblem:i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.following Stored Procedure is ok and does it's job fine with "EXECUTEIntDayView"CREATE PROCEDURE dbo.IntDayView ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)AND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)but now, i want to have variables for the Date string, so i tried followingsyntax:CREATE PROCEDURE dbo.IntDayView @DayViewf DATETIME,@DayViewl DATETIME ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=@DayViewfAND Interviews.DatumZeit <=@DayViewlAND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)--------EXECUTE IntDayView@DayViewf=convert(datetime,'2004-02-10 00:00:00',120),@DayViewl=convert(datetime,'2004-05-16 23:59:59',120)so i tried the convert in execute, and also in the Procedure, but both times anerror occures Error Nr. 156, or 8114,so something with the convert is wrong but, also if i do it without convert itdoes not work.So how to select by time, if this doesn't work?????Any suggestions??Please help me,thanksJan Schmidt
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
Hi expert.. I am doing a project that have function to capture the Day and Night time talking duration of caller.... I make use of MSSQL to store the date time file..Let say I have this date: "2006-10-02 00:02:09".. How di I select "00:02:09" only???? Thanks in advancesuigion
A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help Thanks
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
I have a table tblCustTrans which contains custid int transid int startdate datetime value int
the custid, transid and startid are composite primary key.
the table contains more than 10 million records. Now i want to fetch record for select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000
This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
1. I have a parameter @Param1 that accepts multiple values
2. I have to display data based on selected values of @Param1Â
Here comes the tricky part.
3. I have to aggregate on all the available values of the @Param1 Â Do I need to take a hidden parameter just for aggregation? Or is there any other way?
I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task†or “transfer Database Taskâ€. I would like to create this as a dynamic process.
how is it possible to set a filed in a database to automatically add the current time to the filed at run time, is this possible; this filed has already been identified as 'DateTime' .
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July DressageJuly Jumping July Led in July Led in September Led in May DressageApril DressageJuly Flag and PoleJuly SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
Hello, I have a textbox on my formview which brings in a time from an sqldatasource. The time is displayed as 09:00:00 in the sql select statement i have a convert function - CONVERT (varchar, Time, 8) I am trying to alter the text e.g change to 09:30:00 and send it back to the database however i am having a lot of problems with that. Anyone know what i have to do to sort this. I think it might be something to do with the sql insert or the insert parameters as i dont know how to change the time String back to DateTime. Any help appreciated. Thanks in advance Mike.
Hello, is there a way to set the database to my local time? I am having troubles using the Membership.GetNumberofUsersOnline method - it is showing users online who in test mode I have logged off, but I think due to time zone difference between my server and myself, still appear logged on. This is causing further difficulties in using my admin functions to edit user information, because for some reason I cannot edit a user who is logged on... Help anyone?
Hi all, I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type). how can i manage the Relationship..... plz discuss in detail..... thanx in advance Sajjad Rizvi
