create table question

Hi all,
I bring data from 15 different files into several SQL Server tables every
night. I first bring data from CSV file and store them into 15 different
intermediary tables. Then I execute stored procedure that imports data from
intermediary tables into the tables that our users use.
My question is about clearing the intermediary tables. Right now I am
just truncating the tables before importing new data from CSV files. So I
use Insert Into...Select query. But this can create a problem if any of the
columns in the CSV files are removed. So I am thinking if it is worthwhile
droping the tables and then import data using Select...Into query. This way
even if a column is removed, the data will still be imported from the CSV
files. My question is are there any drawbacks in droping and recreating 15
tables everynight instead of truncating them?
Thanks.Actually, if you're database recovery model is not FULL, a SELECT INTO is
going to run significantly faster than an INSERT SELECT, because it's
considered a BULK operation.
Can't think of any drawbacks.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Nikhil Patel" <nikhil0100@.aol.com> wrote in message
news:O9DvE8GNFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I bring data from 15 different files into several SQL Server tables
> every
> night. I first bring data from CSV file and store them into 15 different
> intermediary tables. Then I execute stored procedure that imports data
> from
> intermediary tables into the tables that our users use.
> My question is about clearing the intermediary tables. Right now I am
> just truncating the tables before importing new data from CSV files. So I
> use Insert Into...Select query. But this can create a problem if any of
> the
> columns in the CSV files are removed. So I am thinking if it is worthwhile
> droping the tables and then import data using Select...Into query. This
> way
> even if a column is removed, the data will still be imported from the CSV
> files. My question is are there any drawbacks in droping and recreating 15
> tables everynight instead of truncating them?
> Thanks.
>|||I can see some drawbacks to creating the tables each time. If columns
are missing or renamed then won't your SPs that access them fail
anyway? You should avoid using SELECT * in production code. Plus,
you'll have to give DDL admin rights to whatever process performs the
load.
One possible approach is to create views that only expose certain
columns in the base table(s) then use a Dynamic Properties task in DTS
to load the data to the appropriate view each time. This does assume
you can read some metadata that determines what columns should be
present. If that metadata isn't available then I would have thought it
desirable to fail the load whenever some data was missing. What if the
file contains zero columns - just empty rows?
David Portas
SQL Server MVP
--sql

Create Table In Store Procedure and inserting data with the Kalen user.

Hello.
First: Sorry for my inglish.
I'need create this "simple" SP
I Work with (SQL 2000 + SP4)
Use TempDB
GO
Create procedure DBO.ProcTest
as
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to public
When I execute this SP whit the user Kalen (She have "Create table"
permission on TempDB) I receive this message
--Invalid object name 'Tempdb..TabTest'
This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
code in the Query analyzer, not have this problem.
The question is: Exist a solution without SQL Dynamic, with persistent table
and creating only one Procedure (not a procedure for each user)?
Thank You
Fernando Flamenco
Ing Sistemas de Informacin
Buenos Aires - Argentina.First of all don't create tables in TempDB. TempDB is a system database.
"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:uD8UmEFgGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello.
> First: Sorry for my inglish.
> I'need create this "simple" SP
> I Work with (SQL 2000 + SP4)
> Use TempDB
> GO
> Create procedure DBO.ProcTest
> as
> drop table Tempdb..TabTest
> create table Tempdb..TabTest (a int)
> insert into Tempdb..TabTest values (1000)
> GO
> grant exec on DBO.ProcTest to public
> When I execute this SP whit the user Kalen (She have "Create table"
> permission on TempDB) I receive this message
> --Invalid object name 'Tempdb..TabTest'
> This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
> code in the Query analyzer, not have this problem.
> The question is: Exist a solution without SQL Dynamic, with persistent
> table and creating only one Procedure (not a procedure for each user)?
> Thank You
> Fernando Flamenco
> Ing Sistemas de Informacin
> Buenos Aires - Argentina.
>

Create Table In Store Procedure and inserting data with the Kalen user.

As Mike said, don't create objects in tempdb. However, your stored
procedure is failing because the first thing you are doing is dropping
a table that doesn't exist.
What are you trying to accomplish?
StuThank stu and mike,
I' know: the tempdb is a system database but i use this database like
workspace.
I explain this again, for this
1) Create a user Kalen with the create table permission on tempdb database
2) Connect with this user and execute
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TabTest]
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
This code is simple and the more important work
3) Connect with a user sa and create this SP
Create procedure DBO.ProcTest
as
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to Kalen
4) Connect with the Kalen user this not work
I receive this message
--Invalid object name 'Tempdb..TabTest'
The question is:
I need create this one SP and not use SQL Dynamic.
Easy'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1148603889.848594.234520@.j73g2000cwa.googlegroups.com...
> As Mike said, don't create objects in tempdb. However, your stored
> procedure is failing because the first thing you are doing is dropping
> a table that doesn't exist.
> What are you trying to accomplish?
> Stu
>|||"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:%23s7zNOMgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Thank stu and mike,
> I' know: the tempdb is a system database but i use this database like
> workspace.
<snip> SQL Server uses it as a workspace as well. You're asking for trouble
here. Have fun.</snip>

> I need create this one SP and not use SQL Dynamic.
1) I HIGHLY recommend against forcing your own DDL inside TempDB. At best
you're creating contention within SQL Server for TempDB resources. It's a
System Database for a reason.
2) I don't see the point of what you're doing, but if you're building the
SP in a separate Database from the one you're executing it in (assuming
that's the reason you feel the need to prefix the table with Tempdb..
everywhere), then you need to look at the effect of not using that prefix in
your IF EXISTS statement. I added the database name prefix to your select *
from dbo.sysobjects and object_id statements and dropped the OBJECTPROPERTY
function in favor of checking the XTYPE column. Works great.

> Easy'
Too Easy.sql

Create Store Procedure to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION

create store procedure to incremental populate full text index tab

I need to create a store procedure to perform incremental population of full
text index table. The store procedure will be call from my program.
Any ideas ?
xxx
This is probably what you want.
exec sp_fulltext_table N'[dbo].[authors]', N'start_incremental'
I suggest you have a look at change tracking and schedule the index updates.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:AEAD8DB8-91F6-484A-AD2C-C6AB06E5B38B@.microsoft.com...
>I need to create a store procedure to perform incremental population of
>full
> text index table. The store procedure will be call from my program.
> Any ideas ?
> --
> xxx

create store procedure that take data from 2 database

hello all..,

i want to make procedure can decreasetotalcost from order table(database:games.dbo) withbalance in bill table(database:bank.dbo). my 2 database in same server is name "boy"

i have 2 database like: bank.dbo and games.dbo

in games.dbo, have a table name is order(user_id,no_order,date,totalcost)

in bank.dbo, have a table name like is bill(no_bill,balance)

this is a list of bill table

no_bill balance

111222 200$

222444 10$

this is a list of order table

user_id no_order date totalcost

a 1 1/1/07 50$

when customer insert no_bill(111222) in page and click a button, then bill table became

no_bill balance

111222 150$

222444 10$

when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"

is procedure can take data from 2 database?

mystore procedure like:

ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.balanc AS money

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill

COMMIT TRANSACTION
END

it's output message "Invalid object name '<boy\sqlexpress>.Bank.dbo.bill'.
Transactioncount after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTIONstatement is missing. Previous count = 0, current count = 1.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[pay]."

plss.. help...

Hi,

Just remove <boy\sqlexpress>.section, it would run. If two databases are on same server, they don't need to reference instance name with them.

|||

thx for information...

i have remove it, but it can not work too...

SET @.balanc= (SELECT [balance] FROM [Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

the error is same... another ways?

plzz...,help..

|||

What database is the pay stored procedure in?

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

When setting the balanc variable you reference the table with [boy\sqlexpress.Bank.dbo.bill], but in the update statement you use UPDATE [bill]

|||

did you try [Bank].[dbo].[bill] instead of [Bank.dbo.bill] ?

|||

thx for dleonard and david information...

i have try it all... but cannot too...

[boy\sqlexpress.bank.dbo.bill]

[boy\sqlexpress].[bank].[dbo].[bill]

[bank.dbo.bill]

[bank].[dbo].[bill]

[bank.bill]

[bank].[bill]

[bank.dbo].[bill]

[boy\sqlexpress.bank.dbo].[bill]

[bill.bank.dbo]

i am create store procedure inboy\sqlexpress.games.dbo.

i haveboy\sqlexpress.bank.dbo andboy\sqlexpress.games.dbo

i have post this in this forums 3 thread, but it's cannot resolved...

pls... another idea?

or maybe store procedure can not get data from 2 database?

|||

hardy:

or maybe store procedure can not get data from 2 database?

I know it can be done with SqlServer. Don't use SqlExpress...

|||

Oh! I just re-read your example and noticed the BEGIN TRANSACTION / COMMIT TRANSACTION commands in it.

That's probably why it's barfing.

I would be surprised if SqlExpress supported transactions across databases. If I'm right, removing those two commands would enable you to proceed.

(Of course, it would also open you up to the dangers of the transaction failing half-way thru and leaving the first half committed in the other database.)

|||

|||

it can not work too...

the errors is focus that bill table can not be found...

the other command is already right... now big problem is [bank.dbo.bill].

maybe connection string?

|||

Let say you have two tables (Table1 in Database1 owned by User1) and (Table2 in Database2 owned by User2)

Make sure that each time you call any object .. call it with its full name: [ServerName].[DatabaseName].[OwnerName].[ObjectName]

Example:

SELECT *FROM Server1.Database1.User1.Table1 aINNERJOIN Server2.Datbase2.User2.Table2 bON a.col1 = b.col1

Good luck.

|||

thx for ur code...

can u make it to my code?

my code in procedure:

ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.balanc AS money

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill

COMMIT TRANSACTION
END

i only want to call bank.dbo. because i create a store procedure in games.dbo, so i dont need call a games.dbo..

now, the problem is bank.dbo can not be detected... any ideas?

|||

Note: I assumed thatpay stored procedure exists in the Bank database, try this:

ALTER PROCEDURE [bank].[dbo].[pay] @.no_billINT, @.no_orderint, @.totalcostmoneyASBEGIN BEGIN TRANSACTION DECLARE @.balancmoney SELECT@.balanc= [balance]FROM [bank].[dbo].[bill]WHERE [no_bill] = @.no_billUPDATE [bank].[dbo].[bill]SET [balance] = (@.balanc - @.totalcost)WHERE [no_bill] = @.no_billCOMMIT TRANSACTIONEND

Good luck.

|||

yess, many thx cs4ever. u are great...

my wrong yesterday is i make in select[bank].[dbo].[bill], i make in update

[bank.dbo.bill]. it cannot be work. i think [bank.dbo.bill] is similar with[bank].[dbo].[bill].i just know that it no similar...

so it must [bank].[dbo].[bill], can work...

and thx to david too, u ways is true too.but u not give example..., so i try ur ways in select not in update...

ok, thx to all.. this day is so great..... 

Create Store Procedure Fails "Incorrect syntax near the keyword 'ON'."

Hi All!

I'm really new to SQL environment in general so, sorry if this is a stupid question.

I'm trying to create a Stored Procedure on my BD with SQL Server Management Studio Express.

I receive this error:
Msg 156, Level 15, State 1, Procedure sprocBlogEntrySelectListByCategory, Line 18
Incorrect syntax near the keyword 'ON'.

This is the sp:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories ON BlogPosts.bp_ID = PostInCategories.bp_ID INNER JOIN

Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

GO

I really don't understand this error and what does this means in my case.

Any suggestion is appreciated.

alan

It seems to me that the error is in your FROM clause. FROM must be followed with a table name, derived table, or view.

In your case, you have a FROM clause followed by a JOIN condition without the JOIN clause.

|||I am guessing you mean this:

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories

INNER JOIN BlogPosts ON BlogPosts.bp_ID = PostInCategories.bp_ID

INNER JOIN Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

hth.

http://www.elsasoft.org|||

I tryed to re-write the sp in SQL Mgm Studio from scratch.
Identical to that one I posted earlier in my opening, and it was accepted without problem.

I think there was same TAB, SPACE or Comma character wrong.

I think my "issue" is resoved.

Thanks for yuor help anyway

Alan.

create store procedure

I need your help again.
I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.
i have two input parameters (@.fname, @.lname).
Thanks.Searching by Name(s) Bad Idea

how to do this? (don't) if you need to
You can write an insert statement using not exists statement
Insert into tbltest
(lname, fname)
select
'Jones', 'John'
where not exists
(select 'x' from
tbltest
where
lname = 'Jones'
and fname = 'John')

you can write and if statment setting a variable = count(*) of select statement using input values in the where clause and then evaluate the if

set @.var = 0
set @.var = (select count(*) from blah blah blah)

if @.var = 0 then
blah
else blah|||As i create a addemployee asp.net form. I don't want the people add more than once. That is why i need check whether the employee name is already exists. What do you think i need to check? Thanks. I am familar with the store procedure syntax. I believe the store procedure is good for this, right?
Many thanks.|||Can you help me to check the syntax. I got error that syntax error near then.
thanks.

CREATE PROCEDURE [AddNewEmployee]
(@.efname varchar(25),
@.elname varchar(25)
)

AS
decare @.var int

set @.var =0
set @.var =(SELECT count(*) FROM tblEmployee
where EmployeeFName =@.efname and EmployeeLName =@.elname)
if @.var =0 then

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@.efname,@.elname)|||Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.|||Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.

Sorry, can't figure out what u want..plz provide some more details..
Joydeep|||Sure. I am creating a web application using asp.net. I have a form called addemployeename.aspx. This form allows us to add new employee. First i created a strore procedure in the previous. Then i want to call this store procedure in addemployee.aspx. In the store procedure, i tried to avoid to add more than one same emaployee name. If you add one more user. the store procedure won't insert the new record. But I would like it can return something so that i can display a message that the employee name you just added is alreday in the database. How could i do this?
Thanks.|||CREATE PROCEDURE [AddNewEmployee]
(@.efname varchar(25),
@.elname varchar(25)
)

AS
if exists (SELECT 1 FROM tblEmployee
where EmployeeFName =@.efname and EmployeeLName =@.elname
)
return (1)

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@.efname,@.elname)
return (@.@.error)|||Thanks. i will try. The store procedure is amazing. I am going to try to run in asp.net. How could l received the error from web form? Please let me know if you use asp.net. Many thanks.sql

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
--
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround :)
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
--
Markus Pöhler
netpoint-edv gmbh
Germany
"chinn" wrote:
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
> > Hi.
> >
> > I want to create a perfmon Logfile and store the Log Data into SQL Database
> > as offered option instead of CSV or Binary File. I want to use those Data
> > with Reporting services lateron. But I can't realize it. I have looked for
> > any articles - without success.
> > I have proceed as follows:
> >
> > 1. Created a database on test Server to retrieve the log data
> > 2. Created an SQL USER to use this DB
> > 3. Created an ODBC Connection to this SQL Database
> > 4. Created the Log File and defined the ODBC Connection as target for data
> >
> > After starting I am getting an error message saying sth like: "The Protocol
> > [name] or warnings have not been started. Refresh the Logfile list to view
> > the error message. Some protocols or warnings may finish after a few minutes,
> > especially when using performance indicators."
> >
> > Markus

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
Markus
Markus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus
|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus
|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to vi
ew
the error message. Some protocols or warnings may finish after a few minutes
,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Databas
e
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protoco
l
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few minut
es,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - the
n
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
>

Create New Table advice

I am creating a table that will store my companys annual punctuality
scale. What is the best way to name the columns? Here is some sample
rows with data.

------------
DPT_CODE_1 | 300 | 121 | 120 | 61 | 60
DPT_CODE_2 | 360 | 241 | 240 | 121 | 120

Thanks for any advice.
Aplatfl"aplatfl" <aplatfl@.bellsouth.net> wrote in message
news:1141139056.502543.249810@.z34g2000cwc.googlegr oups.com...
> I am creating a table that will store my companys annual punctuality
> scale. What is the best way to name the columns? Here is some sample
> rows with data.

Umm, name the columns based on what they represent.

The following rows don't have any meaning to them.

I mean I could simply name them: Code, Number1, Number2, Number3, Number4,
Number5 but that's not very useful.

> ------------
> DPT_CODE_1 | 300 | 121 | 120 | 61 | 60
> DPT_CODE_2 | 360 | 241 | 240 | 121 | 120
> Thanks for any advice.
> Aplatfl|||1.Avoid reserved words
2.singular or plural , it probably doesn't matter

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"aplatfl" <aplatfl@.bellsouth.net> wrote in message
news:1141139056.502543.249810@.z34g2000cwc.googlegr oups.com...
> I am creating a table that will store my companys annual punctuality
> scale. What is the best way to name the columns? Here is some sample
> rows with data.
> ------------
> DPT_CODE_1 | 300 | 121 | 120 | 61 | 60
> DPT_CODE_2 | 360 | 241 | 240 | 121 | 120
> Thanks for any advice.
> Aplatfl

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create Multiple Store Procedures in 1 SQL statement

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

I can't get your Question..

Need more information

|||

I hope you need to write sp like this way.

/*
sp1
*/
CREATE PROC MultipleStoreProcedures;1
AS
BEGIN
SELECT [CategoryID], [CategoryName], [Description], [Picture]
FROM [Northwind].[dbo].[Categories]
END

GO

/*
sp2
*/
CREATE PROC MultipleStoreProcedures;2
AS
BEGIN
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
FROM [Northwind].[dbo].[Customers]
END
GO

**
--execute sp1
exec MultipleStoreProcedures;1
--execute sp2
exec MultipleStoreProcedures;2
**

|||

thx. it helps. :) However, if using 2005 for creating, then the 'GO' statement will be dissappear when u modify the store procedure. But it seems ok anyway. Thx alot.

Best Regards,

Hans

|||

The 'GO' isn't part of a T-SQL statement, it's just a marker that identifies where the end of batch is.
So, the above is, if you save it all as one file, and run it all at the same time, still two batches ( or statements if you will ), but it looks like it's working for you wanted anyway =:o)

/Kenneth

Create Global Variable like VB... can it be done?

I just want to store the value of a parameter in a global variable that all my reports in the same project can use.

My goal is to create a dynamic query. For example:

Company Name: Widgets inc.

Divisions: Sales, Service, Tech, Accounting

I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.

This way, on the next report, my query's where statement is the global variable.

@.GlobalVariable = parameter1.division.value

Select name, address, phone FROM Employee WHERE division = @.GlobalVariable

I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.

Please let me know if anyone can help.

Thanks.

John

There is not a concept of a global variable for multiple reports that I know of.

You could potentially do something with a table that could store the global value by user id & identifier, and retrieve the variable from the table. Or you could setup a web service to get/set the variable.

You may be able to add a reference to a DLL in every report, and share between them, though I woud think it would be destroyed after the report session times out.

http://www.codeproject.com/dll/data_seg_share.asp

cheers,

Andrew

|||

Looks like you need a report parameter. You don't need to show it to the user, but just default it.

Create Cursor with Dynamic SQL

I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

Code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

CURSOR cur_gl_bucket
IS
SELECT <column name >
FROM ledger_stat
WHERE <statement>

BEGIN

Select <column name> from table;

OPEN Cursor cur_gl_bucket with retrieved column name.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this.....

Regards
Patrick McCarthy
mailto: patrick_mccarthy@.canmail.comLooks like you are on Oracle?

create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

TYPE refcur IS REF CURSOR;
cur_gl_bucket refcur;

v_column_name VARCHAR2(30);

BEGIN

Select <column name> INTO v_column_name from table;

OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';