build sql script: building

Showing posts with label building. Show all posts

Sunday, March 25, 2012

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Thursday, March 22, 2012

Bulk Insert Related Tables - PKs

Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:

> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Tuesday, February 14, 2012

Built database with SQL Server Management Studio Express, how can I quikly add test data?

I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager.

I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums.

Please help.

Hi, did you see 'New Query' button in Management Studio? Click it, and a editor window will appear, just like in Query Analyzer.

Or you can press F8 to open 'Object Explorer' (under View menu), then you can manipulate database objects like you do in Enterprise Manager.

Building/Running a Package.

Hi,

I have a solution, and I have a few projects in this solution. Each project has a few packages in them. The problem/question I have is this: when I am working on one of the packages, I have only this package open. When I try to run/test this package, every single package in every project gets opened, recompiled/rebuilt before my current package gets to run. It's very frastrating and time consuming. Is there a way to somehow disable this weird behavior ? Is there a way to just build the package I am currently working on ?

Please, help.

Thanks,

Victor.

This behavior occurs if you configured the project to build deployment utility - unfortunately, opening package was required for this. You may switch the deployment utility off temporary to avoid this.|||

Thank you, Michael. I'll try that.

Otherwise, I'll have to create a solution for each package separately.

Building/Issueing a query with a field with a quote in it?

Using a language that using A4GL to connect to the database. From within the language(COBOL) we have the ability to add a simple query. All is fine with one exception. Some of the fields might have a quote mark in it.
we have a field in particular that a lot of our clients like to put an apostrophe in. such as 0001A'06, 2'11" and so on. Now from inside our programs and using the simple query we add something like 'where ap_id = '0001A'06' which i know is an error but is there a way around this or a way to make it work?You can use REPLACE on the string to change each single quote to two single quotes (NOT the "double-quote" character). But is looks like you are using direct dynamic SQL, and that is an invitation to SQL injection security attacks.|||That works all we have to do now is fix all of programs, (possible 800 or so).

Thanks again...|||COBOL and SQL Server?

It's an abomination I tells ya.....

Building with SQL server, but deploying with MSDE

I am building a site, I'd like to use SQL server, but once the site is finished it i for a organisation who do a lot of community voluntary work. So to keep the costs down I plan to install on their server a copy of MSDE as the datasource.

I know it is a simple question, but if I build the site using SQL server and deploy onto MSDE will the site still work?::I know it is a simple question, but if I build the site using SQL server and deploy onto MSDE
::will the site still work?

This depends on what you do in SqlServer.

Using Sql Server: works.
Using Full Text Search extension: No luck.

And any additional service - out of luck, too.

But if you just use what MSDE can deliber (core SQL), then - yes, it will still work.

Building View Dynamically .. Need Help!

This is my resultset
Value Name
1 A
101 A
2 B
10 B
70 B
But I want it in this way from SQL Server
A B
1 2
101 10
70hangar18 wrote:
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
You should probably do this in your client application, but, run this script
in query analyzer:
set nocount on
select 1 Value,'A' [Name] into #temp
union all select
101 ,'A'
union all select
2 ,'B'
union all select
10 ,'B'
union all select
70 ,'B'
select * from #temp
Select
ta.A,
tb.B
From
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [A]
FROM #temp t1
WHERE t1.Name='A') ta
Full outer join
(select
(select count(*) from #temp where Name=t1.Name AND
Value <= t1.Value) ID,
Value As [B]
FROM #temp t1
WHERE t1.Name='B') tb
ON ta.ID=tb.ID
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE TABLE #Test
(
col INT,
col1 CHAR(1)
)
INSERT INTO #Test VALUES (1,'A')
INSERT INTO #Test VALUES (20,'A')
INSERT INTO #Test VALUES (100,'A')
INSERT INTO #Test VALUES (10,'B')
INSERT INTO #Test VALUES (5,'B')
INSERT INTO #Test VALUES (3,'B')
SELECT
CASE WHEN col1 ='A' THEN col END AS 'A',
CASE WHEN col1 ='B' THEN col END AS 'B'
FROM #Test
"hangar18" <soni.somarajan@.wipro.com> wrote in message
news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
> This is my resultset
> Value Name
> 1 A
> 101 A
> 2 B
> 10 B
> 70 B
> But I want it in this way from SQL Server
> A B
> 1 2
> 101 10
> 70
>|||That was my first thought as well, but it results in:
A B
1 [NULL]
101 [NULL]
[NULL] 2
[NULL] 10
[NULL] 70
Not quite what the OP wants.
Bob
Uri Dimant wrote:
> CREATE TABLE #Test
> (
> col INT,
> col1 CHAR(1)
> )
> INSERT INTO #Test VALUES (1,'A')
> INSERT INTO #Test VALUES (20,'A')
> INSERT INTO #Test VALUES (100,'A')
> INSERT INTO #Test VALUES (10,'B')
> INSERT INTO #Test VALUES (5,'B')
> INSERT INTO #Test VALUES (3,'B')
>
> SELECT
> CASE WHEN col1 ='A' THEN col END AS 'A',
> CASE WHEN col1 ='B' THEN col END AS 'B'
> FROM #Test
>
>
>
> "hangar18" <soni.somarajan@.wipro.com> wrote in message
> news:1133444216.471437.166710@.o13g2000cwo.googlegroups.com...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 1 Dec 2005 05:36:56 -0800, hangar18 wrote:

>This is my resultset
>Value Name
>1 A
>101 A
>2 B
>10 B
>70 B
>But I want it in this way from SQL Server
>A B
>1 2
>101 10
> 70
Hi hangar18,
Bob's post will work. But just for fun, here's another possibility:
SELECT MAX(A) AS A, MAX(B) AS B
FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
CASE WHEN a.col1 ='B' THEN a.col END AS B,
(SELECT COUNT(*)
FROM #Test AS b
WHERE b.col1 = a.col1
AND b.col <= a.col) AS Rank
FROM #Test AS a) AS d
GROUP BY Rank
ORDER BY Rank
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The basic principle of a tiered architecture is that display and
formatting is done in the front end and ** never** in the back end.
This a more basic programming principle than just SQL and RDBMS. This
should have been covered in the first year of your comp sci courses.|||When are you going to learn, perhaps you should actually do some
programming!
Formatting should be done where it is most efficient to do it, you can not
blankly state formatting be done in the front end and **never** the backend
its just not true - any programmer knows that.
Data manipulation is best done in the SQL Server and may well include
formatting, consider - paging, pivoting, security to name but a couple.
I think it is very irresponsible for you to keep taking this line when so
many times myself and other posters have given plenty of examples of when to
do formatting in the engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133490026.932802.161270@.o13g2000cwo.googlegroups.com...
> The basic principle of a tiered architecture is that display and
> formatting is done in the front end and ** never** in the back end.
> This a more basic programming principle than just SQL and RDBMS. This
> should have been covered in the first year of your comp sci courses.
>|||Hugo Kornelis wrote:
> SELECT MAX(A) AS A, MAX(B) AS B
> FROM (SELECT CASE WHEN a.col1 ='A' THEN a.col END AS A,
> CASE WHEN a.col1 ='B' THEN a.col END AS B,
> (SELECT COUNT(*)
> FROM #Test AS b
> WHERE b.col1 = a.col1
> AND b.col <= a.col) AS Rank
> FROM #Test AS a) AS d
> GROUP BY Rank
> ORDER BY Rank
>
Clever!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Building text files

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpdI'd go with the sproc and bcp out

Just change the IN to OUT

SET @.cmd = 'bcp ' + @.db_name + '..ETRS_ASI_FED_TEMP in '
+ @.FilePathAndName + ' -t"\t" -c -S' + @.@.servername + ' -Uscrub -Pscrub'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''''

Select @.Command_String

Exec(@.Command_String)|||Given this problem, I would probably try a VB or PERL script to extract the data. I know how to call bcp from PERL, but VB is still a bit new to me. Fortunately, if the data set you are exporting is small (few thousand rows), you could get away with just using FileObject writes. Biggest problem I have had with bcp is remembering to check the error file for any problems. Again, easy for me in PERL, but VB...|||Thanks! bcp is what I was looking for. Glad I can avoid DTS all together.

-bpd

Building Table for Duplicate Records

I'm new to VBA and SQL, and help is limited. The following code basically selects duplicate records based on name (last name only, I think). I sort of understand up until the 'WHERE' statement, then I'm lost -- too many parenths and brackets! Can someone please explain the structure and dynamics of the statements? In addition to this, I have data that consists of a NAME field (where first and last are together, separated by a space) which I need to SPLIT into two fields. I've been told to use the SPLIT Function in VBA, but am not sure how to implement.

'delete previous records
strSQL = "DELETE * FROM tblDuplicates"
db.Execute strSQL

'insert duplicate names from table BEE into tblDuplicates
strSQL = "INSERT INTO tblDuplicates (fldID, fldLName, fldFName, fldDate, fldType, fldHier, fldPhoneNum) " & _
"SELECT BEE.ID, BEE.fldLast, BEE.fldFirst, BEE.fldDate, BEE.fldType, BEE.fldHierarchy, BEE.fldPhone FROM BEE " & _
"WHERE (((BEE.fldLast) In " & _
"(SELECT [fldLast] FROM [BEE] As Tmp GROUP BY [fldLast],[fldFirst] " & _
"HAVING Count(*)>1 And [fldFirst] = [BEE].[fldFirst])))"

db.Execute strSQL

Quote:

Originally Posted by dayharbor

Go through an SQL tutorial and look up the following
in clause,
count function
group by clause
and then come back to the statement and see if you still don't understand it. For the VBA split function, you'll need to look up a VBA tutorial or ask in the Access (or is it VB?) forum.|||

Quote:

Originally Posted by r035198x

Okay.

Thanks.

Building SQL using variables

Hello all!

I am sure there is a technical name for this, but I am trying to build a sql statement using variables... where the variables would be entire clauses within the statement, not just values. This will ultimately be used in a stored procedure.

ie. Focus on the @.AndClause variable

-----------
declare @.AndClause varchar(128)
select @.AndClause = ' AND lastname like ''jharris%'''

SELECT *
FROM my_customer_table
WHERE 1=1
@.AndClause
-----------

I have seen this done before but can not find it in an of my references. Thank you for your helpdeclare @.AndClause varchar(128)
set @.AndClause = ' AND lastname like ''jharris%'''
declare @.vSQL varchar(200)
set @.vSQL = 'SELECT *
FROM my_customer_table
WHERE 1=1' + @.AndClause

exec(@.vSQL)|||I missed the plus sign... Thanks alot Jora! Do you know if there is technical name for this?|||euh ... building dynamic sql statements? Don't think there is one term for it. Also, you can use the stored procedure sp_executesql for executing dynamic queries. See Books Online for more info on the differences between the two methods.

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Hello,
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
ThanksYou could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> >
> >
> > I have load dates for each row in my database table. I'm trying to
build
> a
> > query that will give me all of the unique years, then all of the unique
> > months for that year, then finally all of the unique days for that
month.
> >
> >
> >
> > I'm trying to load a treeview control with the results like so.
> >
> >
> >
> > Year
> >
> > |_
> >
> > Month
> >
> > |_
> >
> > Day
> >
> > Day
> >
> > Etc.
> >
> > Year
> >
> > |_
> >
> > Month
> >
> > |_
> >
> > Day
> >
> > Etc.
> >
> >
> >
> > If anyone has any ideas or experience on how to do this I would
appreciate
> > it.
> >
> >
> >
> > Thanks
> >
> >
>|||Can you show your table structure (see http://www.aspfaq.com/5006)?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> > You could simply write a query that does a join with a calendar table.
> > http://www.aspfaq.com/2519
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "MJB" <mb2@.email.com> wrote in message
> > news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > >
> > >
> > > I have load dates for each row in my database table. I'm trying to
> build
> > a
> > > query that will give me all of the unique years, then all of the
unique
> > > months for that year, then finally all of the unique days for that
> month.
> > >
> > >
> > >
> > > I'm trying to load a treeview control with the results like so.
> > >
> > >
> > >
> > > Year
> > >
> > > |_
> > >
> > > Month
> > >
> > > |_
> > >
> > > Day
> > >
> > > Day
> > >
> > > Etc.
> > >
> > > Year
> > >
> > > |_
> > >
> > > Month
> > >
> > > |_
> > >
> > > Day
> > >
> > > Etc.
> > >
> > >
> > >
> > > If anyone has any ideas or experience on how to do this I would
> appreciate
> > > it.
> > >
> > >
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
> > Hmm, looks like it could help. Unfortunately, I only want my treeview
to
> > show the dates that are actually in the database, where as this approach
> > seems to just show all dates between a range.
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> > > You could simply write a query that does a join with a calendar table.
> > > http://www.aspfaq.com/2519
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "MJB" <mb2@.email.com> wrote in message
> > > news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> > > > Hello,
> > > >
> > > >
> > > >
> > > > I have load dates for each row in my database table. I'm trying to
> > build
> > > a
> > > > query that will give me all of the unique years, then all of the
> unique
> > > > months for that year, then finally all of the unique days for that
> > month.
> > > >
> > > >
> > > >
> > > > I'm trying to load a treeview control with the results like so.
> > > >
> > > >
> > > >
> > > > Year
> > > >
> > > > |_
> > > >
> > > > Month
> > > >
> > > > |_
> > > >
> > > > Day
> > > >
> > > > Day
> > > >
> > > > Etc.
> > > >
> > > > Year
> > > >
> > > > |_
> > > >
> > > > Month
> > > >
> > > > |_
> > > >
> > > > Day
> > > >
> > > > Etc.
> > > >
> > > >
> > > >
> > > > If anyone has any ideas or experience on how to do this I would
> > appreciate
> > > > it.
> > > >
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>

Building Search

I've never done this before and I have all kinds of issues conflicting in my head (search rank, noise words, injection attacks ..etc). simply i need to search several columns in a table in the database using one search text (just like the simple search in Google). if multiple words are you used then the search should search for each of them. also manage to ignore noise words and other issues.

what is the best way of doing this? I looked at FTS in SQL 2000 but didn't know how to handle all the above mentioned issues. this should be simple, right? but i have been looking all day. I guess i don't know what im looking for because i've never implemented a web search b4.

please help

p.s. I know t-sql and asp.net/c# well.SQL Server hasFull-Text Search capabilities built into it. This might be a solution to your problem.

Terri

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building reports ?

The databases I have in the SQL Server 2000 and i need to use
reporting services in the SQL Server 2005. If i am in SQL Server 2005 and i
want to just have connection to my databases in 2000 without restoring
the db's in the 2005. Can i build the reports using SSRS 2005? If yes
then what steps do i need to know to start building RS reports besides
connecting to SR2000 db?
Thanks.On Jun 20, 12:55 pm, GGill <G...@.discussions.microsoft.com> wrote:
> The databases I have in the SQL Server 2000 and i need to use
> reporting services in the SQL Server 2005. If i am in SQL Server 2005 and i
> want to just have connection to my databases in 2000 without restoring
> the db's in the 2005. Can i build the reports using SSRS 2005? If yes
> then what steps do i need to know to start building RS reports besides
> connecting to SR2000 db?
> Thanks.
When you make the reports, link them to the SQL Server 2000 server.
I've never had any problem pulling data from the older servers. You
would add the connection string the same way you would with the 2005
version. You can even make shared datasources. I just tested it, yep
it works. Just make a shared datasource and use that for all your
reports. It will link to the specified server/database as needed.
Hope that helps.|||Thank you.
"Ayman" wrote:
> On Jun 20, 12:55 pm, GGill <G...@.discussions.microsoft.com> wrote:
> > The databases I have in the SQL Server 2000 and i need to use
> > reporting services in the SQL Server 2005. If i am in SQL Server 2005 and i
> > want to just have connection to my databases in 2000 without restoring
> > the db's in the 2005. Can i build the reports using SSRS 2005? If yes
> > then what steps do i need to know to start building RS reports besides
> > connecting to SR2000 db?
> >
> > Thanks.
> When you make the reports, link them to the SQL Server 2000 server.
> I've never had any problem pulling data from the older servers. You
> would add the connection string the same way you would with the 2005
> version. You can even make shared datasources. I just tested it, yep
> it works. Just make a shared datasource and use that for all your
> reports. It will link to the specified server/database as needed.
> Hope that helps.
>|||When i linked from SSRS2005 to SS2000 .
Should i go first to the Reporting Services Configuration? If yes then
when i go to Reporting Services Configuration it is asking me to select from
drop-down box instance name but my instance name is disabled. How can i
enable that?
If i do not need to do that then after conecting to server should i just
start creating project and designing the new reports from Visual Studio 2005?
"GGill" wrote:
> Thank you.
> "Ayman" wrote:
> > On Jun 20, 12:55 pm, GGill <G...@.discussions.microsoft.com> wrote:
> > > The databases I have in the SQL Server 2000 and i need to use
> > > reporting services in the SQL Server 2005. If i am in SQL Server 2005 and i
> > > want to just have connection to my databases in 2000 without restoring
> > > the db's in the 2005. Can i build the reports using SSRS 2005? If yes
> > > then what steps do i need to know to start building RS reports besides
> > > connecting to SR2000 db?
> > >
> > > Thanks.
> >
> > When you make the reports, link them to the SQL Server 2000 server.
> > I've never had any problem pulling data from the older servers. You
> > would add the connection string the same way you would with the 2005
> > version. You can even make shared datasources. I just tested it, yep
> > it works. Just make a shared datasource and use that for all your
> > reports. It will link to the specified server/database as needed.
> > Hope that helps.
> >
> >

Building out sums inside of a table (SSRS 2000)

Hi all,
I am converting some Crystal reports... and replacing formulas which
are automatically grouped in Crystal. Essentially, I am comparing two
different date ranges, and need to be able to know how many items were
shipped for each customer/item combination within each date range.
I have created calculated fields to hold the quantity shipped if that
row falls in the appropriate date range... this just adds the quantity
shipped in the field if it falls within the appropriate date range.
Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
and
Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
I need to build out a table that looks close to the following example:
Cust No
Item No Total for the Period
{need total quantity here}
PD1 {Qty2ShipPD1}
PD2 {Qty2ShipPD2}
I created a matrix control and dragged cus_no, item_no into the rows,
with item_no in the columns, and then summed the Qty2ShipPD1 in the
Details...
This works, in the sense that I am actually getting grouped
information which is correct for the customer/item combinations...
But the format won't work, and I have no idea how to do the same thing
in a table. Every time I do it in a table, I just get the same,
entire sum for the entire query for the two calculated fields.
Help?
THANKS!
JoshAlternatively - is there a way I can use a Matrix Control with only 1
column? If so, I might be able to live with that.
Thanks,
Josh
On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> Hi all,
> I am converting some Crystal reports... and replacing formulas which
> are automatically grouped in Crystal. Essentially, I am comparing two
> different date ranges, and need to be able to know how many items were
> shipped for each customer/item combination within each date range.
> I have created calculated fields to hold the quantity shipped if that
> row falls in the appropriate date range... this just adds the quantity
> shipped in the field if it falls within the appropriate date range.
> Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> and
> Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> I need to build out a table that looks close to the following example:
> Cust No
> Item No Total for the Period
> {need total quantity here}
> PD1 {Qty2ShipPD1}
> PD2 {Qty2ShipPD2}
> I created a matrix control and dragged cus_no, item_no into the rows,
> with item_no in the columns, and then summed the Qty2ShipPD1 in the
> Details...
> This works, in the sense that I am actually getting grouped
> information which is correct for the customer/item combinations...
> But the format won't work, and I have no idea how to do the same thing
> in a table. Every time I do it in a table, I just get the same,
> entire sum for the entire query for the two calculated fields.
> Help?
> THANKS!
> Josh|||Doh! Got it.
In my
Sum statement, I was referencing the entire dataset, and not just the
group. I just didn't know I could reference the group.
*This* works...
=Sum(Fields!qty_to_ship.Value, "table1_Group1")
*This does not (well, it gives me values for the entire dataset)
=Sum(Fields!qty_to_ship.Value, "devdbds")
FYI. Hopefully this will help someone else.
THANKS
On Apr 24, 9:44 am, rumplyminz <squa...@.gmail.com> wrote:
> Alternatively - is there a way I can use a Matrix Control with only 1
> column? If so, I might be able to live with that.
> Thanks,
> Josh
> On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> > Hi all,
> > I am converting some Crystal reports... and replacing formulas which
> > are automatically grouped in Crystal. Essentially, I am comparing two
> > different date ranges, and need to be able to know how many items were
> > shipped for each customer/item combination within each date range.
> > I have created calculated fields to hold the quantity shipped if that
> > row falls in the appropriate date range... this just adds the quantity
> > shipped in the field if it falls within the appropriate date range.
> > Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > and
> > Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > I need to build out a table that looks close to the following example:
> > Cust No
> > Item No Total for the Period
> > {need total quantity here}
> > PD1 {Qty2ShipPD1}
> > PD2 {Qty2ShipPD2}
> > I created a matrix control and dragged cus_no, item_no into the rows,
> > with item_no in the columns, and then summed the Qty2ShipPD1 in the
> > Details...
> > This works, in the sense that I am actually getting grouped
> > information which is correct for the customer/item combinations...
> > But the format won't work, and I have no idea how to do the same thing
> > in a table. Every time I do it in a table, I just get the same,
> > entire sum for the entire query for the two calculated fields.
> > Help?
> > THANKS!
> > Josh

Sunday, March 25, 2012

bulk insert vs CLR stored proc doing transactioned insert

Thursday, March 22, 2012

Bulk Insert Related Tables - PKs

Tuesday, February 14, 2012

Built database with SQL Server Management Studio Express, how can I quikly add test data?

Building/Running a Package.

Building/Issueing a query with a field with a quote in it?

Building with SQL server, but deploying with MSDE

Building View Dynamically .. Need Help!

Building text files

Building Table for Duplicate Records

Building SQL using variables

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Building SQL Query to fill a treeview control with Year->Month->Day nodes

Building Search

Building schema and tables from XML?

Building schema and tables from XML?

Building reports ?

Building out sums inside of a table (SSRS 2000)

build sql script

Blog Archive

About Me