List availables tables: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Load Ordering for Dimension and Fact tables

Hi ,

I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?

I would be really grateful.

Thanks

The SSIS package designer contains a Control Flow tab and a Data Flow Tab. On the Control Flow tab, you would create two Data Flow tasks linked by a precedence constraint. The first task would load the dimension data and the second would load the fact data only if the first task succeeds or completes depending on the precedence conditions you configure.

Was your question this elementary?

|||In my question I said, I can't use two data flows, I have to use only one data flow. So is there a way to this?

Thanks,|||

DW Developer wrote:

Hi ,
I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?
I would be really grateful.
Thanks

Very very good question. The feature you are referring to is sometimes called "Intrinsic Flow Priority". It doesn't exist in SSIS at the moment and I hope to god they put it into the next release. I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058 and have noted that it exists in Informatica. I would appreicate it if you could click-through and add some comments. We're more likely to get it if more people ask for it and give real reasons why they need it.

In the meantime, you can achieve the same using raw files to pass data between different data-flows. This is explained here:

Splitting order detail and order header information from one file into multiple tables
(http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx)

HTH

-Jamie

load from sqlserver to its instance

I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring
up the properties, click on Server Options, then check the Data Access box.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In Enterprise Mgr, right click on the =linked server - CNS-SFO-A46\QA. Bring up the properties, =click on Server Options, then check the Data Access box.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Sandra" wrote in message news:00db01c3d620$c2=708f40$a601280a@.phx.gbl...I need to load data from a table in sqlserver to a table in an =instance of the same server. The query need to be run in the SQL server itself not =in the instance. So...insert into [servername\instancename].[dbname].[dbo].table1 (col1,col2)selectt2.col1,t2.col2from table2 as =t2 I keep getting this message:Server 'CNS-SFO-A46\QA' is =not configured for DATA ACCESS.Thanks in advance for you =help.

--=_NextPart_000_038B_01C3D5F9.27781520--|||I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you find the linked server under Security->Linked Servers?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:058101c3d631$7e232580$a101280a@.phx.gbl...
I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Did you find the linked server under Security->Linked Servers?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Sandra" wrote in message news:058101c3d631$7e=232580$a101280a@.phx.gbl...I'm affraid I don't see a check box for Data Access. Is it possible I'm =looking in the wrong place?>--Original Message-->In =Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring>up the properties, click on Server Options, then check =the Data Access box.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Sand=ra" wrote in message>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...>=I need to load data from a table in sqlserver to a table>in an instance =of the same server. The query need to be>run in the SQL server itself =not in the instance. So...>>insert into [servername\instancename].[dbname].>[dbo].table1 (>col1,>col2)>select>t2.col1,>t2.col2>from table2 as t2>>I keep getting this =message:>>Server 'CNS-SFO-A46\QA' is not configured for DATA =ACCESS.>>Thanks in advance for you help.>

--=_NextPart_000_0038_01C3D60C.8C53A730--|||Yes, I did and I don't find the name of the server listed
but when I try to create New Linked Server it says the
server already exist. The same response I get when I try
to run EXEC sp_addlinkedserver
@.server = 'servername\instancename'
Also, I see the name of the server in the sysservers table.
>--Original Message--
>Did you find the linked server under Security->Linked
Servers?
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:058101c3d631$7e232580$a101280a@.phx.gbl...
>I'm affraid I don't see a check box for Data Access. Is it
>possible I'm looking in the wrong place?
>>--Original Message--
>>In Enterprise Mgr, right click on the linked server -
CNS-
>SFO-A46\QA. Bring
>>up the properties, click on Server Options, then check
>the Data Access box.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>>I need to load data from a table in sqlserver to a table
>>in an instance of the same server. The query need to be
>>run in the SQL server itself not in the instance. So...
>>insert into [servername\instancename].[dbname].
>>[dbo].table1 (
>>col1,
>>col2)
>>select
>>t2.col1,
>>t2.col2
>>from table2 as t2
>>I keep getting this message:
>>Server 'CNS-SFO-A46\QA' is not configured for DATA
ACCESS.
>>Thanks in advance for you help.
>|||The server might already be listed under "remote servers". This would happen
for example if you were doing replication from one instance to another. You
can allow data access by running the procedure
sp_serveroption 'server' ,
'data access',
'true'
Hope this helps,
Joe Lax
<anonymous@.discussions.microsoft.com> wrote in message
news:06be01c3d641$bbad9740$a101280a@.phx.gbl...
> Yes, I did and I don't find the name of the server listed
> but when I try to create New Linked Server it says the
> server already exist. The same response I get when I try
> to run EXEC sp_addlinkedserver
> @.server = 'servername\instancename'
> Also, I see the name of the server in the sysservers table.
>
>
> >--Original Message--
> >Did you find the linked server under Security->Linked
> Servers?
> >
> >--
> > Tom
> >
> >----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >..
> >"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:058101c3d631$7e232580$a101280a@.phx.gbl...
> >I'm affraid I don't see a check box for Data Access. Is it
> >possible I'm looking in the wrong place?
> >
> >>--Original Message--
> >>In Enterprise Mgr, right click on the linked server -
> CNS-
> >SFO-A46\QA. Bring
> >>up the properties, click on Server Options, then check
> >the Data Access box.
> >>
> >>--
> >>Tom
> >>
> >>----
> -
> >--
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com/sql
> >>
> >>
> >>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
> >>I need to load data from a table in sqlserver to a table
> >>in an instance of the same server. The query need to be
> >>run in the SQL server itself not in the instance. So...
> >>
> >>insert into [servername\instancename].[dbname].
> >>[dbo].table1 (
> >>col1,
> >>col2)
> >>select
> >>t2.col1,
> >>t2.col2
> >>from table2 as t2
> >>
> >>I keep getting this message:
> >>
> >>Server 'CNS-SFO-A46\QA' is not configured for DATA
> ACCESS.
> >>
> >>Thanks in advance for you help.
> >>
> >sql

Load from several CSV files

I have to load around 68 CSV files into one table. I have named the files
1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
load these. I am not very proficient with VBScriptTry using a global variable for the file name, a Dynamic Properties Task and
an ActiveX Script Task to programmatically loop through and change the name
of the input file for each import.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:uzU8pR7vFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I have to load around 68 CSV files into one table. I have named the files
>1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
>load these. I am not very proficient with VBScript
>|||You can concatenate the files and create a big one to be imported.
This is the help for dos command "copy".
*****
C:\>copy /?
Copies one or more files to another location.
COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]
source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
/D Allow the destination file to be created decrypted
destination Specifies the directory and/or filename for the new file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.
The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.
To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).
*****
AMB
"XXX" wrote:

> I have to load around 68 CSV files into one table. I have named the files
> 1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
> load these. I am not very proficient with VBScript
>
>

Load File In A Table

HELLO EVERYBODY...
I NEED YOUR HELP... I HAVE TO LOAD A FILE IN A TABLE OF MY DATABASE AND LATER MANIPULATING THE FILE... HOW CAN I DO THIS?
I'M LOOKING FOR INFORMATION ABOUT RAW DATATYPE AND LOB BUT I COULDN'T DO IT...
PLEASE HELP ME... :(
THANKS...what kind of database do you have to load into?

with oracle you can use sql*loader
with sybase you can use bcp
with db2 you can import with allows fixed length acsii and delimited acii

bernd

Load data from text file into some table implemented in stored procedure.

Hello, I want to load data from text file to MS SQL DB table.

In MySQL, it is the "LOAD DATA INFILE..." query statement.

What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?

I think you need some third party tool, which will do the needed convertions. Executing query form a file you need to read the SQLCMD form BOL, this is command line utility.

|||hi remedios,

you can use the bcp(Bulk Copy Program) to basically do the same as in MySql. or if not why not use DTS in Enterprise Manager and select the text file as the Source

hth

Load data from excel to sql server table

Hi,
I have data in a Excel spread sheet that has been saved as a csv file.
I need to load this data into a sql server table. What is the best way
to do this. Any ideas?
Thanks in advance.use DTS Wizard
vt
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1164900614.237522.111940@.l39g2000cwd.googlegroups.com...
> Hi,
> I have data in a Excel spread sheet that has been saved as a csv file.
> I need to load this data into a sql server table. What is the best way
> to do this. Any ideas?
> Thanks in advance.
>|||Thanks a lot.. that does seem to work.. but I need a script or a
program to do that.. what is the sql command that could be used ? I'm
new to DB.. sorry if I'm asking a dumb question :-)
vt wrote:
> use DTS Wizard
>
> vt
> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
> news:1164900614.237522.111940@.l39g2000cwd.googlegroups.com...
> > Hi,
> >
> > I have data in a Excel spread sheet that has been saved as a csv file.
> > I need to load this data into a sql server table. What is the best way
> > to do this. Any ideas?
> >
> > Thanks in advance.
> >|||Yes DTS or SSIS would be the best choice.
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1164900614.237522.111940@.l39g2000cwd.googlegroups.com...
> Hi,
> I have data in a Excel spread sheet that has been saved as a csv file.
> I need to load this data into a sql server table. What is the best way
> to do this. Any ideas?
> Thanks in advance.
>|||check
OPENDATASOURCE function
vt
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1164903796.447484.26940@.f1g2000cwa.googlegroups.com...
> Thanks a lot.. that does seem to work.. but I need a script or a
> program to do that.. what is the sql command that could be used ? I'm
> new to DB.. sorry if I'm asking a dumb question :-)
> vt wrote:
>> use DTS Wizard
>>
>> vt
>> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
>> news:1164900614.237522.111940@.l39g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I have data in a Excel spread sheet that has been saved as a csv file.
>> > I need to load this data into a sql server table. What is the best way
>> > to do this. Any ideas?
>> >
>> > Thanks in advance.
>> >
>|||or you can use
OPENROWSET function as well
vt
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1164903796.447484.26940@.f1g2000cwa.googlegroups.com...
> Thanks a lot.. that does seem to work.. but I need a script or a
> program to do that.. what is the sql command that could be used ? I'm
> new to DB.. sorry if I'm asking a dumb question :-)
> vt wrote:
>> use DTS Wizard
>>
>> vt
>> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
>> news:1164900614.237522.111940@.l39g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I have data in a Excel spread sheet that has been saved as a csv file.
>> > I need to load this data into a sql server table. What is the best way
>> > to do this. Any ideas?
>> >
>> > Thanks in advance.
>> >
>

Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

|||have you tried to convert the file to another format using excel?|||Try using the OLE DB stage.|||can the ole db destination handle .dat files? i didn't see any mention of that in the documentation.|||

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

|||i have the same problem like you, but i don't have solve, why don't you post your solution,
and how to get data from .dat file using by sql statement
thanks for your help
any supporter can help me, can you show me the detail waysql

Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

load both parent and child into one table!

Hi, how can load both parent and child into one table use the
SQLXMLBulkload.3.0 object like following? Thanks a lot.
Hongtao
<xml>
<ti>date</ti>
<origin>name</origin>
<title>
<titledetail>content</titledetail>
</title>
</xml>
into table
CREATE TABLE Title (
ti VARCHAR (100) NOT NULL ,
origin VARCHAR (100) NOT NULL ,
titledetail VARCHAR (100) NOT NULL )
)
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You would create an XSD where the "xml" element maps to a table and "ti",
"origin", and "Titledetail" map to columns. Then set sql:is-constant="true"
on the "title" element. I think that should do it.
Irwin
"henry job" <anonymous@.comcast.com> wrote in message
news:uL7O71LBFHA.3840@.tk2msftngp13.phx.gbl...
>
> Hi, how can load both parent and child into one table use the
> SQLXMLBulkload.3.0 object like following? Thanks a lot.
> Hongtao
> <xml>
> <ti>date</ti>
> <origin>name</origin>
> <title>
> <titledetail>content</titledetail>
> </title>
> </xml>
> into table
> CREATE TABLE Title (
> ti VARCHAR (100) NOT NULL ,
> origin VARCHAR (100) NOT NULL ,
> titledetail VARCHAR (100) NOT NULL )
> )
> GO
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Irwin, thank you very much. I am new with XML. Data xml like
following. Could you post an XSD? thank you very much!!
Hongtao
<xml>
<ti>date</ti>
<origin>name</origin>
<title>
<titleid>id</titleid>
<titledetail>content</titledetail>
</title>
</xml>
into table
CREATE TABLE Title (
ti VARCHAR (100) NOT NULL ,
origin VARCHAR (100) NOT NULL ,
titledetail VARCHAR (100) NOT NULL )
)
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I believe it would be something like:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="xml" sql:relation="foo" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ti" sql:field="bar1" />
<xsd: element name="origin" sql:field="bar2"/>
<xsd:element name="title" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="titleid" sql:field="bar3"/>
<xsd:element name="titledetail" sql:field="bar4"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
You would have to change "foo" to the table name and "bar" 1-4 to column
names, and may need to / want to add data type info.
"henry job" <anonymous@.comcast.com> wrote in message
news:ucJe2XOBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Hi Irwin, thank you very much. I am new with XML. Data xml like
> following. Could you post an XSD? thank you very much!!
> Hongtao
> <xml>
> <ti>date</ti>
> <origin>name</origin>
> <title>
> <titleid>id</titleid>
> <titledetail>content</titledetail>
> </title>
> </xml>
> into table
> CREATE TABLE Title (
> ti VARCHAR (100) NOT NULL ,
> origin VARCHAR (100) NOT NULL ,
> titledetail VARCHAR (100) NOT NULL )
> )
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Irwin, thank you very much.
Hongta
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I've looked *everywhere* for something like this, and this is the best
example ever!
KB Article 316005 should include this information.
Chris Leiter
MCSE CCNA MCT MCDST MCSA
"Irwin Dolobowsky [MS]" wrote:

> I believe it would be something like:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="xml" sql:relation="foo" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ti" sql:field="bar1" />
> <xsd: element name="origin" sql:field="bar2"/>
> <xsd:element name="title" sql:is-constant="true">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="titleid" sql:field="bar3"/>
> <xsd:element name="titledetail" sql:field="bar4"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> You would have to change "foo" to the table name and "bar" 1-4 to column
> names, and may need to / want to add data type info.
>
> "henry job" <anonymous@.comcast.com> wrote in message
> news:ucJe2XOBFHA.3524@.TK2MSFTNGP15.phx.gbl...
>
>
sql

load both parent and child into one table!

Hi, how can load both parent and child into one table use the
SQLXMLBulkload.3.0 object like following? Thanks a lot.
Hongtao
<xml>
<ti>date</ti>
<origin>name</origin>
<title>
<titledetail>content</titledetail>
</title>
</xml>
into table
CREATE TABLE Title (
ti VARCHAR (100) NOT NULL ,
origin VARCHAR (100) NOT NULL ,
titledetail VARCHAR (100) NOT NULL )
)
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You would create an XSD where the "xml" element maps to a table and "ti",
"origin", and "Titledetail" map to columns. Then set sql:is-constant="true"
on the "title" element. I think that should do it.
Irwin
"henry job" <anonymous@.comcast.com> wrote in message
news:uL7O71LBFHA.3840@.tk2msftngp13.phx.gbl...
>
> Hi, how can load both parent and child into one table use the
> SQLXMLBulkload.3.0 object like following? Thanks a lot.
> Hongtao
> <xml>
> <ti>date</ti>
> <origin>name</origin>
> <title>
> <titledetail>content</titledetail>
> </title>
> </xml>
> into table
> CREATE TABLE Title (
> ti VARCHAR (100) NOT NULL ,
> origin VARCHAR (100) NOT NULL ,
> titledetail VARCHAR (100) NOT NULL )
> )
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi Irwin, thank you very much. I am new with XML. Data xml like
following. Could you post an XSD? thank you very much!!
Hongtao
<xml>
<ti>date</ti>
<origin>name</origin>
<title>
<titleid>id</titleid>
<titledetail>content</titledetail>
</title>
</xml>
into table
CREATE TABLE Title (
ti VARCHAR (100) NOT NULL ,
origin VARCHAR (100) NOT NULL ,
titledetail VARCHAR (100) NOT NULL )
)
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I believe it would be something like:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="xml" sql:relation="foo" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ti" sql:field="bar1" />
<xsd: element name="origin" sql:field="bar2"/>
<xsd:element name="title" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="titleid" sql:field="bar3"/>
<xsd:element name="titledetail" sql:field="bar4"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
You would have to change "foo" to the table name and "bar" 1-4 to column
names, and may need to / want to add data type info.
"henry job" <anonymous@.comcast.com> wrote in message
news:ucJe2XOBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Hi Irwin, thank you very much. I am new with XML. Data xml like
> following. Could you post an XSD? thank you very much!!
> Hongtao
> <xml>
> <ti>date</ti>
> <origin>name</origin>
> <title>
> <titleid>id</titleid>
> <titledetail>content</titledetail>
> </title>
> </xml>
> into table
> CREATE TABLE Title (
> ti VARCHAR (100) NOT NULL ,
> origin VARCHAR (100) NOT NULL ,
> titledetail VARCHAR (100) NOT NULL )
> )
> GO
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi Irwin, thank you very much.
Hongta
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I've looked *everywhere* for something like this, and this is the best
example ever!
KB Article 316005 should include this information.
Chris Leiter
MCSE CCNA MCT MCDST MCSA
"Irwin Dolobowsky [MS]" wrote:

> I believe it would be something like:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="xml" sql:relation="foo" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ti" sql:field="bar1" />
> <xsd: element name="origin" sql:field="bar2"/>
> <xsd:element name="title" sql:is-constant="true">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="titleid" sql:field="bar3"/>
> <xsd:element name="titledetail" sql:field="bar4"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> You would have to change "foo" to the table name and "bar" 1-4 to column
> names, and may need to / want to add data type info.
>
> "henry job" <anonymous@.comcast.com> wrote in message
> news:ucJe2XOBFHA.3524@.TK2MSFTNGP15.phx.gbl...
>
>

Monday, March 26, 2012

Load a text file with email addresses and compare against a database table that has email addres

Hello ALL

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

I also want to update some users using a different text file.

Please help me with the best way to do this

Thanks in advance

You'd use a data flow with a Flat File Source to read the text file, a Lookup to check whether the email address exists, and an OLE DB Command to issue the DELETE statement. Or you write the matching rows to a "temp" table using an OLE DB Destination, and use an Execute SQL task after the data flow to issue a batch DELETE statement. UPDATES can be done the same way.

Working through the tutorials in Books Online will introduce you to some of these concepts.

|||

jwelch

Could you please help may be with an example or something I will really appreciate that.

becuase I don't know what OLE DB command is.

so are you suggesting like so

text file connection

lookup

OLE DB COmmand (what is this?) (issue the delete or update command)

I want to be able to do delete from table A where A.email_address = text_file.email_address

Thanks for your help

|||

The OLE DB Command is one of the transforms in the toolbox in Visual Studio (same place you found the lookup).

The basic flow you have above is correct.

|||

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

|||

NewbietoSSIS wrote:

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

Looks like it.

Friday, March 23, 2012

Little help with a ReturnValue from SQL

I use a SP to write resident information to the SQL table. Then, I want to get the newly created RESIDENT_ID and send it back to my page.

Can someone give me a hand with it? I get the error:
"Cast from type 'DBNull' to type 'Integer' is not valid. "

Here is my SP:


CREATE PROCEDURE LS_resident_add
@.RESIDENT_ID int output,
@.HOUSE_ID as int,
@.Type as int = '0',
@.FName as varchar(50)=NULL,
@.LName as varchar(50)=NULL,
@.NickName as varchar(50)=NULL,
@.Email as varchar(50)=NULL,
@.Notes as varchar(1000)=NULL,
@.Access_Level as int = '0',
@.Password as varchar(8)=NULL,
@.Status as int = '0'AS
INSERT INTO Street_Resident
(House_ID,
Type,
FName,
LName,
NickName,
Email,
Notes,
Access_Level,
Password,
Status)
VALUES
(@.House_ID,
@.Type,
@.FName,
@.LName,
@.NickName,
@.Email,
@.Notes,
@.Access_Level,
@.Password,
@.Status)
GO

Here is my script:


Sub resident_add(Source as Object, E as EventArgs)
Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_LindenStreet"))
Dim myCommand as New SqlCommand("LS_resident_add", myConnection)
myCommand.CommandType=CommandType.StoredProceduremyCommand.Parameters.Add(New SQLParameter("@.HOUSE_ID", frm_HOUSE_ID.SelectedItem.Value))
myCommand.Parameters.Add(New SQLParameter("@.Type", frm_Type.SelectedItem.Value))
IF frm_FName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.FName", frm_FName.Text))
END IF
IF frm_LName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.LName", frm_LName.Text))
END IF
IF frm_NickName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.NickName", frm_NickName.Text))
END IF
IF frm_Email.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Email", frm_Email.Text))
END IF
IF frm_Notes.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Notes", frm_Notes.Text))
END IF
IF frm_Access_Level.SelectedIndex > -1 THEN
myCommand.Parameters.Add(New SQLParameter("@.Access_Level", frm_Access_Level.SelectedItem.Value))
END IF
IF frm_Password.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Password", frm_Password.Text))
END IF
 myCommand.Parameters.Add(New SQLParameter("@.Status", 1))
'Returns the new Resident ID so we can add any activities they signed up for
MyCommand.Parameters.Add("@.RESIDENT_ID", SqlDbType.Int)
MyCommand.Parameters("@.RESIDENT_ID").Direction = ParameterDirection.Output
myCommand.Connection.Open()
myCommand.ExecuteNonQuery
'Returns the new Resident ID so we can add any activities they signed up for
RESIDENT_ID = myCommand.Parameters("@.RESIDENT_ID").Value
myCommand.Connection.Close()
'Resident_Activity_delete()
Resident_Activity_Update()
Notify_User()
Dim strClose As String
strClose = "<script>"
strClose &= "window.close();"
strClose &= "</"
strClose &= "script>"
Response.Write(strClose)
End Sub

You're not returning anything from the sproc, thus the null reference error. Add a line something like this to the stored procedure:

RETURN SCOPE_IDENTITY()

Don

Listing tables in a database

Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'

Listing rows "missing" information?

Here is how part of our database works. We have a table called "Events." Inside the "Events" table, we list various events which takes place for each of our clients. The first event for any client should be "Opened Case." However, some of my employees have not been listing this event so we do not know when the case was actually opened. In theory, all cases should have an "Opened Case" event in them. Is there a way to query that table to find which cases do not contain that event? Or is that more a programming issue? Thanks for any help?I'd suggest something like:SELECT DISTINCT caseID
FROM events AS a
WHERE NOT EXISTS (SELECT *
FROM events AS b
WHERE b.caseID = a.caseID
AND 'Case Opened' = b.eventType)
ORDER BY caseID-PatP|||or something like --select caseID
from events
group
by caseID
having 0
= sum(
case when eventType = 'Case Opened'
then 1 else 0
end ):)|||My, aren't we feeling deviant today! Heck, Rudy's solution will even work in MySQL!

-PatP|||deviant? heh

i was just thinking that yours involves a join (expensive), a NOT EXISTS (expensive), and a sort to remove dupes (expensive)

mine's just one sort, to do grouping

hey pat, did you give that Sock Hop channel a try? i was hoping you'd come back with some comment like "oh wow, i love that stuff!" (which was my reaction)sql

Wednesday, March 21, 2012

Listing my indexes...

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Or why not pick the one which is already in Books Online (under DBCC
SHOWCONTIG)?[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
that.[vbcol=seagreen]
and[vbcol=seagreen]
indexes.[vbcol=seagreen]
I
>
|||This is exactly what the sample code in BOL, under DBCC SHOWCONTIG does. the sample might have been
added to one of the BOL updates, though, so make sure you all are on current BOL (latest is from Jan
2004). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%236tbRXGtEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Because her procedure also does the index defrag he's looking to do.

>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> SHOWCONTIG)?
> that.
> and
> indexes.
> I
>
sql

Listing my indexes...

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Or why not pick the one which is already in Books Online (under DBCC
SHOWCONTIG)?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
that.[vbcol=seagreen]
and[vbcol=seagreen]
indexes.[vbcol=seagreen]
I[vbcol=seagreen]
>|||This is exactly what the sample code in BOL, under DBCC SHOWCONTIG does. the
sample might have been
added to one of the BOL updates, though, so make sure you all are on current
BOL (latest is from Jan
2004). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%236tbRXGtEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Because her procedure also does the index defrag he's looking to do.

>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> SHOWCONTIG)?
> that.
> and
> indexes.
> I
>

Listing my indexes...

Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
retrieve a list of all indexes for mytable. Under the "Name" column, I
notice several indexes that begin with "_WA" that aren't indexes I created;
I'm assuming these are SQL-internal indexes. Can someone explain what these
indexes are?
My ultimate goal is to populate a cursor with the names of my indexes and
then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
This will eventually become a scheduled job that runs weekly. However, I
don't want to be defragmented useless indexes (or those that I haven't
intentionally built).
Any pointers, insights would be appreciated.
Thanks
RozLook at example E in the DBCC SHOWCONTIG topic in Books Online.
--
Jacco Schalkwijk
SQL Server MVP
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
> created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Hi Roz
The _WA_Sys entries in sysindexes are for column statistics, not indexes.
Please read about statistics in the Books Online, and the database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
> created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Also, why reinvent the wheel. Tara Duggan has a proc that does just that.
Here you go:
http://weblogs.sqlteam.com/tarad
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Or why not pick the one which is already in Books Online (under DBCC SHOWCONTIG)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Also, why reinvent the wheel. Tara Duggan has a proc that does just that.
> Here you go:
> http://weblogs.sqlteam.com/tarad
> "Roz" <Roz@.discussions.microsoft.com> wrote in message
> news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
>> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
>> retrieve a list of all indexes for mytable. Under the "Name" column, I
>> notice several indexes that begin with "_WA" that aren't indexes I
> created;
>> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
>> indexes are?
>> My ultimate goal is to populate a cursor with the names of my indexes and
>> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
>> This will eventually become a scheduled job that runs weekly. However, I
>> don't want to be defragmented useless indexes (or those that I haven't
>> intentionally built).
>> Any pointers, insights would be appreciated.
>> Thanks
>> Roz
>|||Because her procedure also does the index defrag he's looking to do. :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Or why not pick the one which is already in Books Online (under DBCC
SHOWCONTIG)?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
> > Also, why reinvent the wheel. Tara Duggan has a proc that does just
that.
> > Here you go:
> >
> > http://weblogs.sqlteam.com/tarad
> >
> > "Roz" <Roz@.discussions.microsoft.com> wrote in message
> > news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> >> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> >> retrieve a list of all indexes for mytable. Under the "Name" column, I
> >> notice several indexes that begin with "_WA" that aren't indexes I
> > created;
> >> I'm assuming these are SQL-internal indexes. Can someone explain what
> > these
> >> indexes are?
> >>
> >> My ultimate goal is to populate a cursor with the names of my indexes
and
> >> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these
indexes.
> >> This will eventually become a scheduled job that runs weekly. However,
I
> >> don't want to be defragmented useless indexes (or those that I haven't
> >> intentionally built).
> >>
> >> Any pointers, insights would be appreciated.
> >>
> >> Thanks
> >> Roz
> >
> >
>|||This is exactly what the sample code in BOL, under DBCC SHOWCONTIG does. the sample might have been
added to one of the BOL updates, though, so make sure you all are on current BOL (latest is from Jan
2004). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%236tbRXGtEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Because her procedure also does the index defrag he's looking to do. :)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
>> Or why not pick the one which is already in Books Online (under DBCC
> SHOWCONTIG)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
>> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
>> > Also, why reinvent the wheel. Tara Duggan has a proc that does just
> that.
>> > Here you go:
>> >
>> > http://weblogs.sqlteam.com/tarad
>> >
>> > "Roz" <Roz@.discussions.microsoft.com> wrote in message
>> > news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
>> >> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
>> >> retrieve a list of all indexes for mytable. Under the "Name" column, I
>> >> notice several indexes that begin with "_WA" that aren't indexes I
>> > created;
>> >> I'm assuming these are SQL-internal indexes. Can someone explain what
>> > these
>> >> indexes are?
>> >>
>> >> My ultimate goal is to populate a cursor with the names of my indexes
> and
>> >> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these
> indexes.
>> >> This will eventually become a scheduled job that runs weekly. However,
> I
>> >> don't want to be defragmented useless indexes (or those that I haven't
>> >> intentionally built).
>> >>
>> >> Any pointers, insights would be appreciated.
>> >>
>> >> Thanks
>> >> Roz
>> >
>> >
>>
>

Listing and Changing Filegroup assignments in SSMS

Using SQL 2005 Server Management Studio how can I:
1. List the Filegroup being used by each Table (or vice versa)?
2. Move a Table to a different Filegroup?
Note on #2: My understanding is that if I change the storage location of a
clustered index then the table should move with it. But if I try to change
the Filegroup from Index Properties -> Storage tab I get an Error 1779
("Recreate failed for index 'pk'. ... Table already has a primary key defined
on it. Could not create constraint.")Hi Dave
"Dave Booker" wrote:
> Using SQL 2005 Server Management Studio how can I:
> 1. List the Filegroup being used by each Table (or vice versa)?
sp_help 'table' will give the data file location or you can query sys.tables
and sys.filegroups (see below)
> 2. Move a Table to a different Filegroup?
> Note on #2: My understanding is that if I change the storage location of a
> clustered index then the table should move with it. But if I try to change
> the Filegroup from Index Properties -> Storage tab I get an Error 1779
> ("Recreate failed for index 'pk'. ... Table already has a primary key defined
> on it. Could not create constraint.")
You would need to drop the PK or Clustered index first. Moving the
PK/clustered index will not move where the text is e.g.
CREATE DATABASE MyDB
ON PRIMARY
( NAME = MyDb_dat,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data1.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 ),
FILEGROUP Secondary
( NAME = MyDB_dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_Data2.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15 )
LOG ON
( NAME = Mydb_log,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MyDb_log.log',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
USe MyDb
GO
CREATE TABLE MyTab ( id int not null constraint PK_MyTable PRIMARY KEY,
col2 varchar(300),
txt text )
ON [PRIMARY]
TEXTIMAGE_ON [SECONDARY]
GO
EXEC sp_help MyTab
CREATE TABLE MyTab2 ( id int not null ,
col2 varchar(300),
txt text )
ON [PRIMARY]
GO
EXEC sp_help MyTab2
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab DROP CONSTRAINT PK_MyTable
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
ALTER TABLE MyTab ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (id)
ON [SECONDARY]
GO
EXEC sp_help MyTab
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
CREATE CLUSTERED INDEX Ind_MyTab2 ON MyTab2 (ID)
ON [SECONDARY]
GO
EXEC sp_help MyTab2
GO
SELECT t.name, s.name as IndexFilegroup, x.name AS TextFilegroup
FROM sys.Tables t
LEFT JOIN sys.filegroups x ON t.lob_data_space_id = x.data_space_id
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id IN (0,1)
JOIN sys.filegroups s ON i.data_space_id = s.data_space_id
GO
To move text columns you would need to create a new table and suck the data
out of the original table
If you have a FK referencing your PK or UNIQUE CLUSTERED index then the FK
would have to be dropped first and re-created after you PK/Index has been
re-created.
John

Friday, March 30, 2012

Load Ordering for Dimension and Fact tables

load from sqlserver to its instance

Load from several CSV files

Load File In A Table

Load data from text file into some table implemented in stored procedure.

Load data from excel to sql server table

Load data from excel to sql server table

Load data from excel to sql server table

Load data from .DAT file

Load data from .DAT file

load both parent and child into one table!

load both parent and child into one table!

Monday, March 26, 2012

Load a text file with email addresses and compare against a database table that has email addres

Friday, March 23, 2012

Little help with a ReturnValue from SQL

Listing tables in a database

Listing rows "missing" information?

Wednesday, March 21, 2012

Listing my indexes...

Listing my indexes...

Listing my indexes...

Listing and Changing Filegroup assignments in SSMS

List availables tables

Blog Archive

About Me