What Are The Magic Tables In Sql Server ?
Mar 11, 2008
Hello what does it mean ? Is it really posible in SQL Server ? If yes then How ?
ADVERTISEMENT
What Are Magic Tables?
Feb 23, 2004Can any one tell me what are magic tables in sql server?
View 6 Replies View RelatedMagic Tables With Text Columns
Oct 6, 2004Hi folks,
I need to execute some kind of
select a,b,c into #inserted from inserted
a = int
b = varchar(50)
c = text
Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to
select a,b into #inserted from inserted
Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.
Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)
Any suggestions ?
Thanks a lot!
Stefan
Looking For Some Magic
May 16, 2006... well, perhaps some sophistication. The setup:
Server A publishes to server B via transactional replication. Server A is to be shut down while some electrical work is done. So we fail over to server B for a couple days. My underpowered brain says to shut down replication, possibly fail over well in advance of the power outage.
Then, when the electrical work is done and server A is restarted: 1) stop work on server B, 2) ?copy? the database on B to A (I suppose after deleting it from A), and 3) redoing the replication from scratch.
Can anyone recommend a better process?
I am very gratefull for any enlightenment.
What Is Magic Table?
Jan 1, 2004Hi,
I want to know about magic table in SQL Server. What is actually magic table?
I tried to find information through google but I didnt able to get sufficient information about it. Can any budy help me in this regard?
Thanx.
Publishing Wizard - Bad Magic?
Aug 26, 2007I've been working with the Database Publishing Wizard and have found what I initially thought was a bug but have decided is a rather nasty "feature". Or am I missing something?
I have membership implemented in a live database. I took a backup and loaded it into a local database. I created a new table locally that is foreign-keyed off aspnet_roles, and added some data.
Using the Publishing Wizard for a partial deployment, I followed the steps which seem perfectly clear to script out ONLY the new table and its data. The screens provide a nice GUI for selecting specific database objects, and I carefully chose the one new table.
From good habits I looked at the script before deploying it. It drops and recreates aspnet_roles and aspnet_applications as well (and inserts the data from the development tables).
Presumably this is because of the foreign key relationships. But there's no need to do so. What if I or another developer had added in some roles to the live table in the meantime? This script would blow them away.
Am I missing an option with the Wizard that would script out only what is specified? I've tidied up the script (i.e. removed the undesirable sections) - but this seems to be a naughty wizard. Any thoughts?
What's Magic About DT_STR And Size 50
May 15, 2006Okay, this is probably my own stupidity but here goes. I am trying to make a very rudimentary package. I've got a data flow that has a flat file source. This source has fields delimited by the | symbol. I give the fields in the flat file source names. The first field is always exactly 1 character. When I use the advanced editor to look at the fields' properties, I see the first field, called ProductLine, is considered to be of SSIS internal data type DT_STR with a length of 50. I change the 50 to 1 because that is as long as that field is.
No matter what I do, when I close the advanced editor and then open it again, the field length for ProductLine is again set to 50. The field is 1 long, not 50 long. How can I make the setting of 1 be retained? Nothing I do changes it. What is the point of allowing the length to be editable if the edit is not retained?
Thanks in advance,
SW
Trigger :: Xp_smtp_sendmail :: Parameter Value In Message/subject :: Magic Table
Oct 23, 2007OK so now I'm setting up a trigger to email info to different people based on values INSERTed into one of my tables. I'm using an IF statement to determine who I'm giong to send the mail to, but I don't really understand how to include the data (parameters) in my message. Thanks for any help. Here's what I have so far.
Code Block
ALTER TRIGGER [smallin].[trig_test]
ON [smallin].[DATALIST]
AFTER INSERT
AS
declare @rc int,
@post bit,
@pre bit
SELECT @post = [POST], @pre = [PRE] FROM inserted
IF @post = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is post data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'
ELSE IF @pre = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is pre data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'
Unzipping A Zip File-The Magic Number In GZip Header Is Not Correct.Make Sure You Are Passing In A GZip Stream.
Jan 25, 2007I have the following code in an SSIS package and I get the following error:
Error: 0x0 at Unzip downloaded file, Boolean ReadGzipHeader(): Unable to decompress C:ETLPOSDataIngramWeeklyINVEN.zip; The magic number in GZip header is not correct. Make sure you are passing in a GZip stream.
Error: 0x4 at Unzip downloaded file: The Script returned a failure result.
Task failed: Unzip downloaded file
The zip file unzips perfectly using winzip utility, so that file is definitely not a problem here.
The code I am using in a script task is as follows:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Imports System.IO.Compression
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
Dim success As Boolean = True
Dim workFilePath As String
workFilePath = Dts.Variables("zipFilePath").Value.ToString()
If File.Exists(workFilePath) Then
If Not workFilePath.EndsWith(".zip") Then
Dts.Events.FireInformation(0, "", workFilePath + " is not compressed; skipping decompression", Nothing, -1, True)
Return
End If
Dim uncompressedFileName As String
Dim bytes(Int16.MaxValue) As Byte
Dim n As Integer = 1
Try
uncompressedFileName = Dts.Variables("unCompressedFileName").Value.ToString()
Dts.Events.FireInformation(0, "", "decompressing " + workFilePath + " to " + uncompressedFileName, Nothing, -1, True)
Using writer As New FileStream(uncompressedFileName, FileMode.Create)
Using compressedStream As Stream = File.Open(workFilePath, FileMode.Open, FileAccess.Read, FileShare.None)
Using unzipper As New GZipStream(compressedStream, CompressionMode.Decompress)
Do Until n = 0
n = unzipper.Read(bytes, 0, bytes.Length)
writer.Write(bytes, 0, n)
Loop
unzipper.Close()
End Using
compressedStream.Close()
End Using
writer.Close()
End Using
Catch ex As Exception
Dts.Events.FireError(0, ex.TargetSite().ToString(), "Unable to decompress " + workFilePath + "; " + ex.Message, Nothing, -1)
success = False
Finally
If success = False And File.Exists(uncompressedFileName) Then
Dts.TaskResult = Dts.Results.Failure
File.Delete(uncompressedFileName)
End If
End Try
Else
Dts.Events.FireError(0, "", workFilePath + " does not exist", Nothing, -1)
Dts.TaskResult = Dts.Results.Failure
Return
End If
End Sub
End Class
Instead of the GZipStream do I have to use some other stream class for a zip file. Please let me know.
Thanks,
Monisha
SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Exporting Data From Excel Tables To SQL Server Tables
Dec 9, 2007Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
Insert Records From Foxpro Tables To SQL Server Tables
Apr 22, 2004Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
Jan 24, 2008I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
ASP.NET 1.1 Component For Creating Pivote Tables With MS SQL Server And With MS SQL Server Analyse Services
Oct 13, 2006Превед!Prompt me please some asp.net 1.1 pivote table component that can use both SQL Server database (SQL queries) and Analyse Services database (MDX queries).Thanks.
View 3 Replies View RelatedSQL Server 2005: Copying Tables And Stored Procedures Between Databases On Same Server
Mar 5, 2008This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
Why I Cannot Create/edit Tables In VS.Net Server Explorer For SQL Server 2000 Developer Local Database?
Apr 7, 2006I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?
View 3 Replies View RelatedImport And Export Wizard: Transferring Multiple Tables From SQL Server 2005 To SQL Server 2000
Jun 15, 2007Hi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
SQL Server 2012 :: Query To Return Top 10 Tables On Each Database On Server?
Nov 21, 2013i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedCan I Access SQL Server 2000 Tables Via SSIS/SQL Server 2005
Apr 9, 2007Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?
View 1 Replies View RelatedTrack The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables
Dec 7, 2006We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedMoving Tables/Procedures From SQL Server Express To SQL Server
Mar 31, 2008My searches have come up blank, so I'm hoping someone else can point me in the right direction.
I'm done some development in SQL Server Express, working on tables and procedures. I've now got to move those tables/procedures into SQL Server and I don't know of an easy way to do this. Are there any tools I can take advantage of that would allow me to get all these objects moved? The only way I can see to do it is to move objects over one-by-one. This would be really time-consuming, and I'd like to avoid it if possible.
I'm positive I'm not the first one to try this, but what should I be searching for? Are there tools/procedures I should be looking for?
Appreciate any help I can get.
Thanks.
SQL Server 2014 :: Data Profiling Tables Within Server
Jun 18, 2015I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:
Null_Count
Null_Percentage
Total_Record_Count
I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.
Transact SQL :: Matching Tables From Server To Another Server Same Database
May 11, 2015I need to generate a script where it should check the tables of a database from one server to another server(If server 1 database XYZ has 10 tables then server 2 database XYZshould match 10 tables). If the tables matches then truncate tables data and insert the data from one server 1 database XYZ to server 2 database XYZ. If tables does not matches, then it should check and create that tables which are not matching.
View 8 Replies View RelatedUploading Tables On Sql Server (domain Server)
Mar 11, 2000Hi everybody,
Please suggest me a guideline to upload tables on sql server
for internet based application.
Actually I want to know the exact procedure what are the files to
upload (of sql server) OR i have to export the whole database
to the domain server.
Thank you
Harish
BCP - Between 2 SQL Server Tables
Feb 5, 2007Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!
View 5 Replies View RelatedSQL Server: How To See Tables
Jan 13, 2007I have VS and SQL server database engine installed in the my office PC (Both legal copies). I would like to see the tables and like to create the new tables / databases as well for learning. How to do that?
Please help me....
How Do I Join Two Tables In SQL Server?
Mar 4, 2008I have been reviewing the video series at this site and have found them very helpful but have a need...
I have two data tables one of which is quite large and another table that is small. They have at least one exact field in common, lets call it room number. How do I recognize both tables (in different databases) and join them so I can search a unique item in one table and select its associated data in the other table? I don't image there is a video on this topic for this VB.NET student?
Can You Have Linked SQL Server Tables?
Mar 9, 2004Hello,
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
Thanks
Josh
Delete From 3 Tables In Sql Server
Mar 25, 2004Hi,
I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:
Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"
CMD As New SqlCommand(Ssql, oConn)
CMD.Parameters.Add("@cust_ID", id)
I am also using a sqldatareader.
I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid
I tried the following delete:
strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "
Dim objCMD As New SqlCommand(strDelete, oConn)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()
I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated
Query Using Tables From 2 Server
Oct 31, 2000Can anybody tell me how to use the query analyzer to
connect to 2 different servers in one query?
If I access a database on another server like that
[servername].[dbname].[dbo].[tablename] I recieve
an error that says: Server servername is not configured
for DATA ACCESS.
Thanks in advance
Gert
Rotate SQL Server Tables
Feb 3, 2003Is there a way to rotate db tables - i.e. to pivot them?
Thanks,
Bianca
Replicating Tables In SQL Server
Sep 23, 1998I have a table that is used for reporting, the problem is that the data in the table is refreshed every 30 minutes with a bulk insert. I am trying to find a way to have two tables that are mirror images of each other and when the loading table is loaded, then the table assumes the identity of the reporting table. The basic prinicpal is I need to have the table be available almost all the time and when the bulk insert is happening, users cannot query. Any help would be greatly appreciated.
Thanks in advance
Marc Brown
Copying Tables In SQL SERVER
Aug 30, 2004I have a 100m row table that I need to come from one database to another database in SQL SERVER.
The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.
Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)
and on a 100m row table it is taking around 52 hours. Not acceptable.