creating a table

I have used this syntax to create a table
create emp(emp_id int,staff_Name varchar(10),det_Name varchar(20));
This creates a table but I cannot insert any data in the table.How can i insert data in the table.insert into emp values(100,'sridhar','programmer')

Creating a large dynamic View

I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

EDIT: Changed the title to read Procedurally generating a large view.you have a stored procedure that creates a view with dynamic sql? seems like a bad idea. stored procedures are for DML, not DDL.

why not just store the view definition as a script in source control and execute it as necessary? when your supplier adds columns to their tables, you just add those columns to your script and execute it again.|||I don't really know what you mean by storing it as a script in a source control. By a source control, do you mean a third party utility? I am not familiar with the term. I don't really have any third party utilities or compilers to work with, just SQL Server 2000. The stored procedure that I have has been working fine. I just wanted to find out if there is a more efficient way to do the same thing. Basically, I need the view to look at different tables every day. The tables are indicated at the end of the table name by day and month of the data they contain. The basic outline of the stored procedure I have is below.

Create Procedure ProcName
as
Declare @.DatabaseName as varchar (128)
Declare @.sql as varchar(8000)
Declare @.view_Name as varchar (128)
Declare @.table_Name as varchar (128)
Declare @.ProcDate as datetime
Declare @.cntr as Int
Declare @.sql2 as varchar(8000)
Declare @.sql3 as varchar(8000)


Select @.DatabaseName = DB_NAME()
exec usp_DayToProcess Null, @.ProcDate output

Set @.cntr = 0
Set @.view_Name = 'ViewName'
Set @.Path2 = ''
Set @.Path3 = ''
Set @.Path = 'CREATE VIEW ViewName AS SELECT * FROM ('

While (@.cntr < 15)
Begin
Set @.table_Name = '[TableName_' + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 1, 2) + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 4, 2) + ']'
if exists (select * from dbo.sysobjects where id = object_id(@.table_Name) and OBJECTPROPERTY(id, N'IsUserTable') = 1 and crdate > DateAdd(year, -1, @.ProcDate))
Begin
If @.cntr <> 0
Begin
Set @.Path = @.Path + 'UNION ALL '
End
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
End
Set @.cntr = @.cntr + 1
If Len(@.Path) > 7000
Begin
If Len(@.Path2) > 7000
Begin
Set @.Path3 = @.Path2
End
Set @.Path2 = @.Path
Set @.Path = ''
End
End

Set @.Path = @.Path + ') TempView ORDER BY ...'

EXECUTE (@.Path3 + @.Path2 + @.Path)
GO

Since I have several fields that have to be reformated from the tables as well as functions to perform on some of the fields in order to get the values I need, the sql gets fairly large. So, it ends up taking more than two varchar variables to store all of the sql to search 15 tables. I am trying to standardize the procedure a bit, so in case more than 15 days of tables are required, it would require more variables. I was wondering if there is a more efficient way of doing this with SQL Server 2000 alone.|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control|||What is the point of this:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
Are you manually coding the column names?|||Personally, I like the SELECT * part|||The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control

So, you are basically saying to modify the code each day/week/whenever it needs to be run?

What is the point of this:

Code:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_NameAre you manually coding the column names?

The '...' is where I am specifying the fields to use. I didn't include all of it, because it is a bit long. For instance, I am adding a date field into the view so that the date of the transaction is a field. The tables do not have a transaction date field, since they are a different table for each day. Also, I specify the field names, because there are times that the company who creates the code that makes the tables will change that code during an update. I could check the table for any changes each time they put out updates, but this aggregates several tables. So, some of the tables would be missing fields that others have within the tables that are being aggregated. This would cause an error if the fields to use were not specified.

Personally, I like the SELECT * part

After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

Originally Posted by Ishe
The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels

I know what you mean, but I didn't really design the tables or the code to make the tables. It's just the only thing I have to work with really.|||So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() (http://msdn2.microsoft.com/en-us/library/3y322t50.aspx) in C#. hard to debug, hard to know what the actual state of the system was at any given time.|||After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.|||SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.

I know, and it has an ORDER BY clause at the end, but I have found from experience with prior UNION views that if you slap an ORDER BY clause at the end of the last union, it doesn't order the entire result set, just the last SELECT. So, I wrapped the entire UNION query making the UNION query a subquery and put the ORDER BY clause at the end of the wrapping query.

Originally Posted by Ishe
So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.

Does that include creating tables through stored procedure?

In this case I am trying to create this in such a way that it won't take someone that knows anything much about SQL Server to be able to use the procedure. Since the tables that the user would need to use change on a daily basis, I don't know of any other way to accomplish this. I can't rewrite the code for them every day. I am actually trying to change the code to be less customized, not more so.|||The '...' is where I am specifying the fields to use.If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.|||If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.

The only reason that it is done in a stored procedure is because the tables that I have to work with are daily transaction tables. The table names indicate the day and month of the transactions that are contained within. I don't think I can create a standard view with daily changing table names.|||But you don't have standard columns!|||But you don't have standard columns!

I'm not sure what you mean. The below contains the code that I replaced '...' with in the example.

Set @.Path = @.Path + 'SELECT FB_MBRNO AS [Member No], Convert(datetime, ' + CHAR(39) + Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 102) + CHAR(39) + ') AS [Tran Date], FB_TLR AS Tlr, FR_TRAN_CODE AS [Tran Code], FB_STATUS AS [Status], FB_CNV_CASH AS CnvCash, FR_TIM AS [Time], FB_CASH AS [Cash], FB_CHECK AS [Checks], FB_APPLIED AS Applied, FB_CASHBACK AS [Cash Back], FB_REVERSED AS Reversed, FB_MISC_CODE As [Misc Code], FB_TRAN_NO As [Tran No] FROM ' + @.table_Name

I think the columns are fairly standard, except the [Tran Date] field, which is there because the tables I am looking at do not have a date field that indicates when the transaction occured.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.

I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.|||I guess you have no choice then. I wouldn't use that software provider again if I were you. ;)|||I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.

Have I mentioned lately I hate 3rd party vendors?

There is no silver bullet|||Have I mentioned lately I hate 3rd party vendors?Not often enough.

Creating a job from a trigger

Hello, All
I have a trigger on a table, which when called, creates a job to run in n
minutes. The trigger runs fine and the job is created, however with a small
issue.
The username that is used as the Job Owner, ends up to be the name of the
user that triggered the trigger eventhough the code that creates the job
specifies a different name.
What am I doing wrong? How can I resolve this?
Here is the SP that is run in the trigger:
exec msdb..sp_add_job @.job_name = 'TEST',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 0,
@.delete_level = 1
The code above says user SA but the job ends up running as TESTUSER and it
makes the job fail because a that user is not a SA nor able to run a CMD
job.
Please help!
Thank you.slamm wrote:
> Hello, All
> I have a trigger on a table, which when called, creates a job to run
> in n minutes. The trigger runs fine and the job is created, however
> with a small issue.
> The username that is used as the Job Owner, ends up to be the name of
> the user that triggered the trigger eventhough the code that creates
> the job specifies a different name.
> What am I doing wrong? How can I resolve this?
> Here is the SP that is run in the trigger:
> exec msdb..sp_add_job @.job_name = 'TEST',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 0,
> @.delete_level = 1
> The code above says user SA but the job ends up running as TESTUSER
> and it makes the job fail because a that user is not a SA nor able to
> run a CMD job.
> Please help!
> Thank you.
I don't think that's going to work. If it did, then any user who had job
creation rights could alias a job as the system administrator, giving
them more rights in the process. You can grant the user rights to do
what needs to run or you can do the following:
Instead of creating a job directly from the trigger, insert the
necessary job criteria into a custom table of application jobs (to be
created). Create a recurring job on the server that monitors this table
at specified intervals and creates the jobs itself. Then you don't even
have to grant users job creation rights. They only need rights to your
application job table.
David Gugick
Imceda Software
www.imceda.com

Creating a Dynamic Temporary Table

When I execute the following Stored Procedure with a parameter (EXEC MyProc
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:

> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>

Creating a database from script - permissions problems

We deploy our app via ClickOnce. Rather than shipping the .mdf and .ldf, the app detects whether the database is there and creates it via script if it's not. The script was originally generated from SQL Exrpess Mangement Console, etc.

In the past, we've made the stipulation that the user must have administrator access to the machine. However, we now need to find a workable solution for users who are not administrators on their machine. Currently, we use the following connection string before attempting to run the db creation script:

conn.ConnectionString = "Server=.\\sqlexpress;Integrated Security = true;User Instance=false";

The script fails when trying to execute "CREATE DATABASE [myDatabaseName] ON PRIMARY "

with the error:

"CREATE DATABASE permission denied in database 'Master'"

How can we get around this permissions issue in the most automated way possible?

TIA!

Hi,

you will need to have a user with dbcreator permissions, otherwise you won′t be able to make an entry in the sysdatabases (Creating a database). Can you use any administrative SQL Server login to connect to the database and make this possible ? The connection string has to include the userid as well as the password then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating A Dataabas Via SQL Script

I have an SQL script file and what it does is it creates a database with tables and stored procedures, the problem that I have is that I dont know how to execute it, I opened it in VS.NET 2003 but there is no option to execute it anyone know how I can accomplish this. thank you all for your time and patience.If you have the SQL Client installed, you can use Query Analyzer. If not, in theory you could use OSQL.exe (for instance, if you have MSDE and do not have the SQL Client installed). Easier would be to install the SQL client, if you can.|||Where can I get a copy of the SQL client? i do have the OSQL.exe but it's command line and I cant seem to find anything in the help file as to how to run database creating procedures. but I do really appreciated the direction that you set me in. thanks.|||The cheapest way (if you are not an MSDN subscriber) is to get the developers edition, about US$40.00 last time I looked.

The osql utility is typically used in these ways:

a.. Users interactively enter Transact-SQL statements in a manner similar
to working on the command prompt. The results are displayed in the command
prompt window.

b.. Users submit an osql job either specifying a single Transact-SQL
statement to execute or pointing the utility to a text file that contains
Transact-SQL statements to execute. The output is usually directed to a text
file, but it also can be displayed in the command prompt window.
The osql utility uses the ODBC database application programming interface
(API). It is a replacement for the isql command prompt utility based on the
DB-Library API. Both utilities are provided with Microsoft SQL ServerT 2000.
The DB-Library API remains at a SQL Server 6.5 level; therefore,
applications that depend on DB-Library, such as isql, do not support some
SQL Server 2000 features. For example, isql cannot access columns defined
with the ntext data type and truncates any char, varchar, nchar, or nvarchar
columns longer than 255 bytes. It also cannot retrieve results as XML
documents. Except for these limitations in isql, both osql and isql support
the same features

Typing OSQL -? gets you this:

E:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql /?
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Creating a CSV file but cant read it.

Hi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.

Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
Dim Cm As SqlCommand
Dim dr As SqlDataReader
Dim FileName As String = Guid.NewGuid.ToString & ".csv"
Dim FilePath As String = Server.MapPath("") & "\CSVFiles\" & FileName

Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
Dim Line As String
Dim lItem As System.Xml.XmlElement
Dim i As Int16
If ValidatePage() Then
Cm = New SqlCommand(BuildQuery, Conn)
Conn.Open()
dr = Cm.ExecuteReader()

Dim lXmlDoc As New System.Xml.XmlDocument()
lXmlDoc.LoadXml(txtHXml.InnerText)

For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes
Select Case lItem.InnerText
Case "chkPRId"
Line &= "PR Id,"
Case "chkDateIssued"
Line &= "Date Issued,"
Case "chkOriginator"
Line &= "Originator,"
Case "chkBuyer"
Line &= "Buyer,"
Case "chkVendor"
Line &= "Vendor,"
Case "chkCostCode"
Line &= "Cost Code,"
Case "chkOracleRef"
Line &= "Oracle Reference,"
Case "chkTotal"
Line &= "Total,"
End Select
Next
Line = Line.Substring(0, Line.Length - 1)
sw.WriteLine(Line)

Line = ""
While dr.Read
For i = 0 To dr.FieldCount - 1
Line = Line & dr(i) & ","
Next
sw.WriteLine(Line)
Line = ""
End While

dr.Close()
Conn.Close()
sw.Close()
fs.Close()
Response.Redirect(FilePath)
End If
ResetPage()
PopulateListBoxFromXML()Have you checked that the file path resolves as expected. At least in C# backslashes in a file path get escaped to some other charcter. Try either:

Dim FilePath As String = Server.MapPath("") & "\\CSVFiles\\" & FileName

or

Dim FilePath As String = Server.MapPath("") & @."\CSVFiles\" & FileName

Creating a CE db at the desktop PC

Hello,

Is it possible to create a .SDF file at the develop machine?I want to write an exe which creates a sdf.So, my customer will be able to create a sdf and import this sdf to the device.

Thanks.

Hi xyzt,

Yes, you can create the .sdf either by writing an application (by calling the engine's CreateDatabase function, and scripting the schema), or with SQL Server Management Studio. You could then deliver that .sdf file to the device in your installation, or some other way.

Either way you do it, people seem to have the best results with scripting the database schema - I've heard the designers in SQL Server Management Studio need a little work.

Good luck!

CreateSubscription service creates all the subscriptions as ASPNET user

I have a custom form which users can use to create subscriptions for specific
reprots. I am able to create subscriptions just fine but my issue is that all
the subscriptions are created as ASPNET user. Am I missing something in the
setup?
Thanks for your help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1Hi,
Do you use Windows NT Authentication or anonymous access is enabled?
"akhan via SQLMonster.com" wrote:
> I have a custom form which users can use to create subscriptions for specific
> reprots. I am able to create subscriptions just fine but my issue is that all
> the subscriptions are created as ASPNET user. Am I missing something in the
> setup?
> Thanks for your help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||I am using Windows Authentication.
eralper wrote:
>Hi,
>Do you use Windows NT Authentication or anonymous access is enabled?
>> I have a custom form which users can use to create subscriptions for specific
>> reprots. I am able to create subscriptions just fine but my issue is that all
>> the subscriptions are created as ASPNET user. Am I missing something in the
>> setup?
>> Thanks for your help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

create word document from rdl-file

Hi everybody,

I'm looking for a tool or template that creates a good looking document from the rdl-files.

I need it for internal documentation.

Can you help me?

Gerd

Wrong forum. Try the Reporting Services forum.

-Jamie

create views in the order of their dependency

In SQL 2K...

i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?

For example if View_BOB says...

CREATE VIEW VIEW_BOB
AS
SELECT COL_1 FROM VIEW_JOHN

I need the script to generate a script that creates View_JOHN before View_BOB.You can use Enterprise Manager to generate the script for you and it will take care of dependency of the objects.

SQL Server Helper
http://www.sql-server-helper.com