Sorry to repost this again since I did not get any replies on my previous post.
I would like to know if there is any way to find out if a string "ABCD" exist in any rows, any tables, any database?
Thanks
I have like 20 databases on my SQL Server. I have like 30 tables in each Database. I would like to find out all the rows in all tables in all database that has a certain text?
Is this possible?
Thanks in Advance
Yalgaar
I'm trying to figure out how to identify the calling database within a sproc on a different database without using a parameter.
View 2 Replies View Relatedgetting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title
from H..Import_Table
returns
'PRN - Concord'
'PRN - San Jose'
'SLP - PRN - San Jose'
'San Jose - PT - PRN'
[code]....
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike
Our products exist in batches. These batches reference a warehouse.Each warehouse carries a set of 'roles'.Users on our system also carry 'roles'. Comparing user and warehouse roles tells the system what warehouses a user can access.
So, if:
WAREHOUSE1 has roles A + B
WAREHOUSE 2 has role B
WAREHOUSE 3 has roles A + C
and
USER1 has role A
USER2 has roles A + B + C
USER3 has role C
then
USER1 will have access to WAREHOUSE1 and WAREHOUSE3
USER2 will have access to all 3 warehouses
USER3 will have access to WAREHOUSE3.
What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.
I can do the *opposite* of this quite easily (ie: show users who HAVE got access):
select distinct sb.batchnumber, ur.userid
from stockbatches sb
inner join warehouseroles wr on sb.warehouseid = wr.warehouseid
inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part
order by sb.batchnumber, ur.userid
- it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.
Fair enough, but it's not what I want.
Using the role data above, with the following batch info:
Batch 1000, warehouse WAREHOUSE1
Batch 1001, warehouse WAREHOUSE2
Batch 1002, warehouse WAREHOUSE3
I actually want to get this results set:
Batch Users who can't use this batch
1000 USER3
1001 USER1
1001 USER3
I'm certain I can write the necessary logic programmatically in T-SQL, looking for single-statement SQL call?
I need to find the object which holds the value(in column)
Whether it is possible?
suppose i need to find the text 'Goal' in stored procedure then i will give:
select * from syscomments where text like '%goal%'
suppose i need to find the text 'Goal' in table level.any system stored procedure?
Txs in Advance..
Hi,
I'm new to SQL 2000 administration and would like to know how to find out what the existing SQL Server Database configurations and what installation options has been installed / selected.
Thanks
Murras68
Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno
View 2 Replies View RelatedHii all,
I want to find the database , table size in MS SQL Server.
Is there is any query /procedures to find out the database and table size.
Thanks in advance .,
Regards.,
Sri
Our system guy wants me to find out just exactly how much room is being taken up by the SQL Express databases (I am using two). I am not sure how to do this. Is there any way to find the size, or, lacking that, how many records overall? We will probably need to move up to SQL 2005 soon, but he needs to figure out the memory requirements, etc.
Thanks,
Michael
Query :-
SELECT
MAX(TIME_VAL) AS Duration1 ,
MIN(TIME_VAL) AS Duration2 ,
-- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL)
-- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL)
(Select LONGITUDE from (select LONGITUDE , row_number()
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
I need to find the name of the user that signed in to the web page. For this, I have a web page with a text box, the program should access a SQL database use, use the userID and return the name. My code (working) does the following:string mySqlQuery = "";
string strCnn = "";SqlDataReader myDataReader = null;
mySqlQuery = "SELECT c_name FROM database WHERE c_user='demo'";
//connect to the databasestrCnn = "Data Source=" + connectionServer + "; Initial Catalog=" + connectionDatabase + "; user id=" + connectionUserID + "; password=" + connectionPassword + "; Integrated Security=false";cnn = new SqlConnection(strCnn);
try
{
cnn.Open();MessageBox.Show("Success Opening the Database");
}
catch
{MessageBox.Show("Problems with the database");
}SqlCommand sqlSelectCommand = new SqlCommand(mySqlQuery, cnn);
tbName.Text = "me";
cnn.Close();
This works, I just need to retrieve the value of c_name. Any help, thanks
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
I know sysprocesses has the information, but I can't seem to find how this is done using the dmv's. sessions doesn have a db context, and requests is only active while activity is occurring.
Thanks!
Hi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
I need to find a way to identify SPIDS where queries are connected to certain databases using the 4 dot notation instead of actually being connected to the database in question.
For instance, I connect and my default database is MASTER. In MASTER, instead of typing USE MyDb ahead of my query, I just type my query as:
SELECT Col1, Col2
FROM MyDB.dbo.MyTable
The above is the simple scenario. We usually have queries connecting 3 or 4 user dbs together. So database1 might have a query pointing to database2. What I need is a way to identify the SPIDS that are connecting to database2 despite being in database1.
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
View 4 Replies View RelatedI have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
999119/2/20119/9/2012
999119/10/20129/10/2012
999119/11/20129/11/2012
999119/12/20126/2/2013
999116/3/20136/30/2013
[Code] ....
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
333112011-09-022012-08-31
333112012-09-012012-09-10
333112012-09-112012-09-11
333112012-09-122013-01-06
333112013-09-01NULL
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE
(
Id INT,
PublicationId INT,
MetricId INT,
ProviderId INT,
DateCreated DATE,
Amount FLOAT
[code]....
Report Builder 3.0 . I have two datasets and I am using LookupSet to return data (a list of dates) but what I am after is the LAST date in the set. I have tried to use Last but I keep getting an error.
=Last(LookupSet(Fields!Model_Name.Value,Fields!Model_Name.Value,Fields!Read_Date.Value,"dsPageCount"))
I even tried doing a Last on the above statement with a Join thrown in, but that just gets me errors as well. I tried to Code in a function to go through the dataset and return the last piece of data, but again...error.
Function LastLookup(ByVal items As Object()) As Date
If items Is Nothing Then
Return Nothing
End If
Dim suma As Date = New Date()
For Each item As Object In items
suma = CDate(item)
Next
Morning forum,
I'm having a problem to which I'm sure the answer is simple...
All I want is a list of databases on my server with their allocated size and the free space within. Something similar to the first table that sp_spaceused gives you but on a server wide scale.
As I say, I'm sure there's a simple solution out there, but alas Google has failed me.
Thanks in advance,
Dan.
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedHi all,
I have to change the date format for one database in a group of databases in my sql server 2000,Can you please tell me how to change the date format.
thanks in advance
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
Stephen
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
Hi,
View 1 Replies View Relatedi have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5.
vineet
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View Relatedguys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
View 8 Replies View Related