Dear All,
i want to find no of empty rack(two dimensional ). i am using sql2000
i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea
there is rackmst( where i define max_no_row and max_no_cols).
Please help
thanks
Hi
I would like to know if there is some way to find a row in a table by the RID that sp_lock returns :
for example
"SELECT * FROM Authors (UPDLOCK) WHERE name = 'John'"
sp_lock returns the locked row id :
2:328:11 (file#page#slot on page)
How do i get this row???
Thanks
PB
Hi all,
I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.
Thanks in advance,
I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.
I was trying this:
select f.name, *
from sysfiles f (nolock)
left join sys.filegroups fg (nolock)
on f.name = fg.name
left join sys.indexes i (nolock)
on i.data_space_id = fg.data_space_id
left join sys.all_objects o (nolock)
ON i.[object_id] = o.[object_id]
where i.name is NULL and o.name is NULL
Did not work.
My file names are the same as the filgroup names containing the file, this is why I was joining by name.
Has anyone find solution for this problem.
i also checked
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and
http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor
----------------------------
ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30))
-1 UnknownMember
1
2 Blue
3 Red
4 Black
Color with the ID 1 is empty string
FactOrders
---------------------------
OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10
OrderID = 2 Color = 'Red' Quantity = 20
OrderID = 3 Color = '' Quantity = 10
OrderID = 4 Color = 'Blue' Quantity = 5
OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is.
------------------------------
OrderID = 1 Color='Black' ColorKey=4
OrderID = 2 Color='Black' ColorKey=3
OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1.
OrderID = 4 Color='Black' ColorKey=2
OrderID = 5 Color='Black' ColorKey=4
Thanks from now.
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.
i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.
I wonder if anyone can help.
I do not understand the error message but I do know it is associated with a specific table and one of its indexes. If I try to drop the index that is the error message I get.
I have copied the data from the table to a new table and rebuilt the indexes on the new table and everything is working fine.
The bad table is now renamed to myTable_BAD. I now want to delete it but can't as everytime I enter
drop table myTable_BAD
I get Page (1:404399), slot 5 for text, ntext, or image node does not exist.
So here is my question
How can I delete this Bad table?
dear friends/collegs:
for the past couple of weeks, we've been seeing:
SQL Server Error:
"kernel RA_Setup_IO [Bucket: 0] [slot: 0]: Process 4 generated access violation; SQL Server is terminating this process"
we're running SQL Server 6.5 sp4. on NT 4. sp3
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 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)
SQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email
View 5 Replies View Related@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)"
when i see the datable in "data visualizer" i find no rows.
It is confused eventhough it does not throw any error
Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.
View 7 Replies View RelatedHi i would like to know how to empty the transaction log programmaticly.
Someone got a clue? :)
Hi, I have a SQL Server 6.50 - 6.50.415 (Intel X86) and one database has 8Gb of data and 2Gb of log, the problem is that
the log space used is never below 44%, even if I back it up this way:
DUMP TRANSACTION DBName TO DBNAME_log_930 VOLUME = 'SS0003' WITH NOUNLOAD , STATS = 10, INIT , NOSKIP
This should clean the log, right?
I check for open transactions via: dbcc opentran (GMS), but there aren't.
The way I check for the log space used is: dbcc sqlperf(logspace)
The recover interval is 5.
Any Ideas?
Thank You
Gaspar Gonzalez
i have a sql sever2000 , and few disk.
when i am doing many opertaion , the log file is full. so how can i empty it?
sorry for my bad english
I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.
use monkey
go
create table tab1 (
myColumn varchar(10) null
)
go
insert into tab1 (myColumn) values ('')
go
exec master..xp_cmdshell 'bcp monkey..tab1 out D:data.csv -c -T'
go
drop table tab1
go
I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?
hi everyone,
i was just wondering, is there anyway to empty a table...like, in mysql u can go EMPTY TABLE
and it resets the identity stuff, so your id's start at zero again...?
Thanks, Justin
hello everyone,
am not sure if this is the right forum, but here goes.
We have a couple of clients we support running MS SQL, and as a rule, all db access is by SP and select statements.
However, we noticed earlier today, that a newly created account was empty. All columns except the users columns had been deleted.
1. Is there a way of checking which user account deleted the columns ?
2. Or finding out what went wrong ?
This has never happened before
Afrika
Okay, so i have a procedure where i'm trying to build a left join, and for some
reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer
i have no problem.
Manual Code:
create PROCEDURE search_orders_11
@SPSirvarchar(3)= null,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
here is the procedure:
create PROCEDURE search_orders_11
@SPSirvarchar(3)= null,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
here is the execution:
exec search_orders_11 @spsir=1
i get the whole command completed sucessfully, but no result. am i missing something here...
Hi,
I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data.
Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!
I will be really thankfull if somebody can help me on this issue.
Thnks,
sqlzagi
Is there any way to sort your results in a way that the empty results comes last? When I sort my recordset on a certain column I first get the empty values and the A, B, C etc. I would like to have it A, B, C, ..., Empty ones.
Regards
Ajmufic
I am trying to import Data from an Excel file to a SQL DB table. there are around 106 rows and 2 columns.
By default, the ssis is selecting around 200 columns and over 2000 rows. But all cells except for the 2 columns and 106 rows are empty. While I can specify that I need to read only 2 columns, I could not figure out a way to specify the rows. Any help?
I'm querying my database with a datetime constraint, only picking up records newer than the last ETL. Although I doubt it would ever happen I was going to put a check in the data flow to trap the case when NO rows were read due to inactivity in the OLAP. I set up a row count and tried to trap it. Apparently if you use an OLE DB source to get a result set and that result set is empty, the program flow bypasses everything and keeps running until it hits something causing an error, like an SQL Insert or Update command.
Is there any way to trap this condition? Currently it is going to cause the program to fail which will cause a snapshot restore (after kicking everyone out of the database).
Thanks.
Hello,
I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.
If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.
I need to do the opposite, be able to filter it only if it's not empty.
Any help would be appreciated.
tbl_store
id numeric(9)
enddate datetime(8)
tbl_store Data
ID Enddate
1 01/02/2020
2
3 01/02/2010
4
5 01/02/2001
(empty enddate data is showing <null>)
This is my query...but i want to get date empty records too.
select * from tbl_store where Enddate> '02/20/2008'
I want output
1 01/02/2020
2
3 01/02/2010
4
good day to everybody
View 5 Replies View RelatedHi,
I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing.
Anyone got any suggestions to why?
web.config:
<!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;"
providerName="System.Data.SqlClient" />
-->
<add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;"
providerName="System.Data.SqlClient" />
default.aspx.cs
dbConnection.Open();
String ISPQuery = "SELECT STATEMENT";
SqlCommand command = new SqlCommand(ISPQuery, dbConnection);
SqlDataReader reader = command.ExecuteReader();
Please help!!!
Best regards
Terje Kristensen
I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows. How can I do that?
using three SP's, one for each table:SqlCommand cmd = new SqlCommand("CompanyCheck", con);SqlCommand cmd2 = new SqlCommand("ContractorVerify", con);SqlCommand cmd3 = new SqlCommand("StoreLocation", con);
cmd.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; cmd3.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CompanyID", CompanyID); cmd2.Parameters.AddWithValue("@CompanyID", CompanyID); cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo"); da.SelectCommand = cmd2; da.Fill(ds, "ContractorInfo"); da.SelectCommand = cmd3; da.Fill(ds, "StoreInfo"); } catch { throw new ApplicationException("Data error"); } finally { con.Close(); }
Thank you
Hi,I wrote two queries to search in three tables mp_parent, mp_page and mp_parentThe first one is: SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentName FROM mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID The second one is:SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_child.ChildName FROM mp_page INNER JOIN mp_child ON mp_page.ChiID = mp_child.ChildID I used this way to display the records in a FormView public HttpContext context = HttpContext.Current; public void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { ViewState["srch"] = context.Items["srch"]; } FormView1.DataSource = GetTable(); FormView1.DataBind(); } private DataTable GetTable() { SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=mp;Integrated Security=True;Pooling=False"); String SQL1 = "select mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentID, mp_parent.ParentName from mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID where PageContent like '%" + Convert.ToString(ViewState["srch"]) + "%'"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon); SqlCommandBuilder CB = new SqlCommandBuilder(Adptr); DataTable Dt = new DataTable(); Adptr.Fill(Dt); return Dt; SqlCon.Close(); } the question is how can I check if one record is empty to witch to another query ??Is it possible to know without using QueryString?Thank you
View 5 Replies View Related(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query. The type of code I'm heading towards is: (but it's wrong). Thank you in advance for your C# code suggestion.
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{if (SqlDataSource1.SelectParameters.Contains is "") then
MessageBox.show ("There are no records available") ;
}