I would like to empty out my 2k8 database (remove all the data in the tables only) And reloaded it with the data from production (2k5). I know I would have to dis-enable any constraints, triggers and remove all indexes before I can run a Truncate/Delete on all tables correct And then import the data via Wizard or script And lastly enable all the constraints & triggers, rebuild the indexes. Is this the only way to go about this? I don't want to do a backup & restore because the 2k5 doesn't have a 20% of the tables that are in 2k8. The 20% of those tables in 2k8 i'm not going to remove.
I've just creates a report with about 20 Tables. To my suprise, it almost runs as intended ;-) But there is one flaw - Is there any way of hiding an empyt table? I want to hide all tables that are not filled with data. But wich one is empty differs from the entered parameter.
I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried: select left(s2.name,32) as TableName, max(s1.rows) as Records from sysindexes s1 inner join sysobjects s2 on s1.id=s2.id where type = 'U' and s2.name like 'New%' group by s2.name HAVING max(rows) = 0 ORDER BY TableName but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like: TableNameRows NewARTxn0 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
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.
I'm running a master package executing 8 child packages.
Each package contains the same connection managers and each package is stored within the MSDB database. The master package executes the packages stored in the MSDB using the 'execute package task'.
When running the master package from either MSDB or visual studio directly the odd thing happens that some tables will be filled, but after package execution I notice each table to be empty. There's no rollback 'procedure 'specified within the package and each package executes successfully because of error row handling. Anybody any hints how to solve this one?
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Does someone know whether it is better to drop and reload or sp_recompile a stored procedure to get a new, recompiled execution plan? I have another DBA telling me it is better to drop and reload the stored procedure rather than use sp_recompile. I would think that sp_recompile would be the preferred method.
I am doing a master page and i want the page to update itself when a new value comes in. Example, my master page default value has login and register (both are set as linkbutton)
Login | Register
Then in the login page, when the person successfully logged in to his/her account, master page update linkbutton login and register text will change to welcome john and logout.
I have a problem with getting a dropdown list to change a txt field every time I select a new value on it.
I have a parameter called "Type" that is selected from a non-queried drop down list. This contains values like "Month" "Day" "Quarter" "Week", etc. I have a query that takes those parameters and then returns a "TimeInterval" for each one. I want this value to appear in a text box (Which is a MaxDate parameter for an entirely separate query) whenever the user selects a value from type. However there is a catch, the user needs to be able to also change the value of the textbox so that he/she can customize that end date for report rather than the values that I have pre-canned for them. This is a bit confusing so I'll try to give an example. The wants to run a report and selects "Week" from the drop down list, this value is passed one of my datasets and that data set returns the value 07/15/2007. That value will then populate in the MaxDate parameter field. The user then changes his/her mind selects "month" from the Type drop down list. What should happen is that the MaxDate field changes to be 07/01/2007 (This is returned correctly in my querry, but I can't get it to show in the maxDate field after the first time.), but instead it still shows 07/15/2007. Here is my SQL code for the dataSet that uses the parameter Type to return a date
IF(@Type= 'Day') BEGIN SELECT dbo.F_START_OF_DAY(getdate()) AS TimeInterval END
IF(@Type='Year') BEGIN SELECT dbo.F_START_OF_YEAR(getdate()) AS TimeInterval END
IF(@Type='Month') BEGIN SELECT dbo.F_START_OF_MONTH(getdate()) AS TimeInterval END
IF(@Type='Quarter') BEGIN SELECT dbo.F_START_OF_QUARTER(getdate()) AS TimeInterval END
ETC....................................
If you need any aditional information or have any questions, I'de be more than happy to answer them. Thanks in advance, /jcarver
here's my situation. i want to take a copy of my database at work and take it home to my home pc. what/how is the best way to do this. i am relatively new to this stuff, so detailed directions would be nice.
sidebar - if i have the .mdf and .ldf files, can I restore from them?
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
I have a dataset that sometimes is empty. I have it as a detail row in a table on the report, but since the dataset is null the row does not appear. What I would like to do is have it say "No results" if there are no results, otherwise have it show the results.
I tried to add a textbox and reference the textbox on the table, but it was a no go.
I have a block of code that does a Fill() with a DataAdapter, which seems to throw an exception if no values are returned. Then, when I try to bind the DataSet that gets filled, an error occurs, it doesn't even try to bind the column headers or anything.
This process seems to work fine when some rows are found, but it is not always guaranteed that the row count will always be > 0. The nice thing about using the Fill() function, is that it creates all my column headers automatically, so I was just wondering if there's a way to do a Fill() then a subsequent DataBind() to a DataGrid which would automatically format the columns even when no rows are found?
Here's a bit of the source.. if the last line finds no rows, it throws an exception ( I assume this is normal):
strSQL = "SELECT * FROM Prods"; cm.CommandText = strSQL; da = new SqlDataAdapter( cm ); da.Fill(shelf);
Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x
I am trying to do a lookup for a value in a codes table and if that code is not there; add it to the table, my problem is if the lookup table is blank no records to begin with it will not add any data, that means the data source is empty so there no data going thru the data pipeline. So how do I get data in the table if we start with an empty table? If I am not clear I can explain it more.
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help
I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.
Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.
Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.
Hello, I have a problem where I need to include data from a table where one of the joined columns can contain empty strings. The emptry string column and its related columns need to be included in the results of the query.
The Join looks like this:
SalesData.dbo.tbl_CYProcessedSales ps INNER JOIN SalesData.dbo.vw_Product_CYProcessedSalesXref xr ON ps.Prod = xr.Prod AND ps.Acct = xr.AcctCode INNER JOIN TxnRptg.dbo.vw_NetNewRevenueUnion nn ON xr.BillingType = nn.BillingType AND xr.ProdGroup = nn.Product AND xr.AcctCode = nn.AcctCode
The problem occurs on the xr.BillingType = nn.BillingType portion of the join. the nn.BillingType can contain empty strings. I've tried a LEFT join to vw_NetNewRevenueUnion, but this has not worked.
In vw_NetNewRevenueUnion, BillingType becomes an empty string within a CASE statement if BillingType is NULL. I've thought about using something like 'All' in the ELSE condition, and doing the same in the vw_Product_CYProcessedSales view just so I don't have to deal with an empty string.
If I were not to do that, is there some way I can handle the empty string issue within the join so that empty string records will be included in the query results?
On my office PC I have a strange problem with Data Flow Tasks. When I check-in a package into Source Control (Team Foundation) I usually have some empty Data Flow Tasks in the packages...even if I haven't changed that Flow Task.
This is really frustrating cause when running the package all goes well, but after a whil you notice that some tables are empty.
I am making a form that takes input for 1 to 5 students using VWD. With the help of previous posts I have been able to make the database insert query work properly. In my form I have a radio list that has the user select if they are entering information for 1, 2, 3,4, or 5 children. Depending on how many children are selected on the radio list, I am displaying the proper number of textboxes and validating the data using the handy RequiredFieldValidator. Now I am at the point where I want to perform the instert to the database depending on the selected number of children in the family. What is the general rule for best practices. Please keep in mind that it is my understanding that ALL fileds in a SQL insert statment must have data. Should I ...1) create alternative SQL statements depending on the textboxes displayed OR2) is it more common to insert a standard string or integer, depending on the datatype, into the unused textboxes to populate the unused fields? Sincerely,Mike
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
Hi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy
So I have an MDX query in an SSRS data set. Here is my MDX query:
Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses)
Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run.
How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
we have a database hosted online on sql server 05. at work, we have a sql 2000 query analyzer on a machine, and sql 2005 management studio on a machine. both machines can connect to the database, both can pull up the data from a certain table. but there are two columns that display info in query analyzer that show up as empty on management studio. not null, just empty. any ideas why?
