Create Table but how?

Hi!

I've searched the tutorial for the create table syntax in asp.net. How to handle the SQL-string. How is it going? Do I have to use a SQL-Adapter or just the Connection? I can't access the Database with the Enterprise-Manager, so I have to do it in the good old way by using SQL. Can somebody give me the statements or a short codesample?

Thanks a lot

tobi.Net Data Access Technology (ADO.NET) is'nt different in windows applications and web applications.
You can do it either using a Data Adapter or just a connection and sqlCommands
If you can't access your database in Enterprise Manager , perhaps your database is'nt installed in SQL Server.|||Well, my SQL Server at home for tests is installed and running. But I still got some problems with connecting to it. Now I'm using a providers SQL-Database I can't connect with the Ent.-Manager to.

Can you give me a codesample for a create table execution? I havent' worked with .net so close...sql

Create subscription failed.

Hi
I am using merge replication to sync sqlserver 2000 sp3 database and sql
server ce 2.0 sp3 via PPC 2003. I received the following error.
"Sql CE Exception: Create subscription failed:
system.Data.Sqlserverce.sqlceException"
"Create subscription failed (27750 - 8004005)"
Please help.
80004005 is a generic access denied. Are you sure the account you are using
to pull the subscription is in the PAL of your merge publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pcherlop" <Pcherlop@.discussions.microsoft.com> wrote in message
news:930ED762-1874-4513-B675-0FFC27A418E0@.microsoft.com...
> Hi
> I am using merge replication to sync sqlserver 2000 sp3 database and sql
> server ce 2.0 sp3 via PPC 2003. I received the following error.
> "Sql CE Exception: Create subscription failed:
> system.Data.Sqlserverce.sqlceException"
> "Create subscription failed (27750 - 8004005)"
> Please help.

Create stored procedure

Hi!
I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.
Please help me!
Mike.I'm not sure that I understand what you mean by inparameters, but if you just read the script file into a string variable, then execute that string variable as a command, then you should be "good to go".

-PatP|||Why would you want to do backend application development from access..

I would imagine it would be severe hoop jumping...

Get the sql server client side tools...

unless we're really talking about MSDE...|||Originally posted by Brett Kaiser
Why would you want to do backend application development from access.. Why do some folks like leather undies? There is no accounting for taste.

I'd suggest using OSQL or better yet Visual Studio, but that's just me!

-PatP|||Originally posted by Pat Phelan
Why do some folks like leather undies?

I have no response|||We are currently (trying) to create an application with Access forms and SQL server database with stored procedures. If you can get out of it, please do. Certainly the part with the stored procedures parameters is a hell. I would also suggest MSDE with osql.

But, probably, you can't drop the Access, so if you can supply some more info and i'll look into it.sql

CREATE STATISTICS locking up tables?

Hi
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>

Create SQL Server developer version database in app_data folder

Hi

I am trying to create a sql server database in the app_data folder of visual studio 2005. It keeps telling me I need the express version. Can I not use the developer version

Thanks

By default installation, SQL Server 2005 Developer version stores its databases' files in places like this (in my computer):C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The express version can save a data file in app_data folder to work with visual studio 2005 or VWD. You need different connection strings to access your database.

Here is a sample section of connection strings in a web.config file to access databases (developer version):

<connectionStrings>

<addname="MSDN_forumConnectionString"connectionString="Data Source=localhost;Initial Catalog=MSDN_forum;Integrated Security=True"

providerName="System.Data.SqlClient" />

<addname="NorthwindConnectionString"connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

|||

Hi

Thankyou for your response. I have no problem connecting to a sql server deveoper version . The problem I have is when I right click app_data foler and add new item & choose databse I get the following error

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.

I dont want to use the Express version but the developer version. Hope this make it a bit more clear

Thanks

|||

Hi,

Just as Limno said, if you want to create your database in app_data folder of Visual Studio 2005, you have to install the SQL Server Express edition because other editions of SQL Server does not support attaching database files automatically at runtime.

Thanks.

|||

Many thanks for your answer. I have now installed sql server express

Create Route

Hi

I have a very simple and probably stupid question. I am new to SB. My question is when I create a service and queue do I have to create a route always?. whats the purpose of creating a route?. what happens if i dont create a route. As I understand creating a route creates a routing table in the database but i am perplexed as to what is the actual use of this routing table and in what way it helps.

Thanks

AK

In the BEGIN DIALOG statement you provide a string as the TO service name:

begin dialog conversation @.handle

from service [initiator]

to service 'target'

on contract [contract]

with encryption = off;

The [initiator] and [contract] are names of actual objects in the database (identifiers), but the 'target' is just any name, a string. What a route does it instructs Service Broker where the service named 'target' is actually located. You create a route like this:

create route [route_to_target]

with service_name = 'target',

address 'tcp://someserver:4022';

which maps the name 'target' to the address 'tcp://someserver:4022'. So a route is always required when you want to reach a service that is located on a different SQL Server instance. You'll see that normally you never have to create a route for cases when both [initiator] and 'target' services are in the same database or the same SQL Server instance. This is because all databases by default contain a route named AutoCreatedLocal that allows any service within the local SQL Server instance to be addressed.

A secondary role for routes is indirection between logical named and physical location. An alternative approach would had been to specify somehow the location in the BEGIN DIALOG, like this:

begin dialog conversation @.handle

from service [initiator]

to service 'tcp://someserver:4022/target'

on contract [contract]

with encryption = off;

but in this moment the application has hardcoded the location of the 'target' service in it's code. In practice once an application gets deployed the physical location of machines hosting services often changes, say the machine 'someserver' gets upgraded to a machine named 'superdome', so the location of the the service 'target' has in fact moved to 'tcp://superdome:4022'. In this case, the administrator can change the route in the database, w/o an application code change beeing necessary (the application still initiates the dialogs with the service 'target', not knowing the actual physical location). Same is true for the case when the database hosting the 'target' service is moved (sp_detach/sp_attach) to a different SQL Server instance.

A third role for routes is to provide support for scale-out load balancing scenarios, by allowing multiple routes for the same service.

A fourth role for routes is to provide support for services hosted in mirrored database (the MIRROR_ADDRESS parameter of CREATE/ALTER ROUTE).

create relationship between tables in different databases??

Hi!
I have two databases. They represent two different systems. Now the two
systems will be merged in many ways. This means that there will now be
need for relations between tables in the two databases. I have existing
database diagrams for both, but is there a way to add tables in a
diagram from another database on the same sql server' Or should I just
have all the tables in one database' Any suggestions would be
appretiated!
Thanks
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You cannot diagram over several databases, nor can you define relationships
(foreign keys) cross
database. If you want to enforce referential integrity cross database, you c
an do it using triggers.
Or, as you say, another option is to put all objects into one database. Whet
her that is the best
thing to do or not is difficult to asses over a newsgroup post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kongsballa" <kongsgballa@.devdex.com> wrote in message news:eET3QYFBFHA.1396@.tk2msftngp13.p
hx.gbl...
> Hi!
> I have two databases. They represent two different systems. Now the two
> systems will be merged in many ways. This means that there will now be
> need for relations between tables in the two databases. I have existing
> database diagrams for both, but is there a way to add tables in a
> diagram from another database on the same sql server' Or should I just
> have all the tables in one database' Any suggestions would be
> appretiated!
> Thanks
> Henning :-)
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi Tibor, and thanks for the quick response!
I guess there are a lot of pros and cons wether too have one or two
databases. The total size of the databases are no more than 500 MB, and
I would really like to see the day it reaches 1 GB. Having said that,
can you give me a hint or some place where this has been discussed'
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hej Henning,
These are small databases, so that is IMO not a factor. I'd like to say as a
rule of thumb that if
the data is related, then go for one database. One practical thing to consid
er is backup. Having
several databases mean that backup of all data will not be a snapshot in tim
e (you first do one
database, then next). I'd search the Google archives (see my web-site) as th
is topic has been
discussed here before.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kongsballa" <kongsgballa@.devdex.com> wrote in message news:uMeUPNGBFHA.3592@.TK2MSFTNGP09.p
hx.gbl...
> Hi Tibor, and thanks for the quick response!
> I guess there are a lot of pros and cons wether too have one or two
> databases. The total size of the databases are no more than 500 MB, and
> I would really like to see the day it reaches 1 GB. Having said that,
> can you give me a hint or some place where this has been discussed'
> Henning :-)
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Tjna Tibor!
Thanks again. I will look around for information on the subject. Have a
nice day in Sweden!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Create procedure to insert records for a project

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS|||

Quote:

Originally Posted by ck9663

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS

------
No this is not one time and i have to insert this set of 11 records for each of the 40 projects i.e. 40 times.. so i need to be able to create a procedure where i can increment the value of task_taskid for each record and insert the corresponding field values.|||

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Try below Logic to create a procedure:

1. Have a Cursor that will hold task_name, task_id, task_budjetdollar, task_budjethours
2. have a counter variable initilized to 1
3. LOOP through 1181..1221 becuase u said u need to add for 40 projectids from 1181,1182 and so on
4. With a FOR LOOP, loop through CURSOR data, and for each record (task_name), insert into table with task_name,task_id and projectid(FOR Loop value) and task_outline_num = counter variable that you have declared before in the procedure
5. COMMIT
6. Increment the counter variable by 1
7. End the Cursor LOOP
8. Reset the counter variable to 1
9. End Outer FOR LOOP
10. End Procedure

Create procedure

Hi

I try to create a very simple stored procedure,

CREATE PROCEDURE Importera_fil

@.fil varchar

AS

INSERT INTO XMLFaktura(Faktura)

SELECT * FROM OPENROWSET

(Bulk '@.fil', SINGLE_CLOB) AS DocumentID

but I get an errormessage when I try to create it,

Msg 4860, Level 16, State 1, Procedure Importera_fil, Line 4

Cannot bulk load. The file "@.fil" does not exist.

So my question is, how can i create the procedure?

Thanks for any help

The parameters of most DDL statements, OPENROWSET, OPENQUERY etc cannot be passed as variables. You have to specify the file name using literals directly. This is a limitation in the language feature. So in order to use @.fil you need to use dynamic SQL to form the SELECT statement and execute it.

create NorthWind Sample Database

hi
i have the instnwnd.sql with the structure of NorthWind Sample Database. How
can i import it?
The original database was deleted.
thx
Use OSQL and specify the file name using the /i parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database. How
> can i import it?
> The original database was deleted.
> thx
>
|||Use osql or open the file in Query analyzer (isqlw) and run the .sql file
using F5.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database.
How
> can i import it?
> The original database was deleted.
> thx
>
|||Alternately, there is a free (for personal use) tool at our site (MSDE
Manager) that you can use for this and other management options. Hope you
find it useful.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:%23LMH1nBOEHA.268@.TK2MSFTNGP11.phx.gbl...
> Use osql or open the file in Query analyzer (isqlw) and run the .sql file
> using F5.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
> news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> How
>

Create new Table from existing table

Hi

I'm trying to Create a new Table from existing table in Q/Analyzer. I figured it would be something like this:

CREATE TABLE newTable AS
(SELECT * FROM OldTable);

but i keep getting

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

also.. is there another method of doing this, something like

INSERT INTO newTable
(SELECT * FROM OldTable);

and it creates the table ( newTable ) for u if it doenst already exist??

Cheers!!!

im using sql2000

You can try this one:

SELECT

* INTO newTableFROM OldTable|||

The easiest method to copy a table with data is :

SELECT * INTO MyNewTable FROM MyTable

Note: this method does not copy constraints and indexes.

|||

SELECT * INTO will create the table and also transfer the data. If you just want the table structure you can do

SELECT * INTO newTable FROM OldTable WHERE 1=0

You would need to add any constraints/indexes manually.

|||

Thanks for ya help fellas

all helpfull answers

Cheers!!!

Create Link from SQL 2000 to AS400

Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
PhilHi,

Could you be more specific?

You need to create link from SQL 2000 to AS400 but which is the AS400 database?
You have installed the adequate driver?

My apologies for my english. I currently write in portuguese.

Regards,

Dlson Castro.
delson_castro@.ig.com.br

Originally posted by pholtquist
Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
Phil|||It sounds like the error is from the linked server defined incorrectly on SQL Server.

You say yhou are accessing the tables from your client machine. If the SQL Server is on a different machine, remember that you need to set up the ODBC DSN (or the 'DB2OLEDB' driver) on the server, not your client.

See sp_addlinkedserver in BOL for a description of what is needed.

Please provide more information if this does not help or is unclear.

HueyStLoui

Originally posted by pholtquist
Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
Phil

create index error

Hi
I am trying to create an index on a large database (~8GB). SQL Server comes
back with error: 3624, Level 20, State 1.
Can someone please decipher this error for me?
Many thanks in advance.
Akz
I didn't find 3624 in the sysmessages table. What is the actual error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Akz" <Akz@.discussions.microsoft.com> wrote in message
news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> Hi
> I am trying to create an index on a large database (~8GB). SQL Server comes
> back with error: 3624, Level 20, State 1.
> Can someone please decipher this error for me?
> Many thanks in advance.
> Akz
|||The 3624 is: "A system assertion check has failed. Check the SQL Server
error log for details."
You need to search your errorlog to find which assertion was fired. If you
could provide this assertion, then we probably will know better why index
creation failed.
Stephen Jiang [ MSFT]
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akz" <Akz@.discussions.microsoft.com> wrote in message
news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> Hi
> I am trying to create an index on a large database (~8GB). SQL Server
comes
> back with error: 3624, Level 20, State 1.
> Can someone please decipher this error for me?
> Many thanks in advance.
> Akz
|||The error log show the following message:
Faild Assertion ='m_SizeRec>0 && m_SizeRec<=MAXDATAROW'
I forgot to mention that I have two files in the primary filegroup because
of the size of my db. Could this have anything to do with my error?
Many thanks
"Stephen Yuan Jiang [MSFT]" wrote:

> The 3624 is: "A system assertion check has failed. Check the SQL Server
> error log for details."
> You need to search your errorlog to find which assertion was fired. If you
> could provide this assertion, then we probably will know better why index
> creation failed.
> Stephen Jiang [ MSFT]
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Akz" <Akz@.discussions.microsoft.com> wrote in message
> news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> comes
>
>

create index error

Hi
I am trying to create an index on a large database (~8GB). SQL Server comes
back with error: 3624, Level 20, State 1.
Can someone please decipher this error for me?
Many thanks in advance.
AkzI didn't find 3624 in the sysmessages table. What is the actual error messag
e?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Akz" <Akz@.discussions.microsoft.com> wrote in message
news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> Hi
> I am trying to create an index on a large database (~8GB). SQL Server com
es
> back with error: 3624, Level 20, State 1.
> Can someone please decipher this error for me?
> Many thanks in advance.
> Akz|||The 3624 is: "A system assertion check has failed. Check the SQL Server
error log for details."
You need to search your errorlog to find which assertion was fired. If you
could provide this assertion, then we probably will know better why index
creation failed.
Stephen Jiang [ MSFT]
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akz" <Akz@.discussions.microsoft.com> wrote in message
news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> Hi
> I am trying to create an index on a large database (~8GB). SQL Server
comes
> back with error: 3624, Level 20, State 1.
> Can someone please decipher this error for me?
> Many thanks in advance.
> Akz|||The error log show the following message:
Faild Assertion ='m_SizeRec>0 && m_SizeRec<=MAXDATAROW'
I forgot to mention that I have two files in the primary filegroup because
of the size of my db. Could this have anything to do with my error?
Many thanks
"Stephen Yuan Jiang [MSFT]" wrote:

> The 3624 is: "A system assertion check has failed. Check the SQL Server
> error log for details."
> You need to search your errorlog to find which assertion was fired. If yo
u
> could provide this assertion, then we probably will know better why index
> creation failed.
> Stephen Jiang [ MSFT]
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>
> "Akz" <Akz@.discussions.microsoft.com> wrote in message
> news:897FA05B-5963-4D71-9AEE-153F58D53C5D@.microsoft.com...
> comes
>
>

CREATE FULLTEXT CATALOG inside a user transaction.

hi:

I try to create full text for new created tables.

Since all new created tables will have same columns with different table name.

After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.

But I got error like this:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Any one has idea how to deal with it?

Thanks

This is by design. Full-text catalog cannot be created in nested/embedded transactions.

I dont' think the DDL trigger would work in this case. Perhap, create a store proc that will scan all the tables that do not have full-text index and add them on fly.

Gary

|||

DDL triger not working. I have stored procedure for creating full-text, the SP works when you exec it inside of the Management studio (not being called from other SP, job or triger etc), otherwise, it will return the same error.

anyway, I guess this is the dead end for creating full text on the fly.

Create Extended Procedures in VS 2005

Hi

I am trying to write an extended procedure that accepts a string parameter and returns an integer value. The extended procedure calls a regular stored procedure of a database passing the string parameter as an input. The int value is an OUT parameter to this procedure.

Can I some one suggest where do I get started with respect to this in VS 2005.Why do you want to write an extended stored procedure to call a TSQL SP? This is overkill actually. Extended SPs are meant for computation intensive operations or other logic that cannot be performed efficiently using TSQL. It has it's limitations, performance, reliability and security issues. Or you trying to just learn extended SP programming? If later you can look at the SQL Server samples. You can also look at ODBC/OLEDB samples that will show you how to call SPs.|||The thing is that we need to make DML changes while calling a function. Since normal UDFs dont allow to do it I am trying to call an xp. Since we also need to look at concurrency I am having a stored procedure with transactions taken care. Hence the need of calling T_SQL sp from xp.|||Where do I look for the Extended Stored Procedure DLL Wizard while I open VS 2005 --> Open Project.

I do not see any such wizrd name.

Regards
Imtiaz|||

Use of side-effecting code from UDF is not recommended. It takes lot of work to get it right (dealing with bound connections, concurrency issues, deadlocks, scalability of xps, virtual memory issues depending on how the xp is written etc). Lastly, use of such UDFs in SELECT statement can cause unexpected behavior.

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Database on Remote Server

Hi
I am using SQL Server 2000 and I would like to know how to create a database
on a remote server.
Right now, my application has to be on the same pc as the Server to create
the database using SQLDMO, I do use scripts to create the database but one
of our customer has a problem, they have a saperate dedicated SQL Server
2000 PC and on another PC they have my application. My application crashes
as it unable to create the database on the remote Server using ADO/SQLDMO.
For example:
I have a Server running SQL2K/MSDE and 1 workstation running my
application. From the workstation I would like to send command to Server to
create a database using ADO/SQLDMO.
Let me know if it's possible to create database on the remote server?
Thanks>> Let me know if it's possible to create database on the remote server?
If you are using 4-part naming, you can use sp_ExecuteSQL like:
EXEC server.master.dbo.sp_ExecuteSQL N'CREATE DATABASE db..'
Anith|||Can explain what is 4-part naming?
Right now, I have a create database script that does the creation.
How do I use the existing script with sp_Execute?
Thanks for the info.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23s3ARcKuFHA.3756@.tk2msftngp13.phx.gbl...
> If you are using 4-part naming, you can use sp_ExecuteSQL like:
> EXEC server.master.dbo.sp_ExecuteSQL N'CREATE DATABASE db..'
> --
> Anith
>|||>> Can explain what is 4-part naming?
4-part naming is a way to refer to objects (tables, views, procedures etc. )
in a remotely located server, say a linked server using
server.database.owner.object scheme. Using it you can express distributed
queries across servers using appropriate OLEDB providers. Details can be
found in SQL Server Books Online under the topic "Linked Servers".
If you already have a CREATE DATABASE script, you can wrap it as a string
and execute it. See the topic sp_ExecuteSQL in SQL Server Books Online for
details and examples.
Anith