new to SQL - create table command question

Getting started with SQL Server

Index ‹ SQL Server

- Previous
  - 1
    - Analysis Services 每 Step by Step: Semi-Additive-Account Most likely the problem is the one that Handerson pinpointed: setting up account mapping. I just wanted to point out that we have detailed steps in chapter 13 of our book: http://www.e-tservice.com/olap.html I'll try to copy the basik idea here, but it's too much for the small post. Follow these steps to assign different aggregation functions to different accounts: 1. Assign the ByAccount aggregation function to the Amount measure. 2. Map each member of an account dimension to a specific account type. Analysis Services recognizes any dimension as an account dimension if the dimension type is Account . To do this mapping, you follow the steps bellow: A. Add a column to the dimension table in the relational database. Each row of the column corresponds to member of the Account dimension and has an account type associated with a member. In general, the account type can be any word or any string expression that makes sense to your organization; for example, it could be Income , Expense , Asset , or Liability . B. Create a dimension attribute of type AccountType , based on the new column. 3. In the Account collection property of the Database object, define the mapping between account types, names of account types used in the system, and aggregation functions as described bellow. Because different companies have different rules for aggregating different accounts, and different companies also have their own naming systems for different account types, Analysis Services enables you to define custom mapping between account types, names of account types used in the system, and aggregation functions. You can create this mapping either by issuing a DDL request that alters the database or by using BI Dev Studio. In BI Dev Studio to create the mapping between account type and aggregation function: Right-click FoodMart 2005 and choose Edit Database from the contextual menu. The database editor, shown in Figure 13.6, appears. You can use the editor to modify account type mapping. Analysis Services uses the value in the Alias column to map from the account type specified in the Account Type attribute to the measure＊s aggregation function. Hope this helps, Irina Tag: new to SQL - create table command question
  - 2
    - error output - ignore just one column? Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1509836&SiteID=1 Tag: new to SQL - create table command question
  - 3
    - SQL 2005 High Protection Failover error Hi I am experiencing a problem while performing failover testing on 2005 Standard. I have an application which uses an ODBC connection SQL Native Client with a failover partner. The following is the series of events that cause the problem. I am using a domain admin account whilst testing which has a server login on both servers with the same privileges. Server A (Principle) Server B (Mirror) **Application can connect** Manual failover Server A (Mirror) Server B (Principle) **Application can connect following a restart** Server A is switched off Server B (Principle, disconnected) **Application continues working** ***restarting the application the following error occurs*** ※Connection Failed SQLState 08001 SQL Server Error 53 [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connectionto SQL Server [53]. Connection failed: SQLState: '08001' SQL Server Error: 53 [Microsoft][SQL Native Client]An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Connection failed: SQLState: 'S1T00' SQL Server Error: 0 [Microsoft][SQL Native Client]Login timeout expired※ Any help with this would be most appreciated Peter Tag: new to SQL - create table command question
  - 4
    - Need to move 3.2 TB data from CA to IL I am involved in a project to move 3.2 TB of data in 30 - 40 databases from a data center in San Diego to another data center in Chicago. The equipment and data centers are owned by two different MSP's. The data currently resides on two 2-node (a-a) MSSQL 2005 clusters. It will be moved to a managed storage environment, also employing MSSQL 2005. The client requires minimal down time. I'm looking for suggestions as to the fastest/safest way to accomplish this migration. Tag: new to SQL - create table command question
  - 5
    - Writeback on consolidated member. Hi Hannes, I am actually writing a stored procedure to do this. I know that it's not a pretty solution. Thanks for your replies. Best Regards, Uzzy Tag: new to SQL - create table command question
  - 6
    - Creating expressions with the query builder Hello, I have a question about using the query builder, or just straight sql, to create expressions. My first example caused the query builder to generate a query configuration error: Expression: EIM_REQUISITION_DETAILS.EST_UNIT_COST * EIM_REQUISITION_DETAILS.REQRD_QTY AS COST Syntax error: Expecting "," or "FROM" after column alias or "*" Initially I thought I was dead in the water but then I decided to just run it anyway and it worked! I got my cost. OK so fine the query tool doesn't recognize some sql expressions. Because I am trying to make a large flat file I thought I'd concatenate 2 fields to generate a recognizable requisition, which everyone knows is the year followed by a hyphen then the req number: My old Access query had this: Req: Right([REQ_YR],2) & "-" & [REQ_NO] and I tried to change it to this: substring (EIM_REQUISITIONS.REQ_YR from 2 for 2) || '-' || EIM_REQUISITIONS.REQ_NO AS REQ So apparently my older sql book doesn't have the syntax right. Since I plan to do more of this could someone point me to a reliable book or site that could help me with sql syntax Thanks, Dana Tag: new to SQL - create table command question
  - 7
    - I have problem in Date format in C# .NET 1.1 !!! HI Friends, My .NET application devloped in C# used mainly class file (OOAD) and user controls etc and it is desktop application not web based application. Database is SQL server 2000. Now I have problem in Date format. Scenario is : If my application is running in the following setup, i.e The SQL server2000 is installed with DDMMY format and PC setting also DDMMYY. Now when the user enters the date like e.g 19-04-2007 (user can enter only numeric no char allowed here i.e only Any 2 digit numbers for month and date and 4 digit number for year in the respective place) in the user interface, it is generating error (Erro msg is : The conversion of char dat type to smalldatetime data type resulted in an out of range small datetime value.) BUT If the PC set up is in MMDDYY format and SQL server too then no problem. i.e Application will allow/Fetch the record when the user enter date like 19-04-2007. Brief about Architecture: My Application has four tier architecture. i.e Presentation, Business Facade (Remoting used here to call Business service), Business service (BS), Datatransfer. This .NET application is generalised application,Means it is not for any specific country. So I need best way to fix this problem. Please help me out to solve this problem. Thank you so much.... Thanks Radhakrishnan.R Tag: new to SQL - create table command question
  - 8
    - Report with constant number of detail lines I am developing a report analog of a machine readable form that has to display a static number of detail rows regardless of the number returned from the database - i.e. if a record set has only three detail records, I need to display three blank rows, while if the record set has ten detail records, I need to display six detail records, print the footer, start a new page, repeat the header information, print the remaining 4 detail records and 2 blank rows, print the footer again and move on to the remaining recordset. I am new to report development and I'm having to pick it up on the fly. I can't seem to locate any documentation about how to handle this scenario. I don't have the time or inclination to re-invent the wheel here, is there anyone who has solved this problem who can point me in the direction of some help Tag: new to SQL - create table command question
  - 9
    - Moving SSRS directory Thanks Dianne. Anyone know if I move the directory and change its setting in IIS - if it has any impact on SSRS Tag: new to SQL - create table command question
  - 10
    - SQL Server Agent Are there any down sides to running the SQL Server Agent What is this agent used for Tag: new to SQL - create table command question
  - 11
    - Minimun SP Level for Cluster Upgrade I'm upgrading MSSQL2000 (RTM) cluster to 2005. I haven't seen any information regarding a minimum service pack level for 2000. This upgrade just makes me overly caution. Tag: new to SQL - create table command question
  - 12
    - SQL Server 2000 Job Problems I'm trying to run a T-SQL job, but it won't let me use more than 3200 characters in the script. Do I have to create a DTS package to run this script since it is much longer than 3200 chars Or is there an easier way to do this -Kyle Tag: new to SQL - create table command question
  - 13
    - Protect from SQL Injection? Hi, got some query on SQL Injection. I tried to inject my own database, my user password is encrypted, but when I write the injection script in the login form, it has been hashed and it doesn't run the injection at all, but the script does the injecting when I code it direct to my page. So is that mean hashing password can prevent SQL injection (those inject from the login form) Tag: new to SQL - create table command question
  - 14
    - Deployment Of SSIS With Config Files Phil Brammer wrote: the best way to make the path portable is to use environment variables to store the base path. Then you can use the environment variable in the package configurations dialog to specify where to store the file. I dont think Sys Admin will allow the usage of environment variables, looks like absolute path is the only way for me. Looks like Microsoft should do something about the config files issues. Thanks Tag: new to SQL - create table command question
  - 15
    - Possible to stop SSRS GUI from destroying hand rolled mdx in datasets by silently reverting to design mode? Since as soon as you extend your mdx datasets manually you can no longer switch back into design mode without losing your changes, right If that's the case, is there some way to disable design mode completely i'm finding that the GUI has the tendency to SILENTLY revert the dataset editor back to design mode while I'm busy editing a layout, thereby losing my carefully crafted MDX. Tag: new to SQL - create table command question
  - 16
    - why any function related to prior period in my data does not work? Hi, Darren, Thanks. But the fact is the time dimension have already been set as 'Time' type in my case. But still the problem remains Why is that Any other ideas Thanks and I am looking forward to hearing from all of you here. With best regards, Yours sincerely, Tag: new to SQL - create table command question
- Next
  - 1
    - SP_Change_Users_Login Raul, I do have the same user (application user) in several databases. But when I check the SID in each database they do not match (they all have different IDs). How to proceed thanks a lot. Tag: new to SQL - create table command question
  - 2
    - Trying to figure out number of pages in report Greetings, I have a report that is created that is typically 2-3 pages long. I've tried the "ExecutionInfo.NumPages" but it always results in "1" as the answer. Code is something like this: ============ ... Dim reportHistoryParameters As sqlprod1_res.ParameterValue() = Nothing Dim SessionId As String Dim execInfo As New ExecutionInfo Dim execHeader As New ExecutionHeader() Dim result As Byte () = Nothing Dim format As String = "PDF" Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>" Dim extension As String = "" Dim encoding As String = "" Dim mimeType As String = "" Dim warnings As sqlprod1_res.Warning() = Nothing Dim streamIDs As String () = Nothing result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs) execInfo = rs.GetExecutionInfo() Dim Pages As Integer = execInfo.NumPages ============ Also tried to count streams with the same result. Any ideas Thanks Dan Ribar Tag: new to SQL - create table command question
  - 3
    - Login scripting to a file I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis. Anyone have any examples to share Tag: new to SQL - create table command question
  - 4
    - truncation error message Yes I have this; SUBSTRING(Name,1,50) Tag: new to SQL - create table command question
  - 5
    - SQL Parameter and wildcards, how to make it work? Hello, I have what should be a very simple problem, but I cant solve it. I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!) Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example: DECLARE @Dv_id nchar ( 15 ) SET @Drv_id = '%' SELECT Diver .*, ( ROW_NUMBER () OVER ( ORDER BY Dv_id )) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id SELECT Diver .*, ( ROW_NUMBER () OVER ( ORDER BY Dv_id )) as RowNum FROM Diver WHERE Dv_id LIKE '%' OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows. The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between: WHERE Drv_id LIKE @Drv_id and WHERE Drv_id LIKE '%' The wildcard statement '%' is supposed match everything, correct It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL. Is there any way around this Tag: new to SQL - create table command question
  - 6
    - SSIS Logs Dear Friend, I dont know what are you talking about... my problem is not running a package, is saving customized logs of some dataflows... like count inserted rows, dataflow duration... etc... So your answer didn's respond to my question, but thanks. Regards Tag: new to SQL - create table command question
  - 7
    - Received message constantly processed. @@ROWCOUNT is set by RECEIVE to the number of rows (messages) actually returned, so it doesn't matter if RETENTION is ON or OFF. From your description of the problem is clear that your procedured does not correctly detect when RECEIVE returned no rows, so it continues to spin on empty RECEIVEs that timeout. Can you post the actual code of the procedure Tag: new to SQL - create table command question
  - 8
    - How to Send a SQL Server notification to Messenger Hello, I'm trying to send a SQL Server notification to MSNMessenger and also to Yahoo Messenger, but I didn't find the way. If you have some information about it, please answer this question as soon as possible. Thank you so much Tag: new to SQL - create table command question
  - 9
    - Error with SMO.TransferData() (Exception from HRESULT: 0xC0011008) This is a little strange and i can not repo so I sent mail to the PM/Arch for SMO to see what he has to say. Tag: new to SQL - create table command question
  - 10
    - Report Builder Is Report Builder capable of building any complex reports that Visual Studio Report Designer can build What are the limitations of using Report Builder with SQL Server Databases Tag: new to SQL - create table command question
  - 11
    - 64-Bit SQL Server 2005 (Enterprise), SQL Agent Notifications and Database Mail We have database mail configured on our 64-Bit SQL Server Enterprise 2005 (SP1) and are able to send test messages from the context menu that appears when selecting database mail component. However for some reason when a SQL Agent Job fails, no emails are sent to the 'operator' that has been defined for the job. I have confirmed that we have the operators correctly configured. We also have a small SQL Agent Job that just sends a test email via the sp_send_dbmail command and that works properly. However if I deliberately create a malformed parameter in the sp_send_dbmail command to 'break' it...no notification of a job failure message is sent. I have verified that the service_broker component is enabled...and the status of database mail in the MSDB database is 'started.' We have setup several other 32-bit SQL Server 2005 systems using database mail...and they all work flawlessly. After reviewing existing message posts...I am unsure if there is a problem with database mail on a 64-bit SQL Server box. Some say 'yes' other say 'no'. There are several MSDN notes about the problem...however these appeared prior to the release of SP1 of SQL Server 2005. Thank you for your assistance. ...cordell... Tag: new to SQL - create table command question
  - 12
    - importing from excel into SQL Server 2005 changing values to NULL Philip Jaques wrote: Phil, Thanks for the advice and link. I was wondering, how would I implement this using dtswizard.exe That is typically how I do quick imports/exports. Do I need to construct an SSIS package so that I can specifically set the connection string or is there a way to implement this using the wizard Thanks again for the advice! Hmm... Not sure as I always build my packages from scratch. Someone else with experience with dtswizard will have to comment. Because it's a connection string property, I doubt it's possible, but am not sure. Tag: new to SQL - create table command question
  - 13
    - Could not load package because of error 0xC001000A Phil Brammer wrote: Fahad349 wrote: Package is there. I have created an SQL Agent schedule and it runs fine. No, I am not on same server. Then I believe you need to use the /Server switch on your dtexec command line. http://msdn2.microsoft.com/en-us/library/ms162810.aspx Thanks Phil. I appreciate it. Tag: new to SQL - create table command question
  - 14
    - Disabling extended stored procedures for security I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks. However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.) No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password. Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt. Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'. But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary. Tag: new to SQL - create table command question
  - 15
    - The SSIS package does not run using SQL Server Agent job Try using a database user and password to connect to Oracle, rather than Windows Authentication. Tag: new to SQL - create table command question
  - 16
    - smalldatettime Well, '05/21/2007' is not the same format of '21/05/2007'. If you are not going to consistently use the same format it is going to barf. Tag: new to SQL - create table command question

simongw

hi

can someone please explain the following please

why does this fail with specified owner testdb does not exist or you do not have permissions

create testdb.testtable (

testval int

)

yet if i create testdb and then run the following against this new db

create testtable (

testval int

)

command command completes successfully......what am i not understanding or doing wrong

thank you

Re: new to SQL - create table command question

simongw

oooops....sorry

im so new i cant even type the command correctly

this command fails....

create table testdb.testtable (

testval int

)

this command works....

create table testtable (

testval int

)

Re: new to SQL - create table command question

Jens K. Suessmeyer

Depending on the version of SQL Server you are using, the prefix testdb indicates either the owner of the table or the schema name that the object / table will be stored in. it does not identify the database where the object has to be created in. For the database identifier you will have to you the three partname which would be (assuming that you want to store the object in the users default schema (for SQL 2k5) or within his owner "schema":

CREATE TABLE testdb..SomeTable
(SomeColumn int)

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---